使用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里自行修改自己的名称。

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

优化了一下这个程序

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

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

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

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持我们。

(0)

相关推荐

  • python读取并定位excel数据坐标系详解

    测试数据:坐标数据:testExcelData.xlsx 使用python读取excel文件需要安装xlrd库: xlrd下载后的压缩文件:xlrd-1.2.0.tar.gz 解压后再进行安装即可,具体安装方法请另行百度. 代码 import xlrd import matplotlib.pyplot as plt import numpy as np #打开文件 data = xlrd.open_workbook(r'testExcelData.xlsx') #获取表格数目 nums = le

  • Python实现将xml导入至excel

    最近在使用Testlink时,发现导入的用例是xml格式,且没有合适的工具转成excel格式,xml使用excel打开显示的东西也太多,网上也有相关工具转成csv格式的,结果也不合人意. 那求人不如尔己,自己写一个吧 需要用到的模块有:xml.dom.minidom(python自带).xlwt 使用版本: python:2.7.5 xlwt:1.0.0 一.先分析Testlink XML格式: 这是一个有两级testusuit的典型的testlink用例结构,我们只需要取testsuite n

  • Python使用xlrd模块操作Excel数据导入的方法

    本文实例讲述了Python使用xlrd模块操作Excel数据导入的方法.分享给大家供大家参考.具体分析如下: xlrd是一个基于python的可以读取excel文件的产品.和pyExcelerator相比,xlrd的主要特点在于读的功能比较强大,提供了表单行数.列数.单元格数据类型等pyExcelrator无法提供的详细信息,使得开发人员无须了解表单的具体结构也能对表单中的数据进行正确的分析转换. 但是xlrd仅仅提供了读取excel文件的功能,不能像pyExcelrator那样生成excel文

  • 使用python将多个excel文件合并到同一个文件的方法

    应用场景:使用pandas把多个相同结构的Excel文件合并为一个. 原始数据: 相关代码: import os import pandas as pd # 将文件读取出来放一个列表里面 pwd = 'test' # 获取文件目录 # 新建列表,存放文件名 file_list = [] # 新建列表存放每个文件数据(依次读取多个相同结构的Excel文件并创建DataFrame) dfs = [] for root,dirs,files in os.walk(pwd): # 第一个为起始路径,第二

  • 用Python将Excel数据导入到SQL Server的例子

    使用环境:Win10 x64 Python:3.6.4 SqlServer:2008R2    因为近期需要将excel导入到SQL Server,但是使用的是其他语言,闲来无事就尝试着用python进行导入,速度还是挺快的,1w多条数据,也只用了1s多,代码也比较简单,就不多解释了. 用到的库有xlrd(用来处理excel),pymssql(用来连接使用sql server) import xlrd import pymssql import datetime # 连接本地sql server

  • python pandas写入excel文件的方法示例

    pandas读取.写入csv数据非常方便,但是有时希望通过excel画个简单的图表看一下数据质量.变化趋势并保存,这时候csv格式的数据就略显不便,因此尝试直接将数据写入excel文件. pandas可以写入一个或者工作簿,两种方法介绍如下: 1.如果是将整个DafaFrame写入excel,则调用to_excel()方法即可实现,示例代码如下: # output为要保存的Dataframe output.to_excel('保存路径 + 文件名.xlsx') 2.有多个数据需要写入多个exce

  • Python向excel中写入数据的方法

    最近做了一项工作需要把处理的数据写入到Excel表格中进行保存,所以在此就简单介绍使用Python如何把数据保存到excel表格中. 数据导入之前需要安装 xlwt依赖包,安装的方法就很简单,直接 pip install xlwt ,如果电脑中安装过就不需要重复安装. 接下来就做一个简单的demo ,把三行数据添加到excel中. 具体代码如下: #!/usr/bin/env python # coding=utf-8 from xlwt import * #需要xlwt库的支持 #import

  • python将字符串list写入excel和txt的实例

    docs = ['icassp improved human face identification using frequency domain representation facial asymmetry', 'pattern recognition unsupervised methods classification hyperspectral images low spatial resolution', 'iscas post layout watermarking method

  • 使用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")

  • Python破解excel进入密码的过程详解

    目录 一.excel进入密码 二.密码解除思路 三.python 1.conf.ini 2.crack.py 一.excel进入密码 加密算法cipher Algorithm=“AES” AES加密算法的详细介绍与实现 二.密码解除思路 通过排列组合的方式进行查找 注意:此方法比较考验对密码字典的选取,且耗费时间较长,仅供参考学习!! 文件夹如图所示: 将待破解的文件放到excel文件夹中. 三.python 1.conf.ini 将准备好的密码字典添加到conf.ini中password后面,

  • Python字节码与程序执行过程详解

    目录 问题: 1. 执行过程 2. 字节码 3. 源码编译 三种编译模式: 4. PyCodeObject 5. 反编译 6. pyc 问题: 我们每天都要编写一些Python程序,或者用来处理一些文本,或者是做一些系统管理工作.程序写好后,只需要敲下python命令,便可将程序启动起来并开始执行: $ python some-program.py 那么,一个文本形式的.py文件,是如何一步步转换为能够被CPU执行的机器指令的呢?此外,程序执行过程中可能会有.pyc文件生成,这些文件又有什么作用

  • python 利用pyttsx3文字转语音过程详解

    这篇文章主要介绍了python 利用pyttsx3文字转语音过程详解,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下 # -*- coding: utf-8 -*- import pyttsx3 engine = pyttsx3.init() with open("all.txt",'r',encoding='utf-8') as f: while 1: line = f.readline() print(line, end = '')

  • python FastApi实现数据表迁移流程详解

    目录 啥是数据迁移 1.需要新的数据表 2.需要对现有表结构进行调整 回到ORM 迁移手段 安装alembic 初始化项目 修改alembic.ini 修改alembic/env.py 开始生成迁移工作 变更数据库 FAQ 啥是数据迁移 在我们平时的开发过程中,经常需要对一些数据进行调整.一般会有以下几种场景: 1.需要新的数据表 我们的接口自动化平台虽然已经较为完善了,但难免会继续迭代一些新的功能,假设我们需要做一个订阅用例的功能. 大体想一下就可以知道,订阅用例以后这个数据得持久化(即入库)

  • Java实现自定义Excel数据排序的方法详解

    目录 1.引入jar包 2.自定义排序 通常,我们可以在Excel中对指定列数据执行升序或者降序排序,排序时可依据单元格中的数值.单元格颜色.字体颜色或图标等.在需要自定义排序情况下,我们也可以自行根据排序需要编辑数据排列顺序.本文,将通过Java应用程序来实现如何自定义排序. 1.引入jar包 使用jar包:Spire.Xls.jar version: 12.8.4 导入方法1:手动下载jar到本地,解压,然后找到lib文件夹下的Spire.Xls.jar文件.然后在IDEA中打开“Proje

  • Python进阶之import导入机制原理详解

    目录 前言 1. Module组成 1.1 Module 内置全局变量 2. 包package 2.1 实战案例 3.sys.modules.命名空间 3.1 sys.modules 3.2 命名空间 4. 导入 4.1 绝对导入 4.2 相对导入 4.3 单独导入包 5. import运行机制 5.1 标准import,顶部导入 5.2 嵌套import 前言 在Python中,一个.py文件代表一个Module.在Module中可以是任何的符合Python文件格式的Python脚本.了解Mo

  • python获取网络图片方法及整理过程详解

    这篇文章主要介绍了python获取网络图片方法及整理过程详解,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下 方式1 使用urllib库 import urllib.request import os ,stat url = "https://cn.bing.com/th?id=OHR.Lidong2019_ZH-CN0761273672_1920x1080.jpg" try: urllib.request.urlretrieve(ur

  • Python统计学一数据的概括性度量详解

    一.数据的概括性度量 1.统计学概括: 统计学是应用数学的一个分支,主要通过利用概率论建立数学模型,收集所观察系统的数据,进行量化的分析.总结,并进而进行推断和预测,为相关决策提供依据和参考.统计学主要又分为描述统计学和推断统计学.给定一组数据,统计学可以摘要并且描述这份数据,这个用法称作为描述统计学.另外,观察者以数据的形态建立出一个用以解释其随机性和不确定性的数学模型,以之来推论研究中的步骤及母体,这种用法被称做推论统计学. 2.数据的概括性度量: 1)集中趋势的度量: 众数:众数(Mode

随机推荐