前言
最近做一个小项目有导入Excel表到Mysql数据库的需求,就借此机会总结一下python解析Excel到Mysql数据库的操作。
以之前写过的文章《物流公司物品邮寄资费可视化分析》中的express.xlsx文件为例,导入到本地Mysql数据库的express表
物品邮寄资费标准Excel表(express.xlsx)内容如下:
数据库建表
数据库建表sql语句如下
/*
Navicat Premium Data Transfer
Source Server : localhost
Source Server Type : MySQL
Source Server Version : 80024
Source Host : localhost:3306
Source Schema : myself
Target Server Type : MySQL
Target Server Version : 80024
File Encoding : 65001
Date: 06/07/2021 15:58:07
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for express
-- ----------------------------
DROP TABLE IF EXISTS `express`;
CREATE TABLE `express` (
`id` int(0) NOT NULL AUTO_INCREMENT,
`name` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '快递公司',
`year` varchar(5) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '年份',
`src_province` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '源省份',
`dst_province` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '目的省份',
`first_weight` varchar(5) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '首重',
`other_price` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '续重',
`remark` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '备注',
`modify_time` datetime(0) NULL DEFAULT NULL COMMENT '修改时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
Python解析Excel表
Excel表导入到Mysql数据库的Python代码如下:
# -*- codeing = utf-8 -*-
# @Time : 2021/7/6 14:03
# @Author : loadding...
# @File : Excel2Mysql.py
# @Software : Sublime Text
import xlrd
import pymysql
import datetime
def main():
#连接数据库(主机,端口,用户,密码等)
#本地库
db=pymysql.connect(host='localhost',port=3306,user='root',password='root',db='myself',charset='utf8')
#数据库游标
cursor=db.cursor()
filename='./express.xlsx'
try:
data=xlrd.open_workbook(filename) # 读取excel文件
except Exception as e:
print('open excel file failed!')
raise e
query='''insert into express(name,year,src_province,dst_province,first_weight,other_price,remark,modify_time) values(%s,%s,%s,%s,%s,%s,%s,%s)'''
#插入数据库中的字段全部初始化
name=''
year=''
src_province=''
dst_province=''
first_weight=''
other_price=''
remark=''
#遍历excel表的sheet页
sheetnames=data.sheet_names();
for i in range(len(sheetnames)):
#获取第i个sheet页
table=data.sheet_by_index(i)
src_province = '吉林'
year='2020'
nameList=['德邦','顺丰','邮政','申通','京东']
provinceList=['','','','','']
firstList=['','','','','']
otherList=['','','','','']
remarkList=['','','','','']
remarkList[0]=table.cell(table.nrows-1,0).value
remarkList[1]=table.cell(table.nrows-2,4).value
remarkList[2]=table.cell(table.nrows-1,8).value
remarkList[3]=table.cell(table.nrows-2,12).value
remarkList[4]=table.cell(table.nrows-2,16).value
for j in range(3,table.nrows-2):
#遍历每一行数据
provinceList[0]=table.cell(j,0).value
provinceList[1]=table.cell(j,4).value
provinceList[2]=table.cell(j,8).value
provinceList[3]=table.cell(j,12).value
provinceList[4]=table.cell(j,16).value
firstList[0]=table.cell(j,1).value
firstList[1]=table.cell(j,5).value
firstList[2]=table.cell(j,6).value
firstList[3]=table.cell(j,13).value
firstList[4]=table.cell(j,17).value
otherList[0]=table.cell(j,2).value
otherList[1]=table.cell(j,6).value
otherList[2]=table.cell(j,10).value
otherList[3]=table.cell(j,14).value
otherList[4]=table.cell(j,18).value
#每一行数据对应5个不同公司的数据
for k in range(5):
name=nameList[k]
dst_province=provinceList[k]
first_weight=firstList[k]
other_price=otherList[k]
remark=remarkList[k]
#获取插入时间
modify_time=datetime.datetime.strftime(datetime.datetime.now(), '%Y-%m-%d %H:%M:%S')
values=(name,year,src_province,dst_province,first_weight,other_price,remark,modify_time)
try:
cursor.execute(query,values)
except Exception as e:
print(e)
#关闭游标
cursor.close()
#提交操作
db.commit()
#关闭数据库
db.close()
if __name__ == '__main__':
main()
导入后的数据库表内容如下: