SQLAlchemy编程错误‘转换小数丢失精度’，'HY000‘
SQLAlchemy编程错误‘转换小数丢失精度’，'HY000‘
提问于 2021-10-06 21:53:37
我正在使用SQL Alchemy engine和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
回答 1
Stack Overflow用户
回答已采纳
发布于 2021-10-18 19:07:23
Gord是对的，有一些数值列被创建为varchar(max)。在创建数据帧时，我必须手动转换它们
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/69473095
复制相关文章
相似问题
腾讯云开发者
