Skip to content

Instantly share code, notes, and snippets.

@topherPedersen
Created December 10, 2019 21:57
Show Gist options
  • Save topherPedersen/1819d7edcb4bcb475339e299b2256ec5 to your computer and use it in GitHub Desktop.
Save topherPedersen/1819d7edcb4bcb475339e299b2256ec5 to your computer and use it in GitHub Desktop.
ON DUPLICATE KEY UPDATE (with python and mysql.connector)
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()
@jf-gofore
Copy link

Thank you for this. Worked like a charm. ❤️

@xivind
Copy link

xivind commented Sep 10, 2022

I really struggled to sort this one out, but your code snippet here solved it. Thanks 🙌

@dungz3us
Copy link

what about a null or NaN

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment