当前位置：文江博客话题详情

sqlalchemy/python查询中的数字字段的SQL Server精度和比例

发布于 2025-02-10 03:09:25 字数 626 浏览 4 评论 0 原文

我在表中有一个SQL Server数字（10,2）字段。示例记录具有DE DATABASE中保存的DE值25.00。当我在Python脚本中执行SQLalchemy查询时，无论我将字段定义为模型脚本的表类中的float或数字，它总是返回值2500。

SSMS所看到的示例值：

percentualExecucao       = db.Column(db.Float)

*** 2500.0＆lt; class'float'＆gt;

如果我将de line类型更改为：

percentualExecucao       = db.Column(db.Numeric)

我有：

*** 2500＆lt;类'Decimal.decimal'＆gt;

我在这里想念什么？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

我是有多爱你 2025-02-17 03:09:25

我能够复制您的问题。用我的Windows“区域”格式设置为“葡萄牙（巴西）”

并使用古代的“ SQL Server” ODBC驱动程序，

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'>

如果我将“区域性”更改为“否”，我会得到正确的结果

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'>

请注意，这不是Sqlalchemy中的错误。我通过普通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'>

实际上，这似乎是pyodbc

htttps> htttps> https ：//github.com/mkleehammer/pyodbc/issues/753

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

回复收藏 0 原文