ON DUPLICATE KEY UPDATE (with python and mysql.connector)

on_duplicate_key_update.py

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()

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

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

what about a null or NaN