使用pandas的df.to_sql()向MySQL插入DataFrame报1064语法错误如何解决

你遇到的报错确实是value列的列表类型、ts_1列的带时区时间类型和MySQL字段类型不匹配导致的，优先在调用to_sql前对列做转换处理，这是成本最低、兼容性最好的方案，也可以搭配to_sql的内置参数解决，具体方案如下：

方案1：提前转换DataFrame列类型（最推荐） #

处理列表类型的value列 #

MySQL没有原生的列表存储类型，根据你的业务需求选一种转换方式：

• 如果只需要把列表整体存储用于后续读取解析：将列表序列化为JSON字符串，对应MySQL字段类型设为JSON或TEXT即可
  代码示例：

  import json
  df['value'] = df['value'].apply(json.dumps)
  

• 如果需要把列表中的每个元素拆成独立行用于SQL查询：用explode方法拆分行，再做存储
  代码示例：

  df = df.explode('value', ignore_index=True)
  

处理带时区的ts_1列 #

MySQL默认的DATETIME类型不携带时区信息，统一转成无时区的标准时间存储即可，建议统一转成UTC时间避免时区混乱：
代码示例：

# 先把所有带时区的时间转成UTC时区，再移除时区信息
df['ts_1'] = df['ts_1'].dt.tz_convert('UTC').dt.tz_localize(None)

转换完成后再调用原来的to_sql方法即可正常插入。

方案2：通过to_sql的dtype参数指定字段类型 #

如果不想修改原DataFrame的数据，可以在调用to_sql时通过dtype参数指定SQLAlchemy的字段类型，让框架自动做类型转换，要求你的MySQL版本支持对应类型：
代码示例：

from sqlalchemy.dialects.mysql import JSON, DATETIME

def add_to_mysql(self, df, table):
    engine = create_engine(self._engine_url)
    df.to_sql(
        table, 
        con=engine, 
        if_exists="append", 
        index=False,
        dtype={
            "value": JSON(),
            "ts_1": DATETIME(timezone=True)
        }
    )

其他替代方案说明 #

不建议换其他库解决，to_sql本身是性能和易用性平衡得最好的方案，即使换用pymysql手写批量插入逻辑，本质还是要做上述的类型转换，反而会增加额外的代码维护成本。

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