Error while passing variables to a sql query in python

Question

I am using an sql query in python. I want to pass some values as parameters/variables in the query. I have seen few related questions. I am following those questions but I am not sure where I am going wrong. My code

import mysql.connector

floor = 'L2'
cursor.execute ("""SELECT t1.deviceId, t1.date, t1.vavId, t1.timestamp, t1.nvo_airflow , t1.nvo_air_damper_position , t1.nvo_temperature_sensor_pps
                   FROM
                       (SELECT deviceId, date, nvo_airflow, nvo_air_damper_position, nvo_temperature_sensor_pps, vavId, timestamp, counter from vavData where date='2019-12-10' and floor=?1) t1
                   INNER JOIN
                        (SELECT date,max(timestamp) as timestamp,vavId from vavData where date='2019-12-10' and floor=?2 group by vavId) t2
                   ON (t1.timestamp = t2.timestamp) """,(floor,floor))

This gives the error:

Not all parameters were used in the SQL statement

I have also tried with %s, I get an error with that method also. Can some one help me with how to correctly pass the variables?

Does this answer your question? [How to use variables in SQL statement in Python?](https://stackoverflow.com/questions/902408/how-to-use-variables-in-sql-statement-in-python) — Florian H, Dec 10 '19 at 08:54
Does this help you: https://stackoverflow.com/questions/902408/how-to-use-variables-in-sql-statement-in-python ? — Florian H, Dec 10 '19 at 08:55
Try following [this answer](https://stackoverflow.com/a/902426/5758529) and let us know if it doesn't work. — Lio Elbammalf, Dec 10 '19 at 09:05
i have seen these questions, and implemented that in my query. Getting some errors. That's why I have added a new question. — chink, Dec 10 '19 at 09:09
"I have also tried with %s, I get an error with that method also" -- do you get the same error? — Will Keeling, Dec 10 '19 at 09:24

Gideon Maina · Accepted Answer · 2019-12-10T10:28:44.430

Use %s parameter markers, so that your query becomes

>>> q="""SELECT t1.deviceId, t1.date, t1.vavId, t1.timestamp, t1.nvo_airflow , t1.nvo_air_damper_position , t1.nvo_temperature_sensor_pps
                        FROM
                            (SELECT deviceId, date, nvo_airflow, nvo_air_damper_position, nvo_temperature_sensor_pps, vavId, timestamp, counter from vavData where date='2019-12-10' and floor=%s) t1
                        INNER JOIN
                             (SELECT date,max(timestamp) as timestamp,vavId from vavData where date='2019-12-10' and floor=%s group by vavId) t2
                        ON (t1.timestamp = t2.timestamp) """

>>> cursor.execute(q,('xxxx', 'xxxx'))

Just run this now with python 3.7 and mysql.connector.version.VERSION (8, 0, 18, '', 1) as expected.

Check this answer also

Error while passing variables to a sql query in python

1 Answers1