How to implement batch new data in Mysql database in python

1. Batch data insertion scenario

• When performing data pressure, a large amount of data needs to be tested

• For example, logging in Thousands of users log in at the same time

• For example, data processing requires us to insert database data because the source data is not available

• Selection method

• Use Jmeter to add interface data in batches

• Use stored procedures for direct database operations

• Use Python performs database operations

2. Selection of tools for inserting data

The selection method should be selected according to the actual situation. It is not which one is better, but which one A more efficient way to solve our problems, for example;

• When we need Jmeter operations to actually add users in batches, and users need to upload images, what should we do at this time? The choice?

• If we choose to use python, we may need to read file operations, obtain names, fill in various fields, etc., which will be more troublesome

• If you choose a stored procedure, it is a bit unrealistic. How to use a stored procedure to upload avatars?

So we prefer to choose a simple and convenient one, which is our jmeter

Back to our topic, if we want to batch insert data, what should we choose? Well, just for database operations, we can actually choose either stored procedures or python. I personally prefer python because it is commonly used for automation and is more convenient, so the follow-up will use python as an example to explain the database.

3. Select Python for batch insertion

Take the local Mysql database as an example

#安装操作数据库的第三方包
C:\Users\Lenovo> pip install pymsql
#全文使用Pycharm进行操作
------------------------------------------------
#导入数据库操作包
import pymysql
#数据库的基本信息[主机、用户名、密码、端口号、连接的数据库]
Host= '127.0.0.1'
user = 'root'
pwd='123456'
port = 3306
database ='sq'
#进行创建数据库的连接
conn = pymysql.connect(host=Host,user=user,password=pwd,port=port,db=database)
#获取游标
cursor = conn.cursor()
#使用format对sql语句进行参数化
sql = "insert into takeout_food values('0{j}','testautoinsert{i}','10','this is auto test','17.jpg');"
#执行数据库的插入语句
j=17
for i in range(1,10):
    j+=1
    data = cursor.execute(sql.format(i=i,j=j))
#连接实例进行数据的提交    
conn.commit()
#关闭游标
cursor.close()

The data is displayed as follows:

The above is the detailed content of How to implement batch new data in Mysql database in python. For more information, please follow other related articles on the PHP Chinese website!