1、存在即忽略 IGNORE
INSERT IGNORE INTO test(name, modified_at)VALUES(%s, NOW());
2、存在即更新,自增id主键不变 ON DUPLICATE KEY UPDATE
INSERT INTO test(name, modified_at)VALUES(%s, NOW())
ON DUPLICATE KEY UPDATE f_modified_at=NOW();
3、存在即更新,自增id主键变 REPLACE
REPLACE INTO test(name, modified_at)VALUES(%s, NOW());
# 批量插入数据到操作表
def many_insert(self, args, table_name):
"""
:param args:
:param table_name:
:return:
"""
# 根据数据获取动态的插入字段
data = args[0]
cols = ', '.join(['`{}`'.format(k) for k in data.keys()])
val_cols = ', '.join(['%({})s'.format(k) for k in data.keys()])
sql = """
INSERT INTO %s(%s) values(%s)
""" % (table_name, cols, val_cols)
logger.sql(sql)
try:
tdata_db.many_insert(sql, args)
except Exception as e:
logger.exception(e)
logger.error(sql)
abort_error(500, message='批量插入数据到' + table_name + '表失败')