正常情況下往數據庫多張表中批量插入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=XXXself.__db_port=XXXself.__db_user=XXXself.__db_password=XXXself.__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 efinally: 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_valueexcept Exception as e: print efinally: cursor.close() self.__db.commit() self.__db.close()

以下是完整代碼：

# #!/usr/bin/python# # -*- coding: UTF-8 -*-import pymysql # 先pip install pymysqlimport randomimport timeclass DatabaseAcess: # 初始化屬性(包括host、port、user、password和database） def __init__(self):self.__db_host=XXXself.__db_port=XXXself.__db_user=XXXself.__db_password=XXXself.__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 efinally: 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_valueexcept Exception as e: print efinally: 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 efinally: 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_listexcept Exception as e: print efinally: 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 efinally: 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數據庫批量插入 內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網！