SQL Server precision and scale of numeric fields in sqlalchemy/python query-CodePudding

I have a sql server numeric(10,2) field in a table. An example record has de value 25.00 saved in de database. When I execute a Sqlalchemy query in a Python script, it always returns the value 2500, no matter I define the field as float or numeric in the table class of the model script.

The example value as seen with SSMS:

The field as defined in the table class (models.py script):

percentualExecucao       = db.Column(db.Float)

The result of a Sqlalchemy query (a print of the field and its type):

*** 2500.0 <class 'float'>

If I change de column type to:

percentualExecucao       = db.Column(db.Numeric)

I have this:

*** 2500 <class 'decimal.Decimal'>

What am I missing here?

CodePudding user response：

I am able to reproduce your issue. With my Windows "Region" format set to "Portugese (Brazil)"

and using the ancient "SQL Server" ODBC driver, I get

import sqlalchemy as sa

connection_url = sa.engine.URL.create(
    "mssql pyodbc",
    username="scott",
    password="tiger^5HHH",
    host="192.168.0.199",
    database="test",
    query = {
        "driver": "SQL Server",
        "regional": "yes",
    }
)
engine = sa.create_engine(connection_url)

with engine.begin() as conn:
    conn.exec_driver_sql("DROP TABLE IF EXISTS foo")
    conn.exec_driver_sql("CREATE TABLE foo (n numeric(10,2))")
    conn.exec_driver_sql("INSERT INTO foo (n) VALUES (25)")

tbl_foo = sa.Table("foo", sa.MetaData(), autoload_with=engine)

with engine.begin() as conn:
    n = conn.execute(sa.select(tbl_foo.c.n)).scalar()
    print(n)  # 2500
    print(type(n))  # <class 'decimal.Decimal'>

If I change "regional" to "no" then I get the correct result

with engine.begin() as conn:
    n = conn.execute(sa.select(tbl_foo.c.n)).scalar()
    print(n)  # 25.00
    print(type(n))  # <class 'decimal.Decimal'>

Note that this is not a bug in SQLAlchemy. I get the same results with plain pyodbc:

import pyodbc

cnxn = pyodbc.connect(
    "Driver=SQL Server;"
    "UID=scott;PWD=tiger^5HHH;"
    "Server=192.168.0.199;"
    "Database=test;"
    "regional=yes;"
)
crsr = cnxn.cursor()

crsr.execute("DROP TABLE IF EXISTS foo")
crsr.execute("CREATE TABLE foo (n numeric(10,2))")
crsr.execute("INSERT INTO foo (n) VALUES (25)")

n = crsr.execute("SELECT n FROM foo").fetchval()
print(n)  # 2500
print(type(n))  # <class 'decimal.Decimal'>

In fact, this seems to be a known issue with pyodbc

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

CodePudding user response：

With the kind help of @Gord Thompson, the problem was solved using

pyodbc.setDecimalSeparator('.')

as suggested in,

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