4

I am trying to insert data into a mssql database. I needed as fast method for this so I set the fast_executemany param to true. The upload works fine for most part but if one of the column is a datetime with timezone it crashes raising: (pyodbc.Error) ('HY104', '[HY104] [Microsoft][ODBC Driver 17 for SQL Server]Invalid precision value (0) (SQLBindParameter)') If I insert the same data with fast_executemany as False then everythhing works perfectly. Did anyone came across a similar problem or know what might be the issue?

Sample code

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})

response:

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 and pandas==1.2.0

As I said the code works perfectly if I dont use fast_executemany.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Asfandyar Abbasi
  • 53
  • 2
  • 7
  • Please [edit] your question to include a [mcve]. FWIW, [this code](https://pastebin.com/88rRWCJ3) works for me. – Gord Thompson May 06 '22 at 14:21
  • Thanks @GordThompson. The only difference I see between the two codes is fast_executemany=True in my code. Also I tried creating raw sql queries as you did but pyodbc (or mssql) doesnt let me run an insert with more than 1000 rows. Does it help you answer my question? – Asfandyar Abbasi May 06 '22 at 14:54
  • 1
    No. You posted code that didn't repro the issue. – David Browne - Microsoft May 06 '22 at 16:03
  • Did you execute with fast_executemany as True? I can't make it work for some reason. I am adding version details in my question maybe that might be the issue but I tried a couple of combinations – Asfandyar Abbasi May 11 '22 at 09:07
  • I copied and pasted your exact code into PyCharm, added the missing `from sqlalchemy import engine` directive, and ran it. It works fine for me. pandas 1.2.0 and 1.4.2, SQLAlchemy 1.4.36, pyodbc 4.0.32, SQL Server 2019. – Gord Thompson May 11 '22 at 13:42
  • Actually I am wrong in my last comment, upgrading to sqlalchemy version 1.4.36 fixed the problem. I am using jupyter to make tests and I forgot to restart the kernel. So the problem here was sqlalchemy. Thanks @GordThompson . :-) – Asfandyar Abbasi May 12 '22 at 11:09

2 Answers2

2

I found the solution by changing the driver in the connection string. I used to use: {SQL Server}. Then I changed it to: {ODBC Driver 17 for SQL Server}

Marco Graziano
  • 81
  • 2
  • 3
0

This issue can be reproduced using SQLAlchemy 1.4.0. It was fixed in SQLAlchemy 1.4.1.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418