Python写入数据库

2022年 11月 5日

需求描述：

模拟个人信息的数据：姓名（长度（6-12位，由大小写
字母数字）），性别（男女随机），年龄（18-50随机），邮箱（姓名@163.com）;将获取的数据存储在数据库中，当文件数据量过大时，
存储文件会打开失败且占用较大的内存空间

问题分析：

创建个人信息比较简单

    写入数据库步骤：
        1.导入模块：import pymysql
        2.建立连接：conn = pymysql.connect("host"=host, "user"=user, "password"=password, "db"=database)
        3.建立游标：conn.cursor()
        4.输入查询语句：“insert into table_name(age int, name varchar) values(18,"zhangsan")”
        5.执行查询语句：conn.cuesor.execute(sql)
        6.提交事务：conn.commit()
        7.关闭游标连接：conn.cursor.close();conn.close()
##### 问题解决：
    通过定义模块化，方便拓展
    定义创建表，使用不定长方式，方便字段的增加：
    
   ...'def create_table(self, table_name, *args):
        table_seg1 = ""
        for i in args:
            table_seg1 +=i
            if i != args[len(args)-1]:
                table_seg1 +=","
            # print(i)
        create_tab_sql = "CREATE TABLE %s (%s)"%(table_name, table_seg1)
        try:
            self.cursor.execute(create_tab_sql)
            # print(create_tabl_sql)
        except:print("创建表出错，请检查")'
        
    程序段说明：*args表示不定长（参数个数不定）参数，不定长参数遵循：在定义中使用函数时打包成一个元组,在调用时解压为对应量；上面的for循环便是这个意义，将打包的元组解为设置字段的字符串。
    定义插入数据方法
        ...def insert_table(self, table_name, keys, values,dict1):
        '''插入数据的方法'''
        insert_data_sql = 'INSERT INTO {table_name}({keys}) values ({value})'\
            .format(table_name=table_name, keys=keys, value=values)
        try:
            self.cursor.execute(insert_data_sql, tuple(dict1.values()))
            # print("插入数据成功！")
            self.conn.commit()
        except:
            print('fail!')
            self.conn.rollback()
        #print(insert_data_sql)'
    采用格式化输入，语句中的参数是一个元组
    定义删除表的方法：线查询需要删除的表是否存在，如果存在，则先备份表，再执行删除命令，这里备份命令有错误，原因暂时未知。
        def drop_table(self, table_name):
        '''删除表的方法'''
        # 查询是否存在此表
        check_table_sql = "SHOW TABLES;"
        drop_table_sql = "DROP TABLE IF EXISTS %s"%table_name
        backup_table_sql = "MYSQLDUMP -u root -p {databse} {table_name} > {tabel_name_bck} ".format(databse='usermessage',table_name=table_name, tabel_name_bck=table_name+'bck.sql'  )
        self.cursor.execute(check_table_sql)
        tables = self.cursor.fetchone() #以序列的序列方式返回余下所有行
        # print(tables)
        try:
            for i in tables:
                if i == table_name:
                    # self.cursor.execute(backup_table_sql)  #备份表会出错，问题暂时未知
                    self.cursor.execute(drop_table_sql)
                # 表进行备份
                # 执行删除命令
                else:print("此前未新建过相同表")
        except:print("删除表失败！")
        
        
##### 程序：
    完整程序：
    
    import pymysql
    from random import *
    import sys
    import time
    
    
    class MenMessage:
        '''
        利用randrange生成姓名长度；利用sample生成姓名样本，循环10000次
        '''
        def __init__(self, seg):
            self.seg = seg
        def name(self):
            # 生成姓名长度
            name_leng = randrange(6, 13)
            name = sample(self.seg, name_leng)
            return name
        def sex(self):
            # 性别：1：男；2：女
            sex = randrange(1, 3)
            if sex == 1:
                return '男'
            else:return '女'
        def age(self):
            age = randrange(18,51)
            return age

    class Table_OP:
        '''
        
        '''
    
        def __init__(self, database):
            self.conn = pymysql.connect(host='localhost', user='root', db=database)
            self.cursor = self.conn.cursor()
    
        def create_table(self, table_name, *args):
            # https://blog.csdn.net/u012102306/article/details/52250028
            table_seg1 = ""
            for i in args:
                table_seg1 +=i
                if i != args[len(args)-1]:
                    table_seg1 +=","
                # print(i)
            create_tab_sql = "CREATE TABLE %s (%s)"%(table_name, table_seg1)#这里的问题是：使用不定长参数写入字段，python传入的是一个元组，建表语句的字符串会有引号；问题就在于使用什么方法将args元组内的引号去除
            # 使其成为MySQL可执行的语句。(解决思路：序列拼接（相加）)
            try:
                self.cursor.execute(create_tab_sql)
                # print(create_tabl_sql)
            except:print("创建表出错，请检查")
    
        def drop_table(self, table_name):
            '''删除表的方法'''
            # 查询是否存在此表
            check_table_sql = "SHOW TABLES;"
            drop_table_sql = "DROP TABLE IF EXISTS %s"%table_name
            backup_table_sql = "MYSQLDUMP -u root -p {databse} {table_name} > {tabel_name_bck} ".format(databse='usermessage',table_name=table_name, tabel_name_bck=table_name+'bck.sql'  )
            self.cursor.execute(check_table_sql)
            tables = self.cursor.fetchone() #以序列的序列方式返回余下所有行
            # print(tables)
            try:
                for i in tables:
                    if i == table_name:
                        # self.cursor.execute(backup_table_sql)  #备份表会出错，问题暂时未知
                        self.cursor.execute(drop_table_sql)
                    # 表进行备份
                    # 执行删除命令
                    else:print("此前未新建过相同表")
            except:print("删除表失败！")
    
        def insert_table(self, table_name, keys, values,dict1): #https://www.cnblogs.com/xiao-xue-di/p/11570451.html
            '''插入数据的方法'''
            insert_data_sql = 'INSERT INTO {table_name}({keys}) values ({value})'\
                .format(table_name=table_name, keys=keys, value=values)
            try:
                self.cursor.execute(insert_data_sql, tuple(dict1.values()))
                # print("插入数据成功！")
                self.conn.commit()
            except:
                print('fail!')
                self.conn.rollback()
            #print(insert_data_sql)
    
        def insert_tablemany(self, table_name, list_rows):
            '''将多条数据插入'''
            #insert_many_sql = "INSERT INTO %s values (%r,%r,%r,%r)"%(table_name, list_rows)
            #print(insert_many_sql)
            try:
    
                self.cursor.executemany("INSERT INTO usermessage values (%s,%s,%s,%s)", list_rows)
                self.conn.commit()
            except:
                print("fail!")
                self.conn.rollback()
        def close_link(self):
            '''关闭连接'''
            self.cursor.close()
            self.conn.close()
    
        ''''''
        start = time.perf_counter()
        list_rows = []
        DATA = range(1, 10001)
        database_name = 'usermessage'
        table_name = 'usermessage' # 表名称
        table_seg =('name varchar(12) NOT NULL', "Email varchar(20)", "age int", "sex char(4)")
        table = Table_OP(database_name)
        table.drop_table(table_name)
        table.create_table(table_name, *table_seg)  # 不定长参数：定义组成元组，调用解元组
        for i in DATA:
            men = MenMessage(
                seg=['W', 'E', 'R', 'T', 'Y', 'U', 'I', 'O', 'P', 'A', 'S', 'D', 'F', 'G', 'H', 'J', 'K', 'L', 'Z', 'X',
                     'C', 'V',
                     'B', 'N', 'M', 'q', 'w', 'e', 'r', 't', 'y', 'u', 'i', 'o', 'p', 'a', 's', 'd', 'f', 'g', 'h', 'j',
                     'k', 'l',
                     'z', 'c', 'v', 'n', 'm', '1', '2', '3', '4', '5', '6', '7', '8', '9', '0']
            )
            # 将列表强制转换成字符串
            name = ''.join(men.name())
            sex = men.sex()
            age = men.age()
            char1 = '{"name": name, "email": name+"@163.com" , "age": age, "sex": sex}'
            list_row = ((name, name+"@163.com" , age,sex),)
            #print(type(list_row))
            list_rows +=list_row
            #print(list_rows)
            #print(type(char1))
            dict1 = eval(char1)
            keys = ','.join(dict1.keys())
            values = ', '.join(['%s']*len(dict1.keys()))
            # print(keys)
            # print(values)
            # table.insert_table(table_name, keys, values, dict1)
        #table.insert_tablemany(table_name,list_rows)
        table.close_link()
        print("写入{DATA}条数据，耗时{time}秒".format(DATA=i, time=time.perf_counter()-start))
        
        执行完成后，发现写入1000条数据大约需要12S；思考改进程序，每次执行生成一条信息便执行一个插入语句，思考，将10000条数据集中插入：
        
         def insert_tablemany(self, table_name, list_rows):
            '''将多条数据插入'''
            #insert_many_sql = "INSERT INTO %s values (%r,%r,%r,%r)"%(table_name, list_rows)
            #print(insert_many_sql)
            try:
    
                self.cursor.executemany("INSERT INTO usermessage values (%s,%s,%s,%s)", list_rows)
                self.conn.commit()
            except:
                print("fail!")
                self.conn.rollback()
    
用时大概两秒，可以接受。