生成数据系列
第一章 python生成随机关联数据存入mysql
前言
软件测试过程中常需要生成大量测试数据,并且这些数据之间有一定的关联
需求
使用python实现以下功能
- 插入mysql 1万条数据,
- 数据类型 id1 id2 score
- id1 为1-600随机数 id2为从文件列表中读取的随机数
- score为1-10直接随机数 保留小数点后2位
分析
要实现这个功能,可以使用Python的random模块来生成随机数,
并使用pymysql或mysql-connector-python等库来与MySQL数据库交互。
#开发
过程分解
从文件中随机读取数据
文本格式
1000058
1000047
1000024
1000044
1000090
1000096
1000006
1000038
100006
……
读取数据转为列表
def read_id2_from_file(filename):
with open(filename, 'r') as file:
return [line.strip() for line in file]
保证随机
selected_id2s = random.sample(id2_list, 60)
多重随机
从大约6000个ID的文件中随机抽取一定数量的ID(例如60到100个)
def read_ids_from_file(filename):
# 读取文件中的所有ID
ids = []
with open(filename, 'r') as file:
for line in file:
id = line.strip()
if id: # 检查行是否为空或只有空白字符
ids.append(id)
return ids
def select_random_ids(ids, min_count, max_count):
# 随机选择ID的数量
count = random.randint(min_count, max_count)
# 从ID列表中随机选择指定数量的ID
selected_ids = random.sample(ids, count)
return selected_ids
数据入库
connection = pymysql.connect(
host='127.0.0.1',
user='test',
password= 'test',
database='test',
port=3306,
cursorclass=pymysql.cursors.DictCursor
)
try:
with connection.cursor() as cursor:
sql = "INSERT INTO your_table (id1, id2, score) VALUES (%s, %s, %s)"
cursor.executemany(sql, data)
connection.commit()
finally:
connection.close()
完整代码
import pymysql
import random
def read_ids_from_file(filename):
# 读取文件中的所有ID
ids = []
with open(filename, 'r') as file:
for line in file:
id = line.strip()
if id: # 检查行是否为空或只有空白字符
ids.append(id)
return ids
def select_random_ids(ids, min_count, max_count):
# 随机选择ID的数量
count = random.randint(min_count, max_count)
# 从ID列表中随机选择指定数量的ID
selected_ids = random.sample(ids, count)
return selected_ids
def generate_random_data(id1,id2_list, num_records=10000):
data = []
for id2 in id2_list:
score = round(random.uniform(1, 10), 2) # 保留两位小数
data.append((id1, id2, score))
return data
def insert_into_mysql(data):
connection = pymysql.connect(
host='127.0.0.1',
user='test',
password= 'test',
database='test',
port=3306,
cursorclass=pymysql.cursors.DictCursor
)
try:
with connection.cursor() as cursor:
sql = "INSERT INTO user_rate (uid, mediaid, score) VALUES (%s, %s, %s)"
cursor.executemany(sql, data)
connection.commit()
finally:
connection.close()
if __name__ == '__main__':
for id1 in range(1, 11):
filename = 'ids.txt'
min_count = 6
max_count = 12
ids = read_ids_from_file(filename)
id2_list = select_random_ids(ids, min_count, max_count)
print(f"ID 1 {id1} Selected ID2s:")
for id in id2_list:
print(id)
data = generate_random_data(id1,id2_list)
print(data)
insert_into_mysql(data)