How to pass variables to MYSQL using Python

Question

I am using MYSQL (ver. 5.5.31-0+wheezy1) and python (ver. 2.7.3) with the following statement:

q ="""INSERT INTO scale_equipment truck_id, product_id, driver_id, field_id, pit_id, harvest_equipment_id, weight, status VALUES (%s, %s, %s, %s, %s, %s, %s, %s)""",(truck_id, product_id, driver_id, field_id, pit_id, harvest_equipment_id, 0, status)

If I use:

q ="""INSERT INTO scale_equipment truck_id, product_id, driver_id, field_id, pit_id, harvest_equipment_id, weight, status VALUES ('002', 'CS', 'BG', 'HD1', 'T1', 'C1', 0, 'U')"""

it works fine, what I am I doing wrong to pass the variables in the SQL statement

I print out the q before it hits the execute query statement using the variables and this is what it looks like:

'INSERT INTO scale_equipment truck_id, product_id, driver_id, field_id, pit_id, harvest_equipment_id, weight, status VALUES (%s, %s, %s, %s, %s, %s, %s, %s)', ('002', 'CS', 'BG', 'HD1', 'T1', 'C1', 0, 'U')

Any help would be greatly appreciated.

Possible duplicate of this: http://stackoverflow.com/questions/16506643/inserting-variables-mysql-using-python-not-working — Destructor, Sep 16 '13 at 17:33
Does this answer your question? [MySQL parameterized queries](https://stackoverflow.com/questions/775296/mysql-parameterized-queries) — Henry Woody, May 21 '22 at 01:20

score 1 · Accepted Answer · answered Sep 16 '13 at 17:43

Query parameters ~~should~~ must be passed in the second argument of execute():

params = ('002', 'CS', 'BG', 'HD1', 'T1', 'C1', 0, 'U')
cursor.execute("""INSERT INTO 
                      scale_equipment 
                      (truck_id, product_id, driver_id, field_id, pit_id, harvest_equipment_id, weight, status) 
                  VALUES 
                       (%s, %s, %s, %s, %s, %s, %s, %s)""", params)

In this case you wouldn't worry about sql injections, mysqldb driver does escaping for you.

How to pass variables to MYSQL using Python

1 Answers1

Linked