CODEWITHSUNDEEP
pythonpython-3.xoracle-databasecx-oracle
Kes Perron
Kes Perron

Reputation: 477

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

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.

Upvotes: 1

Views: 1548

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65433

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

Upvotes: 2

Related Questions