CODEWITHSUNDEEP
pythonsqldatecx-oracle
Roland Jutasi
Roland Jutasi

Reputation: 31

Python cx_Oracle binding in SQL query gives ORA-01036: illegal variable name/number

I would like to write a function that return a pandas DataFrame via an SQL query. I have 4 agruments, the last 2 are the 'query' itself and the 'date_of_report'.

This is the query structure:

practice_query = """select * from <TABLE_NAME> b
        where b.VALUE_DATE = TO_DATE(:date_of_report,'yyyy-mm-dd') """

My funtion:

import time
import cx_Oracle
import pandas as pd

def my_query(username: str = "USERNAME",
              password: str = "PASSWORD",
              query: str = "",
              date_of_report: str = "") -> pd.DataFrame: 

    
    start = time.perf_counter()
    
    # create connection ot EDW tables
    dsn = cx_Oracle.makedsn("....", \
                             1112, \
                             service_name = "...")

    conn = cx_Oracle.connect(username, password, dsn, encoding="UTF-8")

    # create cursor
    cur = conn.cursor()

    cur.execute(query, date_of_report)
    col_names = [row[0] for row in cur.description]
    mydata = pd.DataFrame(cur.fetchall())
    
    mydata.columns = col_names
    
    end = time.perf_counter() - start
    print("Run time: {:.3f}s".format(end))
    
    return mydata

My function call:

mydata = edw_query(query = practice_query, date_of_report = "2020-09-30")

This is my error message:

DatabaseError                             Traceback (most recent call last)
<ipython-input-22-0ecdc646fe92> in <module>
----> 1 mydata = edw_query(query = practice_query, date_of_report = '"2020-09-30"')

<ipython-input-8-c7875fbe0d2a> in edw_query(username, password, query, date_of_report)
     36     cur = conn.cursor()
     37 
---> 38     cur.execute(query, date_of_report)
     39     col_names = [row[0] for row in cur.description]
     40     mydata = pd.DataFrame(cur.fetchall())

DatabaseError: ORA-01036: illegal variable name/number

Could you help find the correct syntax? I've tried date ':date_of_report' in the SQL text where date_of_report = "2020-09-30", I've also tried :date_of_report where date_of_report = '"2020-09-30"', but nothing seems to be working.

I've found these but could not figure out a way:

https://cx-oracle.readthedocs.io/en/latest/user_guide/bind.html

https://www.techonthenet.com/oracle/functions/to_date.php

Many thanks: Roland

Upvotes: 2

Views: 2209

Answers (1)

Christopher Jones
Christopher Jones

Reputation: 10506

Change the execute bind parameter to:

    cur.execute(query, [date_of_report])

or

    cur.execute(query, date_of_report=date_of_report)

See the cx_Oracle manual section Using Bind Variables. If you have suggestions on improving this doc, let us know.

Upvotes: 1

Related Questions