Python SQL query returns "ORA-00936: missing expression" when I replace hard-coded value with variable

Question

I have a database full of information and I'm trying to write a python script that will pull some of the data and organize it into a report. Here's what I have so far:

import cx_Oracle
import pandas as pd

conn = cx_Oracle.connect('REDACTED')
cursor = conn.cursor()

# Currently hard-coded to return single known motor number
cursor.execute('SELECT MOTORID FROM MOTORS WHERE SERIALNUM=804')
# Returns [(11)]
lMotorID = cursor.fetchall()

# Query Assessments for list of how long the motor had run when assessment was taken
cursor.execute("SELECT DISTINCT RUNHOURS FROM ASSESSMENTS WHERE MOTORID = %s \
               ORDER BY RUNHOURS" % lMotorID[0])
# Returns [(0), (0.91), (8), (25), ...]
lHours = cursor.fetchall()

# Query for number of installed sensors by senor type
cursor.execute("SELECT SENSTYP, COUNT(STATUS) FROM HEALTH LEFT JOIN INSTRUMENTATION \
               ON HEALTH.INSTROID = INSTRUMENTATION.INSTROID LEFT JOIN ASSESSMENTS \
               ON HEALTH.ASSESSID = ASSESSMENTS.ASSESSID WHERE ASSESSMENTS.ASSESSID \
               IN (SELECT ASSESSID FROM ASSESSMENTS WHERE MOTORID = 11 AND RUNHOURS = %s) \
               GROUP BY SENSTYP ORDER BY SENSTYP" % lHours[2])
# Returns a 2-column dataframe with sensor type in column 0 and the total in column 1
dfTotal = pd.DataFrame(cursor.fetchall())

Because I want this to work for any motor, I want to replace the hard-coded MOTORID = 11 with a variable. I tried replacing the last query with this:

cursor.execute("SELECT SENSTYP, COUNT(STATUS) FROM HEALTH LEFT JOIN INSTRUMENTATION \
               ON HEALTH.INSTROID = INSTRUMENTATION.INSTROID LEFT JOIN ASSESSMENTS \
               ON HEALTH.ASSESSID = ASSESSMENTS.ASSESSID WHERE ASSESSMENTS.ASSESSID \
               IN (SELECT ASSESSID FROM ASSESSMENTS WHERE MOTORID = %s AND RUNHOURS = %s) \
               GROUP BY SENSTYP ORDER BY SENSTYP" % (lMotorID[0], lHours[2])) 
dfTotal = pd.DataFrame(cursor.fetchall())

And that's when I get the ORA-00936 error. I don't understand why the query is complete with a hard-coded value, but not when the value is replaced by a variable (one that works in a previous query). Thanks in advance.

Aside from the point about using bind variables, try printing your variables, or do a try catch and print Cursor.statement to see exactly what query is being run. I suspect your lMotorID[0] is actually NULL. — kfinity
– kfinity, Commented Nov 6, 2019 at 16:01
@kfinity Then why does lMotorID[0] work in the 2nd query (Begins # Query Assessments)? — Kes Perron
– Kes Perron, Commented Nov 6, 2019 at 17:44
Good point, you probably have a different issue. I'd still recommend printing your variables and/or the full query. — kfinity
– kfinity, Commented Nov 6, 2019 at 17:47
Doing a try except finally definitely helped. Adding print(cursor.statement) to the except block showed me that it was passing tuples rather than the values. Adding [0] after each variable got me the actual value and then the query worked. Thanks! — Kes Perron
– Kes Perron, Commented Nov 6, 2019 at 17:52

Kes Perron · Accepted Answer · 2019-11-06 19:50:57Z

2

Need to fix the syntax for cursor.execute such as

cursor.execute('... WHERE MOTORID = :mt_id AND RUNHOURS = :run_hr', mt_id=lMotorID[0][0], run_hr=lHours[2][0])

prefixing the parameters by percentage sign is valid for MySQL connections through python, while Oracle accepts colons.

or another option would be using an ordered tuple :

cursor.execute('... WHERE MOTORID = :1 AND RUNHOURS = :2', (lMotorID[0][0],lHours[2][0]))

edited Nov 6, 2019 at 19:50

Kes Perron

4775 gold badges13 silver badges24 bronze badges

answered Nov 6, 2019 at 15:26

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

4 Comments

kfinity Over a year ago

For further reading, see the documentation: cx-oracle.readthedocs.io/en/latest/user_guide/bind.html

dorintufar Over a year ago

True, never format query inline with %, it can lead at least to SQL injection vulnerability

Kes Perron Over a year ago

I tried both of these options and both times I got "NotSupportedError: Python value of type tuple not supported."

Barbaros Özhan Over a year ago

I tried on python 3.6.5 ad Oracle DB 12.1.0.2, there is no problem with none of the options. Btw, third option might be using brackets to wrap a tuple such as [ lMotorID[0], lHours[2] ] @SMPerron

Collectives™ on Stack Overflow

Python SQL query returns "ORA-00936: missing expression" when I replace hard-coded value with variable

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related