faker可以用来批量构造测试数据。首先安装:pip install Faker
具体用法参考:https://zhuanlan.zhihu.com/p/87203290
这里主要是讲下利用faker批量生成测试数据,并保存到数据库。
如下图,创建了一个person表,里面有id,name,address,birthdate。这几个字段,id是自增属性。
下面利用faker生成测试数据后,再调用pymysql将生成的数据存储到数据库对应的person表里面。
效果如图:
demo如下:
import pymysql from faker import Faker faker = Faker("zh_CN") # 中文数据 def insert_data(): conn = pymysql.connect(host='localhost', port=3306, user='root', password='root', database='school') cursor = conn.cursor() # SQL 插入语句 sql = "INSERT INTO person(name,address,birthdate)VALUES (%s,%s,%s)" person_data = [] for i in range(100): person_info = (faker.name(), faker.address(), faker.date()) person_data.append(person_info) print(person_data) try: # 执行sql语句 cursor.executemany(sql, person_data) # todo 批量生成数据用executemany # 提交到数据库执行 conn.commit() print("插入数据完成...") except Exception as e: # 如果发生错误则回滚 conn.rollback() raise e finally: conn.close() # 关闭数据库连接 insert_data()
封装后代码:
import pymysql from faker import Faker class InsertData(): def __init__(self, host, port, user, password, database): '''连接初始化''' self.host = host self.port = port self.user = user self.password = password self.database = database try: self.conn = pymysql.connect(host=self.host, port=self.port, user=self.user, password=self.password, database=self.database) self.cursor = self.conn.cursor() print("连接数据库成功...") except Exception as e: raise e def get_data(self, number): '''获取测试数据''' faker = Faker("zh_CN") # 中文数据 self.number = number person_data = [] for i in range(self.number): person_info = (faker.name(), faker.address(), faker.date()) person_data.append(person_info) # print(person_data) return person_data def execute(self, sql, data): '''执行sql''' self.cursor.executemany(sql, data) self.conn.commit() print("插入{}条数据完成...".format(self.number)) def __del__(self): '''关闭连接资源''' self.cursor.close() self.conn.close() print('关闭数据库连接...') if __name__ == '__main__': db = InsertData('localhost', 3306, 'root', 'root', 'school') sql = "INSERT INTO person(name,address,birthdate)VALUES (%s,%s,%s)" db.execute(sql, db.get_data(100))
生成10万条测试数据如图: