Python MySQLdb 循环插入execute与批量插入executemany性能分析(list批量写法亲测成功)

淩亂°似流年 2023-10-07 13:59 80阅读 0赞

用Python连接MySQL数据库时，会用到MySQLdb库，这里下载↓↓↓

https://pypi.python.org/pypi/MySQL-python/

这个库提供了对数据库的普遍操作，增删改查之类的，教程可以参考：

python下的MySQLdb使用

其中，有一个很cooooooooooool的功能就是批量操作executemany，可以进行多行插入

先写sql语句。要注意的是里面的参数，不管什么类型，统一使用%s作为占位符  
例如，向user表(username,salt,pwd)插入数据

sql = 'INSERT INTO 表名 VALUES(%s,%s,%s)'  
对应的param是一个tuple或者list

param = ((username1, salt1, pwd1), (username2, salt2, pwd2), (username3, salt3, pwd3))

这样就包含了三条数据，通过executemany插入  
n=cursor.executemany(sql,param)

上个双休日在学校往毕设系统里导名单，想到Java就心累于是用Python写

作为一个老实的土鳖，在知道有这个酷炫的方法情况下，还是有点不敢用，先保守地写了一个循环的版本

几百条数据很快就导进去的，本来这就该结束了，但是又觉得有点不甘心，想知道两种插入方式效率有多大差别

于是简单模拟了10000个用户的数据，试着用两种方法各跑了一遍

\# -------------------------------------------  
\# Python MySQLdb 循环插入execute与批量插入executemany性能分析  
\# 插入数据量：10000条  
\# 每条字段：username, salt, pwd  
\# Author : Lrg  
\# -------------------------------------------  
\# encoding = utf-8  
import MySQLdb  
import xlrd  
import time  
import sys  
reload(sys)  
sys.setdefaultencoding("utf-8")  
  
\# 从users.xls文件获取10000条用户数据  
\# 该文件由create\_users.py生成  
def get\_table():  
FILE\_NAME = 'users.xls'  
data = xlrd.open\_workbook(FILE\_NAME)  
table = data.sheets()\[0\]  
return table  
  
\# 循环插入execute  
def insert\_by\_loop(table):  
nrows = table.nrows  
for i in xrange(1,nrows):  
param=\[\]  
try:  
sql = 'INSERT INTO user values(%s,%s,%s)'  
\# 第一列username，第二列salt，第三列pwd  
print 'Insert: ',table.cell(i, 0).value, table.cell(i, 1).value, table.cell(i, 2).value  
param = (table.cell(i, 0).value, table.cell(i, 1).value, table.cell(i, 2).value)  
\# 单条插入  
cur.execute(sql, param)  
conn.commit()  
except Exception as e:  
print e  
conn.rollback()  
print '\[insert\_by\_loop execute\] total:',nrows-1  
  
\# 批量插入executemany  
def insert\_by\_many(table):  
nrows = table.nrows  
param=\[\]  
for i in xrange(1,nrows):  
\# 第一列username，第二列salt，第三列pwd  
param.append(\[table.cell(i, 0).value, table.cell(i, 1).value, table.cell(i, 2).value\])  
try:  
sql = 'INSERT INTO user values(%s,%s,%s)'  
\# 批量插入  
cur.executemany(sql, param)  
conn.commit()  
except Exception as e:  
print e  
conn.rollback()  
print '\[insert\_by\_many executemany\] total:',nrows-1  
  
  
\# 连接数据库  
conn = MySQLdb.connect(host="127.0.0.1", port=3306, user="lrg", passwd="lrg", db="pythontest")  
cur = conn.cursor()  
  
\# 新建数据库  
cur.execute('DROP TABLE IF EXISTS user')  
sql = """CREATE TABLE user(  
username CHAR(255) NOT NULL,  
salt CHAR(255),  
pwd CHAR(255)  
)"""  
cur.execute(sql)  
  
\# 从excel文件获取数据  
table = get\_table()  
  
\# 使用循环插入  
start = time.clock()  
insert\_by\_loop(table)  
end = time.clock()  
print '\[insert\_by\_loop execute\] Time Usage:',end-start  
  
\# 使用批量插入  
start = time.clock()  
insert\_by\_many(table)  
end = time.clock()  
print '\[insert\_by\_many executemany\] Time Usage:',end-start  
  
\# 释放数据连接  
if cur:  
cur.close()  
if conn:  
conn.close()

一共10000条数据

一行行循环execute，耗时200秒左右（下面244秒的数据是每次循环加了输出语句的，应该有点影响）

而用executemany一次提交全部，耗时只有0.86秒……

\[insert\_by\_loop execute\] total: 10000  
\[insert\_by\_loop execute\] Time Usage: 244.164735527  
\[insert\_by\_many executemany\] total: 10000  
\[insert\_by\_many executemany\] Time Usage: 0.861406346583  
\[Finished in 245.7s\]

土鳖现在有一种难以言喻的微妙感  
想起前两天用正则表达式用的正爽时，看到一篇文章说，同样的功能用字符串函数完成比正则快几十几百倍……

讨厌啦泥们不要欺负新手啊！！【捂脸】

\--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

附上生成模拟数据user.xls的代码，简单粗暴（MD5部分实验程序：Python简单密码加密程序,加盐(salt)md5）

\# -------------------------------------------  
\# Python生成user程序  
\# Author : Lrg  
\# -------------------------------------------  
\# encoding = utf-8  
from random import Random  
from hashlib import md5  
import xlwt  
  
\# 获取由4位随机大小写字母、数字组成的salt值  
def create\_salt(length = 4):  
salt = ''  
chars = 'AaBbCcDdEeFfGgHhIiJjKkLlMmNnOoPpQqRrSsTtUuVvWwXxYyZz0123456789'  
len\_chars = len(chars) - 1  
random = Random()  
for i in xrange(length):  
\# 每次从chars中随机取一位  
salt += chars\[random.randint(0, len\_chars)\]  
return salt  
  
\# 获取原始密码+salt的md5值  
def create\_md5(pwd,salt):  
md5\_obj = md5()  
md5\_obj.update(pwd + salt)  
return md5\_obj.hexdigest()  
  
\# 创建一个xls文件  
book = xlwt.Workbook()  
\# 创建一个sheet  
sheet = book.add\_sheet('users', cell\_overwrite\_ok=True)  
\# 每列第一行写上列名  
sheet.write(0, 0, 'username')  
sheet.write(0, 1, 'salt')  
sheet.write(0, 2, 'pwd')  
\# 生成user数量  
count = 10000  
\# 第一个id  
first\_id = 311010000  
for i in xrange(count):  
current\_id = str(first\_id + i)  
salt = create\_salt()  
pwd = create\_md5(current\_id, salt)  
sheet.write(i+1, 0, current\_id)  
sheet.write(i+1, 1, salt)  
sheet.write(i+1, 2, pwd)  
\# 保存  
book.save('users.xls')

过了一天，又学到一个打脸方法

MySQL本身有个load data infile的方法，格式类似这样：

load data infile 'D:/Python workspace/user.txt' into table user(username, salt, pwd)

这个方法有多快？  
从txt导5个字段的9086条数据到mysql，先读到程序里，再用executemany是0.29秒，直接用这个语句导的话，0.17秒……

想到昨天一个上午都在磨磨唧唧研究循环跟批量，这下被打脸也是打的有点爽……  
————————————————  
版权声明：本文为CSDN博主「尾巴七」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。  
原文链接：https://blog.csdn.net/colourless/article/details/41444069