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?

Answer 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