by topherPedersen

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

This blog post is brought to you by the developer of BitBudget. BitBudget is an automated budgeting app for Android and iOS which syncs with your bank account and helps you avoid overspending. If you’d like to quit living paycheck-to-paycheck and get a better handle on your finances, download it today! https://bitbudget.io

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()
view raw on_duplicate_key_update.py hosted with ❤ by GitHub
 

topherPedersen