39 Python使用Pandas将Excel存入MySQL

39 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()

# 展示索引的name
df.index.name

df.index.name = "id"
df.head()

创建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

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,)

本文使用 文章同步助手 同步