search

mkleehammer / pyodbc

Python ODBC bridge
https://github.com/mkleehammer/pyodbc/wiki
MIT No Attribution
2.92k stars 563 forks source link

"Converting decimal loses precision" error with fast_executemany = True #1038

Closed ghost closed 2 years ago

ghost commented 2 years ago

Environment

Issue

Decimal columns cannot be transferred reading data from MS SQL Server and writing to MySQL (Aurora in my case) when fast_executemany = True for MySQL cursor. Making columns precision same or even wider on destination side doesn't give desired effect. Setting fast_executemany = False resolved the issue.

ProgrammingError('Converting decimal loses precision', 'HY000')

If someone else can confirm, we can put another note on Wiki

v-chojas commented 2 years ago

Could you post an ODBC trace? It could be driver bug.

https://github.com/mkleehammer/pyodbc/issues/1023

gordthompson commented 2 years ago

@nenkie

If someone else can confirm, we can put another note on Wiki

Confirmed:

from decimal import Decimal

import pyodbc

cnxn = pyodbc.connect(
    "Driver=MySQL ODBC 8.0 Unicode Driver;"
    "Server=localhost;Port=3307;"
    "UID=scott;PWD=tiger;"
    "Database=test;"
)
print(cnxn.getinfo(pyodbc.SQL_DRIVER_VER))
# 08.00.0028

crsr = cnxn.cursor()
crsr.execute("drop table if exists decimal_table")
crsr.execute("create table decimal_table ( d decimal(18, 4) null )")

d = Decimal("3.14")
crsr.fast_executemany = True
crsr.executemany("INSERT INTO decimal_table VALUES (?)", [(d,), (d,)])
"""
pyodbc.ProgrammingError: ('Converting decimal loses precision', 'HY000')
"""

@v-chojas

Could you post an ODBC trace?

SQL.LOG

v-chojas commented 2 years ago

Looks like another driver bug. SQLDescribeParam on a decimal column claims that it's a varchar(255).

issue_1038      17c8-1e10   EXIT  SQLDescribeParam  with return code 0 (SQL_SUCCESS)
        HSTMT               0x000000EA49F5B2D0
        UWORD                        1 
        SWORD *             0x000000EA49D2B502 (12) <--- varchar?
        SQLULEN *           0x000000EA49D2B508 (255)
        SWORD *             0x000000EA49D2B510 (0)
        SWORD *             0x000000EA49A9F358 (2)
gordthompson commented 2 years ago

Wiki updated.

dvanbolt commented 2 years ago

Also having the issue

SjoerdTimovanVliet commented 8 months ago

Is ithe error resolved?

gordthompson commented 8 months ago

Is ithe error resolved?

The issue has not been fixed in the latest version of MySQL Connector/ODBC (currently version 8.2).

The error can be avoided by using .setinputsizes(), e.g., 

d = Decimal("3.14")
crsr.fast_executemany = True
crsr.setinputsizes([(pyodbc.SQL_DECIMAL, 18, 4)])
crsr.executemany("INSERT INTO decimal_table VALUES (?)", [(d,), (d,)])
cnxn.commit()

but you will likely find that the performance with fast_executemany = True is no better than with fast_executemany = False as noted in the wiki.

You will probably get much better insert performance with .executemany() by using MySQL Connector/Python

pip install mysql-connector-python

Docs here.