37. Python使用Pandas将Excel存入MySQL

Python使用Pandas将Excel存入MySQL

一个典型的数据处理流：

1. Pandas从多方数据源读取数据，比如excel、csv、mysql、爬虫等等
2. Pandas对数据做过滤、统计分析
3. 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,)