python读取excel写入mysql pandas_Python 使用 Pandas 将 Excel 存入 MySQL - 《Pandas 数据分析从入门到实战》...

54 阅读 0 评论 36 点赞

我是靠谱客的博主高兴发箍，最近开发中收集的这篇文章主要介绍python读取excel写入mysql pandas_Python 使用 Pandas 将 Excel 存入 MySQL - 《Pandas 数据分析从入门到实战》...，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

一个典型的数据处理流：

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.namedf.index.name = "id"

df.head()学号姓名性别年龄籍贯

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")ngine.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=utf8mb4engine.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学号姓名性别年龄籍贯

id0S001怠涵女23山东

1S002婉清女25河南

2S003溪榕女23湖北

3S004漠涓女19陕西

4S005祈博女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.indexRangeIndex(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=4engine.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,)