使用mysql.connector时pandas to_sql方法报错如何解决

mysql.connector 生成的原生数据库连接对象，不支持 pandas 的 df.to_sql() 方法。
报错信息中出现 SQLite 专属系统表 sqlite_master，是因为 pandas 的 SQL 写入逻辑在未识别到适配的数据库方言时，会默认使用 SQLite 语法生成查询/写入语句，和实际连接的 MySQL 数据库语法不兼容，最终执行失败。
pd.read_sql 能正常运行是因为读数据逻辑对原生 DBAPI 连接做了兼容，但 pandas 1.0 及以上版本的 to_sql 方法明确要求传入 SQLAlchemy 生成的连接引擎作为 con 参数，不再支持各数据库的原生直连对象。

• 安装依赖包
  执行命令安装所需依赖：
  pip install pandas mysql-connector-python sqlalchemy
• 替换连接方式
  不要直接使用 mysql.connector 创建的连接对象，改用 SQLAlchemy 生成 MySQL 连接引擎，连接串格式为 mysql+mysqlconnector://用户名:密码@主机地址:端口/数据库名，AWS RDS for MySQL 默认端口为3306，连接时可指定字符集避免中文乱码。
• 调整to_sql参数
  写入时建议关闭pandas默认索引写入，设置批量写入大小提升稳定性。

修正后的可运行代码：

import pandas as pd
from sqlalchemy import create_engine

# 初始化SQLAlchemy连接引擎
db_engine = create_engine(
    'mysql+mysqlconnector://user:pw@124685.eu-central-1.rds.amazonaws.com:3306/db_name?charset=utf8mb4'
)

# 读取数据
query = 'SELECT * FROM table_name'
df = pd.read_sql(sql=query, con=db_engine)

# 数据处理
df["Person_Name"] = "xx"

# 写入MySQL
df.to_sql(
    name='table_name',
    con=db_engine,
    if_exists='replace',
    index=False,
    chunksize=1000
)

• if_exists='replace' 会先删除原表再重建新表写入，原表设置的主键、索引、字段约束、触发器都会被清除，如果仅需新增数据请替换为 if_exists='append'，如果需要覆盖部分数据建议先手动执行DELETE语句清空目标数据，再用append模式写入。
• 你当前的DataFrame中包含Last refresh、Bio-Import-Assistant这类带空格、特殊符号的列名，pandas写入时会自动做MySQL语法转义，无需手动修改列名。
• 连接AWS RDS前需要将当前客户端IP加入RDS安全组的入站白名单，否则会出现连接超时错误。

内容的提问来源于stack exchange，提问作者Jnl