2022年 11月 9日

Python写入数据库

需求描述:
模拟个人信息的数据:姓名(长度(6-12位,由大小写
字母数字)),性别(男女随机),年龄(18-50随机),邮箱(姓名@163.com);将获取的数据存储在数据库中,当文件数据量过大时,
存储文件会打开失败且占用较大的内存空间
  • 1
  • 2
  • 3
问题分析:
创建个人信息比较简单
  • 1
    写入数据库步骤:
        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()
    
用时大概两秒,可以接受。
    

        

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97
  • 98
  • 99
  • 100
  • 101
  • 102
  • 103
  • 104
  • 105
  • 106
  • 107
  • 108
  • 109
  • 110
  • 111
  • 112
  • 113
  • 114
  • 115
  • 116
  • 117
  • 118
  • 119
  • 120
  • 121
  • 122
  • 123
  • 124
  • 125
  • 126
  • 127
  • 128
  • 129
  • 130
  • 131
  • 132
  • 133
  • 134
  • 135
  • 136
  • 137
  • 138
  • 139
  • 140
  • 141
  • 142
  • 143
  • 144
  • 145
  • 146
  • 147
  • 148
  • 149
  • 150
  • 151
  • 152
  • 153
  • 154
  • 155
  • 156
  • 157
  • 158
  • 159
  • 160
  • 161
  • 162
  • 163
  • 164
  • 165
  • 166
  • 167
  • 168
  • 169
  • 170
  • 171
  • 172
  • 173
  • 174
  • 175
  • 176
  • 177
  • 178
  • 179
  • 180
  • 181
  • 182
  • 183
  • 184
  • 185
  • 186
  • 187
  • 188
  • 189
  • 190
  • 191
  • 192
  • 193
  • 194
  • 195
  • 196
  • 197
  • 198
  • 199
  • 200
  • 201
  • 202
  • 203
  • 204
  • 205
  • 206
  • 207
  • 208
  • 209
  • 210
  • 211
  • 212
  • 213
  • 214
  • 215
  • 216
  • 217
  • 218
  • 219
  • 220