pandas调用to_sql导入数据到MySQL出现字符串格式化错误如何解决

问题原因 #

• 核心错误：pandas.DataFrame.to_sql 方法要求传入的数据库连接是SQLAlchemy引擎/连接实例，两次调用to_sql时传入的都是mysql.connect()生成的原生数据库连接对象，pandas会默认将原生连接识别为SQLite连接，因此会执行查询SQLite系统表sqlite_master的语句，和MySQL不兼容触发报错。
• 调整代码后的遗留问题：虽然导入了create_engine创建了SQLAlchemy引擎，但调用to_sql时仍然传入了旧的原生self.connection，没有用到新建的引擎，所以问题没有解决。

解决方法 #

替换原生数据库连接逻辑，直接使用SQLAlchemy引擎操作：

1. 拼接SQLAlchemy连接串 #

from sqlalchemy import create_engine
from urllib.parse import quote_plus

# 密码含特殊字符时需要先做URL转义
db_pwd = quote_plus(config["knihy"]["password"])
# 读取配置生成引擎
engine = create_engine(f'mysql+pymysql://{config["knihy"]["user"]}:{db_pwd}@{config["knihy"]["host"]}/{config["knihy"]["database"]}')

2. 传入引擎调用to_sql #

# 不需要额外手动commit，to_sql默认会自动提交
my_df.to_sql("KNIHY", engine, if_exists="append", index=False)

可选：手动控制事务 #

如果需要手动管理事务，可以用连接上下文实现：

with engine.connect() as conn:
    my_df.to_sql("KNIHY", conn, if_exists="append", index=False)
    conn.commit()

类型转换建议 #

当前DataFrame所有字段均为object类型，写入MySQL会默认存为TEXT/VARCHAR类型，建议提前转换对应字段的类型避免后续查询问题：

import pandas as pd
# 示例：将数值类字段转为数字类型
my_df['book_rating'] = pd.to_numeric(my_df['book_rating'], errors='coerce')
my_df['book_reviews'] = pd.to_numeric(my_df['book_reviews'], errors='coerce')
my_df['book_year'] = pd.to_numeric(my_df['book_year'], errors='coerce')

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