使用python将excel数据导入数据库过程详解
作者:大长胡子 发布时间:2024-01-22 11:28:31
标签: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')
思考,如果数据插入有错误,怎么解决,
其实有很多数据库工具可以直接来解决这个问题,注意字符转换的格式就好。
来源:https://www.cnblogs.com/longbigbeard/p/9309180.html
0
投稿
猜你喜欢
- 目录外键(Foreign Key)如何确定表关系如何建立表关系一对多关系 - 员工表和部门表多对多一对一表关系总结外键(Foreign Ke
- “看图购”beta版今日起正式在淘宝网上线了,域名是:http://go.taobao.com。从此您也可以像翻看杂志一样翻看女装的图片了,
- 本文实例讲述了Python实现线程状态监测。分享给大家供大家参考,具体如下:# -*- coding:utf-8 -*-from threa
- Keras保存为可部署的pb格式加载已训练好的.h5格式的keras模型传入如下定义好的export_savedmodel()方法内即可成功
- 本文实例为大家分享了python实现图片横向和纵向拼接的具体代码,供大家参考,具体内容如下直接上代码:# -*- coding:utf-8
- 在做自动化测试时,遇到两种情况需要判断usb是否已连接上(注,本文仅针对用adb命令来control手机)一种是在开测时(前提是同时要测试多
- 一、MySQL-Proxy基础MySQL Proxy是一个处于你的Client端和MySQL server端之间的简单程序,它可以监测、分析
- Python3.7 基于 pycryptodome 的AES加密解密、RSA加密解密、加签验签,具体代码如下所示:#!/usr/bin/en
- 本文实例讲述了Python将阿拉伯数字转换为罗马数字的方法。分享给大家供大家参考。具体实现方法如下:def numToRomanNum(Nu
- 本文也是开发项目中的一个小经验Tip,虽然很简单,但对很多朋友也有小帮助。我们实际工程中,可能遇到开发环境、预上线环境、线上环境等环境场景,
- 为庆祝jQuery的四周年生日,jQuery官方团队正式发布了jQuery 1.4版本。在这个版本中,jQuery官方团队做了大量的编码、测
- 我认为在ASP中最好的办法是用编程实现定时刷新Cache,也就是说给Application中储存的设一个过期时间。当然,在ASP中Appli
- 简介Go的标准包Container中包含了常用的容器类型,包括conatiner/list,container/heap,container
- ‘Microsoft OLE DB Provider for ODBC Drivers (0x80004005) [Microsoft][O
- 返回被去除指定字符的字符串默认去除空白字符删除首尾字符:str.strip([char]) 删除首字符:str.lstrip([char])
- 一、ini文件介绍INI文件格式是某些平台或软件上的配置文件的非正式标准,以节(section)和键(key)构成,常用于微软Windows
- 【1】 以XML 返回 (1)未定义属性的 select logisticsId,logisticsName from LogisticsC
- 一、python中“::-1”代表什么?在Python中::-1表示将字符或数字倒序输出(也称【反转
- 本文实例讲述了Python使用百度翻译开发平台实现英文翻译为中文功能。分享给大家供大家参考,具体如下:#coding=utf8import
- 本文实例讲述了Python设计模式之模板方法模式。分享给大家供大家参考,具体如下:模板方法模式(Template Method Patter