39 Python使用Pandas将Excel存入MySQL
2023-03-14 本文已影响0人
Viterbi
39 Python使用Pandas将Excel存入MySQL
一个典型的数据处理流：
- Pandas从多方数据源读取数据，比如excel、csv、mysql、爬虫等等
- Pandas对数据做过滤、统计分析
- Pandas将数据存储到MySQL，用于Web页面展示、后续的进一步SQL分析等等
官网文档： https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_sql.html#pandas.DataFrame.to_sql
数据准备：学生信息Excel表
import pandas as pd
df = pd.read_excel("./course_datas/c23_excel_vlookup/学生信息表.xlsx")
df.head()
| 学号 | 姓名 | 性别 | 年龄 | 籍贯 | |
|---|---|---|---|---|---|
| 0 | S001 | 怠涵 | 女 | 23 | 山东 |
| 1 | S002 | 婉清 | 女 | 25 | 河南 |
| 2 | S003 | 溪榕 | 女 | 23 | 湖北 |
| 3 | S004 | 漠涓 | 女 | 19 | 陕西 |
| 4 | S005 | 祈博 | 女 | 24 | 山东 |
# 展示索引的name
df.index.name
df.index.name = "id"
df.head()
| 学号 | 姓名 | 性别 | 年龄 | 籍贯 | |
|---|---|---|---|---|---|
| id | |||||
| 0 | S001 | 怠涵 | 女 | 23 | 山东 |
| 1 | S002 | 婉清 | 女 | 25 | 河南 |
| 2 | S003 | 溪榕 | 女 | 23 | 湖北 |
| 3 | S004 | 漠涓 | 女 | 19 | 陕西 |
| 4 | S005 | 祈博 | 女 | 24 | 山东 |
创建sqlalchemy对象连接MySQL
SQLAlchemy是Python中的ORM框架， Object-Relational Mapping，把关系数据库的表结构映射到对象上。
- 官网：https://www.sqlalchemy.org/
- 如果sqlalchemy包不存在，用这个命令安装：pip install sqlalchemy
- 需要安装依赖Python库：pip install mysql-connector-python
可以直接执行SQL语句
from sqlalchemy import create_engine
engine = create_engine("mysql+mysqlconnector://root:123456@127.0.0.1:3306/test", echo=False)
方法1：当数据表不存在时，每次覆盖整个表
每次运行会drop table，新建表
df.to_sql(name='student', con=engine, if_exists="replace")
engine.execute("show create table student").first()[1]
'CREATE TABLE `student` (\n `id` bigint(20) DEFAULT NULL,\n `学号` text,\n `姓名` text,\n `性别` text,\n `年龄` bigint(20) DEFAULT NULL,\n `籍贯` text,\n KEY `ix_student_id` (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4'
print(engine.execute("show create table student").first()[1])
CREATE TABLE `student` (
`id` bigint(20) DEFAULT NULL,
`学号` text,
`姓名` text,
`性别` text,
`年龄` bigint(20) DEFAULT NULL,
`籍贯` text,
KEY `ix_student_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
engine.execute("select count(1) from student").first()
(24,)
engine.execute("select * from student limit 5").fetchall()
[(0, 'S001', '怠涵', '女', 23, '山东'),
(1, 'S002', '婉清', '女', 25, '河南'),
(2, 'S003', '溪榕', '女', 23, '湖北'),
(3, 'S004', '漠涓', '女', 19, '陕西'),
(4, 'S005', '祈博', '女', 24, '山东')]
方法2：当数据表存在时，每次新增数据
场景：每天会新增一部分数据，要添加到数据表，怎么处理？
df_new = df.loc[:4, :]
df_new
| 学号 | 姓名 | 性别 | 年龄 | 籍贯 | |
|---|---|---|---|---|---|
| id | |||||
| 0 | S001 | 怠涵 | 女 | 23 | 山东 |
| 1 | S002 | 婉清 | 女 | 25 | 河南 |
| 2 | S003 | 溪榕 | 女 | 23 | 湖北 |
| 3 | S004 | 漠涓 | 女 | 19 | 陕西 |
| 4 | S005 | 祈博 | 女 | 24 | 山东 |
df_new.to_sql(name='student', con=engine, if_exists="append")
engine.execute("SELECT * FROM student where id<5 ").fetchall()
[(0, 'S001', '怠涵', '女', 23, '山东'),
(1, 'S002', '婉清', '女', 25, '河南'),
(2, 'S003', '溪榕', '女', 23, '湖北'),
(3, 'S004', '漠涓', '女', 19, '陕西'),
(4, 'S005', '祈博', '女', 24, '山东'),
(0, 'S001', '怠涵', '女', 23, '山东'),
(1, 'S002', '婉清', '女', 25, '河南'),
(2, 'S003', '溪榕', '女', 23, '湖北'),
(3, 'S004', '漠涓', '女', 19, '陕西'),
(4, 'S005', '祈博', '女', 24, '山东')]
问题解决：先根据数据KEY删除旧数据
df_new.index
RangeIndex(start=0, stop=5, step=1, name='id')
for id in df_new.index:
## 先删除要新增的数据
delete_sql = f"delete from student where id={id}"
print(delete_sql)
engine.execute(delete_sql)
delete from student where id=0
delete from student where id=1
delete from student where id=2
delete from student where id=3
delete from student where id=4
engine.execute("SELECT * FROM student where id<5 ").fetchall()
[]
engine.execute("select count(1) from student").first()
(19,)
# 新增数据到表中
df_new.to_sql(name='student', con=engine, if_exists="append")
engine.execute("SELECT * FROM student where id<5 ").fetchall()
[(0, 'S001', '怠涵', '女', 23, '山东'),
(1, 'S002', '婉清', '女', 25, '河南'),
(2, 'S003', '溪榕', '女', 23, '湖北'),
(3, 'S004', '漠涓', '女', 19, '陕西'),
(4, 'S005', '祈博', '女', 24, '山东')]
engine.execute("SELECT count(1) FROM student").first()
(24,)
本文使用 文章同步助手 同步
