Python MySQLdb 循环插入execute与批量插入executemany性能分析

Stella981 等级 302 0 0

Python MySQLdb 循环插入execute与批量插入executemany性能分析

用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秒……

想到昨天一个上午都在磨磨唧唧研究循环跟批量,这下被打脸也是打的有点爽……

收藏
评论区

相关推荐

MongoDB入门实操《中篇》续
![](https://oscimg.oschina.net/oscnet/661d000a-c96c-4651-b49e-7c234a2f12d4.png) 点击上方蓝字关注我们 ![](https://oscimg.oschina.net/oscnet/2e287382-0e03-4da8-8b96-d910cfa51f8a.png) 1 **M
java向python ,text文件动态传参或传值问题完美解决
由于业务需要对python文件进行参数传递,通过下面两个java方法完美解决此问题,我的思路是:首先我要先把上次写的参数删除,第二我要新的参数写到python文件中。 第一个方法解决了删除上次传递的参数问题。 第二个方法解决了参数传递到python文件 /** * @param file python文件的路径
Mysql(九):Python连接MySQL数据库之pymysql模块使用
Python3连接MySQL ============== 本文介绍Python3连接MySQL的第三方库--PyMySQL的基本使用。 PyMySQL介绍 --------- PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库,Python2中则使用mysqldb。 Django中也可以使用PyMySQL连接MyS
mysql 导入hive的脚本
#!/bin/bash #tanj #2017-09-11 DATE=\`date +%Y-%m-%d\` TIME=\`date +%H:%M:%S\` echo "$DATE $TIME-----------------start-----------------" if  \[ ! -n "$1" \] ;then    
mysqldump unknown table 'column_statistics'
mysqldump unknown table 'column\_statistics' in information\_schema =================================================================== mysqldump: Couldn't execute 'SELECT COLUMN\
Airflow笔记
### 1\. 依赖 `MySqlOperator` 的数据库交互通过 `MySQLdb` 模块来实现, 使用前需要安装相关依赖: pip install apache-airflow[mysql] ### 2\. 使用 使用 `MySqlOperator` 执行sql任务的一个简单例子: from airflow imp
Jquery创建表格、填充表格数据、重置表格
1.Jquery创建表格 /** * 创建表格 * @param label 标题 * @param data 数据 * @param tableElement html元素,表格插入至此元素中 */ function createTable(label, data, tableElemen
Python MySQLdb 循环插入execute与批量插入executemany性能分析
Python MySQLdb 循环插入execute与批量插入executemany性能分析 ============================================== 用Python连接MySQL数据库时,会用到MySQLdb库,这里下载↓↓↓ https://pypi.python.org/pypi/MySQL-python/ 这
Python 实现网页截屏、查库、发邮件
本文介绍了使用 Python(2.7版本)实现网页截屏、查库、发邮件的 demo。用到了 selenium、phantomjs、mailer、jinja2、mysqldb 还有 image,都是比较典型的用法,可复用性比较强。 **代码** 本 demo 是用于发周报邮件的,周报内容包括数据库中的记录以及网页指定元素的截屏。linux 中可以用 cro
Python 连接mysql数据库
python连接数据库的代码: import MySQLdb #该模块需要安装,目前(20161217)只支持到python 3.4 ,我用的是 python 2.7 def fetch\_batch\_data():     #Link DB     global k     conn= MySQLdb.connect(        
Python之路【第十七篇】:Django【进阶篇 】(转自银角大王博客)
### Model 到目前为止,当我们的程序涉及到数据库相关操作时,我们一般都会这么搞: * 创建数据库,设计表结构和字段 * 使用 MySQLdb 来连接数据库,并编写数据访问层代码 * 业务逻辑层去调用数据访问层执行数据库操作 ![](https://oscimg.oschina.net/oscnet/2f863fee384e3d877
Python接入mysql数据库
**方法一:直接在models里连接mysql数据库,用sql语言操作** ===================================== python2的代码: #coding=utf-8 import MySQLdb conn= MySQLdb.connect( host='loc
Python操作MySQL存储
在Python 2中,连接MySQL的库大多是使用MySQLdb,但是此库的官方并不支持Python 3,所以这里推荐使用的库是PyMySQL。 本节中,我们就来讲解使用PyMySQL操作MySQL数据库的方法。 1. 准备工作 在开始之前,请确保已经安装好了MySQL数据库并保证它能正常运行,而且需要安装好PyMy
Python链接MySQL
* [MySQLdb](https://www.oschina.net/action/GoToLink?url=https%3A%2F%2Fpypi.python.org%2Fpypi%2FMySQL-python%2F1.2.4) is a native driver that has been developed and supported for
sql分析web可视化平台
下载地址:http://git.oschina.net/luoyedao/viuSQL 下载github  https://github.com/luoyedao/viuSQL 安装环境, 环境:     centos 7  kali     web:   django 1.8.3   bootstrap(不需要安装,但是要联网)      软件: