How to use ON DUPLICATE KEY UPDATE with Python, MySQL, and mysql.connector

This dev tip is brought to you by the developer of Moolabeast: Automated Expense Tracking for Android and iOS. Avoid overspending when you start tracking your expenses with Moolabeast.

Fret not distressed developer! Using the ON DUPLICATE KEY UPDATE feature of SQL with Python, MySQL, and mysql.connector can be a bit of a doozy once you add in parameterizing your queries. This personally took me quite awhile to figure out since most of the SQL references regarding ON DUPLICATE KEY UPDATE do not include the Python/mysql.connector specific aspects, and most of the Python/mysq.connector references do not mention ON DUPLICATE KEY UPDATE.

So here is the trick, you won’t be passing the SQL query values like usual with %s for the UPDATE part of your query. Instead you will write something like VALUES(name_column). For reference, here is a little snippet of code from one of my projects which should guide you in the right direction:

import mysql.connector
# Create MySQL Database Connection
database_connection = mysql.connector.connect(
host="99.111.11.99", # dummy ip address
user="your-super-sweet-admin-name",
passwd="y0uRpAsSw0Rd",
database="your_db"
)
cursor = database_connection.cursor()
values = (
"myTransactionID",
"myUserID",
"myName",
9.99,
"myCategoryA",
"myCategoryB",
"myCategoryC",
123,
"1970-01-01",
"XYZ",
"myAddress",
50.0,
50.0,
"CA",
"90210",
1,
"myTransactionType") # single tuple will hold one row worth of database data
sql = "\
INSERT INTO transaction_table \
(transaction_id_column, \
user_id_column, \
name_column, \
amount_column, \
category_a_column, \
category_b_column, \
category_c_column, \
category_id_column, \
date_column, \
iso_currency_code_column, \
address_column, \
lat_column, \
lon_column, \
state_column, \
zip_column, \
pending_column, \
transaction_type_column) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) \
ON DUPLICATE KEY UPDATE \
user_id_column = VALUES(user_id_column), \
name_column = VALUES(name_column), \
amount_column = VALUES(amount_column), \
category_a_column = VALUES(category_a_column), \
category_b_column = VALUES(category_b_column), \
category_c_column = VALUES(category_c_column), \
category_id_column = VALUES(category_id_column), \
date_column = VALUES(date_column), \
iso_currency_code_column = VALUES(iso_currency_code_column), \
address_column = VALUES(address_column), \
lat_column = VALUES(lat_column), \
lon_column = VALUES(lon_column), \
state_column = VALUES(state_column), \
zip_column = VALUES(zip_column), \
pending_column = VALUES(pending_column), \
transaction_type_column = VALUES(transaction_type_column)"
cursor.execute(sql, values)
database_connection.commit()
 

topherPedersen