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? — Florian H
– Florian H, Commented Dec 10, 2019 at 8:54
Does this help you: stackoverflow.com/questions/902408/… ? — Florian H
– Florian H, Commented Dec 10, 2019 at 8:55
Try following this answer and let us know if it doesn't work. — Lio Elbammalf
– Lio Elbammalf, Commented Dec 10, 2019 at 9: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
– chink, Commented Dec 10, 2019 at 9:09
"I have also tried with %s, I get an error with that method also" -- do you get the same error? — Will Keeling
– Will Keeling, Commented Dec 10, 2019 at 9:24

Gideon Maina · Accepted Answer · 2019-12-10 10:28:44Z

1

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

edited Dec 10, 2019 at 10:28

answered Dec 10, 2019 at 10:21

Gideon Maina

87011 silver badges26 bronze badges

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

Collectives™ on Stack Overflow

Error while passing variables to a sql query in python

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related