Skip to content

Commit 4006cb3

Browse files
TallJimboCaselIT
TallJimbo
authored and
CaselIT
committed
Fix typing generics in PostgreSQL range types.
Correctly type PostgreSQL RANGE and MULTIRANGE types as ``Range[T]`` and ``Sequence[Range[T]]``. Introduced utility sequence ``MultiRange`` to allow better interoperability of MULTIRANGE types. Fixes sqlalchemy#9736 Closes: sqlalchemy#10625 Pull-request: sqlalchemy#10625 Pull-request-sha: 2c17bc5 Change-Id: I4f91d0233b29fd8101e67bdd4cd0aa2524ab788a
1 parent ffdbd32 commit 4006cb3

12 files changed

Lines changed: 352 additions & 81 deletions

File tree

‎doc/build/changelog/unreleased_20/9736.rst‎

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,16 @@
1+
.. change::
2+
:tags: postgresql, usecase
3+
:tickets: 9736
4+
5+
Correctly type PostgreSQL RANGE and MULTIRANGE types as ``Range[T]``
6+
and ``Sequence[Range[T]]``.
7+
Introduced utility sequence :class:`_postgresql.MultiRange` to allow better
8+
interoperability of MULTIRANGE types.
9+
10+
.. change::
11+
:tags: postgresql, usecase
12+
13+
Differentiate between INT4 and INT8 ranges and multi-ranges types when
14+
inferring the database type from a :class:`_postgresql.Range` or
15+
:class:`_postgresql.MultiRange` instance, preferring INT4 if the values
16+
fit into it.

‎doc/build/dialects/postgresql.rst‎

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -238,6 +238,8 @@ dialect, **does not** support multirange datatypes.
238238
.. versionadded:: 2.0.17 Added multirange support for the pg8000 dialect.
239239
pg8000 1.29.8 or greater is required.
240240

241+
.. versionadded:: 2.0.26 :class:`_postgresql.MultiRange` sequence added.
242+
241243
The example below illustrates use of the :class:`_postgresql.TSMULTIRANGE`
242244
datatype::
243245

@@ -260,6 +262,7 @@ datatype::
260262

261263
id: Mapped[int] = mapped_column(primary_key=True)
262264
event_name: Mapped[str]
265+
added: Mapped[datetime]
263266
in_session_periods: Mapped[List[Range[datetime]]] = mapped_column(TSMULTIRANGE)
264267

265268
Illustrating insertion and selecting of a record::
@@ -294,6 +297,38 @@ Illustrating insertion and selecting of a record::
294297
a new list to the attribute, or use the :class:`.MutableList`
295298
type modifier. See the section :ref:`mutable_toplevel` for background.
296299

300+
.. _postgresql_multirange_list_use:
301+
302+
Use of a MultiRange sequence to infer the multirange type
303+
"""""""""""""""""""""""""""""""""""""""""""""""""""""""""
304+
305+
When using a multirange as a literal without specifying the type
306+
the utility :class:`_postgresql.MultiRange` sequence can be used::
307+
308+
from sqlalchemy import literal
309+
from sqlalchemy.dialects.postgresql import MultiRange
310+
311+
with Session(engine) as session:
312+
stmt = select(EventCalendar).where(
313+
EventCalendar.added.op("<@")(
314+
MultiRange(
315+
[
316+
Range(datetime(2023, 1, 1), datetime(2013, 3, 31)),
317+
Range(datetime(2023, 7, 1), datetime(2013, 9, 30)),
318+
]
319+
)
320+
)
321+
)
322+
in_range = session.execute(stmt).all()
323+
324+
with engine.connect() as conn:
325+
row = conn.scalar(select(literal(MultiRange([Range(2, 4)]))))
326+
print(f"{row.lower} -> {row.upper}")
327+
328+
Using a simple ``list`` instead of :class:`_postgresql.MultiRange` would require
329+
manually setting the type of the literal value to the appropriate multirange type.
330+
331+
.. versionadded:: 2.0.26 :class:`_postgresql.MultiRange` sequence added.
297332

298333
The available multirange datatypes are as follows:
299334

@@ -416,6 +451,8 @@ construction arguments, are as follows:
416451
.. autoclass:: sqlalchemy.dialects.postgresql.AbstractRange
417452
:members: comparator_factory
418453

454+
.. autoclass:: sqlalchemy.dialects.postgresql.AbstractSingleRange
455+
419456
.. autoclass:: sqlalchemy.dialects.postgresql.AbstractMultiRange
420457

421458

@@ -529,6 +566,9 @@ construction arguments, are as follows:
529566
.. autoclass:: TSTZMULTIRANGE
530567

531568

569+
.. autoclass:: MultiRange
570+
571+
532572
PostgreSQL SQL Elements and Functions
533573
--------------------------------------
534574

‎lib/sqlalchemy/dialects/postgresql/__init__.py‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -57,12 +57,14 @@
5757
from .named_types import NamedType
5858
from .ranges import AbstractMultiRange
5959
from .ranges import AbstractRange
60+
from .ranges import AbstractSingleRange
6061
from .ranges import DATEMULTIRANGE
6162
from .ranges import DATERANGE
6263
from .ranges import INT4MULTIRANGE
6364
from .ranges import INT4RANGE
6465
from .ranges import INT8MULTIRANGE
6566
from .ranges import INT8RANGE
67+
from .ranges import MultiRange
6668
from .ranges import NUMMULTIRANGE
6769
from .ranges import NUMRANGE
6870
from .ranges import Range

‎lib/sqlalchemy/dialects/postgresql/asyncpg.py‎

Lines changed: 4 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -178,8 +178,6 @@
178178
import re
179179
import time
180180
from typing import Any
181-
from typing import cast
182-
from typing import Iterable
183181
from typing import NoReturn
184182
from typing import Optional
185183
from typing import Protocol
@@ -368,7 +366,7 @@ class AsyncpgCHAR(sqltypes.CHAR):
368366
render_bind_cast = True
369367

370368

371-
class _AsyncpgRange(ranges.AbstractRangeImpl):
369+
class _AsyncpgRange(ranges.AbstractSingleRangeImpl):
372370
def bind_processor(self, dialect):
373371
asyncpg_Range = dialect.dbapi.asyncpg.Range
374372

@@ -422,10 +420,7 @@ def to_range(value):
422420
)
423421
return value
424422

425-
return [
426-
to_range(element)
427-
for element in cast("Iterable[ranges.Range]", value)
428-
]
423+
return [to_range(element) for element in value]
429424

430425
return to_range
431426

@@ -444,7 +439,7 @@ def to_range(rvalue):
444439
return rvalue
445440

446441
if value is not None:
447-
value = [to_range(elem) for elem in value]
442+
value = ranges.MultiRange(to_range(elem) for elem in value)
448443

449444
return value
450445

@@ -1063,7 +1058,7 @@ class PGDialect_asyncpg(PGDialect):
10631058
OID: AsyncpgOID,
10641059
REGCLASS: AsyncpgREGCLASS,
10651060
sqltypes.CHAR: AsyncpgCHAR,
1066-
ranges.AbstractRange: _AsyncpgRange,
1061+
ranges.AbstractSingleRange: _AsyncpgRange,
10671062
ranges.AbstractMultiRange: _AsyncpgMultiRange,
10681063
},
10691064
)

‎lib/sqlalchemy/dialects/postgresql/pg8000.py‎

Lines changed: 4 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -253,7 +253,7 @@ class _PGOIDVECTOR(_SpaceVector, OIDVECTOR):
253253
pass
254254

255255

256-
class _Pg8000Range(ranges.AbstractRangeImpl):
256+
class _Pg8000Range(ranges.AbstractSingleRangeImpl):
257257
def bind_processor(self, dialect):
258258
pg8000_Range = dialect.dbapi.Range
259259

@@ -304,15 +304,13 @@ def result_processor(self, dialect, coltype):
304304
def to_multirange(value):
305305
if value is None:
306306
return None
307-
308-
mr = []
309-
for v in value:
310-
mr.append(
307+
else:
308+
return ranges.MultiRange(
311309
ranges.Range(
312310
v.lower, v.upper, bounds=v.bounds, empty=v.is_empty
313311
)
312+
for v in value
314313
)
315-
return mr
316314

317315
return to_multirange
318316

‎lib/sqlalchemy/dialects/postgresql/psycopg.py‎

Lines changed: 7 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -165,7 +165,7 @@ class _PGBoolean(sqltypes.Boolean):
165165
render_bind_cast = True
166166

167167

168-
class _PsycopgRange(ranges.AbstractRangeImpl):
168+
class _PsycopgRange(ranges.AbstractSingleRangeImpl):
169169
def bind_processor(self, dialect):
170170
psycopg_Range = cast(PGDialect_psycopg, dialect)._psycopg_Range
171171

@@ -221,18 +221,18 @@ def to_range(value):
221221

222222
def result_processor(self, dialect, coltype):
223223
def to_range(value):
224-
if value is not None:
225-
value = [
224+
if value is None:
225+
return None
226+
else:
227+
return ranges.MultiRange(
226228
ranges.Range(
227229
elem._lower,
228230
elem._upper,
229231
bounds=elem._bounds if elem._bounds else "[)",
230232
empty=not elem._bounds,
231233
)
232234
for elem in value
233-
]
234-
235-
return value
235+
)
236236

237237
return to_range
238238

@@ -289,7 +289,7 @@ class PGDialect_psycopg(_PGDialect_common_psycopg):
289289
sqltypes.Integer: _PGInteger,
290290
sqltypes.SmallInteger: _PGSmallInteger,
291291
sqltypes.BigInteger: _PGBigInteger,
292-
ranges.AbstractRange: _PsycopgRange,
292+
ranges.AbstractSingleRange: _PsycopgRange,
293293
ranges.AbstractMultiRange: _PsycopgMultiRange,
294294
},
295295
)

‎lib/sqlalchemy/dialects/postgresql/psycopg2.py‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -513,7 +513,7 @@ def result_processor(self, dialect, coltype):
513513
return None
514514

515515

516-
class _Psycopg2Range(ranges.AbstractRangeImpl):
516+
class _Psycopg2Range(ranges.AbstractSingleRangeImpl):
517517
_psycopg2_range_cls = "none"
518518

519519
def bind_processor(self, dialect):

0 commit comments

Comments
 (0)