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

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

    • 因为需要对数据处理，将excel数据导入到数据库，记录一下过程。
    • 使用到的库：xlrd 和 pymysql （如果需要写到excel可以使用xlwt）
    • 直接丢代码，使用python3，注释比较清楚。
    • 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
      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贴上来。
    •  
    •  
    •  优化了一下这个程序
    • 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
      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()

      　　

    •  
    •  
    •  
    •  
    •  
    •  再改一下，每一万条数据写入到数据库一次
    • 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
      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')

      　　

    •  
    •  
    • 思考，如果数据插入有错误，怎么解决，
    •  其实有很多数据库工具可以直接来解决这个问题，注意字符转换的格式就好。
    •  
    •  
    • 批量插入数据请看： https://www.cnblogs.com/longbigbeard/p/9317141.html
  • 相关阅读:
    A1049. 命题逻辑
    矩形面积交：输出0.00
    完美的代价
    枚举孪生素数对
    改变参数的两种方法
    二面准备：React、Typescript、其他基础补充
    【TypeScript】基础及问题汇总
    【React】做一个百万答题小项目
    【React】相关题目总结
    【React】半小时深刻理解《半小时深刻理解React》（老套娃了）
  • 原文地址：https://www.cnblogs.com/MaxElephant/p/10175338.html
Copyright © 2020-2023  润新知