使用SQLAlchemy和pyodbc注入DataFrame至SQL时遇HY104错误

问题详情 #

使用SQLAlchemy和pyodbc将DataFrame写入SQL Server，脚本打包成可执行文件后运行完全正常，但在Visual Studio Code中运行相同脚本时，触发HY104精度错误。已确认DataFrame与SQL表的数据类型完全匹配。

代码示例 #

from sqlalchemy import create_engine
import urllib
import pyodbc

quoted = urllib.parse.quote_plus("DRIVER={SQL Server};SERVER=vmalsdisdb;DATABASE=Produits")
engine = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(quoted))

Leo.to_sql('PRELEVEMENT_CANAL_DIRECT', con=engine, schema='dbo', method='multi', chunksize=200, index=False, if_exists='append')

错误信息 #

Exception in Tkinter callback

pyodbc.Error: ('HY104', '[HY104] [Microsoft][ODBC SQL Server Driver]Invalid precision value (0) (SQLBindParameter)')
   
Exception in Tkinter callback Traceback (most recent call last): File "C:\Users\HAMZAM\Desktop\projet python\pythonProject1\testprojet\Lib\site-packages\sqlalchemy\engine\base.py", line 1969, in _exec_single_context self.dialect.do_execute( File "C:\Users\HAMZAM\Desktop\projet python\pythonProject1\testprojet\Lib\site-packages\sqlalchemy\engine\default.py", line 922, in do_execute cursor.execute(statement, parameters) pyodbc.Error: ('HY104', '[HY104] [Microsoft][ODBC SQL Server Driver]Valeur de précision non valide (0) (SQLBindParameter)')The above exception was the direct cause of the following exception:Traceback (most recent call last): File "C:\Users\HAMZAM\AppData\Local\Programs\Python\Python311\Lib\tkinter_init_.py", line 1948, in call return self.func(*args) ^^^^^^^^^^^^^^^^ File "c:\Users\HAMZAM\Desktop\projet python\pythonProject1\Test universel2.py", line 450, in inject Leo.to_sql('PRELEVEMENT_CANAL_DIRECT', con=engine, schema='dbo', method='multi', chunksize=200, index=False,if_exists='append') File "C:\Users\HAMZAM\Desktop\projet python\pythonProject1\testprojet\Lib\site-packages\pandas\util_decorators.py", line 333, in wrapper return func(*args, **kwargs) ^^^^^^^^^^^^^^^^^^^^^ File "C:\Users\HAMZAM\Desktop\projet python\pythonProject1\testprojet\Lib\site-packages\pandas\core\generic.py", line 3081, in to_sql return sql.to_sql( ^^^^^^^^^^^ File "C:\Users\HAMZAM\Desktop\projet python\pythonProject1\testprojet\Lib\site-packages\pandas\io\sql.py", line 842, in to_sql return pandas_sql.to_sql( ^^^^^^^^^^^^^^^^^^ File "C:\Users\HAMZAM\Desktop\projet python\pythonProject1\testprojet\Lib\site-packages\pandas\io\sql.py", line 2011, in to_sql table = self.prep_table( ^^^^^^^^^^^^^^^^ File "C:\Users\HAMZAM\Desktop\projet python\pythonProject1\testprojet\Lib\site-packages\pandas\io\sql.py", line 1915, in prep_table table.create() File "C:\Users\HAMZAM\Desktop\projet python\pythonProject1\testprojet\Lib\site-packages\pandas\io\sql.py", line 984, in create if self.exists(): ^^^^^^^^^^^^^ File "C:\Users\HAMZAM\Desktop\projet python\pythonProject1\testprojet\Lib\site-packages\pandas\io\sql.py", line 970, in exists return self.pd_sql.has_table(self.name, self.schema) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "C:\Users\HAMZAM\Desktop\projet python\pythonProject1\testprojet\Lib\site-packages\pandas\io\sql.py", line 2044, in has_table return insp.has_table(name, schema or self.meta.schema) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "C:\Users\HAMZAM\Desktop\projet python\pythonProject1\testprojet\Lib\site-packages\sqlalchemy\engine\reflection.py", line 429, in has_table return self.dialect.has_table( ^^^^^^^^^^^^^^^^^^^^^^^ File "C:\Users\HAMZAM\Desktop\projet python\pythonProject1\testprojet\Lib\site-packages\sqlalchemy\dialects\mssql\base.py", line 2892, in wrap return _switch_db( ^^^^^^^^^^^ File "C:\Users\HAMZAM\Desktop\projet python\pythonProject1\testprojet\Lib\site-packages\sqlalchemy\dialects\mssql\base.py", line 2916, in _switch_db return fn(*arg, **kw) ^^^^^^^^^^^^^^ File "C:\Users\HAMZAM\Desktop\projet python\pythonProject1\testprojet\Lib\site-packages\sqlalchemy\dialects\mssql\base.py", line 3305, in has_table return self._internal_has_table(connection, tablename, owner, **kw) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "", line 2, in _internal_has_table File "C:\Users\HAMZAM\Desktop\projet python\pythonProject1\testprojet\Lib\site-packages\sqlalchemy\engine\reflection.py", line 97, in cache ret = fn(self, con, *args, **kw) ^^^^^^^^^^^^^^^^^^^^^^^^^^ File "C:\Users\HAMZAM\Desktop\projet python\pythonProject1\testprojet\Lib\site-packages\sqlalchemy\dialects\mssql\base.py", line 3408, in _internal_has_table c = connection.execute(s) ^^^^^^^^^^^^^^^^^^^^^ File "C:\Users\HAMZAM\Desktop\projet python\pythonProject1\testprojet\Lib\site-packages\sqlalchemy\engine\base.py", line 1416, in execute return meth( ^^^^^ File "C:\Users\HAMZAM\Desktop\projet python\pythonProject1\testprojet\Lib\site-packages\sqlalchemy\sql\elements.py", line 517, in _execute_on_connection return connection._execute_clauseelement( ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "C:\Users\HAMZAM\Desktop\projet python\pythonProject1\testprojet\Lib\site-packages\sqlalchemy\engine\base.py", line 1639, in _execute_clauseelement ret = self._execute_context( ^^^^^^^^^^^^^^^^^^^^^^ File "C:\Users\HAMZAM\Desktop\projet python\pythonProject1\testprojet\Lib\site-packages\sqlalchemy\engine\base.py", line 1848, in _execute_context return self._exec_single_context( ^^^^^^^^^^^^^^^^^^^^^^^^^^ File "C:\Users\HAMZAM\Desktop\projet python\pythonProject1\testprojet\Lib\site-packages\sqlalchemy\engine\base.py", line 1988, in _exec_single_context self._handle_dbapi_exception( File "C:\Users\HAMZAM\Desktop\projet python\pythonProject1\testprojet\Lib\site-packages\sqlalchemy\engine\base.py", line 2344, in _handle_dbapi_exception raise sqlalchemy_exception.with_traceback(exc_info[2]) from e File "C:\Users\HAMZAM\Desktop\projet python\pythonProject1\testprojet\Lib\site-packages\sqlalchemy\engine\base.py", line 1969, in _exec_single_context self.dialect.do_execute( File "C:\Users\HAMZAM\Desktop\projet python\pythonProject1\testprojet\Lib\site-packages\sqlalchemy\engine\default.py", line 922, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('HY104', '[HY104] [Microsoft][ODBC SQL Server Driver]Valeur de précision non valide (0) (SQLBindParameter)') [SQL: SELECT [INFORMATION_SCHEMA].[TABLES].[TABLE_NAME] FROM [INFORMATION_SCHEMA].[TABLES] WHERE ([INFORMATION_SCHEMA].[TABLES].[TABLE_TYPE] = CAST(? AS NVARCHAR(max)) OR [INFORMATION_SCHEMA].[TABLES].[TABLE_TYPE] = CAST(? AS NVARCHAR(max))) AND [INFORMATION_SCHEM A].[TABLES].[TABLE_NAME] = CAST(? AS NVARCHAR(max)) AND [INFORMATION_SCHEMA].[TABLES].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max))] [parameters: ('BASE TABLE', 'VIEW', 'PRELEVEMENT_CANAL_DIRECT', 'dbo')]

解决方案 #

1. 更新ODBC驱动 #

错误来自旧版SQL Server驱动的参数绑定问题，改用官方推荐的ODBC Driver 17 for SQL Server，修改连接字符串：

quoted = urllib.parse.quote_plus("DRIVER={ODBC Driver 17 for SQL Server};SERVER=vmalsdisdb;DATABASE=Produits")

2. 同步依赖包版本 #

VS Code的虚拟环境与打包工具（如PyInstaller）使用的依赖版本不一致，导致行为差异。执行以下命令同步pandas、sqlalchemy、pyodbc的版本：

# 替换为打包时使用的具体版本号
pip install pandas==x.x.x sqlalchemy==x.x.x pyodbc==x.x.x

3. 调整to_sql参数 #

错误发生在pandas检查表存在性的反射阶段，可尝试：

• 移除method='multi'，使用默认插入方式排查问题：

Leo.to_sql('PRELEVEMENT_CANAL_DIRECT', con=engine, schema='dbo', chunksize=200, index=False, if_exists='append')

• 手动确认表存在后，直接执行插入，跳过反射检查（可通过pyodbc直接编写插入逻辑）。

4. 清理DataFrame特殊值 #

检查并清理DataFrame中的空字符串或异常值，避免参数绑定出错：

# 将空字符串替换为SQL兼容的NULL
Leo = Leo.replace('', None)
# 确保字符串列长度不超过SQL表字段的定义长度

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