Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.8/site-packages/sqlalchemy/ext/hybrid.py: 45%

1# ext/hybrid.py

3# <see AUTHORS file>

5# This module is part of SQLAlchemy and is released under

6# the MIT License: https://www.opensource.org/licenses/mit-license.php

8r"""Define attributes on ORM-mapped classes that have "hybrid" behavior.

10"hybrid" means the attribute has distinct behaviors defined at the

11class level and at the instance level.

13The :mod:`~sqlalchemy.ext.hybrid` extension provides a special form of

14method decorator, is around 50 lines of code and has almost no

15dependencies on the rest of SQLAlchemy. It can, in theory, work with

16any descriptor-based expression system.

18Consider a mapping ``Interval``, representing integer ``start`` and ``end``

19values. We can define higher level functions on mapped classes that produce SQL

20expressions at the class level, and Python expression evaluation at the

21instance level. Below, each function decorated with :class:`.hybrid_method` or

22:class:`.hybrid_property` may receive ``self`` as an instance of the class, or

23as the class itself::

25 from sqlalchemy import Column, Integer

26 from sqlalchemy.ext.declarative import declarative_base

27 from sqlalchemy.orm import Session, aliased

28 from sqlalchemy.ext.hybrid import hybrid_property, hybrid_method

30 Base = declarative_base()

32 class Interval(Base):

33 __tablename__ = 'interval'

35 id = Column(Integer, primary_key=True)

36 start = Column(Integer, nullable=False)

37 end = Column(Integer, nullable=False)

39 def __init__(self, start, end):

40 self.start = start

41 self.end = end

43 @hybrid_property

44 def length(self):

45 return self.end - self.start

47 @hybrid_method

48 def contains(self, point):

49 return (self.start <= point) & (point <= self.end)

51 @hybrid_method

52 def intersects(self, other):

53 return self.contains(other.start) | self.contains(other.end)

55Above, the ``length`` property returns the difference between the

56``end`` and ``start`` attributes. With an instance of ``Interval``,

57this subtraction occurs in Python, using normal Python descriptor

58mechanics::

60 >>> i1 = Interval(5, 10)

61 >>> i1.length

62 5

64When dealing with the ``Interval`` class itself, the :class:`.hybrid_property`

65descriptor evaluates the function body given the ``Interval`` class as

66the argument, which when evaluated with SQLAlchemy expression mechanics

67(here using the :attr:`.QueryableAttribute.expression` accessor)

68returns a new SQL expression::

70 >>> print(Interval.length.expression)

71 interval."end" - interval.start

73 >>> print(Session().query(Interval).filter(Interval.length > 10))

74 SELECT interval.id AS interval_id, interval.start AS interval_start,

75 interval."end" AS interval_end

76 FROM interval

77 WHERE interval."end" - interval.start > :param_1

79ORM methods such as :meth:`_query.Query.filter_by`

80generally use ``getattr()`` to

81locate attributes, so can also be used with hybrid attributes::

83 >>> print(Session().query(Interval).filter_by(length=5))

84 SELECT interval.id AS interval_id, interval.start AS interval_start,

85 interval."end" AS interval_end

86 FROM interval

87 WHERE interval."end" - interval.start = :param_1

89The ``Interval`` class example also illustrates two methods,

90``contains()`` and ``intersects()``, decorated with

91:class:`.hybrid_method`. This decorator applies the same idea to

92methods that :class:`.hybrid_property` applies to attributes. The

93methods return boolean values, and take advantage of the Python ``|``

94and ``&`` bitwise operators to produce equivalent instance-level and

95SQL expression-level boolean behavior::

97 >>> i1.contains(6)

98 True

99 >>> i1.contains(15)

100 False

101 >>> i1.intersects(Interval(7, 18))

102 True

103 >>> i1.intersects(Interval(25, 29))

104 False

105

106 >>> print(Session().query(Interval).filter(Interval.contains(15)))

107 SELECT interval.id AS interval_id, interval.start AS interval_start,

108 interval."end" AS interval_end

109 FROM interval

110 WHERE interval.start <= :start_1 AND interval."end" > :end_1

111

112 >>> ia = aliased(Interval)

113 >>> print(Session().query(Interval, ia).filter(Interval.intersects(ia)))

114 SELECT interval.id AS interval_id, interval.start AS interval_start,

115 interval."end" AS interval_end, interval_1.id AS interval_1_id,

116 interval_1.start AS interval_1_start, interval_1."end" AS interval_1_end

117 FROM interval, interval AS interval_1

118 WHERE interval.start <= interval_1.start

119 AND interval."end" > interval_1.start

120 OR interval.start <= interval_1."end"

121 AND interval."end" > interval_1."end"

122

123.. _hybrid_distinct_expression:

124

125Defining Expression Behavior Distinct from Attribute Behavior

126--------------------------------------------------------------

127

128Our usage of the ``&`` and ``|`` bitwise operators above was

129fortunate, considering our functions operated on two boolean values to

130return a new one. In many cases, the construction of an in-Python

131function and a SQLAlchemy SQL expression have enough differences that

132two separate Python expressions should be defined. The

133:mod:`~sqlalchemy.ext.hybrid` decorators define the

134:meth:`.hybrid_property.expression` modifier for this purpose. As an

135example we'll define the radius of the interval, which requires the

136usage of the absolute value function::

137

138 from sqlalchemy import func

139

140 class Interval(object):

141 # ...

142

143 @hybrid_property

144 def radius(self):

145 return abs(self.length) / 2

146

147 @radius.expression

148 def radius(cls):

149 return func.abs(cls.length) / 2

150

151Above the Python function ``abs()`` is used for instance-level

152operations, the SQL function ``ABS()`` is used via the :data:`.func`

153object for class-level expressions::

154

155 >>> i1.radius

156 2

157

158 >>> print(Session().query(Interval).filter(Interval.radius > 5))

159 SELECT interval.id AS interval_id, interval.start AS interval_start,

160 interval."end" AS interval_end

161 FROM interval

162 WHERE abs(interval."end" - interval.start) / :abs_1 > :param_1

163

164.. note:: When defining an expression for a hybrid property or method, the

165 expression method **must** retain the name of the original hybrid, else

166 the new hybrid with the additional state will be attached to the class

167 with the non-matching name. To use the example above::

168

169 class Interval(object):

170 # ...

171

172 @hybrid_property

173 def radius(self):

174 return abs(self.length) / 2

175

176 # WRONG - the non-matching name will cause this function to be

177 # ignored

178 @radius.expression

179 def radius_expression(cls):

180 return func.abs(cls.length) / 2

181

182 This is also true for other mutator methods, such as

183 :meth:`.hybrid_property.update_expression`. This is the same behavior

184 as that of the ``@property`` construct that is part of standard Python.

185

186Defining Setters

187----------------

188

189Hybrid properties can also define setter methods. If we wanted

190``length`` above, when set, to modify the endpoint value::

191

192 class Interval(object):

193 # ...

194

195 @hybrid_property

196 def length(self):

197 return self.end - self.start

198

199 @length.setter

200 def length(self, value):

201 self.end = self.start + value

202

203The ``length(self, value)`` method is now called upon set::

204

205 >>> i1 = Interval(5, 10)

206 >>> i1.length

207 5

208 >>> i1.length = 12

209 >>> i1.end

210 17

211

212.. _hybrid_bulk_update:

213

214Allowing Bulk ORM Update

215------------------------

216

217A hybrid can define a custom "UPDATE" handler for when using the

218:meth:`_query.Query.update` method, allowing the hybrid to be used in the

219SET clause of the update.

220

221Normally, when using a hybrid with :meth:`_query.Query.update`, the SQL

222expression is used as the column that's the target of the SET. If our

223``Interval`` class had a hybrid ``start_point`` that linked to

224``Interval.start``, this could be substituted directly::

225

226 session.query(Interval).update({Interval.start_point: 10})

227

228However, when using a composite hybrid like ``Interval.length``, this

229hybrid represents more than one column. We can set up a handler that will

230accommodate a value passed to :meth:`_query.Query.update` which can affect

231this, using the :meth:`.hybrid_property.update_expression` decorator.

232A handler that works similarly to our setter would be::

233

234 class Interval(object):

235 # ...

236

237 @hybrid_property

238 def length(self):

239 return self.end - self.start

240

241 @length.setter

242 def length(self, value):

243 self.end = self.start + value

244

245 @length.update_expression

246 def length(cls, value):

247 return [

248 (cls.end, cls.start + value)

249 ]

250

251Above, if we use ``Interval.length`` in an UPDATE expression as::

252

253 session.query(Interval).update(

254 {Interval.length: 25}, synchronize_session='fetch')

255

256We'll get an UPDATE statement along the lines of::

257

258 UPDATE interval SET end=start + :value

259

260In some cases, the default "evaluate" strategy can't perform the SET

261expression in Python; while the addition operator we're using above

262is supported, for more complex SET expressions it will usually be necessary

263to use either the "fetch" or False synchronization strategy as illustrated

264above.

265

266.. note:: For ORM bulk updates to work with hybrids, the function name

267 of the hybrid must match that of how it is accessed. Something

268 like this wouldn't work::

269

270 class Interval(object):

271 # ...

272

273 def _get(self):

274 return self.end - self.start

275

276 def _set(self, value):

277 self.end = self.start + value

278

279 def _update_expr(cls, value):

280 return [

281 (cls.end, cls.start + value)

282 ]

283

284 length = hybrid_property(

285 fget=_get, fset=_set, update_expr=_update_expr

286 )

287

288 The Python descriptor protocol does not provide any reliable way for

289 a descriptor to know what attribute name it was accessed as, and

290 the UPDATE scheme currently relies upon being able to access the

291 attribute from an instance by name in order to perform the instance

292 synchronization step.

293

294.. versionadded:: 1.2 added support for bulk updates to hybrid properties.

295

296Working with Relationships

297--------------------------

298

299There's no essential difference when creating hybrids that work with

300related objects as opposed to column-based data. The need for distinct

301expressions tends to be greater. The two variants we'll illustrate

302are the "join-dependent" hybrid, and the "correlated subquery" hybrid.

303

304Join-Dependent Relationship Hybrid

305^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

306

307Consider the following declarative

308mapping which relates a ``User`` to a ``SavingsAccount``::

309

310 from sqlalchemy import Column, Integer, ForeignKey, Numeric, String

311 from sqlalchemy.orm import relationship

312 from sqlalchemy.ext.declarative import declarative_base

313 from sqlalchemy.ext.hybrid import hybrid_property

314

315 Base = declarative_base()

316

317 class SavingsAccount(Base):

318 __tablename__ = 'account'

319 id = Column(Integer, primary_key=True)

320 user_id = Column(Integer, ForeignKey('user.id'), nullable=False)

321 balance = Column(Numeric(15, 5))

322

323 class User(Base):

324 __tablename__ = 'user'

325 id = Column(Integer, primary_key=True)

326 name = Column(String(100), nullable=False)

327

328 accounts = relationship("SavingsAccount", backref="owner")

329

330 @hybrid_property

331 def balance(self):

332 if self.accounts:

333 return self.accounts[0].balance

334 else:

335 return None

336

337 @balance.setter

338 def balance(self, value):

339 if not self.accounts:

340 account = Account(owner=self)

341 else:

342 account = self.accounts[0]

343 account.balance = value

344

345 @balance.expression

346 def balance(cls):

347 return SavingsAccount.balance

348

349The above hybrid property ``balance`` works with the first

350``SavingsAccount`` entry in the list of accounts for this user. The

351in-Python getter/setter methods can treat ``accounts`` as a Python

352list available on ``self``.

353

354However, at the expression level, it's expected that the ``User`` class will

355be used in an appropriate context such that an appropriate join to

356``SavingsAccount`` will be present::

357

358 >>> print(Session().query(User, User.balance).

359 ... join(User.accounts).filter(User.balance > 5000))

360 SELECT "user".id AS user_id, "user".name AS user_name,

361 account.balance AS account_balance

362 FROM "user" JOIN account ON "user".id = account.user_id

363 WHERE account.balance > :balance_1

364

365Note however, that while the instance level accessors need to worry

366about whether ``self.accounts`` is even present, this issue expresses

367itself differently at the SQL expression level, where we basically

368would use an outer join::

369

370 >>> from sqlalchemy import or_

371 >>> print (Session().query(User, User.balance).outerjoin(User.accounts).

372 ... filter(or_(User.balance < 5000, User.balance == None)))

373 SELECT "user".id AS user_id, "user".name AS user_name,

374 account.balance AS account_balance

375 FROM "user" LEFT OUTER JOIN account ON "user".id = account.user_id

376 WHERE account.balance < :balance_1 OR account.balance IS NULL

377

378Correlated Subquery Relationship Hybrid

379^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

380

381We can, of course, forego being dependent on the enclosing query's usage

382of joins in favor of the correlated subquery, which can portably be packed

383into a single column expression. A correlated subquery is more portable, but

384often performs more poorly at the SQL level. Using the same technique

385illustrated at :ref:`mapper_column_property_sql_expressions`,

386we can adjust our ``SavingsAccount`` example to aggregate the balances for

387*all* accounts, and use a correlated subquery for the column expression::

388

389 from sqlalchemy import Column, Integer, ForeignKey, Numeric, String

390 from sqlalchemy.orm import relationship

391 from sqlalchemy.ext.declarative import declarative_base

392 from sqlalchemy.ext.hybrid import hybrid_property

393 from sqlalchemy import select, func

394

395 Base = declarative_base()

396

397 class SavingsAccount(Base):

398 __tablename__ = 'account'

399 id = Column(Integer, primary_key=True)

400 user_id = Column(Integer, ForeignKey('user.id'), nullable=False)

401 balance = Column(Numeric(15, 5))

402

403 class User(Base):

404 __tablename__ = 'user'

405 id = Column(Integer, primary_key=True)

406 name = Column(String(100), nullable=False)

407

408 accounts = relationship("SavingsAccount", backref="owner")

409

410 @hybrid_property

411 def balance(self):

412 return sum(acc.balance for acc in self.accounts)

413

414 @balance.expression

415 def balance(cls):

416 return select(func.sum(SavingsAccount.balance)).\

417 where(SavingsAccount.user_id==cls.id).\

418 label('total_balance')

419

420The above recipe will give us the ``balance`` column which renders

421a correlated SELECT::

422

423 >>> print(s.query(User).filter(User.balance > 400))

424 SELECT "user".id AS user_id, "user".name AS user_name

425 FROM "user"

426 WHERE (SELECT sum(account.balance) AS sum_1

427 FROM account

428 WHERE account.user_id = "user".id) > :param_1

429

430.. _hybrid_custom_comparators:

431

432Building Custom Comparators

433---------------------------

434

435The hybrid property also includes a helper that allows construction of

436custom comparators. A comparator object allows one to customize the

437behavior of each SQLAlchemy expression operator individually. They

438are useful when creating custom types that have some highly

439idiosyncratic behavior on the SQL side.

440

441.. note:: The :meth:`.hybrid_property.comparator` decorator introduced

442 in this section **replaces** the use of the

443 :meth:`.hybrid_property.expression` decorator.

444 They cannot be used together.

445

446The example class below allows case-insensitive comparisons on the attribute

447named ``word_insensitive``::

448

449 from sqlalchemy.ext.hybrid import Comparator, hybrid_property

450 from sqlalchemy import func, Column, Integer, String

451 from sqlalchemy.orm import Session

452 from sqlalchemy.ext.declarative import declarative_base

453

454 Base = declarative_base()

455

456 class CaseInsensitiveComparator(Comparator):

457 def __eq__(self, other):

458 return func.lower(self.__clause_element__()) == func.lower(other)

459

460 class SearchWord(Base):

461 __tablename__ = 'searchword'

462 id = Column(Integer, primary_key=True)

463 word = Column(String(255), nullable=False)

464

465 @hybrid_property

466 def word_insensitive(self):

467 return self.word.lower()

468

469 @word_insensitive.comparator

470 def word_insensitive(cls):

471 return CaseInsensitiveComparator(cls.word)

472

473Above, SQL expressions against ``word_insensitive`` will apply the ``LOWER()``

474SQL function to both sides::

475

476 >>> print(Session().query(SearchWord).filter_by(word_insensitive="Trucks"))

477 SELECT searchword.id AS searchword_id, searchword.word AS searchword_word

478 FROM searchword

479 WHERE lower(searchword.word) = lower(:lower_1)

480

481The ``CaseInsensitiveComparator`` above implements part of the

482:class:`.ColumnOperators` interface. A "coercion" operation like

483lowercasing can be applied to all comparison operations (i.e. ``eq``,

484``lt``, ``gt``, etc.) using :meth:`.Operators.operate`::

485

486 class CaseInsensitiveComparator(Comparator):

487 def operate(self, op, other, **kwargs):

488 return op(

489 func.lower(self.__clause_element__()),

490 func.lower(other),

491 **kwargs,

492 )

493

494.. _hybrid_reuse_subclass:

495

496Reusing Hybrid Properties across Subclasses

497-------------------------------------------

498

499A hybrid can be referred to from a superclass, to allow modifying

500methods like :meth:`.hybrid_property.getter`, :meth:`.hybrid_property.setter`

501to be used to redefine those methods on a subclass. This is similar to

502how the standard Python ``@property`` object works::

503

504 class FirstNameOnly(Base):

505 # ...

506

507 first_name = Column(String)

508

509 @hybrid_property

510 def name(self):

511 return self.first_name

512

513 @name.setter

514 def name(self, value):

515 self.first_name = value

516

517 class FirstNameLastName(FirstNameOnly):

518 # ...

519

520 last_name = Column(String)

521

522 @FirstNameOnly.name.getter

523 def name(self):

524 return self.first_name + ' ' + self.last_name

525

526 @name.setter

527 def name(self, value):

528 self.first_name, self.last_name = value.split(' ', 1)

529

530Above, the ``FirstNameLastName`` class refers to the hybrid from

531``FirstNameOnly.name`` to repurpose its getter and setter for the subclass.

532

533When overriding :meth:`.hybrid_property.expression` and

534:meth:`.hybrid_property.comparator` alone as the first reference to the

535superclass, these names conflict with the same-named accessors on the class-

536level :class:`.QueryableAttribute` object returned at the class level. To

537override these methods when referring directly to the parent class descriptor,

538add the special qualifier :attr:`.hybrid_property.overrides`, which will de-

539reference the instrumented attribute back to the hybrid object::

540

541 class FirstNameLastName(FirstNameOnly):

542 # ...

543

544 last_name = Column(String)

545

546 @FirstNameOnly.name.overrides.expression

547 def name(cls):

548 return func.concat(cls.first_name, ' ', cls.last_name)

549

550.. versionadded:: 1.2 Added :meth:`.hybrid_property.getter` as well as the

551 ability to redefine accessors per-subclass.

552

553

554Hybrid Value Objects

555--------------------

556

557Note in our previous example, if we were to compare the ``word_insensitive``

558attribute of a ``SearchWord`` instance to a plain Python string, the plain

559Python string would not be coerced to lower case - the

560``CaseInsensitiveComparator`` we built, being returned by

561``@word_insensitive.comparator``, only applies to the SQL side.

562

563A more comprehensive form of the custom comparator is to construct a *Hybrid

564Value Object*. This technique applies the target value or expression to a value

565object which is then returned by the accessor in all cases. The value object

566allows control of all operations upon the value as well as how compared values

567are treated, both on the SQL expression side as well as the Python value side.

568Replacing the previous ``CaseInsensitiveComparator`` class with a new

569``CaseInsensitiveWord`` class::

570

571 class CaseInsensitiveWord(Comparator):

572 "Hybrid value representing a lower case representation of a word."

573

574 def __init__(self, word):

575 if isinstance(word, basestring):

576 self.word = word.lower()

577 elif isinstance(word, CaseInsensitiveWord):

578 self.word = word.word

579 else:

580 self.word = func.lower(word)

581

582 def operate(self, op, other, **kwargs):

583 if not isinstance(other, CaseInsensitiveWord):

584 other = CaseInsensitiveWord(other)

585 return op(self.word, other.word, **kwargs)

586

587 def __clause_element__(self):

588 return self.word

589

590 def __str__(self):

591 return self.word

592

593 key = 'word'

594 "Label to apply to Query tuple results"

595

596Above, the ``CaseInsensitiveWord`` object represents ``self.word``, which may

597be a SQL function, or may be a Python native. By overriding ``operate()`` and

598``__clause_element__()`` to work in terms of ``self.word``, all comparison

599operations will work against the "converted" form of ``word``, whether it be

600SQL side or Python side. Our ``SearchWord`` class can now deliver the

601``CaseInsensitiveWord`` object unconditionally from a single hybrid call::

602

603 class SearchWord(Base):

604 __tablename__ = 'searchword'

605 id = Column(Integer, primary_key=True)

606 word = Column(String(255), nullable=False)

607

608 @hybrid_property

609 def word_insensitive(self):

610 return CaseInsensitiveWord(self.word)

611

612The ``word_insensitive`` attribute now has case-insensitive comparison behavior

613universally, including SQL expression vs. Python expression (note the Python

614value is converted to lower case on the Python side here)::

615

616 >>> print(Session().query(SearchWord).filter_by(word_insensitive="Trucks"))

617 SELECT searchword.id AS searchword_id, searchword.word AS searchword_word

618 FROM searchword

619 WHERE lower(searchword.word) = :lower_1

620

621SQL expression versus SQL expression::

622

623 >>> sw1 = aliased(SearchWord)

624 >>> sw2 = aliased(SearchWord)

625 >>> print(Session().query(

626 ... sw1.word_insensitive,

627 ... sw2.word_insensitive).\

628 ... filter(

629 ... sw1.word_insensitive > sw2.word_insensitive

630 ... ))

631 SELECT lower(searchword_1.word) AS lower_1,

632 lower(searchword_2.word) AS lower_2

633 FROM searchword AS searchword_1, searchword AS searchword_2

634 WHERE lower(searchword_1.word) > lower(searchword_2.word)

635

636Python only expression::

637

638 >>> ws1 = SearchWord(word="SomeWord")

639 >>> ws1.word_insensitive == "sOmEwOrD"

640 True

641 >>> ws1.word_insensitive == "XOmEwOrX"

642 False

643 >>> print(ws1.word_insensitive)

644 someword

645

646The Hybrid Value pattern is very useful for any kind of value that may have

647multiple representations, such as timestamps, time deltas, units of

648measurement, currencies and encrypted passwords.

649

650.. seealso::

651

652 `Hybrids and Value Agnostic Types

653 <https://techspot.zzzeek.org/2011/10/21/hybrids-and-value-agnostic-types/>`_

654 - on the techspot.zzzeek.org blog

655

656 `Value Agnostic Types, Part II

657 <https://techspot.zzzeek.org/2011/10/29/value-agnostic-types-part-ii/>`_ -

658 on the techspot.zzzeek.org blog

659

660.. _hybrid_transformers:

661

662Building Transformers

663----------------------

664

665A *transformer* is an object which can receive a :class:`_query.Query`

666object and

667return a new one. The :class:`_query.Query` object includes a method

668:meth:`.with_transformation` that returns a new :class:`_query.Query`

669transformed by

670the given function.

671

672We can combine this with the :class:`.Comparator` class to produce one type

673of recipe which can both set up the FROM clause of a query as well as assign

674filtering criterion.

675

676Consider a mapped class ``Node``, which assembles using adjacency list into a

677hierarchical tree pattern::

678

679 from sqlalchemy import Column, Integer, ForeignKey

680 from sqlalchemy.orm import relationship

681 from sqlalchemy.ext.declarative import declarative_base

682 Base = declarative_base()

683

684 class Node(Base):

685 __tablename__ = 'node'

686 id = Column(Integer, primary_key=True)

687 parent_id = Column(Integer, ForeignKey('node.id'))

688 parent = relationship("Node", remote_side=id)

689

690Suppose we wanted to add an accessor ``grandparent``. This would return the

691``parent`` of ``Node.parent``. When we have an instance of ``Node``, this is

692simple::

693

694 from sqlalchemy.ext.hybrid import hybrid_property

695

696 class Node(Base):

697 # ...

698

699 @hybrid_property

700 def grandparent(self):

701 return self.parent.parent

702

703For the expression, things are not so clear. We'd need to construct a

704:class:`_query.Query` where we :meth:`_query.Query.join` twice along

705``Node.parent`` to get to the ``grandparent``. We can instead return a

706transforming callable that we'll combine with the :class:`.Comparator` class to

707receive any :class:`_query.Query` object, and return a new one that's joined to

708the ``Node.parent`` attribute and filtered based on the given criterion::

709

710 from sqlalchemy.ext.hybrid import Comparator

711

712 class GrandparentTransformer(Comparator):

713 def operate(self, op, other, **kwargs):

714 def transform(q):

715 cls = self.__clause_element__()

716 parent_alias = aliased(cls)

717 return q.join(parent_alias, cls.parent).filter(

718 op(parent_alias.parent, other, **kwargs)

719 )

720

721 return transform

722

723 Base = declarative_base()

724

725 class Node(Base):

726 __tablename__ = 'node'

727 id = Column(Integer, primary_key=True)

728 parent_id = Column(Integer, ForeignKey('node.id'))

729 parent = relationship("Node", remote_side=id)

730

731 @hybrid_property

732 def grandparent(self):

733 return self.parent.parent

734

735 @grandparent.comparator

736 def grandparent(cls):

737 return GrandparentTransformer(cls)

738

739The ``GrandparentTransformer`` overrides the core :meth:`.Operators.operate`

740method at the base of the :class:`.Comparator` hierarchy to return a query-

741transforming callable, which then runs the given comparison operation in a

742particular context. Such as, in the example above, the ``operate`` method is

743called, given the :attr:`.Operators.eq` callable as well as the right side of

744the comparison ``Node(id=5)``. A function ``transform`` is then returned which

745will transform a :class:`_query.Query` first to join to ``Node.parent``,

746then to

747compare ``parent_alias`` using :attr:`.Operators.eq` against the left and right

748sides, passing into :meth:`_query.Query.filter`:

749

750.. sourcecode:: pycon+sql

751

752 >>> from sqlalchemy.orm import Session

753 >>> session = Session()

754 {sql}>>> session.query(Node).\

755 ... with_transformation(Node.grandparent==Node(id=5)).\

756 ... all()

757 SELECT node.id AS node_id, node.parent_id AS node_parent_id

758 FROM node JOIN node AS node_1 ON node_1.id = node.parent_id

759 WHERE :param_1 = node_1.parent_id

760 {stop}

761

762We can modify the pattern to be more verbose but flexible by separating the

763"join" step from the "filter" step. The tricky part here is ensuring that

764successive instances of ``GrandparentTransformer`` use the same

765:class:`.AliasedClass` object against ``Node``. Below we use a simple

766memoizing approach that associates a ``GrandparentTransformer`` with each

767class::

768

769 class Node(Base):

770

771 # ...

772

773 @grandparent.comparator

774 def grandparent(cls):

775 # memoize a GrandparentTransformer

776 # per class

777 if '_gp' not in cls.__dict__:

778 cls._gp = GrandparentTransformer(cls)

779 return cls._gp

780

781 class GrandparentTransformer(Comparator):

782

783 def __init__(self, cls):

784 self.parent_alias = aliased(cls)

785

786 @property

787 def join(self):

788 def go(q):

789 return q.join(self.parent_alias, Node.parent)

790 return go

791

792 def operate(self, op, other, **kwargs):

793 return op(self.parent_alias.parent, other, **kwargs)

794

795.. sourcecode:: pycon+sql

796

797 {sql}>>> session.query(Node).\

798 ... with_transformation(Node.grandparent.join).\

799 ... filter(Node.grandparent==Node(id=5))

800 SELECT node.id AS node_id, node.parent_id AS node_parent_id

801 FROM node JOIN node AS node_1 ON node_1.id = node.parent_id

802 WHERE :param_1 = node_1.parent_id

803 {stop}

804

805The "transformer" pattern is an experimental pattern that starts to make usage

806of some functional programming paradigms. While it's only recommended for

807advanced and/or patient developers, there's probably a whole lot of amazing

808things it can be used for.

809

810""" # noqa

811from .. import util

812from ..orm import attributes

813from ..orm import interfaces

814

815HYBRID_METHOD = util.symbol("HYBRID_METHOD")

816"""Symbol indicating an :class:`InspectionAttr` that's

817 of type :class:`.hybrid_method`.

818

819 Is assigned to the :attr:`.InspectionAttr.extension_type`

820 attribute.

821

822 .. seealso::

823

824 :attr:`_orm.Mapper.all_orm_attributes`

825

826"""

827

828HYBRID_PROPERTY = util.symbol("HYBRID_PROPERTY")

829"""Symbol indicating an :class:`InspectionAttr` that's

830 of type :class:`.hybrid_method`.

831

832 Is assigned to the :attr:`.InspectionAttr.extension_type`

833 attribute.

834

835 .. seealso::

836

837 :attr:`_orm.Mapper.all_orm_attributes`

838

839"""

840

841

842class hybrid_method(interfaces.InspectionAttrInfo):

843 """A decorator which allows definition of a Python object method with both

844 instance-level and class-level behavior.

845

846 """

847

848 is_attribute = True

849 extension_type = HYBRID_METHOD

850

851 def __init__(self, func, expr=None):

852 """Create a new :class:`.hybrid_method`.

853

854 Usage is typically via decorator::

855

856 from sqlalchemy.ext.hybrid import hybrid_method

857

858 class SomeClass(object):

859 @hybrid_method

860 def value(self, x, y):

861 return self._value + x + y

862

863 @value.expression

864 def value(self, x, y):

865 return func.some_function(self._value, x, y)

866

867 """

868 self.func = func

869 self.expression(expr or func)

870

871 def __get__(self, instance, owner):

872 if instance is None:

873 return self.expr.__get__(owner, owner.__class__)

874 else:

875 return self.func.__get__(instance, owner)

876

877 def expression(self, expr):

878 """Provide a modifying decorator that defines a

879 SQL-expression producing method."""

880

881 self.expr = expr

882 if not self.expr.__doc__:

883 self.expr.__doc__ = self.func.__doc__

884 return self

885

886

887class hybrid_property(interfaces.InspectionAttrInfo):

888 """A decorator which allows definition of a Python descriptor with both

889 instance-level and class-level behavior.

890

891 """

892

893 is_attribute = True

894 extension_type = HYBRID_PROPERTY

895

896 def __init__(

897 self,

898 fget,

899 fset=None,

900 fdel=None,

901 expr=None,

902 custom_comparator=None,

903 update_expr=None,

904 ):

905 """Create a new :class:`.hybrid_property`.

906

907 Usage is typically via decorator::

908

909 from sqlalchemy.ext.hybrid import hybrid_property

910

911 class SomeClass(object):

912 @hybrid_property

913 def value(self):

914 return self._value

915

916 @value.setter

917 def value(self, value):

918 self._value = value

919

920 """

921 self.fget = fget

922 self.fset = fset

923 self.fdel = fdel

924 self.expr = expr

925 self.custom_comparator = custom_comparator

926 self.update_expr = update_expr

927 util.update_wrapper(self, fget)

928

929 def __get__(self, instance, owner):

930 if instance is None:

931 return self._expr_comparator(owner)

932 else:

933 return self.fget(instance)

934

935 def __set__(self, instance, value):

936 if self.fset is None:

937 raise AttributeError("can't set attribute")

938 self.fset(instance, value)

939

940 def __delete__(self, instance):

941 if self.fdel is None:

942 raise AttributeError("can't delete attribute")

943 self.fdel(instance)

944

945 def _copy(self, **kw):

946 defaults = {

947 key: value

948 for key, value in self.__dict__.items()

949 if not key.startswith("_")

950 }

951 defaults.update(**kw)

952 return type(self)(**defaults)

953

954 @property

955 def overrides(self):

956 """Prefix for a method that is overriding an existing attribute.

957

958 The :attr:`.hybrid_property.overrides` accessor just returns

959 this hybrid object, which when called at the class level from

960 a parent class, will de-reference the "instrumented attribute"

961 normally returned at this level, and allow modifying decorators

962 like :meth:`.hybrid_property.expression` and

963 :meth:`.hybrid_property.comparator`

964 to be used without conflicting with the same-named attributes

965 normally present on the :class:`.QueryableAttribute`::

966

967 class SuperClass(object):

968 # ...

969

970 @hybrid_property

971 def foobar(self):

972 return self._foobar

973

974 class SubClass(SuperClass):

975 # ...

976

977 @SuperClass.foobar.overrides.expression

978 def foobar(cls):

979 return func.subfoobar(self._foobar)

980

981 .. versionadded:: 1.2

982

983 .. seealso::

984

985 :ref:`hybrid_reuse_subclass`

986

987 """

988 return self

989

990 def getter(self, fget):

991 """Provide a modifying decorator that defines a getter method.

992

993 .. versionadded:: 1.2

994

995 """

996

997 return self._copy(fget=fget)

998

999 def setter(self, fset):

1000 """Provide a modifying decorator that defines a setter method."""

1001

1002 return self._copy(fset=fset)

1003

1004 def deleter(self, fdel):

1005 """Provide a modifying decorator that defines a deletion method."""

1006

1007 return self._copy(fdel=fdel)

1008

1009 def expression(self, expr):

1010 """Provide a modifying decorator that defines a SQL-expression

1011 producing method.

1012

1013 When a hybrid is invoked at the class level, the SQL expression given

1014 here is wrapped inside of a specialized :class:`.QueryableAttribute`,

1015 which is the same kind of object used by the ORM to represent other

1016 mapped attributes. The reason for this is so that other class-level

1017 attributes such as docstrings and a reference to the hybrid itself may

1018 be maintained within the structure that's returned, without any

1019 modifications to the original SQL expression passed in.

1020

1021 .. note::

1022

1023 When referring to a hybrid property from an owning class (e.g.

1024 ``SomeClass.some_hybrid``), an instance of

1025 :class:`.QueryableAttribute` is returned, representing the

1026 expression or comparator object as well as this hybrid object.

1027 However, that object itself has accessors called ``expression`` and

1028 ``comparator``; so when attempting to override these decorators on a

1029 subclass, it may be necessary to qualify it using the

1030 :attr:`.hybrid_property.overrides` modifier first. See that

1031 modifier for details.

1032

1033 .. seealso::

1034

1035 :ref:`hybrid_distinct_expression`

1036

1037 """

1038

1039 return self._copy(expr=expr)

1040

1041 def comparator(self, comparator):

1042 """Provide a modifying decorator that defines a custom

1043 comparator producing method.

1044

1045 The return value of the decorated method should be an instance of

1046 :class:`~.hybrid.Comparator`.

1047

1048 .. note:: The :meth:`.hybrid_property.comparator` decorator

1049 **replaces** the use of the :meth:`.hybrid_property.expression`

1050 decorator. They cannot be used together.

1051

1052 When a hybrid is invoked at the class level, the

1053 :class:`~.hybrid.Comparator` object given here is wrapped inside of a

1054 specialized :class:`.QueryableAttribute`, which is the same kind of

1055 object used by the ORM to represent other mapped attributes. The

1056 reason for this is so that other class-level attributes such as

1057 docstrings and a reference to the hybrid itself may be maintained

1058 within the structure that's returned, without any modifications to the

1059 original comparator object passed in.

1060

1061 .. note::

1062

1063 When referring to a hybrid property from an owning class (e.g.

1064 ``SomeClass.some_hybrid``), an instance of

1065 :class:`.QueryableAttribute` is returned, representing the

1066 expression or comparator object as this hybrid object. However,

1067 that object itself has accessors called ``expression`` and

1068 ``comparator``; so when attempting to override these decorators on a

1069 subclass, it may be necessary to qualify it using the

1070 :attr:`.hybrid_property.overrides` modifier first. See that

1071 modifier for details.

1072

1073 """

1074 return self._copy(custom_comparator=comparator)

1075

1076 def update_expression(self, meth):

1077 """Provide a modifying decorator that defines an UPDATE tuple

1078 producing method.

1079

1080 The method accepts a single value, which is the value to be

1081 rendered into the SET clause of an UPDATE statement. The method

1082 should then process this value into individual column expressions

1083 that fit into the ultimate SET clause, and return them as a

1084 sequence of 2-tuples. Each tuple

1085 contains a column expression as the key and a value to be rendered.

1086

1087 E.g.::

1088

1089 class Person(Base):

1090 # ...

1091

1092 first_name = Column(String)

1093 last_name = Column(String)

1094

1095 @hybrid_property

1096 def fullname(self):

1097 return first_name + " " + last_name

1098

1099 @fullname.update_expression

1100 def fullname(cls, value):

1101 fname, lname = value.split(" ", 1)

1102 return [

1103 (cls.first_name, fname),

1104 (cls.last_name, lname)

1105 ]

1106

1107 .. versionadded:: 1.2

1108

1109 """

1110 return self._copy(update_expr=meth)

1111

1112 @util.memoized_property

1113 def _expr_comparator(self):

1114 if self.custom_comparator is not None:

1115 return self._get_comparator(self.custom_comparator)

1116 elif self.expr is not None:

1117 return self._get_expr(self.expr)

1118 else:

1119 return self._get_expr(self.fget)

1120

1121 def _get_expr(self, expr):

1122 def _expr(cls):

1123 return ExprComparator(cls, expr(cls), self)

1124

1125 util.update_wrapper(_expr, expr)

1126

1127 return self._get_comparator(_expr)

1128

1129 def _get_comparator(self, comparator):

1130

1131 proxy_attr = attributes.create_proxied_attribute(self)

1132

1133 def expr_comparator(owner):

1134 # because this is the descriptor protocol, we don't really know

1135 # what our attribute name is. so search for it through the

1136 # MRO.

1137 for lookup in owner.__mro__:

1138 if self.__name__ in lookup.__dict__:

1139 if lookup.__dict__[self.__name__] is self:

1140 name = self.__name__

1141 break

1142 else:

1143 name = attributes.NO_KEY

1144

1145 return proxy_attr(

1146 owner,

1147 name,

1148 self,

1149 comparator(owner),

1150 doc=comparator.__doc__ or self.__doc__,

1151 )

1152

1153 return expr_comparator

1154

1155

1156class Comparator(interfaces.PropComparator):

1157 """A helper class that allows easy construction of custom

1158 :class:`~.orm.interfaces.PropComparator`

1159 classes for usage with hybrids."""

1160

1161 property = None

1162

1163 def __init__(self, expression):

1164 self.expression = expression

1165

1166 def __clause_element__(self):

1167 expr = self.expression

1168 if hasattr(expr, "__clause_element__"):

1169 expr = expr.__clause_element__()

1170 return expr

1171

1172 def adapt_to_entity(self, adapt_to_entity):

1173 # interesting....

1174 return self

1175

1176

1177class ExprComparator(Comparator):

1178 def __init__(self, cls, expression, hybrid):

1179 self.cls = cls

1180 self.expression = expression

1181 self.hybrid = hybrid

1182

1183 def __getattr__(self, key):

1184 return getattr(self.expression, key)

1185

1186 @property

1187 def info(self):

1188 return self.hybrid.info

1189

1190 def _bulk_update_tuples(self, value):

1191 if isinstance(self.expression, attributes.QueryableAttribute):

1192 return self.expression._bulk_update_tuples(value)

1193 elif self.hybrid.update_expr is not None:

1194 return self.hybrid.update_expr(self.cls, value)

1195 else:

1196 return [(self.expression, value)]

1197

1198 @property

1199 def property(self):

1200 return self.expression.property

1201

1202 def operate(self, op, *other, **kwargs):

1203 return op(self.expression, *other, **kwargs)

1204

1205 def reverse_operate(self, op, other, **kwargs):

1206 return op(other, self.expression, **kwargs)