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

Stella981
• 阅读 424

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

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

点赞
收藏
评论区
推荐文章
blmius blmius
1年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录 问题 用navicat导入数据时,报错: 原因这是因为当前的MySQL不支持datetime为0的情况。 解决修改sql\mode: sql\mode:SQL Mode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。 全局s
Easter79 Easter79
11个月前
swap空间的增减方法
(1)增大swap空间 去激活swap交换区: #swapoff -v /dev/vg00/lvswap 扩展交换lv: #lvextend -L 10G /dev/vg00/lvswap 重新生成swap交换区: #mkswap /dev/vg00/lvswap 激活新生成的交换区: #swapon -v /dev/vg00/lvswap
Wesley13 Wesley13
11个月前
Java获得今日零时零分零秒的时间(Date型)
public Date zeroTime() throws ParseException {     Date time = new Date();     SimpleDateFormat simp= new SimpleDateFormat("yyyy-MM-dd 00:00:00");     SimpleDateFormat simp2= new S
Wesley13 Wesley13
11个月前
Java爬虫之JSoup使用教程
title: Java爬虫之JSoup使用教程 date: 2018-12-24 8:00:00 +0800 update: 2018-12-24 8:00:00 +0800 author: me cover: [https://img-blog.csdnimg.cn/20181224144920712](https://www.oschin
Stella981 Stella981
11个月前
Python之time模块的时间戳、时间字符串格式化与转换
Python处理时间和时间戳的内置模块就有`time`,和`datetime`两个,本文先说`time`模块。 ### 关于时间戳的几个概念 * 时间戳,根据1970年1月1日00:00:00开始按秒计算的偏移量。 * 时间元组(`struct_time`),包含9个元素。  `time.struct_time(tm_y
Wesley13 Wesley13
11个月前
Java日期时间API系列36
  十二时辰,古代劳动人民把一昼夜划分成十二个时段,每一个时段叫一个时辰。二十四小时和十二时辰对照表: **时辰** **时间** **24时制** 子时 深夜 11:00 - 凌晨 01:00 23:00 - 01 :00 丑时 上午 01:00 - 上午 03:00 01:00 - 03 :00 寅时 上午 03:00 - 上午 0
Wesley13 Wesley13
11个月前
MySQL查询按照指定规则排序
1.按照指定(单个)字段排序 select * from table_name order id desc; 2.按照指定(多个)字段排序 select * from table_name order id desc,status desc; 3.按照指定字段和规则排序 selec
Wesley13 Wesley13
11个月前
PHP中的NOW()函数
是否有一个PHP函数以与MySQL函数`NOW()`相同的格式返回日期和时间? 我知道如何使用`date()`做到这一点,但是我问是否有一个仅用于此的函数。 例如,返回: 2009-12-01 00:00:00 * * * ### #1楼 使用此功能: function getDatetimeNow() {
Wesley13 Wesley13
11个月前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
#### 背景描述 # Time: 2019-01-24T00:08:14.705724+08:00 # User@Host: **[**] @ [**] Id: ** # Schema: sentrymeta Last_errno: 0 Killed: 0 # Query_time: 0.315758 Lock_
helloworld_34035044 helloworld_34035044
2个月前
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。 uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid() 或 uuid(sep)参数说明:sep 布尔值,生成的uuid中是否包含分隔符'',缺省为