CTF of Z2BNS
0%

Python解析Excel表数据到Mysql数据库中

发表于 分类于
| 阅读次数： |
Valine：
本文字数： 268 阅读时长 ≈ 1 分钟

前言

最近做一个小项目有导入Excel表到Mysql数据库的需求，就借此机会总结一下python解析Excel到Mysql数据库的操作。

以之前写过的文章《物流公司物品邮寄资费可视化分析》中的express.xlsx文件为例，导入到本地Mysql数据库的express表

物品邮寄资费标准Excel表（express.xlsx）内容如下：

img点击并拖拽以移动

数据库建表

数据库建表sql语句如下

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
/*
 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代码如下：

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
87
88
89
90
# -*- 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()

点击并拖拽以移动

导入后的数据库表内容如下：

img点击并拖拽以移动

参考

mysql下载与安装

python里面的xlrd模块详解

------------- THE END! THANKS! -------------