Inserting rows into Microsoft SQL Server using pandas raises precision error

Question

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.

Jeepers

Please edit your question to include a minimal reproducible example. FWIW, this code works for me. – Rasbora

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? – Jeepers

No. You posted code that didn't repro the issue. – Knowledgeable

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 – Jeepers

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. – Rasbora

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 . :-) – Jeepers

What about "mssql_engine = create_engine('mssql+pymssql://host/database')" ? That worked for me – Camboose

Answer 1

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

Rasbora

I have the same problem with SQLAlchemy 2.0.5.post1 – Disendow

@Disendow - Then you should ask a new question that includes a minimal reproducible example. – Rasbora

@Disendow - FWIW, the repro code in the question does not throw an error with SQLAlchemy 2.0.5.post1 or 2.0.6, so your issue must be something different. – Rasbora

In my case using use_setinputsizes=False in create_engine make it works. Anyway, thanks! – Disendow

github.com/sqlalchemy/sqlalchemy/issues/8681 indicates more information on how to create that fix. – Hepza

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

Deen

Answer 3

Faced this problem and solved it after installing the below version of SQLAlchemy.

pip install SQLAlchemy==1.4.45

Ceres