使用pandas向Microsoft SQL Server插入行会引发精度错误

Question

我正在尝试将数据插入mssql数据库。为此我需要一个快速方法，所以我将fast_executemany参数设置为true。上传在大多数情况下工作得很好，但如果其中一列是带时区的日期时间，则会引发:(pyodbc.Error) ('HY104', '[HY104] [Microsoft][ODBC Driver 17 for SQL Server]Invalid precision value (0) (SQLBindParameter)')崩溃如果我插入fast_executemany为False的相同数据，那么一切都工作完美。有没有人遇到过类似的问题，或者知道是什么问题?

示例代码

from sqlalchemy import create_engine, engine
import pytz
import pandas as pd
from sqlalchemy.dialects.mssql import DATETIMEOFFSET
import datetime

engine_url = engine.URL.create(
drivername='mssql',
username='admin',
password='**',
host='**',
port='1433',
database='mytestdb1',
query={'driver': "ODBC Driver 17 for SQL Server"}
)
mssql_engine = create_engine(engine_url, echo=False, fast_executemany=True)

base = datetime.datetime.today().replace(tzinfo=pytz.utc)
date_list = [base - datetime.timedelta(days=x) for x in range(20)]
df = pd.DataFrame(date_list, columns = ['date_time'])
df.to_sql('test_insert', mssql_engine, schema='testschema1', if_exists='replace', dtype = {'date_time':DATETIMEOFFSET})

反应:

DBAPIError: (pyodbc.Error) ('HY104', '[HY104] [Microsoft][ODBC Driver 17 for SQL Server]Invalid precision value (0) (SQLBindParameter)')
[SQL: INSERT INTO testschema1.test_datetime ([index], date_time) VALUES (?, ?)]
[parameters: ((0, '2022-05-06 16:40:05.434984 +00:00'), (1, '2022-05-05 16:40:05.434984 +00:00'), (2, '2022-05-04 16:40:05.434984 +00:00'), (3, '2022-05-03 16:40:05.434984 +00:00'), (4, '2022-05-02 16:40:05.434984 +00:00'), (5, '2022-05-01 16:40:05.434984 +00:00'), (6, '2022-04-30 16:40:05.434984 +00:00'), (7, '2022-04-29 16:40:05.434984 +00:00')  ... displaying 10 of 20 total bound parameter sets ...  (18, '2022-04-18 16:40:05.434984 +00:00'), (19, '2022-04-17 16:40:05.434984 +00:00'))]
(Background on this error at: http://sqlalche.me/e/14/dbapi)

sqlalchemy = = 1.4, pyodbc = = 4.0.32和熊猫= = 1.2.0

正如我所说的，如果我不使用fast_executemany，代码就可以完美地工作。

Answer 1

我通过更改连接字符串中的驱动程序找到了解决方案。我曾经使用:{SQL Server}。然后我把它改成:{ODBC Driver 17 for SQL Server}

Answer 2

使用SQLAlchemy 1.4.0可以重现此问题。在SQLAlchemy 1.4.1中已修复。