Python使用MySQL，无记录则插入，有记录则更新 - ON DUPLICATE KEY UPDATE

酒酿小圆子～

已于 2024-07-23 11:08:52 修改

阅读量1.1k

点赞数 1

分类专栏： Python 文章标签： mysql android 数据库

于 2023-10-10 11:01:07 首次发布

本文链接：https://blog.csdn.net/u012856866/article/details/133741827

版权

Python 专栏收录该内容

68 篇文章 13 订阅

订阅专栏

一、基本语法

ON DUPLICATE KEY UPDATE 语句基本功能是：当表中没有原来记录时，就插入，有的话就更新。

使用注意事项如下：

ON DUPLICATE KEY UPDATE语句根据主键id或唯一键来判断当前插入是否已存在。
记录已存在时，只会更新ON DUPLICATE KEY UPDATE之后指定的字段。
如果同时传递了主键和唯一键，以主键为判断存在依据，唯一键字段内容可以被修改。

二、用法示例

下面基于Python语言，给出具体使用示例：

1. 在MySQL中建立 student 表，将code设置为主键，如下所示：
在这里插入图片描述
2. 插入基础数据：

3. 使用 ON DUPLICATE KEY UPDATE 更新数据。

假设我们要将第二条记录的name修改为Lily：

# 数据库连接
conn = pymysql.connect(
    host='172.171.xxx.yyy',
    user='admin',
    passwd='xxxxxx',
    db='xxx',
    port=3306
)

data = ['002', 'Lily']
SQL_FORMAT = """INSERT INTO student(code, name) VALUES ('{0}', '{1}')  ON DUPLICATE KEY UPDATE name ='{2}' """
sql_1 = SQL_FORMAT.format(data[0], data[1], data[1])
print(sql_1)  # INSERT INTO student VALUES ('002', 'Lily')  ON DUPLICATE KEY UPDATE name ='Lily' 

with conn.cursor() as cursor:
    cursor.execute(sql_1) 
    cursor.close()
conn.commit()  # 提交

查看数据库中，第二条记录中的name已经被更新为Lily，如下所示：
在这里插入图片描述

【核心代码】：

写法1：

data = ['002', 'Lily']
SQL_FORMAT = """INSERT INTO student(code, name) VALUES ('{0}', '{1}')  ON DUPLICATE KEY UPDATE name ='{2}' """
sql_1 = SQL_FORMAT.format(data[0], data[1], data[1])
print(sql_1)  # INSERT INTO student VALUES ('002', 'Lily')  ON DUPLICATE KEY UPDATE name ='Lily'

写法2：

data = ['002', 'Lily']
SQL_FORMAT = "INSERT INTO student(code, name) VALUES (%s, %s) ON DUPLICATE KEY UPDATE name = %s "
sql_1 = SQL_FORMAT % (data[0], data[1], data[1])
print(sql_1)  # Insert  INTO student VALUES ('002', 'Lily') ON DUPLICATE KEY UPDATE name = 'Lily'

三、ON DUPLICATE KEY UPDATE 与 execute_many_sql 一起使用

在某些场景下，我们需要批量存入数据，为了提高效率可以采用“executemany + ON DUPLICATE KEY UPDATE”方式提交。

Python中使用executemany的方法可以参考博客：Python使用executemany实现数据批量插入

这里需要注意：当executemany与ON DUPLICATE KEY UPDATE一起使用时，不能在SQL语句中ON DUPLICATE KEY UPDATE后面的部分继续使用%s表示变量，而是使用values(columnName)表示需要update的变量。

sql_1 = "INSERT INTO student(code, name) VALUES (%s, %s) ON DUPLICATE KEY UPDATE code=values(code), name=values(name)"
data = [(001, 'David'),(002, 'Kate')]
cursor.executemany(sql_1, data)