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.
On Sat, Nov 26, 2022, at 12:22 PM, Lele Gaifax wrote:
> 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"
> l...@etour.tn.it | -- Rens Troost
>
> --
> 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+unsubscr...@googlegroups.com
> <mailto:sqlalchemy%2bunsubscr...@googlegroups.com>.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/877czhr5q1.fsf%40metapensiero.it.
>
--
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/ffc4dc68-e39f-48e0-81d5-b02eda611543%40app.fastmail.com.
