使用Pandas to_sql向DB2写入数据触发NotImplementedError问题排查

我有一个Pandas DataFrame，列及类型如下：

account_number                 int64
prediction                     int64
probability_of_prediction    float64
probability_of_redemption    float64

注意：该DataFrame的整体dtype为object（可能存在列内类型不一致的情况）。

我要将数据上传到DB2数据库的MYSCHEMA.MYTABLE表，目标表的创建语句为：

CREATE TABLE MYSCHEMA.MYTABLE
(
ACCOUNT_NUMBER              INT,
PREDICTION                  INT,
PROBABILITY_OF_PREDICTION   DECIMAL(5,2),
PROBABILITY_OF_REDEMPTION   DECIMAL(5,2)
)
IN MYSCHEMA
COMPRESS YES
;

执行上传的代码如下：

df.to_sql(name = "mytable", con = connection, schema = "myschema", if_exists = 'replace', index = False, chunksize = 1000, method = "multi")

执行后报错NotImplementedError，完整错误堆栈如下：

ERROR:root:Problem:
Traceback (most recent call last):
  File "C:\My_filepath\my_script.py", line 185, in <module>
    modelling()
  File "C:\My_filepath\my_script.py", line 164, in modelling
    df.to_sql(name = "mytable", con = connection, schema = "myschema", if_exists = 'replace', index = False, chunksize = 1000, method = "multi")
  File "C:\My_filepath\Python\Python39\lib\site-packages\pandas\core\generic.py", line 2951, in to_sql
    return sql.to_sql(
  File "C:\My_filepath\Python\Python39\lib\site-packages\pandas\io\sql.py", line 697, in to_sql
    return pandas_sql.to_sql(
  File "C:\My_filepath\Python\Python39\lib\site-packages\pandas\io\sql.py", line 1729, in to_sql
    table = self.prep_table(
  File "C:\My_filepath\Python\Python39\lib\site-packages\pandas\io\sql.py", line 1628, in prep_table
    table.create()
  File "C:\My_filepath\Python\Python39\lib\site-packages\pandas\io\sql.py", line 835, in create
    self.pd_sql.drop_table(self.name, self.schema)
  File "C:\My_filepath\Python\Python39\lib\site-packages\pandas\io\sql.py", line 1787, in drop_table
    self.meta.reflect(bind=self.connectable, only=[table_name], schema=schema)
  File "C:\My_filepath\Python\Python39\lib\site-packages\sqlalchemy\sql\schema.py", line 4860, in reflect
    Table(name, self, **reflect_opts)
  File "<string>", line 2, in __new__
  File "C:\My_filepath\Python\Python39\lib\site-packages\sqlalchemy\util\deprecations.py", line 309, in warned
    return fn(*args, **kwargs)
  File "C:\My_filepath\Python\Python39\lib\site-packages\sqlalchemy\sql\schema.py", line 616, in __new__
    metadata._remove_table(name, schema)
  File "C:\My_filepath\Python\Python39\lib\site-packages\sqlalchemy\util\langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "C:\My_filepath\Python\Python39\lib\site-packages\sqlalchemy\util\compat.py", line 207, in raise_
    raise exception
  File "C:\My_filepath\Python\Python39\lib\site-packages\sqlalchemy\sql\schema.py", line 611, in __new__
    table._init(name, metadata, *args, **kw)
  File "C:\My_filepath\Python\Python39\lib\site-packages\sqlalchemy\sql\schema.py", line 686, in _init
    self._autoload(
  File "C:\My_filepath\Python\Python39\lib\site-packages\sqlalchemy\sql\schema.py", line 721, in _autoload
    conn_insp.reflect_table(
  File "C:\My_filepath\Python\Python39\lib\site-packages\sqlalchemy\engine\reflection.py", line 791, in reflect_table
    self._reflect_pk(
  File "C:\My_filepath\Python\Python39\lib\site-packages\sqlalchemy\engine\reflection.py", line 920, in _reflect_pk
    pk_cons = self.get_pk_constraint(
  File "C:\My_filepath\Python\Python39\lib\site-packages\sqlalchemy\engine\reflection.py", line 528, in get_pk_constraint
    return self.dialect.get_pk_constraint(
  File "C:\My_filepath\Python\Python39\lib\site-packages\sqlalchemy\engine\interfaces.py", line 285, in get_pk_constraint
    raise NotImplementedError()
NotImplementedError

请问哪里操作有误？

从错误堆栈能看到，报错出现在get_pk_constraint方法——也就是SQLAlchemy尝试读取目标表的主键约束时，你的DB2驱动没有实现这个方法，直接抛出了NotImplementedError。

核心原因 #

你用了if_exists='replace'参数，这个参数的逻辑是先删除已存在的目标表，再重建并写入数据。但删除表之前，Pandas会通过SQLAlchemy反射目标表的结构（包括主键信息），而你的DB2驱动不支持主键约束的反射操作，所以触发了错误。另外，DataFrame整体是object类型，列内可能存在类型不一致的情况，也可能导致后续写入时的类型匹配问题。

解决方案 #

1. 改用if_exists='append'（目标表已存在且无需重建）

如果目标表已经创建好，不需要重新建表，直接追加数据即可跳过反射表结构的步骤：

df.to_sql(name = "mytable", con = connection, schema = "myschema", if_exists = 'append', index = False, chunksize = 1000, method = "multi")

2. 手动删表后再用replace（需要重建表时）

先手动执行SQL删除目标表，再调用to_sql，这样Pandas不需要反射已存在的表结构：

# 手动删除表
with connection.cursor() as cursor:
    cursor.execute("DROP TABLE MYSCHEMA.MYTABLE")
    connection.commit()

# 写入数据
df.to_sql(name = "mytable", con = connection, schema = "myschema", if_exists = 'replace', index = False, chunksize = 1000, method = "multi")

3. 修复DataFrame的类型问题

先把DataFrame的列转换成正确的类型，避免写入时出现类型不匹配：

# 转换列类型
df['account_number'] = df['account_number'].astype('int64')
df['prediction'] = df['prediction'].astype('int64')
df['probability_of_prediction'] = df['probability_of_prediction'].astype('float64')
df['probability_of_redemption'] = df['probability_of_redemption'].astype('float64')

# 执行写入
df.to_sql(name = "mytable", con = connection, schema = "myschema", if_exists = 'append', index = False, chunksize = 1000, method = "multi")

4. 更新DB2驱动版本

确保你用的SQLAlchemy DB2驱动（比如ibm_db_sa）是最新版本，新版本可能已经修复了主键反射的兼容问题。

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