Using Parameter Substitution for Column Names with Python MySQL Connector

The use of data parameter substitution with MySQL Connector is well documented:

To create a new row in a table by passing in multiple data parameters the syntax might be:

cur.execute('INSERT INTO table1 (name, address, phone, postcode) values (%s, %s, %s, %s)', ('John', '1 Front Street', '416-111-1234', 'N4R2E5'))
db.commit()

Where the %s are placeholders for the data values being passed in the following list.

You can also use syntax such as:

cur.execute('UPDATE table1 SET name = %s where address = %s, ( 'Bob', '1 Front Street'))
db.commit()

But what if you want to also use parameter substitution for column names as well?

Recently, I had a list of column names and values in the form of a Python dictionary and I wanted to loop through the dictionary and update a database column from each column/data pair.

At first, I tried something like this, where I used %s as a placeholder for the column name:

cur.execute('UPDATE table1 SET %s = %s where address = %s, ( 'column_name', 'Bob', '1 Front Street'))
db.commit()

But this produced an exception. After a little research, I realized that the SQL command is just a Python string so standard Python string formatting techniques should be used. This worked great.

cur.execute('UPDATE table1 SET {} = %s where address = %s'.format(column_name), ('Bob', '1 Front Street'))
db.commit()

I then wondered if I could use Python string formatting for the column names and data values but this also produced an exception.

So in summary, the correct syntax uses Python string formatting for column name substitution and MySQL connector %s substitution for data value substitution.

Leave a Reply

Your email address will not be published. Required fields are marked *