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?

CodePudding user response：

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)

CodePudding user response：

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()