- Getting «Duplicate entry ‘foo’ for key ‘PRIMARY'» While using ON DUPLICATE KEY UPDATE
- topherpedersen.blog
- A Blog About my Misadventures in Startups, Software Development, & Whatever Else I Feel Like Writing About
- How to use ON DUPLICATE KEY UPDATE with Python, MySQL, and mysql.connector
- topherPedersen / on_duplicate_key_update.py
Getting «Duplicate entry ‘foo’ for key ‘PRIMARY'» While using ON DUPLICATE KEY UPDATE
Right off the bat I want to say, due to my position I cannot paste the full code. So I will do what I can to symbolize the code and get straight to the point. Programmed in: Python. Simply put I am getting a Duplicate Key Error. I have looked into other questions that have been raised about this and to my knowledge I am following the suggestion those answers have provided. Table Structure Snippet:
CREATE TABLE `BAR_TABLE` ( `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `foo` tinytext, `bar` tinytext, `uuid` varchar(25) NOT NULL, PRIMARY KEY (`uuid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; In this case ‘uuid’ is the PRIMARY as there already a dataset this is taking in, that uuid is always unique(Unless it already exists then it’s updating potential info) SQL Snippet
INSERT INTO BAR_TABLE ( foo, bar, uuid ) VALUES ( %(foo)s, %(bar)s, %(uuid)s ) ON DUPLICATE KEY UPDATE foo = VALUES(foo), bar = VALUES(bar) So the reason the SQL looks like this is because I am using executemany(). As well as the data that comes in is a Python Dictionary. So this allows it to assign all the values in the dictionary data to the SQL statement. Then all that gets shifted into the DB using executemany(). The item it is throwing the duplicate entry is actually in the table. I managed to get this to run a couple times, and then at some point during its testing it hit this error, and hasn’t moved past it. Obviously I am miss understanding something. Am I miss-understanding how the PRIMARY KEY works? Or what a KEY is using the ON DUPLICATE KEY UPDATE?
topherpedersen.blog
A Blog About my Misadventures in Startups, Software Development, & Whatever Else I Feel Like Writing About
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:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters
| 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 / on_duplicate_key_update.py
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters
| 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 () |