Illegal Variable Name/Number when Passing in Python List

Question

I'm trying to run SQL statements through Python on a list. By passing in a list, in this case date. Since i want to run multiple SELECT SQL queries and return them. I've tested this by passing in integers, however when trying to pass in a date I am getting ORA-01036 error. Illegal variable name/number. I'm using an Oracle DB.

cursor = connection.cursor()
date = ["'01-DEC-21'", "'02-DEC-21'"]

sql = "select * from table1 where datestamp = :date"

for item in date:
    cursor.execute(sql,id=item)
    res=cursor.fetchall()
    print(res)

Any suggestions to make this run?

@AhmedSunny thanks for this info. It is a larger table and sometimes takes longer to parse through using IN vs. running one at a time — Mark T
– Mark T, Commented Dec 7, 2021 at 19:39
Read the cx_Oracle documentation Binding Multiple Values to a SQL WHERE IN Clause which has some general solutions. — Christopher Jones
– Christopher Jones, Commented Dec 7, 2021 at 22:04

kfinity · Accepted Answer · 2021-12-07 15:07:24Z

1

You can't name a bind variable date, it's an illegal name. Also your named variable in cursor.execute should match the bind variable name. Try something like:

sql = "select * from table1 where datestamp = :date_input"

for item in date:
  cursor.execute(sql,date_input=item)
  res=cursor.fetchall()
  print(res)

answered Dec 7, 2021 at 15:07

kfinity

9,1411 gold badge15 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marmite Bomber · Accepted Answer · 2021-12-07 18:16:04Z

1

Some recommendation and warnings to your approach:

you should not depend on your default NLS date setting, while binding a String (e.g. "'01-DEC-21'") to a DATE column. (You probably need also remone one of the quotes).
You should ommit to fetch data in a loop if you can fetch them in one query (using an IN list)
use prepared statement

Example

date = ['01-DEC-21', '02-DEC-21']

This generates the query that uses bind variables for your input list

in_list = ','.join([f" TO_DATE(:d{ind},'DD-MON-RR','NLS_DATE_LANGUAGE = American')" for ind, d in enumerate(date)])

sql_query = "select * from table1 where datestamp in ( " + in_list + " )"

The sql_query generate is

select * from table1 where datestamp in 
(  TO_DATE(:d0,'DD-MON-RR','NLS_DATE_LANGUAGE = American'), TO_DATE(:d1,'DD-MON-RR','NLS_DATE_LANGUAGE = American') )

Note that the INlist contains one bind variable for each member of your input list.

Note also the usage of to_date with explicite mask and fixing the language to avoid problems with interpretation of the month abbreviation. (e.g. ORA-01843: not a valid month)

Now you can use the query to fetch the data in one pass

cur.prepare(sql_query)
cur.execute(None, date)
res = cur.fetchall()

edited Dec 7, 2021 at 18:16

answered Dec 7, 2021 at 16:17

Marmite Bomber

21.3k4 gold badges31 silver badges59 bronze badges

3 Comments

Mark T Over a year ago

Thanks for this input. I've modified and now i am getting cx_Oracle.DatabaseError: ORA-01830: date format picture ends before converting entire input string

Christopher Jones Over a year ago

Since Oracle has a statement cache there may be little benefit to an explicit prepare()

Marmite Bomber Over a year ago

This is true and also the cursor cache works fine @ChristopherJones. So in this simple example there will be no visible effect. Anyway IMO the recommendation to use prepared statements in a large scale applications still holds.

Collectives™ on Stack Overflow

Illegal Variable Name/Number when Passing in Python List

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related