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"
[email protected] | -- 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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/877czhr5q1.fsf%40metapensiero.it.
