Using MySQL python module to Insert or update if row exists

Question

I am using MySql and python MySQLdb module. I want to insert a new row or update certain fields if the row already exists.

The list of tuples that contains the values to be entered looks like this;

ListTuple=[('A_val', 'B_val', 'C_val', 'D_val', 'C_val'), ('AA_val', 'BB_val', 'CC_val', 'DD_val', 'CC_val')]

The MySQL query looks like this;

query="""INSERT INTO table (A, B, C, D)
             VALUES (%s, %s, %s, %s)  
             ON DUPLICATE KEY UPDATE
             C= %s
      """

try:
    Cursor.executemany(query, ListTuple)  
    Db_conn.commit()
except:
    Db_conn.rollback()

Query execution fails with this code. Can someone point out what is wrong with it? Thank you very much for your help.

Barmar · Accepted Answer · 2014-04-10 03:26:09Z

3

Try this:

query="""INSERT INTO table (A, B, C, D)
             VALUES (%s, %s, %s, %s)  
             ON DUPLICATE KEY UPDATE
             C= VALUES(C)
      """

Your query had 5 placeholders, but your tuples only have 4 values. You needed to duplicate C, but the VALUES(C) option will automatically get that from the original VALUES clause.

answered Apr 10, 2014 at 3:26

Barmar

788k57 gold badges554 silver badges668 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

guagay_wk Over a year ago

Sorry, I had a typo in my question. My tuples actually do have 5 values. I have re-edited the question. Thanks for spotting it and sorry for misleading you.

guagay_wk Over a year ago

But you have solved my problem!! I just removed the duplicate element from the 5-element tuple and made it a 4-element tuple, then applied your answer. It works!!

guagay_wk Over a year ago

Again thanks for spotting the mistake. I have re-edited the question.

Barmar Over a year ago

Glad it worked, although I'm not sure why it didn't work with the 5-tuples.

guagay_wk Over a year ago

I was lucky as one of the elements is a duplicate. But what if there were no duplicates? How would the code look like? I think I will open another question on this issue.

|

Collectives™ on Stack Overflow

Using MySQL python module to Insert or update if row exists

1 Answer 1

8 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Related