如何在 Pandas 中安全高效地连接 MySQL 数据库

在数据分析场景中，Pandas作为常用的数据处理工具，经常需要和MySQL数据库交互来获取或存储数据。直接拼接连接字符串或者频繁创建短连接的方式，不仅存在安全风险，还会降低数据处理的效率。下面介绍一套安全高效的连接方案。

依赖库准备

要实现Pandas和MySQL的连接，需要安装两个核心依赖库：pymysql作为MySQL的驱动，SQLAlchemy作为数据库连接的中间层，Pandas的read_sql和to_sql方法都依赖SQLAlchemy的引擎来工作。安装命令如下：

# 安装所需依赖
pip install pandas pymysql sqlalchemy

安全配置连接参数

为了避免在代码中硬编码数据库账号密码，建议将连接参数存放在独立的配置文件或者环境变量中，降低敏感信息泄露的风险。以下是使用配置文件存储参数的示例：

配置文件config.ini

[mysql]
host = 127.0.0.1
port = 3306
user = data_reader
password = your_secure_password
database = analysis_db
charset = utf8mb4

读取配置创建连接引擎

通过configparser读取配置文件，再使用SQLAlchemy创建数据库引擎，避免明文暴露连接信息：

import configparser
from sqlalchemy import create_engine
import pandas as pd

# 读取配置文件
config = configparser.ConfigParser()
config.read('config.ini', encoding='utf-8')

# 获取MySQL配置
mysql_config = config['mysql']
host = mysql_config['host']
port = mysql_config['port']
user = mysql_config['user']
password = mysql_config['password']
database = mysql_config['database']
charset = mysql_config['charset']

# 创建SQLAlchemy引擎，设置连接池参数提升效率
engine = create_engine(
    f"mysql+pymysql://{user}:{password}@{host}:{port}/{database}?charset={charset}",
    pool_size=5,          # 连接池大小
    max_overflow=10,      # 超出连接池大小的额外连接数
    pool_recycle=3600,    # 连接回收时间，避免连接过期
    echo=False            # 是否打印SQL语句，生产环境设为False
)

高效读写数据

从MySQL读取数据到Pandas

使用pd.read_sql方法可以直接执行SQL查询并将结果转为DataFrame，支持传入表名或者自定义SQL语句：

# 读取整张表的数据
df = pd.read_sql('user_info', engine)

# 执行自定义SQL查询，只读取需要的字段
query_sql = """
SELECT user_id, user_name, register_time 
FROM user_info 
WHERE register_time >= '2024-01-01'
"""
df = pd.read_sql(query_sql, engine)

print(df.head())

将Pandas数据写入MySQL

使用DataFrame.to_sql方法可以将数据写入MySQL表，支持追加或者替换表数据：

# 构造测试数据
data = {
    'user_id': [1, 2, 3],
    'user_name': ['张三', '李四', '王五'],
    'score': [88, 92, 85]
}
df = pd.DataFrame(data)

# 写入数据，if_exists参数：fail（表存在则报错）、replace（替换表）、append（追加数据）
df.to_sql(
    name='user_score',
    con=engine,
    if_exists='append',
    index=False  # 不写入DataFrame的索引列
)

常见问题与优化建议

• 连接超时问题：设置pool_recycle参数，让连接池定期回收过期连接，避免出现MySQL server has gone away的错误。
• 大数据量读写：读取大量数据时，可以使用chunksize参数分块读取，避免一次性加载全部数据占用过多内存：

  # 分块读取数据，每次读取10000条
  chunk_iter = pd.read_sql(query_sql, engine, chunksize=10000)
  for chunk in chunk_iter:
      # 处理每一块数据
      process_chunk(chunk)
  

• SQL注入风险：执行自定义SQL时，不要直接拼接字符串，使用参数化查询：

  # 安全的参数化查询
  user_id = 1001
  safe_sql = "SELECT * FROM user_info WHERE user_id = %s"
  df = pd.read_sql(safe_sql, engine, params=(user_id,))
  

• 连接资源释放：如果不需要长期使用连接，可以在使用完成后调用engine.dispose()释放连接池资源。

总结

通过SQLAlchemy创建带连接池的数据库引擎，配合安全的参数配置和合理的读写方式，就能实现Pandas和MySQL的安全高效连接。这种方式既避免了敏感信息硬编码的问题，又通过连接池提升了连接复用率，减少了频繁建立连接的开销，适合日常数据分析场景使用。

PandasMySQL数据库连接pymysqlSQLAlchemy修改时间：2026-07-13 16:00:26