Under normal circumstances to the database in a number of tables to batch insert 1000 pieces of data, if a one by one insert insert, then call the sql statement query insert need to be executed thousands of times, spending time long
Now using (sql, args) , you can batch insert data.
args is a list of tuples, each tuple corresponds to a piece of data in mysql.
The following are examples:
Insert 1000 orders, order details, and payments into the order, order_detail, and payment tables in the database.
The id field in the table is the pay_id field in the order table
The id field in the table is the order_id field in the order_detail table
1. Initialization properties (including host, port, user, password and 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. Connect to the database
def isConnection(self):
self.__db=(
host=self.__db_host,
port=self.__db_port,
user=self.__db_user,
password=self.__db_password,
database=self.__db_database,
charset='utf8'
)
3. Bulk insert 1000 pieces of data into the pay table.
# Insert data into the pay table
def pay_insert(self,pay_value):
try:
# Connect to the database
()
# Create cursors
global cursor
cursor=self.__db.cursor()
# Execution
('insert into `paya meter (measuring sth)`(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:
()
self.__db.commit()
self.__db.close()
# Generate the required fields for the pay table and call sql
def pay_data(self):
pay_value=list()
for i in range(1,1000):
pay_value.append((0,8800,(),str((712300000000,712399999999)),3,49338,(),49338,()))
now_time=()
self.pay_insert(pay_value)
return now_time
The 1000 pieces of data generated in the table, in order to take out the ids
# Get pay_id
def get_pay_id(self,now_time):
try:
()
global cursor
cursor=self.__db.cursor()
('select id from `paya meter (measuring sth)` where create_time >= %s',now_time)
id_value=list()
for i in range(1,1000):
pay_id=()
id_value.append(pay_id)
return id_value
except Exception as e:
print e
finally:
()
self.__db.commit()
self.__db.close()
Here is the full code:
# #!/usr/bin/python
# # -*- coding: UTF-8 -*-
import pymysql # first pip install pymysql
import random
import time
class DatabaseAcess:
# Initialize attributes (including host, port, user, password and database)
def __init__(self):
self.__db_host=XXX
self.__db_port=XXX
self.__db_user=XXX
self.__db_password=XXX
self.__db_database=XXX
# Connect to the database
def isConnection(self):
self.__db=(
host=self.__db_host,
port=self.__db_port,
user=self.__db_user,
password=self.__db_password,
database=self.__db_database,
charset='utf8'
)
# Insert data into the pay table
def pay_insert(self,pay_value):
try:
# Connect to the database
()
# Create cursors
global cursor
cursor=self.__db.cursor()
# Execution
('insert into `paya meter (measuring sth)`(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:
()
self.__db.commit()
self.__db.close()
# Generate the required fields for the pay table and call sql
def pay_data(self,data_number):
pay_value=list()
for i in range(1,data_number):
pay_value.append((0,8800,(),str((712300000000,712399999999)),3,49338,(),49338,()))
now_time=()
self.pay_insert(pay_value)
return now_time
# Get pay_id
def get_pay_id(self,now_time,data_number):
try:
()
global cursor
cursor=self.__db.cursor()
('select id from `paya meter (measuring sth)` where create_time >= %s',now_time)
id_value=list()
for i in range(1,data_number):
pay_id=()
id_value.append(pay_id)
return id_value
except Exception as e:
print e
finally:
()
self.__db.commit()
self.__db.close()
# Insert data into the order table
def order_insert(self,order_value):
try:
()
global cursor
cursor=self.__db.cursor()
('insert into `ordera meter (measuring sth)` (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:
()
self.__db.commit()
self.__db.close()
# Generate the required fields for the order table and call 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(("Zhou Yu",35999,346,"A-Town Elementary School","13322222222",130,1,pay_id,str((7100000000,7999999999)),2,8800,8800,1,49338,(),49338,(),405,121,564123698745632,"Third grade, class 3.",30,30,30))
sys_time=()
self.order_insert(order_value)
return sys_time
# Get order_id
def get_order_id(self,sys_time,data_number):
try:
()
global cursor
cursor=self.__db.cursor()
('select id from `ordera meter (measuring sth)` where create_time >= %s',sys_time)
order_id_list=list()
for i in range(1,data_number):
order_id_list.append(())
return order_id_list
except Exception as e:
print e
finally:
()
self.__db.commit()
self.__db.close()
# Insert data into order_detail table
def order_detail_insert(self,detail_value):
try:
()
global cursor
cursor=self.__db.cursor()
('insert into `order_detailsa meter (measuring sth)` (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:
()
self.__db.commit()
self.__db.close()
# Generate the required fields for the order_detail table and call 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-Town Elementary School Spring Uniforms.","1382932636506902530",8800,8800,":443/image%",1,2,49338,(),49338,()))
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}Data insertion complete".format(data_number-1))
to this article on the realization of the python database batch insertion of data (executemany use) of the article is introduced to this, more related python database batch insertion content please search my previous posts or continue to browse the following related articles I hope you will support me in the future more!