SQLAlchemy - Python 编程错误'转换小数会丢失精度'，'HY000'

SQLAlchemy – Python 编程错误’转换小数会丢失精度’，’HY000′

2023年4月7日上午9:59 • Python开发

【问题标题】：SQLAlchemy - Python Programming Error 'Converting decimal loses precision', 'HY000'SQLAlchemy - Python 编程错误'转换小数会丢失精度'，'HY000'
【发布时间】：2023-04-06 13:59:01
【问题描述】：

我正在使用 SQL Alchemy 引擎和 pandas 并尝试实现 fast_executemany=True，但在尝试将 df 帧行插入 SQL SERVER DB 时出现此错误。

我的代码是这样的

engine = create_engine('mssql+pyodbc://@SERVER/DATABASE?driver=ODBC+Driver+17+for+SQL+Server',
                       encoding="utf-8", fast_executemany=True)
conn = engine.connect()

然后我将数据帧分成块并将 pd.to_sql 应用于每个块

chunksize = int(len(df) / 10) # 10%
with tqdm(total=len(df)) as pbar:
    for i, cdf in enumerate(chunker(df, chunksize)):
        replace_var = "replace" if i == 0 else "append"
        cdf.to_sql(output_table_name, con=engine, schema=output_schema, if_exists=replace_var, index=False)
        pbar.update(chunksize)

尝试运行第一个块后，出现以下错误

Traceback (most recent call last):
  File "X:\avelazquez\retail\parquet_import_to_sql\parquet_import.py", line 97, in <module>
    insert_with_progress(df, engine, table_output, schema)
  File "X:\avelazquez\retail\parquet_import_to_sql\parquet_import.py", line 25, in insert_with_progress
    cdf.to_sql(output_table_name, con=engine, schema=output_schema, if_exists=replace_var, index=False)
  File "X:\avelazquez\retail\venv\lib\site-packages\pandas\core\generic.py", line 2872, in to_sql
    sql.to_sql(
  File "X:\avelazquez\retail\venv\lib\site-packages\pandas\io\sql.py", line 717, in to_sql
    pandas_sql.to_sql(
  File "X:\avelazquez\retail\venv\lib\site-packages\pandas\io\sql.py", line 1761, in to_sql
    sql_engine.insert_records(
  File "X:\avelazquez\retail\venv\lib\site-packages\pandas\io\sql.py", line 1350, in insert_records
    raise err
  File "X:\avelazquez\retail\venv\lib\site-packages\pandas\io\sql.py", line 1340, in insert_records
    table.insert(chunksize=chunksize, method=method)
  File "X:\avelazquez\retail\venv\lib\site-packages\pandas\io\sql.py", line 967, in insert
    exec_insert(conn, keys, chunk_iter)
  File "X:\avelazquez\retail\venv\lib\site-packages\pandas\io\sql.py", line 882, in _execute_insert
    conn.execute(self.table.insert(), data)
  File "X:\avelazquez\retail\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1286, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "X:\avelazquez\retail\venv\lib\site-packages\sqlalchemy\sql\elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(
  File "X:\avelazquez\retail\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1478, in _execute_clauseelement
    ret = self._execute_context(
  File "X:\avelazquez\retail\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1842, in _execute_context
    self._handle_dbapi_exception(
  File "X:\avelazquez\retail\venv\lib\site-packages\sqlalchemy\engine\base.py", line 2023, in _handle_dbapi_exception
    util.raise_(
  File "X:\avelazquez\retail\venv\lib\site-packages\sqlalchemy\util\compat.py", line 207, in raise_
    raise exception
  File "X:\avelazquez\retail\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1779, in _execute_context
    self.dialect.do_executemany(
  File "X:\avelazquez\retail\venv\lib\site-packages\sqlalchemy\dialects\mssql\pyodbc.py", line 550, in do_executemany
    super(MSDialect_pyodbc, self).do_executemany(
  File "X:\avelazquez\retail\venv\lib\site-packages\sqlalchemy\engine\default.py", line 714, in do_executemany
    cursor.executemany(statement, parameters)
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('Converting decimal loses precision', 'HY000')

为了完成这个故事，我在此之前遇到了 MemoryError，但将 SQLAlchemy 更新到最新版本并将其作为 SQL Server 的驱动程序 ODBC Driver 17，但现在我遇到了这个问题。

我在网上没有找到太多东西，有些人说要使用dtype 来更改我有错误但我有 70 列的列的精度，并且大部分是数字：P

【问题讨论】：