Parameterized mysql.connector query

user_18277159 0
Viewed 54

Previously, I parameterized my queries with pyodbc successfully. Now I'm trying to do the same in mysql.connector (python). Without the parameterization, the query runs fine. Below is my query, with one '%s' shown that I want to replace with a string containing a number, for example:'17360'

query = 
'''SELECT meas.pk_measurement
    FROM tbl_basic_patient pat
    JOIN tbl_basic_examination exam ON pat.pk_patient = exam.fk_patid 
    WHERE pat.patientid = %s 
    ORDER BY meas.pk_measurement'''



def query_l(query,pid):
    try:
        conn = mysql.connect(**database)
        cursor = conn.cursor()
        cursor.execute(query, pid)
        result = cursor.fetchall()
    finally:
        conn.close()
    return result

query_l(query,('17360'))

Unfortunately, I keep getting a mysql syntax error and I keep going in circles figuring out my error. Online I found that %s should be replaced by a tuple. Could any of you give me a hint on how to resolve my error?

mysql-connector-python mysql python
1 Answers

Your're correct: you need a tuple (or a dictionary) as the second parameter to cursor.execute(). Docs here.

Try this:

cursor.execute(query, (pid,))
user_2056080
Related