Python数据库批量插⼊数据（executemany的使⽤）
正常情况下往数据库多张表中批量插⼊1000条数据，若⼀条⼀条insert插⼊，则调⽤sql语句查询插⼊需要执⾏⼏千次，花费时间长
现使⽤utemany（sql，args） ，可对数据进⾏批量插⼊，
其中args是⼀个包含多个元组的list列表，每个元组对应mysql当中的⼀条数据
以下是实例：
往数据库中的order表、order_detail表和pay表中插⼊1000条订单数据，订单详情数据以及⽀付数据
1.pay表中的id字段是order表中的pay_id字段
1.初始化属性(包括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
2.连接数据库
def isConnection(self):
self.__t(
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条数据
# 插⼊数据进pay表
def pay_insert(self,pay_value):
try:
# 连接数据库
self.isConnection()
# 创建游标
global cursor
cursor=self.__db.cursor()
# 执⾏
except Exception as e:
print e
finally:
cursor.close()
self.__dbmit()
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
# 获取pay_id
def get_pay_id(self,now_time):
try:
self.isConnection()
global cursor
cursor=self.__db.cursor()
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.__dbmit()
self.__db.close()
以下是完整代码：
# #!/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.__t(
host=self.__db_host,
port=self.__db_port,acess数据库
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()
# 执⾏
except Exception as e:
print e
finally:
cursor.close()
self.__dbmit()
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()
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.__dbmit()
self.__db.close()
# 插⼊数据进order表
def order_insert(self,order_value):
try:
self.isConnection()
global cursor
cursor=self.__db.cursor()
val ue (%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.__dbmit()
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城⼩学","133********",130,1,pay_id,str(random.randint(7100000000,7999999999)),2,8800,8800,1,493 38,time.localtime(),49338,time.localtime(),405,121,564123698745632,"三年级 3班",30,30,30))
sys_time=time.localtime()
return sys_time
# 获取order_id
def get_order_id(self,sys_time,data_number):
try:
self.isConnection()
global cursor
cursor=self.__db.cursor()
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.__dbmit()
self.__db.close()
# 插⼊数据进order_detail表
def order_detail_insert(self,detail_value):
try:
self.isConnection()
global cursor
cursor=self.__db.cursor()
except Exception as e:
print e
finally:
cursor.close()
self.__dbmit()
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,"ygxf-de
huaweicloud:443/i mage%2F1618551784845-589.jpg",1,2,49338,time.localtime(),49338,time.localtime()))
if __name__ == '__main__':
db=DatabaseAcess()
data_number=3
print ("{0}条数据插⼊完成".format(data_number-1))

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。