Python批量更新的方法

Replace Into

• 存在更新，不存在插入
• 不传递的参数会被更新成null
• 语法

REPLACE INTO table (A, B, C) VALUES('1', '2', '3')

Insert into …on duplicate key update…

• 存在更新，不存在插入
• 不传递的参数会保留原来的值
• 语法

INSERT INTO TABLE(id, url) VALUES ('1', 'WWW.XXX.COM') AS TABLE_ALIAS ON DUPLICATE KEY UPDATE url = TABLE_ALIAS.url

• python语法

import mysql.connector

db_connector = mysql.connector.connect(
    host="127.0.0.1",
    user="用户名",
    password="密码",
    database="数据库",
    buffered=True
)
cursor = db_connector.cursor()
sql = '''
    insert into find_in_set_test (id, url) values (%s, %s) as fist on duplicate key update url = fist.url
'''
update_list = []
for _index in range(0, 1000):
    inner_list = []
    inner_list.append(_index)
    inner_list.append('http://www.baidu.com' + str(_index))
    # inner_list.append('cccc' + str(_index))
    update_list.append(inner_list)
try:
    cursor.executemany(sql, update_list)
except Exception as e:
    print(e)
# 关闭数据库连接
cursor.close()
db_connector.commit()
db_connector.close()