Learning to use new PG Ranges

[Lele Gaifax]

Hi,

now and then I'm spending some time migrating one app from SA v1.4 to
v2: most of the porting problems stem from the new PG Range class, and
today I hit the following, that I'm not sure how I should fix.

This script is a contrived example, reducing the real code down to the
essential:

from datetime import date

from sqlalchemy import Date, Integer, create_engine, func, select
from sqlalchemy.dialects.postgresql import DATERANGE, Range
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session


class Base(DeclarativeBase):
pass


class Contract(Base):
__tablename__ = "contracts"

id: Mapped[int] = mapped_column(primary_key=True)
validity: Mapped[Range[date]] = mapped_column(DATERANGE)


class Usage(Base):
__tablename__ = 'usages'

id: Mapped[int] = mapped_column(primary_key=True)
date: Mapped[date] = mapped_column(Date)
used_amount: Mapped[int] = mapped_column(Integer)


engine = create_engine("postgresql+psycopg://postgres@localhost/sa_tests", echo=True)

Base.metadata.create_all(engine)

with Session(engine) as session:
for usage in session.scalars(select(Usage)):
session.delete(usage)

for contract in session.scalars(select(Contract)):
session.delete(contract)

session.commit()

session.add(Contract(validity=Range(date(2022, 1, 1), date(2022, 12, 31))))
session.add(Usage(date=date(2022, 1, 10), used_amount=15))
session.add(Usage(date=date(2022, 2, 22), used_amount=22))

session.commit()

ccontract = session.execute(
select(Contract)
.where(Contract.validity.contains(date.today()))).scalar_one()
tusage = session.execute(
select(func.sum(Usage.used_amount))
.where(Usage.date.op('<@')(ccontract.validity))).scalar_one()


As said, original code is written slightly different as it targets v1.4,
but at the SQL level the logic is the same: given a DATERANGE column, it
executes

SELECT sum(used_amount) FROM some_table WHERE date <@ :validity

When using v2 though, I get the following log:

...
INSERT INTO usages (date, used_amount) VALUES (%(date__0)s::DATE, %(used_amount__0)s::INTEGER), (%(date__1)s::DATE, %(used_amount__1)s::INTEGER) RETURNING usages.id
[generated in 0.00037s (insertmanyvalues)] {'used_amount__0': 15, 'date__0': datetime.date(2022, 1, 10), 'used_amount__1': 22, 'date__1': datetime.date(2022, 2, 22)}
COMMIT
BEGIN (implicit)
SELECT contracts.id, contracts.validity
FROM contracts
WHERE contracts.validity @> %(validity_1)s::DATE
[generated in 0.00064s] {'validity_1': datetime.date(2022, 11, 26)}
SELECT sum(usages.used_amount) AS sum_1
FROM usages
WHERE usages.date <@ %(date_1)s::DATE
[generated in 0.00042s] {'date_1': Range(lower=datetime.date(2022, 1, 1), upper=datetime.date(2022, 12, 31), bounds='[)', empty=False)}
ROLLBACK
Traceback (most recent call last):
File "/nix/.../sqlalchemy/engine/base.py", line 1964, in _exec_single_context
self.dialect.do_execute(
File "/nix/.../sqlalchemy/engine/default.py", line 743, in do_execute
cursor.execute(statement, parameters)
File "/nix/.../psycopg/cursor.py", line 725, in execute
raise ex.with_traceback(None)
psycopg.ProgrammingError: cannot adapt type 'Range' using placeholder '%s' (format: AUTO)

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "/home/lele/wip/etour/ndn/hopi/next/tmp/sa_range_contains/test.py", line 49, in <module>
tusage = session.execute(
File "/nix/.../sqlalchemy/orm/session.py", line 2081, in execute
return self._execute_internal(
File "/nix/.../sqlalchemy/orm/session.py", line 1976, in _execute_internal
result: Result[Any] = compile_state_cls.orm_execute_statement(
File "/nix/.../sqlalchemy/orm/context.py", line 250, in orm_execute_statement
result = conn.execute(
File "/nix/.../sqlalchemy/engine/base.py", line 1414, in execute
return meth(
File "/nix/.../sqlalchemy/sql/elements.py", line 487, in _execute_on_connection
return connection._execute_clauseelement(
File "/nix/.../sqlalchemy/engine/base.py", line 1638, in _execute_clauseelement
ret = self._execute_context(
File "/nix/.../sqlalchemy/engine/base.py", line 1842, in _execute_context
return self._exec_single_context(
File "/nix/.../sqlalchemy/engine/base.py", line 1983, in _exec_single_context
self._handle_dbapi_exception(
File "/nix/.../sqlalchemy/engine/base.py", line 2325, in _handle_dbapi_exception
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "/nix/.../sqlalchemy/engine/base.py", line 1964, in _exec_single_context
self.dialect.do_execute(
File "/nix/.../sqlalchemy/engine/default.py", line 743, in do_execute
cursor.execute(statement, parameters)
File "/nix/.../psycopg/cursor.py", line 725, in execute
raise ex.with_traceback(None)
sqlalchemy.exc.ProgrammingError: (psycopg.ProgrammingError) cannot adapt type 'Range' using placeholder '%s' (format: AUTO)
[SQL: SELECT sum(usages.used_amount) AS sum_1
FROM usages
WHERE usages.date <@ %(date_1)s::DATE]
[parameters: {'date_1': Range(lower=datetime.date(2022, 1, 1), upper=datetime.date(2022, 12, 31), bounds='[)', empty=False)}]
(Background on this error at: https://sqlalche.me/e/20/f405)

What is the proper way to do the above with v2?

For the sake of completeness, here is the v1.4 variant:

from datetime import date

from psycopg2.extras import DateRange
from sqlalchemy import Column, Date, Integer, create_engine, func, select
from sqlalchemy.dialects.postgresql import DATERANGE
from sqlalchemy.orm import declarative_base, Session


Base = declarative_base()


class Contract(Base):
__tablename__ = "contracts"

id = Column(Integer, primary_key=True)
validity = Column(DATERANGE)


class Usage(Base):
__tablename__ = 'usages'

id = Column(Integer, primary_key=True)
date = Column(Date)
used_amount = Column(Integer)


engine = create_engine("postgresql://postgres@localhost/sa_tests", echo=True)

Base.metadata.create_all(engine)

with Session(engine) as session:
for usage in session.scalars(select(Usage)):
session.delete(usage)

for contract in session.scalars(select(Contract)):
session.delete(contract)

session.commit()

session.add(Contract(validity=DateRange(date(2022, 1, 1), date(2022, 12, 31))))
session.add(Usage(date=date(2022, 1, 10), used_amount=15))
session.add(Usage(date=date(2022, 2, 22), used_amount=22))

session.commit()

ccontract = session.execute(
select(Contract)
.where(Contract.validity.contains(date.today()))).scalar_one()
tusage = session.execute(
select(func.sum(Usage.used_amount))
.where(Usage.date.op('<@')(ccontract.validity))).scalar_one()

that emits the following:

...
INSERT INTO usages (date, used_amount) VALUES (%(date)s, %(used_amount)s) RETURNING usages.id
[generated in 0.00055s] ({'date': datetime.date(2022, 1, 10), 'used_amount': 15}, {'date': datetime.date(2022, 2, 22), 'used_amount': 22})
COMMIT
BEGIN (implicit)
SELECT contracts.id, contracts.validity
FROM contracts
WHERE contracts.validity @> %(validity_1)s
[generated in 0.00055s] {'validity_1': datetime.date(2022, 11, 26)}
SELECT sum(usages.used_amount) AS sum_1
FROM usages
WHERE usages.date <@ %(date_1)s
[generated in 0.00055s] {'date_1': DateRange(datetime.date(2022, 1, 1), datetime.date(2022, 12, 31), '[)')}
ROLLBACK

Thanks in advance,
ciao, lele.
--
nickname: Lele Gaifax | Dire che Emacs è "conveniente" è come
real: Emanuele Gaifas | etichettare l'ossigeno come "utile"
le...@etour.tn.it | -- Rens Troost

[Mike Bayer]

well this fixes for now

    tusage = session.execute(

        select(func.sum(Usage.used_amount))

        .where(Usage.date.op('<@')(cast(ccontract.validity, DATERANGE)))).scalar_one()

otherwise what is happening is Usage.date.op(...)(Range(...)) doesn't have any way at the moment to know what SQL type applies to the object that is coming in, because left side is a Date and if it doesnt know what right side is, assumes it should be interpreted as Date also which is a passthrough.

so the quick and easy way is to add Range to sqlalchemy.sql.sqltypes._type_map, but that's "cheating" because if I were a third party dialect, I'd not have that privilege without hardcoding to private API.   We dont seem to have any precedent for adding new types to this map at the moment, and it seems in the case of Range, cross-type operations like this are more common, so it would be good to come up with something, so I've created a feature at https://github.com/sqlalchemy/sqlalchemy/issues/8884 .  if you can test this out, we can write a few test_compiler.py tests and that can go in.

-- 

SQLAlchemy - 

The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full description.

--- 

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/877czhr5q1.fsf%40metapensiero.it.