Geek asks and answers

UPDATE or INSERT MySQL Python

I need to update the row if the record already exists or create a new one if it is not ready. I am disappointed in the DUPLICATE KEY, will do this with MYSQLdb, however I am having problems with its operation. My code is below

        cursor = database.cursor()
        cursor.execute("INSERT INTO userfan (user_id, number, round VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE user_id =%s, number=%s, round=%s", (user_id, number, round))
        database.commit()

The primary key is user_id

+5
source share
1 answer

The brackets were missing. You can also use VALUES(column)the ON DUPLICATE KEY UPDATEinstructions in the section :

    cursor = database.cursor()
    cursor.execute("""
        INSERT INTO userfan 
            (user_id, number, round)
        VALUES 
            (%s, %s, %s) 
        ON DUPLICATE KEY UPDATE 
                                          -- no need to update the PK
            number  = VALUES(number), 
            round   = VALUES(round) ;
                   """, (user_id, number, round)     # python variables
                  )
    database.commit()
+14
source

More articles:


All Articles