python数据库批量插入 python数据库批量插入数据的实现(executemany的使用)
芝麻芋圆 人气:0想了解python数据库批量插入数据的实现(executemany的使用)的相关内容吗,芝麻芋圆在本文为您仔细讲解python数据库批量插入 的相关知识和一些Code实例,欢迎阅读和指正,我们先划重点:python数据库批量插入,python,批量插入,下面大家一起来学习吧。
正常情况下往数据库多张表中批量插入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)
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.__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条数据
# 插入数据进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
# 获取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()
以下是完整代码:
# #!/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))
加载全部内容