参考https://cloud.tencent.com/developer/article/1663417
增加了自己的代码,使得只需要构建内容映射字典,然后根据字典就可以直接将数据插入到数据库中
from faker import Faker
import pandas as pd
from urllib import parse
# from pymongo import MongoClient
import pymysql
import random
import numpy as np
import traceback
import sys
fake = Faker('zh_CN')
def yes_or_no():
return np.random.choice([0,1])
def money():
return round(random.uniform(10,10000),4)
# 定义fakedic,后续的所有数据都基于这个规则来生成和新建
fake_dic = {
"iana_id":fake.iana_id, # 唯一id
"date_time":fake.date_time, # 时间
"yes_or_no": yes_or_no, # 1 或者 0
"name":fake.name, # 名字
"text":fake.text, # 随机文本
"money":money, # 钱(10~10000)
"class":fake.ipv4_network_class # 类别
}
lc_bill_dic = {
"id": "iana_id", # 唯一id
"create_by":"name",# 名字
"create_time":"date_time",# 创建日期
"deteled":"yes_or_no", # 状态
"class": "class", # 类别
"rent": "money",# 日租金
"text": "text",# 备注
}
def get_df(data_dic, need_nums):
# 根据传入参数只做数据
# need_nums = 10
data_list = []
for i in range(need_nums):
data_row = {}
for key, value in data_dic.items():
data_row[key] = fake_dic[value]()
if isinstance(data_row[key], str):
data_row[key] = data_row[key].replace("\n","")[:64]
data_list.append(data_row)
return pd.DataFrame(data_list)
# 插入数据库
def deal_mysql(table_name, data_df):
# 打开数据库连接
db = pymysql.connect(host="localhost", user="root", password="root", database="MyTest")
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# SQL 插入语句
## 拼接sql插入语句
columns = list(data_df.columns)
sql_columns = f""" `{"`,`".join(columns)}` """
sql_placeholder = ",".join(["'%s'"] * len(columns))
for each in data_df.to_dict("index").values():
sql_data = tuple([each[column] for column in columns])
# print(sql_placeholder)
# print(sql_data)
sql = f"insert into {table_name} ({sql_columns}) value ({sql_placeholder})"%sql_data
a = sql
# print(sql)
try:
# 执行sql语句
cursor.execute(sql)
# 执行sql语句
db.commit()
print("insert ok")
except:
# 发生错误时回滚
exc_type, exc_value, exc_traceback = sys.exc_info()
print("exc_type:",exc_type)
print("exc_value:",exc_value)
print("exc_traceback:",exc_traceback)
db.rollback()
# 关闭数据库连接
cursor.close()
deal_mysql("bill", get_df(lc_bill_dic, 20))