【超详细】用Python行云流水地操作Excel和数据库
前言
本想就着这个机会学习下Java读取Excel的，奈何搜了一圈，发现还是Pandas最为简单明了。打算先就Python写一版，后面在学习时可能还会发一篇《用Java优雅地操作Excel和数据库》。
准备工作
软件包
关于软件安装和环境配置，网上有成堆的教程，此处不再赘述。
大体上需要用到的软件就以下几款：
- Anaconda（建议用Anaconda里的jupyter notebook，如果喜欢PyCharm也可以，当然也可以只用Python）
- Python（Anaconda和PyCharm都内置，如果只有Python也可）
- MySQL（不多说了，免费）
数据
数据只要随便找一个Excel文件，自己建一个Excel文档（不论是.xls还是.xlsx后缀都可以），并随便写一些内容，保存为 成绩单.xlsx。
Pandas读取Excel文件
import pandas as pd # 如果没有安装pandas报错，直接在jupyter里面运行后述语句 !pip install pandas
df = pd.read_excel(r"C:\Users\Administrator\Desktop\成绩单.xlsx") # 读取test.xlsx的sheet1，并以第一行作为Dataframe的标题头
df.head() # 查看表格内容概况
创建数据库
打开MySQL，创建数据库students
# 创建数据库
create database students;
# 使用数据库
use database;
# 查看数据表，这个时候是空数据库
show tables;
创建数据表
create table `students` (`id` int not null auto_increment primary key, `student_id` varchar(255) comment '学号', `name` varchar(255) comment '姓名', `results` int comment '成绩');
注：创建数据表有两种方式，一种是在MySQL中先创建好数据表，并定义好数据字段的名称、属性，然后再用Pandas进行导入，若属性不符则导入失败；另一种是直接用Pandas在导出的同时创建数据表，这样导出的数据表字段属性都为TEXT，然后根据需要更改数据字段属性。
将记录导入数据库
调用Pandas.io.sql.to_sql()方法，其中if_exists控制重复记录是替换还是追加。
from sqlalchemy import create_engine
# 建立连接，username替换为用户名，passwd替换为密码，students替换为数据库名
conn = create_engine('mysql+pymysql://username:passwd@localhost:3306/students',encoding='utf8')
# 以df中的header为标题，写入数据库，test替换为数据表名
pd.io.sql.to_sql(df, "students", conn, if_exists='append', index=False)
一般这时候会报错，因为Pandas里面的字段名称和数据库里的字段名称不一致
修改df里面的标题(几种重命名的方式汇总 https://blog.csdn.net/littleRpl/article/details/100117428)
df.rename(columns={'学号':'student_id',
'姓名':'name',
'成绩':'results'},inplace=True)
再次尝试写入，成功。我们直接查询student数据表
使用PyMySQL连接数据库进行增删改查
连接数据库
import pymysql
db = pymysql.connect(host="127.0.0.1", user="root", database="students", passwd="root") # 主机名，用户名，数据库名称，密码
查询操作
cursor = db.cursor()
# 编写查询语句
sql = "SELECT * FROM test;"
# 执行查询语句
cursor.execute(sql)
# 获取查询结果
result = cursor.fetchall()
可以看到返回的是一个二维元组
增删改操作
新增记录
insert_sql = "INSERT INTO students (student_id, name, results) values ('Y031', 'Jack', 99);"
cursor.execute(insert_sql)
db.commit()
更新记录
update_sql = "update students set student_id='Y006' where student_id='Y031';"
cursor.execute(update_sql)
db.commit()
删除记录（不建议，一般使用逻辑删除，即新增一个is_delete字段，然后update这个字段）
同样的也是套用上面的模板进行操作，读者可以自行尝试。
这里稍微讲一下逻辑删除的原理，由于我进行了一次物理删除，导致自增id跳过6到了7.
如果是逻辑删除，先修改表结构新增is_delete字段
更新指定主键的记录的is_delete属性为1，查询的时候加上限定条件。
结语
通过Python操作数据库和Excel文件，以Pandas为桥梁，就可以快速进行Excel数据分析，Pandas数据处理，MySQL数据库存储，实现全流程自动化操作。通过数据库连接Web开发组件，Pandas连接机器学习，Excel导出统计报表，实现一站式编程。
