使用python，将excel数据批量导入数据库

￡神魔★判官ぃ 2022-03-09 00:26 437阅读 0赞

*  这是上一篇文章的优化版本，相较于一条一条的执行sql语句，本文中，将excel中所有的数据先写到list列表中
 *  在通过函数
    
    <table> 
     <tbody> 
      <tr> 
       <td> <p>1</p> </td> 
       <td> <p><code>cursor.executemany(sql,&nbsp;</code><code>list</code><code>）</code></p> </td> 
      </tr> 
     </tbody> 
    </table>
    
    一次性写入到数据库中
 *      import pymysql
        import xlrd
         
         
        '''
            连接数据库
            args：db_name（数据库名称）
            returns:db
             
        '''
        def mysql_link(de_name):
            try:
                db = pymysql.connect(host="127.0.0.1", user="root",
                                     passwd="XXX",
                                     db=de_name,
                                     charset='utf8')
                return db
            except:
                print("could not connect to mysql server")
         
         
        '''
            读取excel函数
            args：excel_file（excel文件，目录在py文件同目录）
            returns：book
        '''
        def open_excel(excel_file):
            try:
                book = xlrd.open_workbook(excel_file)  #文件名，把文件与py文件放在同一目录下
                return book
            except:
                print("open excel file failed!")
         
         
        '''
            执行插入操作
            args:db_name（数据库名称）
                 table_name(表名称）
                 excel_file（excel文件名，把文件与py文件放在同一目录下）
                  
        '''
        def store_to(db_name,table_name,excel_file):
            db = mysql_link(db_name)  # 打开数据库连接
            cursor = db.cursor()  # 使用 cursor() 方法创建一个游标对象 cursor
         
            book = open_excel(excel_file)   # 打开excel文件
            sheets = book.sheet_names()   # 获取所有sheet表名
            for sheet in sheets:
                sh = book.sheet_by_name(sheet)   # 打开每一张表
                row_num = sh.nrows
                print(row_num)
                list = []   # 定义列表用来存放数据
                for i in range(1, row_num):  # 第一行是标题名，对应表中的字段名所以应该从第二行开始，计算机以0开始计数，所以值是1
                    row_data = sh.row_values(i)  # 按行获取excel的值
                    value = (row_data[0], row_data[1],row_data[2],row_data[3],row_data[4],row_data[5],\
                             row_data[6],row_data[7],row_data[8],row_data[9],row_data[10],row_data[11],row_data[12],row_data[13],row_data[14])
                    list.append(value)  # 将数据暂存在列表
                    # print(i)
                sql = "INSERT INTO "+ table_name + " ( bj_shijian,bjr_xingbie,anfa_didian,\
                    zb_x,zb_y,bj_chongfu,jiejing_lb_name,baojing_lb_name,baojing_lx_name,baojing_lx_xl_name,\
                    guanxia_qy_name,guanxian_dw_name,anfa_qulu,anfa_xiaoqu,chujing_dw_name)VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
                cursor.executemany(sql, list)  # 执行sql语句
                db.commit()  # 提交
                list.clear()  # 清空list
                print("worksheets: " + sheet + " has been inserted " + str(row_num) + " datas!")
            cursor.close()  # 关闭连接
            db.close()
         
         
        if __name__ == '__main__':
            store_to('demo','demo_yangben','qh.xlsx')
    
     
 *  可以选择传入的数据库，及数据库表名，excel文件名（excel文件和py文件同目录，否则加上路径）
 *   每一万条写入一次
 *      import pymysql
        import xlrd
        import sys
          
        '''
            连接数据库
            args：db_name（数据库名称）
            returns:db
          
        '''
          
          
        def mysql_link(de_name):
            try:
                db = pymysql.connect(host="127.0.0.1", user="xxx",
                                     passwd="xxx",
                                     db=xxx,
                                     charset='utf8')
                return db
            except:
                print("could not connect to mysql server")
          
          
        '''
            读取excel函数
            args：excel_file（excel文件，目录在py文件同目录）
            returns：book
        '''
          
          
        def open_excel(excel_file):
            try:
                book = xlrd.open_workbook(excel_file)  # 文件名，把文件与py文件放在同一目录下
                print(sys.getsizeof(book))
                return book
            except:
                print("open excel file failed!")
          
          
        '''
            执行插入操作
            args:db_name（数据库名称）
                 table_name(表名称）
                 excel_file（excel文件名，把文件与py文件放在同一目录下）
          
        '''
          
          
        def store_to(db_name, table_name, excel_file):
            db = mysql_link(db_name)  # 打开数据库连接
            cursor = db.cursor()  # 使用 cursor() 方法创建一个游标对象 cursor
            list = []  # 定义列表用来存放数据
         
         
            book = open_excel(excel_file)  # 打开excel文件
            sheets = book.sheet_names()  # 获取所有sheet表名
            for sheet in sheets:
                sh = book.sheet_by_name(sheet)  # 打开每一张表
                row_num = sh.nrows
                print(row_num)
                 
                num = 0  # 用来控制每次插入的数量
                for i in range(1, row_num):  # 第一行是标题名，对应表中的字段名所以应该从第二行开始，计算机以0开始计数，所以值是1
                    row_data = sh.row_values(i)  # 按行获取excel的值
                    value = (row_data[0], row_data[1], row_data[2], row_data[3], row_data[4], row_data[5], \
                             row_data[6], row_data[7], row_data[8], row_data[9], row_data[10], row_data[11], row_data[12],
                             row_data[13], row_data[14])
                    list.append(value)  # 将数据暂存在列表
                    num += 1
                    if( num>= 10000 ):  # 每一万条数据执行一次插入
                        print(sys.getsizeof(list))
                        sql = "INSERT INTO " + table_name + " (time, xingbie, afdd, xzb, yzb, cfbj, jjlbmc, \
                        bjlbmc, bjlxmc, bjlxxlmc, gxqymc,gxdwmc, afql, afxqxx, cjdwmc)\
                        VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
                        cursor.executemany(sql, list)  # 执行sql语句
          
                        num = 0  # 计数归零
                        list.clear()  # 清空list
                        print("worksheets: " + sheet + " has been inserted 10000 datas!")<br>        # 将剩下不足10000的数据执行插入　　
                sql = "INSERT INTO " + table_name + " (time, xingbie, afdd, xzb, yzb, cfbj, jjlbmc, \
                        bjlbmc, bjlxmc, bjlxxlmc, gxqymc,gxdwmc, afql, afxqxx, cjdwmc)\
                        VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
                cursor.executemany(sql, list)  # 执行sql语句
                print("worksheets: " + sheet + " has been inserted "+len(list)+" datas!")
                list.clear()  # 清空list
            print("worksheets: " + sheet + " has been inserted " + str(row_num) + " datas!")
            db.commit()  # 提交
            cursor.close()  # 关闭连接
            db.close()
          
          
        if __name__ == '__main__':
            store_to('demo', 'demo_yangben', 'xxx.xlsx')
    
    　sys.getsizeof（）方法监测内存占用