使用python插入5000w数据到mysql

应用场景，数据库数据达到千万以后，做数据分析执行脚本开始变慢，所以用到clickhouse，先插入5000w数据测试

先安装pip支持mysql.connector,我安装的python3.11
pip3 install mysql-connector-python
再使用pip3 list查看是否安装成功

以下是mysql创建表语句

CREATE TABLE test_table (
id int(11) unsigned NOT NULL AUTO_INCREMENT,
user_id int(11) DEFAULT NULL,
recharge_amount int(11) DEFAULT NULL,
bonus decimal(12,2) DEFAULT NULL,
message varchar(255) DEFAULT NULL,
content varchar(255) DEFAULT NULL,
remark varchar(255) DEFAULT NULL,
order_id varchar(32) DEFAULT NULL,
create_time timestamp NULL DEFAULT CURRENT_TIMESTAMP,
update_time timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=50000001 DEFAULT CHARSET=utf8;

python代码如下:

import mysql.connector
import random
import string
import datetime

MySQL 连接信息

config = {
'user': 'root',
'password': 'root',
'host': '127.0.0.1',
'database': 'db1',
}

生成模拟数据

def generate_data(batch_size=10000):
for i in range(batch_size):
# 生成模拟数据
yield [random_string(32),random_string(16),random_string(64),getTimeStr(),random.randint(1000000, 9999999)]

生成随机字符串

def random_string(length):
letters = string.ascii_lowercase + string.digits
return ''.join(random.choice(letters) for i in range(length))

def getTimeStr():
# 获取当前日期和时间
now = datetime.datetime.now()
# 格式化为YmdHis格式
formatted_time = now.strftime("%Y%m%d%H%M%S")
# 将日期和时间转换为整数
timestamp = int(now.timestamp())
return formatted_time+str(timestamp)

def insertData():
connection = mysql.connector.connect(**config)
cursor = connection.cursor()
# 数据总量
total = 50000000

每批次插入的数据量

batch_size = 100000

循环插入数据

for i in range(0, total, batch_size):
# 准备SQL语句，插入一批数据到表格中
sql = "INSERT INTO test_table (message, content, remark, order_id,user_id,recharge_amount,bonus) VALUES "
values = []
for j in range(batch_size):
values.append((random_string(32),random_string(16),random_string(64),getTimeStr(),random.randint(1000000, 9999999),random.randint(1, 10000),random.uniform(1.0, 100.0)))
sql += ", ".join(["(%s, %s,%s,%s,%s,%s,%s)"] * len(values))
val = [item for sublist in values for item in sublist]
cursor.execute(sql, val)

提交事务

connection.commit()

输出当前进度信息

print("{:.2f}% 完成".format((i + batch_size) / total * 100))

print(cursor.rowcount, "记录插入成功。")

if name == 'main':
insertData()