1 Reply Latest reply on Feb 28, 2018 3:27 PM by Tarik Abdel-Monem

    Pass Date to Python in Calculated field - ProgrammingError 1064

    Tarik Abdel-Monem

      If I hard code the date, then it works just fine and returns the number of records:

       

      [code]

       

      SCRIPT_REAL("

       

      import pymysql

       

      db = pymysql.connect(

                           host='localhost',

                           port=4408,

                           user='username',

                           passwd='passw',

                           db='someDb'

                           )

       

      cur = db.cursor()

       

      i = 0

       

      sqlStr = 'select distinct APPL_ID from APPL_APP where APPL_ACTIVE_FLAG > 0 and APPL_SUBMIT_DT >= \'2018-02-01\'' '

       

      cur.execute(sqlStr)

       

      for i in range(len(cur.fetchall())):

          print i

       

      return i

       

      db.close()

      ",

      ATTR([F1]))

      [/code]

       

      However, if I try to pass the date [F1], then I receive the following error - it's like it is putting brackets around the date:

      ProgrammingError : (1064, u"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2018-02-01']'' at line 1")

       

      [code]

       

      SCRIPT_REAL("

       

      import pymysql

       

      db = pymysql.connect(

                           host='localhost',

                           port=4408,

                           user='username',

                           passwd='passw',

                           db='someDb'

                           )

       

      cur = db.cursor()

       

      i = 0

       

      t1 = _arg1

       

      sqlStr = 'select distinct APPL_ID from APPL_APP where APPL_ACTIVE_FLAG > 0 and APPL_SUBMIT_DT >= \'%s\' ' % t1

       

      cur.execute(sqlStr)

       

      for i in range(len(cur.fetchall())):

          print i

       

      return i

       

      db.close()

      ",

      ATTR([F1]))

      [/code]

        • 1. Re: Pass Date to Python in Calculated field - ProgrammingError 1064
          Tarik Abdel-Monem

          Figured it out:

          I used parameters to pass dates, because one data set has it's own date field, which will be used in a similar way for the other data sets that have their own date fields

           

          SCRIPT_REAL("

           

          import pymysql

          import datetime

           

          db = pymysql.connect(

                               host='localhost',

                               port=4408,

                               user='usern',

                               passwd='passw',

                               db='dbName'

                               )

           

          cur = db.cursor()

           

          t1 = str(*_arg1)

           

          t2 = str(*_arg2)

           

          sqlStr = 'select distinct APPL_ID from APPL_APP where APPL_ACTIVE_FLAG > 0 and APPL_SUBMIT_DT >= \'%s 00:00:00\' and APPL_SUBMIT_DT <= \'%s 23:59:59\'' % (t1,t2)

           

          cur.execute(sqlStr)

           

          for i in range(len(cur.fetchall())):

              print i

           

          # first app is 1 not 0, which is what the for loop starts with 0

          return i + 1

           

          db.close()

          ",

          ATTR(STR([start-date])), ATTR(STR([end-date]))

          )

          1 of 1 people found this helpful