Python使用pymysql模块的executemany()方法批量插入数据 提高Mysql数据库写入效率

首先，我们建立如下的数据库，用于后续的测试：

CREATE TABLE `test` (
	`id`  bigint NOT NULL ,
	`random_value`  bigint NULL ,
	PRIMARY KEY (`id`)
);

列名	类型
id(主键)	bigint
random_value	bigint

随机生成20000条数据，用于后续测试：

注意： 使用excutemany(sql, list)批量插入时，List中数据类型必须为Tuple元组
eg. [(0, 3132), (1, 1298), (2, 6543), (3, 4553) ……]

import numpy as np

# 随机生成2W条数据
dataNum = 20000
idList = list(range(dataNum))
randomList = np.random.randint(low=100000, high=999999, size=dataNum)

data = list(zip(idList, randomList))

1 使用execute()逐行插入

实现

# 连接数据库
conn = pymysql.connect("ip", "user", "password", "databaseName")

cursor = conn.cursor()
sql = "INSERT INTO `test`(`id`, `random_value`) VALUES(%s, %s)"
# 循环逐条插入
start = time.clock()
for row in data:
    try:
        cursor.execute(sql, row)
    except pymysql.Error as e:
        print(e)
        conn.rollback()
        conn.close()
        sys.exit(1)
conn.commit()
conn.close()
end = time.clock()
print("execute方法用时：", end-start, "秒")

结果

execute方法用时： 13.6468353 秒

2 使用executemany()批量插入

executemany()用法

在数据库连接后，使用cursor.excutemany(sql, list)执行批量插入，其中sql为数据库SQL语句，其中的变量可以写为%s；list为要插入数据库的元组列表，其中的元组元素依次与SQL语句中的%s对应。

注意： List中数据类型必须为Tuple元组 eg.[(0, 3132), (1, 1298), (2, 6543), (3, 4553) ……]

实现

# 连接数据库
conn = pymysql.connect("ip", "user", "password", "databaseName")

cursor = conn.cursor()
sql = "INSERT INTO `test`(`id`, `random_value`) VALUES(%s, %s)"
# 批量插入
start = time.clock()
try:
    cursor.executemany(sql, data)
except pymysql.Error as e:
    print(e)
    conn.rollback()
    conn.close()
    sys.exit(1)
conn.commit()
conn.close()
end = time.clock()
print("executemany方法用时：", end-start, "秒")

结果

executemany方法用时： 0.1756037 秒

3 总结

方法	数据量	耗时
execute()	20000	13.6468353 秒
executemany()	20000	0.1756037 秒

显然，使用**executemany()的效率要比使用execute()好太多了。在插入大量数据时，当然优选executemany()**批量插入。

注意：当executemany与ON DUPLICATE KEY UPDATE一起使用

此时，不能在SQL语句中ON DUPLICATE KEY UPDATE后面的部分继续使用%s表示变量

eg.

sql = 
	'''
	INSERT INTO `test`(`id`, `random_value`) VALUES(%s, %s)
	ON DUPLICATE KEY UPDATE `random_value` = %s
	'''

这种常规方式书写的话，executemany()将会报以下错误：

TypeError: not all arguments converted during string formatting.

正确的方法如下

使用value(columnName)表示变量

eg.

sql = 
	'''
	INSERT INTO `test`(`id`, `random_value`) VALUES(%s, %s)
	ON DUPLICATE KEY UPDATE `random_value` = values(random_value)
	'''