更新或插入MySQL Python [英] UPDATE or INSERT MySQL Python

问题描述

如果一条记录已经存在，我需要更新一行；否则，请创建一条新记录.我对ON DUPLICATE KEY的了解不足，将使用MYSQLdb完成此操作，但是我无法使其正常工作.我的代码在下面

I need to update a row if a record already exists or create a new one if it dosen't. I undersant ON DUPLICATE KEY will accomplish this using MYSQLdb, however I'm having trouble getting it working. 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()

主键是user_id

primary key is user_id

推荐答案

括号中缺少括号.您还可以在语句的ON DUPLICATE KEY UPDATE部分中使用VALUES(column):

A parenthesis was missiing. You can also use the VALUES(column) in the ON DUPLICATE KEY UPDATE section of the statement:

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

这篇关于更新或插入MySQL Python的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！