使用python批量插入数据到mysql的三种方法
自动化单条insert
# -*- coding:utf-8 -*-
import time
from pymysql import *
# 装饰器,计算插入50000条数据需要的时间
def timer(func):
def decor(*args):
start_time = time.time()
func(*args)
end_time = time.time()
d_time = end_time - start_time
print("the running time is : ", d_time)
return decor
@timer
def add_test_users():
conn = connect(host='主机名', port='端口号', user='用户名', password='密码', database='数据库名', charset='utf8')
cs = conn.cursor() # 获取游标
for num in range(0, 58000):
try:
sql = "insert into '表名'(字段名) values(值)"
cs.execute(sql)
except Exception as e:
return
conn.commit() # 提交
cs.close()
conn.close()
print('OK')
add_test_users()
单条insert的话插入5w条数据大约用时5秒左右,相对来说效率不高
拼接sql语句,只执行一次insert
# -*- coding:utf-8 -*-
import time
from pymysql import *
# 装饰器,计算插入50000条数据需要的时间
def timer(func):
def decor(*args):
start_time = time.time()
func(*args)
end_time = time.time()
d_time = end_time - start_time
print("the running time is : ", d_time)
return decor
@timer
def add_test_users():
usersvalues = []
for num in range(1, 50000):
usersvalues.append(('需要插入的字段对应的value')) # 注意要用两个括号扩起来
conn = connect(host='主机名', port='端口号', user='用户名', password='密码', database='数据库名', charset='utf8')
cs = conn.cursor() # 获取光标
# 注意这里使用的是executemany而不是execute,下边有对executemany的详细说明
cs.executemany('insert into '表名'(字段名) values(%s,%s,%s,%s)', usersvalues)
conn.commit()
cs.close()
conn.close()
print('OK')
add_test_users()
使用这种批量插入方式插入5w条数据用时大约不到1秒
另外,针对executemany
execute(sql) : 接受一条语句从而执行
executemany(templet,args):能同时执行多条语句,执行同样多的语句可比execute()快很多,强烈建议执行多条语句时使用executemany
templet : sql模板字符串, 例如 ‘insert into table(id,name,age) values(%s,%s,%s)’
args: 模板字符串中的参数,是一个list,在list中的每一个元素必须是元组!!! 例如: [(1,‘mike’),(2,‘jordan’),(3,‘james’),(4,‘rose’)]
使用load data infile 加载到mysql
需要注意的是,这种方式本来是在mysql里边执行的命令,而这里是用代码实现的命令
如果是在linux下,需要进行下边几个步骤
在桌面写个文本文件,里边的内容对应下边的数据表字段即可
cd /var/lib/mysql-files/
切换到这个目录,将文本文件移动到这个目录下,如果不在这个目录下执行的话加载的时候会出现错误,当然也可以选择修改mysql配置,只不过这种方法相对比较简单
# -*- coding:utf-8 -*-
import time
from pymysql import *
# 装饰器 记录时间
def timer(func):
def decor(*args):
start_time = time.time()
func(*args)
end_time = time.time()
d_time = end_time - start_time
print("the running time is : ", d_time)
return decor
@timer
def exc_data():
conn = connect(host='主机名', port='端口号', user='用户名', password='密码', database='数据库名', charset='utf8')
cs = conn.cursor()
# 注意表明后边添加的字段名要与自己写的文本文件相对应,如果文本文件有主键,而且与其他字段也是一一对应,这里只
需要写上一个数据表即可,但是如果文本文件一般情况下是没有定义主键的,因此我们需要在这里添加上几个除主键外其他
的字段名
sql = "load data infile '/var/lib/mysql-files/data.txt' into table '表名'(字段名);" # 针对定
义好的数据表以及文本,就是应该添加上这几个字段 (name,age,gender,classify_id)
cs.execute(sql)
conn.commit()
cs.close()
conn.close()
print('OK')
exc_data()
对应load data infile 可以在mysql里边直接执行文件即可
load data infile ‘/var/lib/mysql-files/data.txt’ into table ‘表名’(字段名) 这个命令
load data [low_priority] [local] infile ‘file_name txt’ [replace | ignore]
into table tbl_name
[fields
[terminated by’t’]
[OPTIONALLY] enclosed by ‘’]
[escaped by’’ ]]
[lines terminated by’n’]
[ignore number lines]
[(col_name, )]
这个是load data infile的完整命令,带[ ]的是可选参数 下面解释几个比较常用的参数
[terminated by’t’] 这个意思就是文本字段里边以什么分隔
如果文本文件是这样:
小一,12,男
小二,13,女
小三,12,男
那么就是应该terminated by ’ , ’ ,不设置默认的就是制表符分隔,上边定义的文本文件正好也就是制表符,因此没有做设置,按照的就是默认值
==enclosed by == 这个的意思是字段是以什么包裹着
如果文本文件是这样:
“小一”,“12”,“男”
“小二”,“13”,“女”
“小三”,“12”,“男”
那么参数就应该这样写:enclosed by “”
…