当前位置：首页 > article >正文

python pandas 对mysql 一些常见操作

article 2025/1/7 13:33:26

使用 Python 的 pandas 库与 MySQL 数据库进行交互是非常常见的任务。pandas 提供了一个非常方便的方法 read_sql() 和 to_sql()，能够直接读取和写入 MySQL 数据。结合 SQLAlchemy 或 pymysql 作为 MySQL 的连接器，可以轻松实现数据的传输。

常见操作：

1. 连接到 MySQL 数据库

首先，你需要使用 SQLAlchemy 或 pymysql 来建立与 MySQL 的连接。

pip install sqlalchemy pymysql pandas

然后使用下面的代码来建立连接：

from sqlalchemy import create_engine
import pandas as pd

# 使用 SQLAlchemy 的 create_engine 创建连接
engine = create_engine('mysql+pymysql://username:password@host:port/database_name')

# 或者直接使用 pymysql 连接
import pymysql
connection = pymysql.connect(host='host', user='username', password='password', database='database_name', port=3306)

2. 读取 MySQL 数据到 pandas DataFrame

通过 pandas.read_sql() 方法，可以直接将 MySQL 查询结果加载到 DataFrame 中：

# 使用 SQLAlchemy engine
query = "SELECT * FROM table_name"
df = pd.read_sql(query, engine)

# 或者使用 pymysql connection
df = pd.read_sql(query, connection)

3. 写入 pandas DataFrame 到 MySQL

pandas 提供了 to_sql() 方法，可以将 DataFrame 中的数据写入 MySQL 表格中。

# 将 DataFrame 写入 MySQL
df.to_sql('table_name', engine, if_exists='replace', index=False)

if_exists 参数有以下几种选项：

'fail': 如果表已经存在，什么都不做。
'replace': 如果表已经存在，删除旧表并创建新表。
'append': 如果表已存在，将数据追加到现有表中。

4. 执行 SQL 查询操作

如果你只需要执行 SQL 语句而不关心结果（例如插入数据、更新数据），可以使用 engine.execute() 或 connection.cursor()：

# 执行 SQL 语句
with engine.connect() as connection:
    connection.execute("UPDATE table_name SET column_name = value WHERE condition")

或者使用 pymysql 的方式：

cursor = connection.cursor()
cursor.execute("UPDATE table_name SET column_name = value WHERE condition")
connection.commit()  # 提交更改

5. 从 MySQL 读取数据并进行操作

# 读取数据到 DataFrame
df = pd.read_sql("SELECT * FROM employees WHERE salary > 50000", engine)

# 进行 pandas 操作，如筛选、聚合等
df_filtered = df[df['age'] > 30]

6. 写入大数据（批量插入）

在处理大量数据时，使用 to_sql() 方法时可以通过设置 chunksize 来分批插入，避免内存溢出。

# 分批插入数据
df.to_sql('table_name', engine, if_exists='append', index=False, chunksize=1000)

7. 读取大数据（使用分页）

当需要从 MySQL 中读取大量数据时，建议使用分页技术（通过 LIMIT 和 OFFSET）来分批读取，避免一次性加载过多数据导致内存问题。

# 分页查询
offset = 0
batch_size = 1000
query = f"SELECT * FROM table_name LIMIT {batch_size} OFFSET {offset}"
df_batch = pd.read_sql(query, engine)

8. 查询结果按条件筛选

# 使用 SQL 语句进行筛选
df = pd.read_sql("SELECT * FROM table_name WHERE column_name = 'value'", engine)

# 或者在 pandas 中进行条件筛选
df_filtered = df[df['column_name'] == 'value']