Error: invalid syntax using cx_oracle in python

Question

Below code is for extracting the data from oracle database in csv file. In query,For converting from Fractional decimal into date format,i have used To_Date('12/30/1899', 'MM/DD/YYYY HH24:MI:SS')+DTIMESTAMP) Decoded_Date.

And also specified the date range for extracting the data between dates. Please help what's wrong in below code giving invalid syntax.

import csv
import cx_Oracle
dsn_tns = cx_Oracle.makedsn('hostname', 'port', sid='sid') # if needed, place an 'r' before any parameter in order to address special characters such as '\'.
conn = cx_Oracle.connect(user=r'username', password='password', dsn=dsn_tns)
cursor = conn.cursor()
csv_file = open("C:/Users/locations.csv", "w")
writer = csv.writer(csv_file, delimiter=',', lineterminator="\n", quoting=csv.QUOTE_NONNUMERIC)
r = cursor.execute("""SELECT * 
                        FROM (SELECT LROWNUM,DTIMESTAMP,LSCENARIO,LYEAR,LPERIOD,
                                     LENTITY,LPARENT,LVALUE,LACCOUNT,LICP,LCUSTOM1,
                                     LCUSTOM2,STRUSERNAME,STRSERVERNAME,
                                     LACTIVITY,DDATAVALUE,BNODATA,
                                     (To_Date('12/30/1899', 'MM/DD/YYYY HH24:MI:SS')+DTIMESTAMP) Decoded_Date
                                FROM TABLE_NAME
                             ) SUB
                       WHERE SUB.Decoded_Date between '23-MAR-2020' and '24-APR-2020';
    """)
for row in cursor:
    writer.writerow(row)

cursor.close()
conn.close()
csv_file.close()

Anthony Tuininga · Accepted Answer · 2020-06-16 17:23:04Z

The opening and closing parentheses should not be present. I can't test the SQL directly, of course, but this should in theory work for you!

    r = cursor.execute"""
            SELECT * 
            FROM
                ( SELECT LROWNUM,DTIMESTAMP,LSCENARIO,LYEAR,LPERIOD,
                      LENTITY,LPARENT,LVALUE,LACCOUNT,LICP,LCUSTOM1,
                      LCUSTOM2,STRUSERNAME,STRSERVERNAME,
                      LACTIVITY,DDATAVALUE,BNODATA,
                      To_Date('12/30/1899','MM/DD/YYYY') +
                          DTIMESTAMP as Decoded_Date
                  FROM TABLE_NAME
                ) SUB
            WHERE SUB.Decoded_Date between to_date('23-MAR-2020', 'DD-MON-YYYY')
                  and to_date('24-APR-2020', 'DD-MON-YYYY')
    """

Note the changes to the last line as well. Unless you know the value of NLS_DATE_FORMAT you can't compare strings with dates directly. Note that you can also bind date values directly as in

sql = "select ... where sub.decoded_date between :1 and :2"
cursor.execute(sql, [datetime.date(2020, 3, 23), datetime.date(2020, 4, 24)])

Collectives™ on Stack Overflow

Error: invalid syntax using cx_oracle in python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related