python操作数据之批量添加数据

import pymysql
import random
import time
from datetime import datetime

type_dict = {'测试01':'001', '测试02':'002', '测试03':'003', '测试04':'004'}
fid_start = 100
fproduct_id_list = ['01','02','03','04','05','06','07','08']

class Sqldriver(object):
    # 初始化属性
    def __init__(self):
        self.host = '*****'
        self.port = 3306
        self.user = 'root'
        self.password = '*****'
        self.database = '******'

    # 连接数据库
    def Connect(self):
        self.con = pymysql.connect(
            host=self.host,
            port=self.port,
            user=self.user,
            password=self.password,
            database=self.database,
            charset='utf8'
        )

    # 插入数据
    def insert(self, fid, fdate, fproduct_id, fcost_time, fbus_type, fbus_name, fpmoney):
        try:
            # 连接数据库
            self.Connect()
            # 创建游标
            global cursor
            cursor = self.con.cursor()

            # 查看表库是否存在
            try:
                cursor.execute('create database microsite')
            except:
                print('Database microsite exists!')

            # 查看表是否存在
            try:
                cursor.execute("CREATE TABLE IF NOT EXISTS\
                                Chi(id INT PRIMARY KEY AUTO_INCREMENT,\
                                    fid VARCHAR(255),\
                                    fdate VARCHAR(255),\
                                    fproduct_id VARCHAR(255),\
                                    fcost_time VARCHAR(255),\
                                    fbus_type VARCHAR(255),\
                                    fbus_name VARCHAR(255),\
                                    fpmoney VARCHAR(255))")
            except:
                print('The table test exists!')
            # sql命令
            sql = "insert into Chi(fid, fdate, fproduct_id, fcost_time, fbus_type, fbus_name, fpmoney)" \
                  "values(%s,%s,%s,%s,%s,%s,%s)"
            # 执行sql语句
            cursor.execute(sql, (fid, fdate, fproduct_id, fcost_time, fbus_type, fbus_name, fpmoney))
        except Exception as e:
            print(e)
        finally:
            cursor.close()
            self.con.commit()
            self.con.close()

    # 生成随机日期
    def MkDate(self):
        a1 = (2016, 1, 1, 0, 0, 0, 0, 0, 0)
        a2 = (2019, 12, 31, 23, 59, 59, 0, 0, 0)

        start = time.mktime(a1)  # time.mktime(t) t -- 结构化的时间或者完整的9位元组元素
        end = time.mktime(a2)

        for i in range(10):
            t = random.randint(start, end)
            date_touple = time.localtime(t)
            date = time.strftime("%Y-%m-%d", date_touple)
            print(type(date))
            return date

    # 数据生成并调用数据插入方法
    def data_make(self):
        fid = fid_start
        fdate = self.MkDate()
        fproduct_id = random.choice(fproduct_id_list)
        fcost_time = str(round(random.uniform(1, 100), 2))  #  方法返回浮点数x的四舍五入值,保留两位小数。
        fbus_type = random.choice(list(type_dict.keys()))
        fbus_name = str(type_dict[fbus_type])
        fpmoney = str(random.randint(10000, 99999999))

        self.insert(fid, fdate, fproduct_id, fcost_time, fbus_type, fbus_name, fpmoney)
        fid = fid + 1


if __name__ == '__main__':
    db = Sqldriver()
    # for循环
    for record in range(3, 10):
        db.data_make()
        fid_start +=1