使用python将excel数据导入数据库

  • 因为需要对数据处理,将excel数据导入到数据库,记录一下过程。
  • 使用到的库:xlrd 和 pymysql (如果需要写到excel可以使用xlwt)
  • 直接丢代码,使用python3,注释比较清楚。
  • import xlrd
    import pymysql
    # import importlib
    # importlib.reload(sys) #出现呢reload错误使用
    
    
    def open_excel():
        try:
            book = xlrd.open_workbook("XX.xlsx")  #文件名,把文件与py文件放在同一目录下
        except:
            print("open excel file failed!")
        try:
            sheet = book.sheet_by_name("sheet名称")   #execl里面的worksheet1
            return sheet
        except:
            print("locate worksheet in excel failed!")
    
    
    #连接数据库
    try:
        db = pymysql.connect(host="127.0.0.1",user="root",
            passwd="XXX",
            db="XXX",
            charset='utf8')
    except:
        print("could not connect to mysql server")
    
    def search_count():
        cursor = db.cursor()
        select = "select count(id) from XXXX" #获取表中xxxxx记录数
        cursor.execute(select) #执行sql语句
        line_count = cursor.fetchone()
        print(line_count[0])
    
    
    def insert_deta():
        sheet = open_excel()
        cursor = db.cursor()
        for i in range(1, sheet.nrows): #第一行是标题名,对应表中的字段名所以应该从第二行开始,计算机以0开始计数,所以值是1
    
            name = sheet.cell(i,0).value #取第i行第0列
            data = sheet.cell(i,1).value#取第i行第1列,下面依次类推
            print(name)
            print(data)
            value = (name,data)
            print(value)
            sql = "INSERT INTO XXX(name,data)VALUES(%s,%s)"
            cursor.execute(sql,value) #执行sql语句
            db.commit()
        cursor.close() #关闭连接
    
    
    
    insert_deta()
    
    db.close()#关闭数据
    print ("ok ")
    

      XXX里自行修改自己的名称。

  • 说明:对于不规则的单元格,例如合并过的单元格会取到空值。
  • 有机会把数据库写到excel贴上来。
  •  
  •  
  •  优化了一下这个程序
  • import pymysql
    import xlrd
    
    
    # 连接数据库
    try:
        db = pymysql.connect(host="127.0.0.1", user="root",
                             passwd="XXX",
                             db="XXX",
                             charset='utf8')
    except:
        print("could not connect to mysql server")
    
    
    def open_excel():
        try:
            book = xlrd.open_workbook("XXX.xlsx")  #文件名,把文件与py文件放在同一目录下
        except:
            print("open excel file failed!")
        try:
            sheet = book.sheet_by_name("XXX")   #execl里面的worksheet1
            return sheet
        except:
            print("locate worksheet in excel failed!")
    
    
    def insert_deta():
        sheet = open_excel()
        cursor = db.cursor()
        row_num = sheet.nrows
        for i in range(1, row_num):  # 第一行是标题名,对应表中的字段名所以应该从第二行开始,计算机以0开始计数,所以值是1
            row_data = sheet.row_values(i)
            value = (row_data[0],row_data[1],row_data[2],row_data[3])
            print(i)
            sql = "INSERT INTO demo_yangben(xxx,xxxx,xxxx,xxxx)VALUES(%s,%s,%s,%s)"
            cursor.execute(sql, value)  # 执行sql语句
            db.commit()
        cursor.close()  # 关闭连接
    
    
    open_excel()
    insert_deta()
    

      

  •  
  •  
  •  
  •  
  •  
  •  再改一下,每一万条数据写入到数据库一次
  • 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
    
        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 = []  # 定义列表用来存放数据
            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!")
    
        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')
    

      

  •  
  •  
  • 思考,如果数据插入有错误,怎么解决,
  •  其实有很多数据库工具可以直接来解决这个问题,注意字符转换的格式就好。
  •  
  •  上面这个程序在当数据不足10000的时候会出现问题,下面的一篇文章里进行了更正。
  • 批量插入数据请看: https://www.cnblogs.com/longbigbeard/p/9317141.html
  • 以上。
posted @ 2018-07-14 12:06  大长胡子  阅读(...)  评论(...编辑  收藏