一聚教程网:一个值得你收藏的教程网站

最新下载

热门教程

使用python将excel数据导入数据库的教程

时间:2019-08-27 编辑:猪哥 来源:一聚教程网

因为需要对数据处理,将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里自行修改自己的名称。

说明:对于不规则的单元格,例如合并过的单元格会取到空值。

优化了一下这个程序

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')

思考,如果数据插入有错误,怎么解决,

其实有很多数据库工具可以直接来解决这个问题,注意字符转换的格式就好。

热门栏目