一.性能对比
今天突然想测试一下, pymysql中exectue ,和 executemany 插入数据库的效率,
看看区别大不大.
pymysql 中 execute 和 executemany 性能对比
users表结构
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`email` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
import pymysql
# 数据库配置信息
SHOUFUYOU_USER_PROFILE_CONFIG = {
'host': 'xxx.xxx.xxx.xxx',
'port': xxxx,
'user': 'xxxx',
'password': 'xxxxxxx',
'db': 'xxxxx',
'charset': 'utf8',
}
def fn_timer(fn):
"""
计算 fn 的运算时间
:param fn:
:return:
"""
@wraps(fn)
def function_timer(*args, **kwargs):
start = now()
result = fn(*args, **kwargs)
logger.info(f'{fn.__name__} total running time {now() - start} seconds')
return result
return function_timer
@fn_timer
def test_execute(connection, sql):
rows_count = 0
with connection.cursor(pymysql.cursors.DictCursor) as cursor:
for i in range(1000):
rows_count += cursor.execute(sql, ('frank@python.org', 'test' + str(i)))
connection.commit()
return rows_count
@fn_timer
def test_execute_many(connection, sql):
with connection.cursor(pymysql.cursors.DictCursor) as cursor:
datas = [('webmaster@python.org', 'test' + str(i)) for i in range(1,1000)]
rows_count = cursor.executemany(sql, datas)
connection.commit()
return rows_count
if __name__ == '__main__':
insert_sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
connection = pymysql.connect(**SHOUFUYOU_USER_PROFILE_CONFIG)
test_execute(connection, insert_sql)
test_execute_many(connection, insert_sql)
Connected to pydev debugger (build 173.4127.16)
test_execute total running time 33.28491401672363 seconds
test_execute_many total running time 0.12204098701477051 seconds
二. 总结
可以看出明显的区别, 如果需要批量插入数据库,还是用 executemany方法 这个和execute 不是一个数量级.
如果只是插入比较少的数量量 用 exectue 比较好点.