Python使用Pandas库实现MySQL数据库的读写

桃夭

发布时间：2026-10-13| 4人浏览过

原创

本文详细介绍如何在Python中利用Pandas库结合SQLAlchemy和pymysql模块实现MySQL数据库的读写操作。文章解析了ORM概念，重点讲解了to_sql与read_sql_query函数的使用，并通过DataFrame写入及CSV文件导入两个实例，展示了从连接配置到数据持久化的完整流程。

Python Pandas读写MySQL数据库教程

本文主要介绍如何在Python环境中利用Pandas库完成对MySQL数据库的数据读取与写入操作。在深入具体代码实现之前，有必要先了解相关的基础概念，特别是对象关系映射（ORM）技术。

ORM技术基础

ORM技术

对象关系映射（Object-Relational Mapping，简称ORM）是一种编程技术，旨在将关系型数据库的表结构映射到面向对象程序中的类上。通过维护描述对象与数据库之间映射关系的元数据，ORM框架能够自动将程序中的对象状态持久化保存到关系型数据库中，从而减少手动编写SQL语句的工作量。

在Python生态系统中，最知名的ORM框架是SQLAlchemy。而在Java开发领域，常见的ORM中间件包括Hibernate、iBatis以及SpeedFrameWork等。

SQLAlchemy模块介绍

SQLAlchemy

SQLAlchemy是Python语言下的一个开源软件包，它同时提供了SQL工具包和对象关系映射（ORM）工具。该库采用MIT许可证进行发行，允许开发者在多种场景下灵活使用。

SQLAlchemy模块核心提供了create_engine()函数，用于初始化数据库连接。该函数接收一个表示连接信息的字符串作为参数，格式通常包含数据库类型、驱动、用户名、密码、主机地址、端口及数据库名。

'数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名

Pandas读写MySQL数据库实现

Pandas读写MySQL数据库

要在Python中实现Pandas与MySQL数据库的交互，需要安装并引入以下三个核心库：

pandas
sqlalchemy
pymysql

其中，pandas模块提供了read_sql_query()函数用于执行数据库查询，以及to_sql()函数用于将数据写入数据库。值得注意的是，使用这些功能时，无需手动预先创建MySQL数据表，Pandas会自动处理表结构的创建或更新。

sqlalchemy模块负责建立与不同数据库的连接通道，而pymysql模块则提供了Python操作MySQL数据库的具体驱动支持。

在后续示例中，我们将使用MySQL数据库中的mydb数据库以及其中的employee表，该表包含以下数据内容：

注意：

1. 查阅相关文档可知，to_sql函数支持两种MySQL引擎：一种是sqlalchemy，另一种是sqlite3。需要特别强调的是，在写入数据时，不能直接使用pymysql作为引擎参数。

同样，mysqldb驱动也不适用于to_sql函数的直接写入操作。用户只能选择使用sqlalchemy或sqlite3引擎。鉴于sqlite3更新维护频率较低，本文建议使用sqlalchemy引擎。

2. to_sql函数并非直接定义在pandas主命名空间pd中，而是位于pd.io.sql模块下的一个类。因此，推荐的调用写法应明确指定模块路径，例如：


pd.io.sql.to_sql(df1,tablename,con=conn,if_exists='repalce')

下面将通过一个简单示例，展示如何在Pandas中实现对MySQL数据库的基本读写操作：


import pandas as pd
from sqlalchemy import create_engine
# 初始化数据库连接，使用pymysql模块
# MySQL的用户：root, 密码:147369, 端口：3306,数据库：test
engine = create_engine('mysql+pymysql://root:123456@localhost:3306/test')
# 查询语句，选出employee表中的所有数据
sql = ''' select * from employee; '''
# read_sql_query的两个参数: sql语句， 数据库连接
df = pd.read_sql_query(sql, engine)
# 输出employee表的查询结果
print(df)

# 新建pandas中的DataFrame, 只有id,num两列
df = pd.DataFrame({'id': [1, 2, 3, 4], 'name': ['zhangsan', 'lisi', 'wangwu', 'zhuliu']})
# 将新建的DataFrame储存为MySQL中的数据表，储存index列
df.to_sql('mydf', engine, index=True)
print('Read from and write to Mysql table successfully!')

运行结果：

上述结果表明，我们成功将Pandas中创建的DataFrame数据写入到了MySQL数据库中。

将CSV文件写入到MySQL中

前文示例展示了直接使用Pandas对象读写MySQL数据库的方法。接下来，我们将介绍另一个常见场景：将本地CSV文件的数据写入到MySQL数据库中。示例中使用的example.csv文件内容如下：

对应的Python实现代码如下：


# -*- coding: utf-8 -*-

# 导入必要模块
import pandas as pd
from sqlalchemy import create_engine

# 初始化数据库连接，使用pymysql模块
db_info = {'user': 'root',
      'password': '123456',
      'host': 'localhost',
      'port': 3306,
      'database': 'test'
      }

engine = create_engine('mysql+pymysql://%(user)s:%(password)s@%(host)s:%(port)d/%(database)s?charset=utf8' % db_info, encoding='utf-8')
# 直接使用下一种形式也可以
# engine = create_engine('mysql+pymysql://root:123456@localhost:3306/test')

# 读取本地CSV文件
df = pd.read_csv("C:/Users/fuqia/Desktop/example.csv", sep=',')
print(df)
# 将新建的DataFrame储存为MySQL中的数据表，不储存index列(index=False)
# if_exists:
# 1.fail:如果表存在，啥也不做
# 2.replace:如果表存在，删了表，再建立一个新表，把数据插入
# 3.append:如果表存在，把数据插入，如果表不存在创建一个表！！
pd.io.sql.to_sql(df, 'example', con=engine, index=False, if_exists='replace')
# df.to_sql('example', con=engine, if_exists='replace')这种形式也可以
print("Write to MySQL successfully!")

执行代码后，可在MySQL数据库中查看生成的example表格，验证数据是否写入成功。

补充说明：engine.execute(sql)方法可以直接执行SQL语句。


from sqlalchemy import create_engine 
 
 engine = create_engine('mysql+pymysql://root:123456@localhost:3306/test')
sql = "DROP TABLE IF EXISTS example"
engine.execute(sql)

如果选择使用pymysql库，则必须通过cursor对象来执行SQL操作。读者可以对比这两种方式的区别。


import pymysql
from sqlalchemy import create_engine

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='test')
# engine = create_engine('mysql+pymysql://root:123456@localhost:3306/test')
sql = "DROP TABLE IF EXISTS test_input"
cursor = conn.cursor()
cursor.execute(sql)