Floats are rounded to whole numbers when inserted in batches #9701
Comments
rbuffat
changed the title
zzzeek
added
postgresql
external driver issues
|
hi - the test case does not involve the "Data1" class in any way and this would appear to be superfluous. This appears to be a bug in the psycopg driver, and/or special handling would be needed. the MCVE below is a complete example and works fine with asyncpg from __future__ import annotations
import asyncio
from sqlalchemy import Double
from sqlalchemy import Integer
from sqlalchemy import PrimaryKeyConstraint
from sqlalchemy import select
from sqlalchemy import Sequence
from sqlalchemy.ext.asyncio import async_sessionmaker
from sqlalchemy.ext.asyncio import create_async_engine
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
class Base(DeclarativeBase):
pass
class Update(Base):
__tablename__ = "test"
__table_args__ = (PrimaryKeyConstraint("id", name="update_pkey"),)
id: Mapped[int] = mapped_column(
Integer, Sequence("test_id_seq"), primary_key=True
)
value: Mapped[float] = mapped_column(Double(53))
async def main():
engine = create_async_engine(
# asyncpg works fine
#"postgresql+asyncpg://scott:tiger@localhost/test", echo=True
# psycopg does not
"postgresql+psycopg://scott:tiger@localhost/test", echo=True
)
async with engine.begin() as conn:
await conn.run_sync(Base.metadata.drop_all)
await conn.run_sync(Base.metadata.create_all)
async_session = async_sessionmaker(engine)
for _ in range(10):
async with async_session() as session:
for _ in range(4):
update = Update(value=8.5514716)
session.add(update)
await session.commit()
async with async_session() as session:
for val in await session.scalars(select(Update.value)):
assert val == 8.5514716
asyncio.run(main()) |
|
I'd report this to https://github.com/psycopg/psycopg and if we need to convert to decimal here or something, they should tell us that. cc @dvarrazzo |
|
@CaselIT this works with 2.0.9 and fails w 2.0.10 so psycopg is not interpreting our alternate form correctly |
|
@dvarrazzo this is the query that's not being interpreted correctly is there some kind of SQL parsing in psycopg that is affecting this? |
zzzeek
added
regression
zzzeek
added
the
near-term release
|
we're diong casts for asyncpg. we will add these in for psycopg. @dvarrazzo sorry for the noise |
|
only when that precision value is there btw, which is wierd |
|
Looking at the statement I think that the issue is on psycopg side. The values are passed as float, and the query has the correct casts. Not sure what different we should be doing here |
sure, but I don't think psycopg should take a float, do |
|
Nevermind, the issue is that we transform Double(53) in Numeric(53). select 8.5514716::numeric(53)returns 9 |
CaselIT
added
bug
|
Mike Bayer has proposed a fix for this issue in the main branch: use casts for floats under both psycopg drivers https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/4585 |
|
oh it's even that...that explains something here.. |
|
oh neat it's just that first cast, great |
|
🥳 Huge thank you to all who were involved in fixing this issue! |
Describe the bug
A working example of this behavior can be found in the following repository: https://github.com/rbuffat/sqlissue
In short, the following code should insert the value 8.5514716, but inserts 9:
Full example code can be found here: https://github.com/rbuffat/sqlissue/blob/main/test/src/main.py
When the session.commit is moved into the inner loop, the correct value is saved into the database.
Postgres log:
SQLalchemy log:
Result:
Optional link from https://docs.sqlalchemy.org which documents the behavior that is expected
No response
SQLAlchemy Version in Use
2.0.10
DBAPI (i.e. the database driver)
postgresql+psycopg (psycopg==3.1.8)
Database Vendor and Major Version
postgis/postgis:13-3.3-alpine
Python Version
3.11
Operating system
Linux
To Reproduce
Error
No errors shown
Additional context
No response
The text was updated successfully, but these errors were encountered: