正常情况下往数据库多张表中批量插入1000条数据，若一条一条insert插入，则调用sql语句查询插入需要执行几千次，花费时间长
现使用cursor.executemany（sql，args） ，可对数据进行批量插入，
其中args是一个包含多个元组的list列表，每个元组对应mysql当中的一条数据
以下是实例：
往数据库中的order表、order_detail表和pay表中插入1000条订单数据，订单详情数据以及支付数据
1.pay表中的id字段是order表中的pay_id字段
2.order表中的id字段是order_detail表中的order_id字段
1.初始化属性(包括host、port、user、password和database）
|
1
2
3
4
5
6
|
def __init__(self):
self.__db_host=XXX
self.__db_port=XXX
self.__db_user=XXX
self.__db_password=XXX
self.__db_database=XXX
|
2.连接数据库
|
1
2
3
4
5
6
7
8
9
|
def isConnection(self):
self.__db=pymysql.connect(
host=self.__db_host,
port=self.__db_port,
user=self.__db_user,
password=self.__db_password,
database=self.__db_database,
charset='utf8'
)
|
3.批量往pay表中插入1000条数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
# 插入数据进pay表
def pay_insert(self,pay_value):
try:
# 连接数据库
self.isConnection()
# 创建游标
global cursor
cursor=self.__db.cursor()
# 执行
cursor.executemany('insert into `pay表`(type,pay_money,pay_time,pay_no,STATUS,create_by,create_time,update_by,update_time) value (%s,%s,%s,%s,%s,%s,%s,%s,%s)',pay_value)
except Exception as e:
print e
finally:
cursor.close()
self.__db.commit()
self.__db.close()
# 生成pay表所需字段，并调用sql
def pay_data(self):
pay_value=list()
for i in range(1,1000):
pay_value.append((0,8800,time.localtime(),str(random.randint(712300000000,712399999999)),3,49338,time.localtime(),49338,time.localtime()))
now_time=time.localtime()
self.pay_insert(pay_value)
return now_time
|
4.pay表中生成的1000条数据，依次取出id
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
# 获取pay_id
def get_pay_id(self,now_time):
try:
self.isConnection()
global cursor
cursor=self.__db.cursor()
cursor.execute('select id from `pay表` where create_time >= %s',now_time)
id_value=list()
for i in range(1,1000):
pay_id=cursor.fetchone()
id_value.append(pay_id)
return id_value
except Exception as e:
print e
finally:
cursor.close()
self.__db.commit()
self.__db.close()
|
以下是完整代码：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
|
# #!/usr/bin/python
# # -*- coding: UTF-8 -*-
import pymysql # 先pip install pymysql
import random
import time
class DatabaseAcess:
# 初始化属性(包括host、port、user、password和database）
def __init__(self):
self.__db_host=XXX
self.__db_port=XXX
self.__db_user=XXX
self.__db_password=XXX
self.__db_database=XXX
# 连接数据库
def isConnection(self):
self.__db=pymysql.connect(
host=self.__db_host,
port=self.__db_port,
user=self.__db_user,
password=self.__db_password,
database=self.__db_database,
charset='utf8'
)
# 插入数据进pay表
def pay_insert(self,pay_value):
try:
# 连接数据库
self.isConnection()
# 创建游标
global cursor
cursor=self.__db.cursor()
# 执行
cursor.executemany('insert into `pay表`(type,pay_money,pay_time,pay_no,STATUS,create_by,create_time,update_by,update_time) value (%s,%s,%s,%s,%s,%s,%s,%s,%s)',pay_value)
except Exception as e:
print e
finally:
cursor.close()
self.__db.commit()
self.__db.close()
# 生成pay表所需字段，并调用sql
def pay_data(self,data_number):
pay_value=list()
for i in range(1,data_number):
pay_value.append((0,8800,time.localtime(),str(random.randint(712300000000,712399999999)),3,49338,time.localtime(),49338,time.localtime()))
now_time=time.localtime()
self.pay_insert(pay_value)
return now_time
# 获取pay_id
def get_pay_id(self,now_time,data_number):
try:
self.isConnection()
global cursor
cursor=self.__db.cursor()
cursor.execute('select id from `pay表` where create_time >= %s',now_time)
id_value=list()
for i in range(1,data_number):
pay_id=cursor.fetchone()
id_value.append(pay_id)
return id_value
except Exception as e:
print e
finally:
cursor.close()
self.__db.commit()
self.__db.close()
# 插入数据进order表
def order_insert(self,order_value):
try:
self.isConnection()
global cursor
cursor=self.__db.cursor()
cursor.executemany('insert into `order表` (student_name,student_id,school_id,school_name,tel,height,sex,pay_id,order_no,status,original_price,payment_price,order_type,create_by,create_time,update_by,update_time,purchase_id,dept_id,sub_order_mid,class_name,shoe_size,student_no,weight) value (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)',order_value)
except Exception as e:
print e
finally:
cursor.close()
self.__db.commit()
self.__db.close()
# 生成order表所需字段，并调用sql
def order_data(self,id_value,data_number):
order_value=list()
for i in range(1,data_number):
pay_id=str(id_value[i-1]).replace("L,)","").replace("(","")
order_value.append(("周瑜",35999,346,"A城小学","13322222222",130,1,pay_id,str(random.randint(7100000000,7999999999)),2,8800,8800,1,49338,time.localtime(),49338,time.localtime(),405,121,564123698745632,"三年级 3班",30,30,30))
sys_time=time.localtime()
self.order_insert(order_value)
return sys_time
# 获取order_id
def get_order_id(self,sys_time,data_number):
try:
self.isConnection()
global cursor
cursor=self.__db.cursor()
cursor.execute('select id from `order表` where create_time >= %s',sys_time)
order_id_list=list()
for i in range(1,data_number):
order_id_list.append(cursor.fetchone())
return order_id_list
except Exception as e:
print e
finally:
cursor.close()
self.__db.commit()
self.__db.close()
# 插入数据进order_detail表
def order_detail_insert(self,detail_value):
try:
self.isConnection()
global cursor
cursor=self.__db.cursor()
cursor.executemany('insert into `order_details表` (order_id,commodity_name,commodity_id,original_price,payment_price,img,number,status,create_by,create_time,update_by,update_time) value (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)',detail_value)
except Exception as e:
print e
finally:
cursor.close()
self.__db.commit()
self.__db.close()
# 生成order_detail表所需字段，并调用sql
def order_detail_data(self,order_id_list,data_number):
detail_value=list()
for i in range(1,data_number):
order_id=str(order_id_list[i-1]).replace("L,)","").replace("(","")
detail_value.append((order_id,"A城小学春季校服","1382932636506902530",8800,8800,"https://ygxf-dev2.obs.cn-north-1.myhuaweicloud.com:443/image%2F1618551784845-589.jpg",1,2,49338,time.localtime(),49338,time.localtime()))
self.order_detail_insert(detail_value)
if __name__ == '__main__':
db=DatabaseAcess()
data_number=3
db.order_detail_data(order_id_list=db.get_order_id(sys_time=db.order_data(id_value=db.get_pay_id(now_time=db.pay_data(data_number=data_number),data_number=data_number),data_number=data_number),data_number=data_number),data_number=data_number)
print ("{0}条数据插入完成".format(data_number-1))
|
到此这篇关于python数据库批量插入数据的实现(executemany的使用)的文章就介绍到这了,更多相关python数据库批量插入 内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！
原文链接：https://blog.csdn.net/weixin_43848422/article/details/116236801