1============================= 2What's New in SQLAlchemy 1.2? 3============================= 4 5.. admonition:: About this Document 6 7 This document describes changes between SQLAlchemy version 1.1 8 and SQLAlchemy version 1.2. 9 10 11Introduction 12============ 13 14This guide introduces what's new in SQLAlchemy version 1.2, 15and also documents changes which affect users migrating 16their applications from the 1.1 series of SQLAlchemy to 1.2. 17 18Please carefully review the sections on behavioral changes for 19potentially backwards-incompatible changes in behavior. 20 21Platform Support 22================ 23 24Targeting Python 2.7 and Up 25--------------------------- 26 27SQLAlchemy 1.2 now moves the minimum Python version to 2.7, no longer 28supporting 2.6. New language features are expected to be merged 29into the 1.2 series that were not supported in Python 2.6. For Python 3 support, 30SQLAlchemy is currently tested on versions 3.5 and 3.6. 31 32 33New Features and Improvements - ORM 34=================================== 35 36.. _change_3954: 37 38"Baked" loading now the default for lazy loads 39---------------------------------------------- 40 41The :mod:`sqlalchemy.ext.baked` extension, first introduced in the 1.0 series, 42allows for the construction of a so-called :class:`.BakedQuery` object, 43which is an object that generates a :class:`_query.Query` object in conjunction 44with a cache key representing the structure of the query; this cache key 45is then linked to the resulting string SQL statement so that subsequent use 46of another :class:`.BakedQuery` with the same structure will bypass all the 47overhead of building the :class:`_query.Query` object, building the core 48:func:`_expression.select` object within, as well as the compilation of the :func:`_expression.select` 49into a string, cutting out well the majority of function call overhead normally 50associated with constructing and emitting an ORM :class:`_query.Query` object. 51 52The :class:`.BakedQuery` is now used by default by the ORM when it generates 53a "lazy" query for the lazy load of a :func:`_orm.relationship` construct, e.g. 54that of the default ``lazy="select"`` relationship loader strategy. This 55will allow for a significant reduction in function calls within the scope 56of an application's use of lazy load queries to load collections and related 57objects. Previously, this feature was available 58in 1.0 and 1.1 through the use of a global API method or by using the 59``baked_select`` strategy, it's now the only implementation for this behavior. 60The feature has also been improved such that the caching can still take place 61for objects that have additional loader options in effect subsequent 62to the lazy load. 63 64The caching behavior can be disabled on a per-relationship basis using the 65:paramref:`_orm.relationship.bake_queries` flag, which is available for 66very unusual cases, such as a relationship that uses a custom 67:class:`_query.Query` implementation that's not compatible with caching. 68 69 70:ticket:`3954` 71 72.. _change_3944: 73 74New "selectin" eager loading, loads all collections at once using IN 75-------------------------------------------------------------------- 76 77A new eager loader called "selectin" loading is added, which in many ways 78is similar to "subquery" loading, however produces a simpler SQL statement 79that is cacheable as well as more efficient. 80 81Given a query as below:: 82 83 q = session.query(User).\ 84 filter(User.name.like('%ed%')).\ 85 options(subqueryload(User.addresses)) 86 87The SQL produced would be the query against ``User`` followed by the 88subqueryload for ``User.addresses`` (note the parameters are also listed):: 89 90 SELECT users.id AS users_id, users.name AS users_name 91 FROM users 92 WHERE users.name LIKE ? 93 ('%ed%',) 94 95 SELECT addresses.id AS addresses_id, 96 addresses.user_id AS addresses_user_id, 97 addresses.email_address AS addresses_email_address, 98 anon_1.users_id AS anon_1_users_id 99 FROM (SELECT users.id AS users_id 100 FROM users 101 WHERE users.name LIKE ?) AS anon_1 102 JOIN addresses ON anon_1.users_id = addresses.user_id 103 ORDER BY anon_1.users_id 104 ('%ed%',) 105 106With "selectin" loading, we instead get a SELECT that refers to the 107actual primary key values loaded in the parent query:: 108 109 q = session.query(User).\ 110 filter(User.name.like('%ed%')).\ 111 options(selectinload(User.addresses)) 112 113Produces:: 114 115 SELECT users.id AS users_id, users.name AS users_name 116 FROM users 117 WHERE users.name LIKE ? 118 ('%ed%',) 119 120 SELECT users_1.id AS users_1_id, 121 addresses.id AS addresses_id, 122 addresses.user_id AS addresses_user_id, 123 addresses.email_address AS addresses_email_address 124 FROM users AS users_1 125 JOIN addresses ON users_1.id = addresses.user_id 126 WHERE users_1.id IN (?, ?) 127 ORDER BY users_1.id 128 (1, 3) 129 130The above SELECT statement includes these advantages: 131 132* It doesn't use a subquery, just an INNER JOIN, meaning it will perform 133 much better on a database like MySQL that doesn't like subqueries 134 135* Its structure is independent of the original query; in conjunction with the 136 new :ref:`expanding IN parameter system <change_3953>` we can in most cases 137 use the "baked" query to cache the string SQL, reducing per-query overhead 138 significantly 139 140* Because the query only fetches for a given list of primary key identifiers, 141 "selectin" loading is potentially compatible with :meth:`_query.Query.yield_per` to 142 operate on chunks of a SELECT result at a time, provided that the 143 database driver allows for multiple, simultaneous cursors (SQLite, PostgreSQL; 144 **not** MySQL drivers or SQL Server ODBC drivers). Neither joined eager 145 loading nor subquery eager loading are compatible with :meth:`_query.Query.yield_per`. 146 147The disadvantages of selectin eager loading are potentially large SQL 148queries, with large lists of IN parameters. The list of IN parameters themselves 149are chunked in groups of 500, so a result set of more than 500 lead objects 150will have more additional "SELECT IN" queries following. Also, support 151for composite primary keys depends on the database's ability to use 152tuples with IN, e.g. 153``(table.column_one, table_column_two) IN ((?, ?), (?, ?) (?, ?))``. 154Currently, PostgreSQL and MySQL are known to be compatible with this syntax, 155SQLite is not. 156 157.. seealso:: 158 159 :ref:`selectin_eager_loading` 160 161:ticket:`3944` 162 163.. _change_3948: 164 165"selectin" polymorphic loading, loads subclasses using separate IN queries 166-------------------------------------------------------------------------- 167 168Along similar lines as the "selectin" relationship loading feature just 169described at :ref:`change_3944` is "selectin" polymorphic loading. This 170is a polymorphic loading feature tailored primarily towards joined eager 171loading that allows the loading of the base entity to proceed with a simple 172SELECT statement, but then the attributes of the additional subclasses 173are loaded with additional SELECT statements: 174 175.. sourcecode:: python+sql 176 177 from sqlalchemy.orm import selectin_polymorphic 178 179 query = session.query(Employee).options( 180 selectin_polymorphic(Employee, [Manager, Engineer]) 181 ) 182 183 {opensql}query.all() 184 SELECT 185 employee.id AS employee_id, 186 employee.name AS employee_name, 187 employee.type AS employee_type 188 FROM employee 189 () 190 191 SELECT 192 engineer.id AS engineer_id, 193 employee.id AS employee_id, 194 employee.type AS employee_type, 195 engineer.engineer_name AS engineer_engineer_name 196 FROM employee JOIN engineer ON employee.id = engineer.id 197 WHERE employee.id IN (?, ?) ORDER BY employee.id 198 (1, 2) 199 200 SELECT 201 manager.id AS manager_id, 202 employee.id AS employee_id, 203 employee.type AS employee_type, 204 manager.manager_name AS manager_manager_name 205 FROM employee JOIN manager ON employee.id = manager.id 206 WHERE employee.id IN (?) ORDER BY employee.id 207 (3,) 208 209.. seealso:: 210 211 :ref:`polymorphic_selectin` 212 213:ticket:`3948` 214 215.. _change_3058: 216 217ORM attributes that can receive ad-hoc SQL expressions 218------------------------------------------------------ 219 220A new ORM attribute type :func:`_orm.query_expression` is added which 221is similar to :func:`_orm.deferred`, except its SQL expression 222is determined at query time using a new option :func:`_orm.with_expression`; 223if not specified, the attribute defaults to ``None``:: 224 225 from sqlalchemy.orm import query_expression 226 from sqlalchemy.orm import with_expression 227 228 class A(Base): 229 __tablename__ = 'a' 230 id = Column(Integer, primary_key=True) 231 x = Column(Integer) 232 y = Column(Integer) 233 234 # will be None normally... 235 expr = query_expression() 236 237 # but let's give it x + y 238 a1 = session.query(A).options( 239 with_expression(A.expr, A.x + A.y)).first() 240 print(a1.expr) 241 242.. seealso:: 243 244 :ref:`mapper_querytime_expression` 245 246:ticket:`3058` 247 248.. _change_orm_959: 249 250ORM Support of multiple-table deletes 251------------------------------------- 252 253The ORM :meth:`_query.Query.delete` method supports multiple-table criteria 254for DELETE, as introduced in :ref:`change_959`. The feature works 255in the same manner as multiple-table criteria for UPDATE, first 256introduced in 0.8 and described at :ref:`change_orm_2365`. 257 258Below, we emit a DELETE against ``SomeEntity``, adding 259a FROM clause (or equivalent, depending on backend) 260against ``SomeOtherEntity``:: 261 262 query(SomeEntity).\ 263 filter(SomeEntity.id==SomeOtherEntity.id).\ 264 filter(SomeOtherEntity.foo=='bar').\ 265 delete() 266 267.. seealso:: 268 269 :ref:`change_959` 270 271:ticket:`959` 272 273.. _change_3229: 274 275Support for bulk updates of hybrids, composites 276----------------------------------------------- 277 278Both hybrid attributes (e.g. :mod:`sqlalchemy.ext.hybrid`) as well as composite 279attributes (:ref:`mapper_composite`) now support being used in the 280SET clause of an UPDATE statement when using :meth:`_query.Query.update`. 281 282For hybrids, simple expressions can be used directly, or the new decorator 283:meth:`.hybrid_property.update_expression` can be used to break a value 284into multiple columns/expressions:: 285 286 class Person(Base): 287 # ... 288 289 first_name = Column(String(10)) 290 last_name = Column(String(10)) 291 292 @hybrid.hybrid_property 293 def name(self): 294 return self.first_name + ' ' + self.last_name 295 296 @name.expression 297 def name(cls): 298 return func.concat(cls.first_name, ' ', cls.last_name) 299 300 @name.update_expression 301 def name(cls, value): 302 f, l = value.split(' ', 1) 303 return [(cls.first_name, f), (cls.last_name, l)] 304 305Above, an UPDATE can be rendered using:: 306 307 session.query(Person).filter(Person.id == 5).update( 308 {Person.name: "Dr. No"}) 309 310Similar functionality is available for composites, where composite values 311will be broken out into their individual columns for bulk UPDATE:: 312 313 session.query(Vertex).update({Edge.start: Point(3, 4)}) 314 315 316.. seealso:: 317 318 :ref:`hybrid_bulk_update` 319 320.. _change_3911_3912: 321 322Hybrid attributes support reuse among subclasses, redefinition of @getter 323------------------------------------------------------------------------- 324 325The :class:`sqlalchemy.ext.hybrid.hybrid_property` class now supports 326calling mutators like ``@setter``, ``@expression`` etc. multiple times 327across subclasses, and now provides a ``@getter`` mutator, so that 328a particular hybrid can be repurposed across subclasses or other 329classes. This now is similar to the behavior of ``@property`` in standard 330Python:: 331 332 class FirstNameOnly(Base): 333 # ... 334 335 first_name = Column(String) 336 337 @hybrid_property 338 def name(self): 339 return self.first_name 340 341 @name.setter 342 def name(self, value): 343 self.first_name = value 344 345 class FirstNameLastName(FirstNameOnly): 346 # ... 347 348 last_name = Column(String) 349 350 @FirstNameOnly.name.getter 351 def name(self): 352 return self.first_name + ' ' + self.last_name 353 354 @name.setter 355 def name(self, value): 356 self.first_name, self.last_name = value.split(' ', maxsplit=1) 357 358 @name.expression 359 def name(cls): 360 return func.concat(cls.first_name, ' ', cls.last_name) 361 362Above, the ``FirstNameOnly.name`` hybrid is referenced by the 363``FirstNameLastName`` subclass in order to repurpose it specifically to the 364new subclass. This is achieved by copying the hybrid object to a new one 365within each call to ``@getter``, ``@setter``, as well as in all other 366mutator methods like ``@expression``, leaving the previous hybrid's definition 367intact. Previously, methods like ``@setter`` would modify the existing 368hybrid in-place, interfering with the definition on the superclass. 369 370.. note:: Be sure to read the documentation at :ref:`hybrid_reuse_subclass` 371 for important notes regarding how to override 372 :meth:`.hybrid_property.expression` 373 and :meth:`.hybrid_property.comparator`, as a special qualifier 374 :attr:`.hybrid_property.overrides` may be necessary to avoid name 375 conflicts with :class:`.QueryableAttribute` in some cases. 376 377.. note:: This change in ``@hybrid_property`` implies that when adding setters and 378 other state to a ``@hybrid_property``, the **methods must retain the name 379 of the original hybrid**, else the new hybrid with the additional state will 380 be present on the class as the non-matching name. This is the same behavior 381 as that of the ``@property`` construct that is part of standard Python:: 382 383 class FirstNameOnly(Base): 384 @hybrid_property 385 def name(self): 386 return self.first_name 387 388 # WRONG - will raise AttributeError: can't set attribute when 389 # assigning to .name 390 @name.setter 391 def _set_name(self, value): 392 self.first_name = value 393 394 class FirstNameOnly(Base): 395 @hybrid_property 396 def name(self): 397 return self.first_name 398 399 # CORRECT - note regular Python @property works the same way 400 @name.setter 401 def name(self, value): 402 self.first_name = value 403 404:ticket:`3911` 405 406:ticket:`3912` 407 408.. _change_3896_event: 409 410New bulk_replace event 411---------------------- 412 413To suit the validation use case described in :ref:`change_3896_validates`, 414a new :meth:`.AttributeEvents.bulk_replace` method is added, which is 415called in conjunction with the :meth:`.AttributeEvents.append` and 416:meth:`.AttributeEvents.remove` events. "bulk_replace" is called before 417"append" and "remove" so that the collection can be modified ahead of comparison 418to the existing collection. After that, individual items 419are appended to a new target collection, firing off the "append" 420event for items new to the collection, as was the previous behavior. 421Below illustrates both "bulk_replace" and 422"append" at the same time, including that "append" will receive an object 423already handled by "bulk_replace" if collection assignment is used. 424A new symbol :attr:`~.attributes.OP_BULK_REPLACE` may be used to determine 425if this "append" event is the second part of a bulk replace:: 426 427 from sqlalchemy.orm.attributes import OP_BULK_REPLACE 428 429 @event.listens_for(SomeObject.collection, "bulk_replace") 430 def process_collection(target, values, initiator): 431 values[:] = [_make_value(value) for value in values] 432 433 @event.listens_for(SomeObject.collection, "append", retval=True) 434 def process_collection(target, value, initiator): 435 # make sure bulk_replace didn't already do it 436 if initiator is None or initiator.op is not OP_BULK_REPLACE: 437 return _make_value(value) 438 else: 439 return value 440 441 442:ticket:`3896` 443 444.. _change_3303: 445 446New "modified" event handler for sqlalchemy.ext.mutable 447------------------------------------------------------- 448 449A new event handler :meth:`.AttributeEvents.modified` is added, which is 450triggered corresponding to calls to the :func:`.attributes.flag_modified` 451method, which is normally called from the :mod:`sqlalchemy.ext.mutable` 452extension:: 453 454 from sqlalchemy.ext.declarative import declarative_base 455 from sqlalchemy.ext.mutable import MutableDict 456 from sqlalchemy import event 457 458 Base = declarative_base() 459 460 class MyDataClass(Base): 461 __tablename__ = 'my_data' 462 id = Column(Integer, primary_key=True) 463 data = Column(MutableDict.as_mutable(JSONEncodedDict)) 464 465 @event.listens_for(MyDataClass.data, "modified") 466 def modified_json(instance): 467 print("json value modified:", instance.data) 468 469Above, the event handler will be triggered when an in-place change to the 470``.data`` dictionary occurs. 471 472:ticket:`3303` 473 474.. _change_3991: 475 476Added "for update" arguments to Session.refresh 477------------------------------------------------ 478 479Added new argument :paramref:`.Session.refresh.with_for_update` to the 480:meth:`.Session.refresh` method. When the :meth:`_query.Query.with_lockmode` 481method were deprecated in favor of :meth:`_query.Query.with_for_update`, 482the :meth:`.Session.refresh` method was never updated to reflect 483the new option:: 484 485 session.refresh(some_object, with_for_update=True) 486 487The :paramref:`.Session.refresh.with_for_update` argument accepts a dictionary 488of options that will be passed as the same arguments which are sent to 489:meth:`_query.Query.with_for_update`:: 490 491 session.refresh(some_objects, with_for_update={"read": True}) 492 493The new parameter supersedes the :paramref:`.Session.refresh.lockmode` 494parameter. 495 496:ticket:`3991` 497 498.. _change_3853: 499 500In-place mutation operators work for MutableSet, MutableList 501------------------------------------------------------------ 502 503Implemented the in-place mutation operators ``__ior__``, ``__iand__``, 504``__ixor__`` and ``__isub__`` for :class:`.mutable.MutableSet` and ``__iadd__`` 505for :class:`.mutable.MutableList`. While these 506methods would successfully update the collection previously, they would 507not correctly fire off change events. The operators mutate the collection 508as before but additionally emit the correct change event so that the change 509becomes part of the next flush process:: 510 511 model = session.query(MyModel).first() 512 model.json_set &= {1, 3} 513 514 515:ticket:`3853` 516 517.. _change_3769: 518 519AssociationProxy any(), has(), contains() work with chained association proxies 520------------------------------------------------------------------------------- 521 522The :meth:`.AssociationProxy.any`, :meth:`.AssociationProxy.has` 523and :meth:`.AssociationProxy.contains` comparison methods now support 524linkage to an attribute that is 525itself also an :class:`.AssociationProxy`, recursively. Below, ``A.b_values`` 526is an association proxy that links to ``AtoB.bvalue``, which is 527itself an association proxy onto ``B``:: 528 529 class A(Base): 530 __tablename__ = 'a' 531 id = Column(Integer, primary_key=True) 532 533 b_values = association_proxy("atob", "b_value") 534 c_values = association_proxy("atob", "c_value") 535 536 537 class B(Base): 538 __tablename__ = 'b' 539 id = Column(Integer, primary_key=True) 540 a_id = Column(ForeignKey('a.id')) 541 value = Column(String) 542 543 c = relationship("C") 544 545 546 class C(Base): 547 __tablename__ = 'c' 548 id = Column(Integer, primary_key=True) 549 b_id = Column(ForeignKey('b.id')) 550 value = Column(String) 551 552 553 class AtoB(Base): 554 __tablename__ = 'atob' 555 556 a_id = Column(ForeignKey('a.id'), primary_key=True) 557 b_id = Column(ForeignKey('b.id'), primary_key=True) 558 559 a = relationship("A", backref="atob") 560 b = relationship("B", backref="atob") 561 562 b_value = association_proxy("b", "value") 563 c_value = association_proxy("b", "c") 564 565We can query on ``A.b_values`` using :meth:`.AssociationProxy.contains` to 566query across the two proxies ``A.b_values``, ``AtoB.b_value``: 567 568.. sourcecode:: pycon+sql 569 570 >>> s.query(A).filter(A.b_values.contains('hi')).all() 571 {opensql}SELECT a.id AS a_id 572 FROM a 573 WHERE EXISTS (SELECT 1 574 FROM atob 575 WHERE a.id = atob.a_id AND (EXISTS (SELECT 1 576 FROM b 577 WHERE b.id = atob.b_id AND b.value = :value_1))) 578 579Similarly, we can query on ``A.c_values`` using :meth:`.AssociationProxy.any` 580to query across the two proxies ``A.c_values``, ``AtoB.c_value``: 581 582.. sourcecode:: pycon+sql 583 584 >>> s.query(A).filter(A.c_values.any(value='x')).all() 585 {opensql}SELECT a.id AS a_id 586 FROM a 587 WHERE EXISTS (SELECT 1 588 FROM atob 589 WHERE a.id = atob.a_id AND (EXISTS (SELECT 1 590 FROM b 591 WHERE b.id = atob.b_id AND (EXISTS (SELECT 1 592 FROM c 593 WHERE b.id = c.b_id AND c.value = :value_1))))) 594 595:ticket:`3769` 596 597.. _change_4137: 598 599Identity key enhancements to support sharding 600--------------------------------------------- 601 602The identity key structure used by the ORM now contains an additional 603member, so that two identical primary keys that originate from different 604contexts can co-exist within the same identity map. 605 606The example at :ref:`examples_sharding` has been updated to illustrate this 607behavior. The example shows a sharded class ``WeatherLocation`` that 608refers to a dependent ``WeatherReport`` object, where the ``WeatherReport`` 609class is mapped to a table that stores a simple integer primary key. Two 610``WeatherReport`` objects from different databases may have the same 611primary key value. The example now illustrates that a new ``identity_token`` 612field tracks this difference so that the two objects can co-exist in the 613same identity map:: 614 615 tokyo = WeatherLocation('Asia', 'Tokyo') 616 newyork = WeatherLocation('North America', 'New York') 617 618 tokyo.reports.append(Report(80.0)) 619 newyork.reports.append(Report(75)) 620 621 sess = create_session() 622 623 sess.add_all([tokyo, newyork, quito]) 624 625 sess.commit() 626 627 # the Report class uses a simple integer primary key. So across two 628 # databases, a primary key will be repeated. The "identity_token" tracks 629 # in memory that these two identical primary keys are local to different 630 # databases. 631 632 newyork_report = newyork.reports[0] 633 tokyo_report = tokyo.reports[0] 634 635 assert inspect(newyork_report).identity_key == (Report, (1, ), "north_america") 636 assert inspect(tokyo_report).identity_key == (Report, (1, ), "asia") 637 638 # the token representing the originating shard is also available directly 639 640 assert inspect(newyork_report).identity_token == "north_america" 641 assert inspect(tokyo_report).identity_token == "asia" 642 643 644:ticket:`4137` 645 646New Features and Improvements - Core 647==================================== 648 649.. _change_4102: 650 651Boolean datatype now enforces strict True/False/None values 652----------------------------------------------------------- 653 654In version 1.1, the change described in :ref:`change_3730` produced an 655unintended side effect of altering the way :class:`.Boolean` behaves when 656presented with a non-integer value, such as a string. In particular, the 657string value ``"0"``, which would previously result in the value ``False`` 658being generated, would now produce ``True``. Making matters worse, the change 659in behavior was only for some backends and not others, meaning code that sends 660string ``"0"`` values to :class:`.Boolean` would break inconsistently across 661backends. 662 663The ultimate solution to this problem is that **string values are not supported 664with Boolean**, so in 1.2 a hard ``TypeError`` is raised if a non-integer / 665True/False/None value is passed. Additionally, only the integer values 6660 and 1 are accepted. 667 668To accommodate for applications that wish to have more liberal interpretation 669of boolean values, the :class:`.TypeDecorator` should be used. Below 670illustrates a recipe that will allow for the "liberal" behavior of the pre-1.1 671:class:`.Boolean` datatype:: 672 673 from sqlalchemy import Boolean 674 from sqlalchemy import TypeDecorator 675 676 class LiberalBoolean(TypeDecorator): 677 impl = Boolean 678 679 def process_bind_param(self, value, dialect): 680 if value is not None: 681 value = bool(int(value)) 682 return value 683 684 685:ticket:`4102` 686 687.. _change_3919: 688 689Pessimistic disconnection detection added to the connection pool 690---------------------------------------------------------------- 691 692The connection pool documentation has long featured a recipe for using 693the :meth:`_events.ConnectionEvents.engine_connect` engine event to emit a simple 694statement on a checked-out connection to test it for liveness. The 695functionality of this recipe has now been added into the connection pool 696itself, when used in conjunction with an appropriate dialect. Using 697the new parameter :paramref:`_sa.create_engine.pool_pre_ping`, each connection 698checked out will be tested for freshness before being returned:: 699 700 engine = create_engine("mysql+pymysql://", pool_pre_ping=True) 701 702While the "pre-ping" approach adds a small amount of latency to the connection 703pool checkout, for a typical application that is transactionally-oriented 704(which includes most ORM applications), this overhead is minimal, and 705eliminates the problem of acquiring a stale connection that will raise 706an error, requiring that the application either abandon or retry the operation. 707 708The feature does **not** accommodate for connections dropped within 709an ongoing transaction or SQL operation. If an application must recover 710from these as well, it would need to employ its own operation retry logic 711to anticipate these errors. 712 713 714.. seealso:: 715 716 :ref:`pool_disconnects_pessimistic` 717 718 719:ticket:`3919` 720 721.. _change_3907: 722 723The IN / NOT IN operator's empty collection behavior is now configurable; default expression simplified 724------------------------------------------------------------------------------------------------------- 725 726An expression such as ``column.in_([])``, which is assumed to be false, 727now produces the expression ``1 != 1`` 728by default, instead of ``column != column``. This will **change the result** 729of a query that is comparing a SQL expression or column that evaluates to 730NULL when compared to an empty set, producing a boolean value false or true 731(for NOT IN) rather than NULL. The warning that would emit under 732this condition is also removed. The old behavior is available using the 733:paramref:`_sa.create_engine.empty_in_strategy` parameter to 734:func:`_sa.create_engine`. 735 736In SQL, the IN and NOT IN operators do not support comparison to a 737collection of values that is explicitly empty; meaning, this syntax is 738illegal:: 739 740 mycolumn IN () 741 742To work around this, SQLAlchemy and other database libraries detect this 743condition and render an alternative expression that evaluates to false, or 744in the case of NOT IN, to true, based on the theory that "col IN ()" is always 745false since nothing is in "the empty set". Typically, in order to 746produce a false/true constant that is portable across databases and works 747in the context of the WHERE clause, a simple tautology such as ``1 != 1`` is 748used to evaluate to false and ``1 = 1`` to evaluate to true (a simple constant 749"0" or "1" often does not work as the target of a WHERE clause). 750 751SQLAlchemy in its early days began with this approach as well, but soon it 752was theorized that the SQL expression ``column IN ()`` would not evaluate to 753false if the "column" were NULL; instead, the expression would produce NULL, 754since "NULL" means "unknown", and comparisons to NULL in SQL usually produce 755NULL. 756 757To simulate this result, SQLAlchemy changed from using ``1 != 1`` to 758instead use th expression ``expr != expr`` for empty "IN" and ``expr = expr`` 759for empty "NOT IN"; that is, instead of using a fixed value we use the 760actual left-hand side of the expression. If the left-hand side of 761the expression passed evaluates to NULL, then the comparison overall 762also gets the NULL result instead of false or true. 763 764Unfortunately, users eventually complained that this expression had a very 765severe performance impact on some query planners. At that point, a warning 766was added when an empty IN expression was encountered, favoring that SQLAlchemy 767continues to be "correct" and urging users to avoid code that generates empty 768IN predicates in general, since typically they can be safely omitted. However, 769this is of course burdensome in the case of queries that are built up dynamically 770from input variables, where an incoming set of values might be empty. 771 772In recent months, the original assumptions of this decision have been 773questioned. The notion that the expression "NULL IN ()" should return NULL was 774only theoretical, and could not be tested since databases don't support that 775syntax. However, as it turns out, you can in fact ask a relational database 776what value it would return for "NULL IN ()" by simulating the empty set as 777follows:: 778 779 SELECT NULL IN (SELECT 1 WHERE 1 != 1) 780 781With the above test, we see that the databases themselves can't agree on 782the answer. PostgreSQL, considered by most to be the most "correct" database, 783returns False; because even though "NULL" represents "unknown", the "empty set" 784means nothing is present, including all unknown values. On the 785other hand, MySQL and MariaDB return NULL for the above expression, defaulting 786to the more common behavior of "all comparisons to NULL return NULL". 787 788SQLAlchemy's SQL architecture is more sophisticated than it was when this 789design decision was first made, so we can now allow either behavior to 790be invoked at SQL string compilation time. Previously, the conversion to a 791comparison expression were done at construction time, that is, the moment 792the :meth:`.ColumnOperators.in_` or :meth:`.ColumnOperators.notin_` operators were invoked. 793With the compilation-time behavior, the dialect itself can be instructed 794to invoke either approach, that is, the "static" ``1 != 1`` comparison or the 795"dynamic" ``expr != expr`` comparison. The default has been **changed** 796to be the "static" comparison, since this agrees with the behavior that 797PostgreSQL would have in any case and this is also what the vast majority 798of users prefer. This will **change the result** of a query that is comparing 799a null expression to the empty set, particularly one that is querying 800for the negation ``where(~null_expr.in_([]))``, since this now evaluates to true 801and not NULL. 802 803The behavior can now be controlled using the flag 804:paramref:`_sa.create_engine.empty_in_strategy`, which defaults to the 805``"static"`` setting, but may also be set to ``"dynamic"`` or 806``"dynamic_warn"``, where the ``"dynamic_warn"`` setting is equivalent to the 807previous behavior of emitting ``expr != expr`` as well as a performance 808warning. However, it is anticipated that most users will appreciate the 809"static" default. 810 811:ticket:`3907` 812 813.. _change_3953: 814 815Late-expanded IN parameter sets allow IN expressions with cached statements 816--------------------------------------------------------------------------- 817 818Added a new kind of :func:`.bindparam` called "expanding". This is 819for use in ``IN`` expressions where the list of elements is rendered 820into individual bound parameters at statement execution time, rather 821than at statement compilation time. This allows both a single bound 822parameter name to be linked to an IN expression of multiple elements, 823as well as allows query caching to be used with IN expressions. The 824new feature allows the related features of "select in" loading and 825"polymorphic in" loading to make use of the baked query extension 826to reduce call overhead:: 827 828 stmt = select([table]).where( 829 table.c.col.in_(bindparam('foo', expanding=True)) 830 conn.execute(stmt, {"foo": [1, 2, 3]}) 831 832The feature should be regarded as **experimental** within the 1.2 series. 833 834 835:ticket:`3953` 836 837.. _change_3999: 838 839Flattened operator precedence for comparison operators 840------------------------------------------------------- 841 842The operator precedence for operators like IN, LIKE, equals, IS, MATCH, and 843other comparison operators has been flattened into one level. This will 844have the effect of more parenthesization being generated when comparison 845operators are combined together, such as:: 846 847 (column('q') == null()) != (column('y') == null()) 848 849Will now generate ``(q IS NULL) != (y IS NULL)`` rather than 850``q IS NULL != y IS NULL``. 851 852 853:ticket:`3999` 854 855.. _change_1546: 856 857Support for SQL Comments on Table, Column, includes DDL, reflection 858------------------------------------------------------------------- 859 860The Core receives support for string comments associated with tables 861and columns. These are specified via the :paramref:`_schema.Table.comment` and 862:paramref:`_schema.Column.comment` arguments:: 863 864 Table( 865 'my_table', metadata, 866 Column('q', Integer, comment="the Q value"), 867 comment="my Q table" 868 ) 869 870Above, DDL will be rendered appropriately upon table create to associate 871the above comments with the table/ column within the schema. When 872the above table is autoloaded or inspected with :meth:`_reflection.Inspector.get_columns`, 873the comments are included. The table comment is also available independently 874using the :meth:`_reflection.Inspector.get_table_comment` method. 875 876Current backend support includes MySQL, PostgreSQL, and Oracle. 877 878:ticket:`1546` 879 880.. _change_959: 881 882Multiple-table criteria support for DELETE 883------------------------------------------ 884 885The :class:`_expression.Delete` construct now supports multiple-table criteria, 886implemented for those backends which support it, currently these are 887PostgreSQL, MySQL and Microsoft SQL Server (support is also added to the 888currently non-working Sybase dialect). The feature works in the same 889was as that of multiple-table criteria for UPDATE, first introduced in 890the 0.7 and 0.8 series. 891 892Given a statement as:: 893 894 stmt = users.delete().\ 895 where(users.c.id == addresses.c.id).\ 896 where(addresses.c.email_address.startswith('ed%')) 897 conn.execute(stmt) 898 899The resulting SQL from the above statement on a PostgreSQL backend 900would render as:: 901 902 DELETE FROM users USING addresses 903 WHERE users.id = addresses.id 904 AND (addresses.email_address LIKE %(email_address_1)s || '%%') 905 906.. seealso:: 907 908 :ref:`multi_table_deletes` 909 910:ticket:`959` 911 912.. _change_2694: 913 914New "autoescape" option for startswith(), endswith() 915---------------------------------------------------- 916 917The "autoescape" parameter is added to :meth:`.ColumnOperators.startswith`, 918:meth:`.ColumnOperators.endswith`, :meth:`.ColumnOperators.contains`. 919This parameter when set to ``True`` will automatically escape all occurrences 920of ``%``, ``_`` with an escape character, which defaults to a forwards slash ``/``; 921occurrences of the escape character itself are also escaped. The forwards slash 922is used to avoid conflicts with settings like PostgreSQL's 923``standard_confirming_strings``, whose default value changed as of PostgreSQL 9249.1, and MySQL's ``NO_BACKSLASH_ESCAPES`` settings. The existing "escape" parameter 925can now be used to change the autoescape character, if desired. 926 927.. note:: This feature has been changed as of 1.2.0 from its initial 928 implementation in 1.2.0b2 such that autoescape is now passed as a boolean 929 value, rather than a specific character to use as the escape character. 930 931An expression such as:: 932 933 >>> column('x').startswith('total%score', autoescape=True) 934 935Renders as:: 936 937 x LIKE :x_1 || '%' ESCAPE '/' 938 939Where the value of the parameter "x_1" is ``'total/%score'``. 940 941Similarly, an expression that has backslashes:: 942 943 >>> column('x').startswith('total/score', autoescape=True) 944 945Will render the same way, with the value of the parameter "x_1" as 946``'total//score'``. 947 948 949:ticket:`2694` 950 951.. _change_floats_12: 952 953Stronger typing added to "float" datatypes 954------------------------------------------ 955 956A series of changes allow for use of the :class:`.Float` datatype to more 957strongly link itself to Python floating point values, instead of the more 958generic :class:`.Numeric`. The changes are mostly related to ensuring 959that Python floating point values are not erroneously coerced to 960``Decimal()``, and are coerced to ``float`` if needed, on the result side, 961if the application is working with plain floats. 962 963* A plain Python "float" value passed to a SQL expression will now be 964 pulled into a literal parameter with the type :class:`.Float`; previously, 965 the type was :class:`.Numeric`, with the default "asdecimal=True" flag, which 966 meant the result type would coerce to ``Decimal()``. In particular, 967 this would emit a confusing warning on SQLite:: 968 969 970 float_value = connection.scalar( 971 select([literal(4.56)]) # the "BindParameter" will now be 972 # Float, not Numeric(asdecimal=True) 973 ) 974 975* Math operations between :class:`.Numeric`, :class:`.Float`, and 976 :class:`.Integer` will now preserve the :class:`.Numeric` or :class:`.Float` 977 type in the resulting expression's type, including the ``asdecimal`` flag 978 as well as if the type should be :class:`.Float`:: 979 980 # asdecimal flag is maintained 981 expr = column('a', Integer) * column('b', Numeric(asdecimal=False)) 982 assert expr.type.asdecimal == False 983 984 # Float subclass of Numeric is maintained 985 expr = column('a', Integer) * column('b', Float()) 986 assert isinstance(expr.type, Float) 987 988* The :class:`.Float` datatype will apply the ``float()`` processor to 989 result values unconditionally if the DBAPI is known to support native 990 ``Decimal()`` mode. Some backends do not always guarantee that a floating 991 point number comes back as plain float and not precision numeric such 992 as MySQL. 993 994:ticket:`4017` 995 996:ticket:`4018` 997 998:ticket:`4020` 999 1000.. change_3249: 1001 1002Support for GROUPING SETS, CUBE, ROLLUP 1003--------------------------------------- 1004 1005All three of GROUPING SETS, CUBE, ROLLUP are available via the 1006:attr:`.func` namespace. In the case of CUBE and ROLLUP, these functions 1007already work in previous versions, however for GROUPING SETS, a placeholder 1008is added to the compiler to allow for the space. All three functions 1009are named in the documentation now:: 1010 1011 >>> from sqlalchemy import select, table, column, func, tuple_ 1012 >>> t = table('t', 1013 ... column('value'), column('x'), 1014 ... column('y'), column('z'), column('q')) 1015 >>> stmt = select([func.sum(t.c.value)]).group_by( 1016 ... func.grouping_sets( 1017 ... tuple_(t.c.x, t.c.y), 1018 ... tuple_(t.c.z, t.c.q), 1019 ... ) 1020 ... ) 1021 >>> print(stmt) 1022 SELECT sum(t.value) AS sum_1 1023 FROM t GROUP BY GROUPING SETS((t.x, t.y), (t.z, t.q)) 1024 1025:ticket:`3429` 1026 1027.. _change_4075: 1028 1029Parameter helper for multi-valued INSERT with contextual default generator 1030-------------------------------------------------------------------------- 1031 1032A default generation function, e.g. that described at 1033:ref:`context_default_functions`, can look at the current parameters relevant 1034to the statement via the :attr:`.DefaultExecutionContext.current_parameters` 1035attribute. However, in the case of a :class:`_expression.Insert` construct that specifies 1036multiple VALUES clauses via the :meth:`_expression.Insert.values` method, the user-defined 1037function is called multiple times, once for each parameter set, however there 1038was no way to know which subset of keys in 1039:attr:`.DefaultExecutionContext.current_parameters` apply to that column. A 1040new function :meth:`.DefaultExecutionContext.get_current_parameters` is added, 1041which includes a keyword argument 1042:paramref:`.DefaultExecutionContext.get_current_parameters.isolate_multiinsert_groups` 1043defaulting to ``True``, which performs the extra work of delivering a sub-dictionary of 1044:attr:`.DefaultExecutionContext.current_parameters` which has the names 1045localized to the current VALUES clause being processed:: 1046 1047 1048 def mydefault(context): 1049 return context.get_current_parameters()['counter'] + 12 1050 1051 mytable = Table('mytable', meta, 1052 Column('counter', Integer), 1053 Column('counter_plus_twelve', 1054 Integer, default=mydefault, onupdate=mydefault) 1055 ) 1056 1057 stmt = mytable.insert().values( 1058 [{"counter": 5}, {"counter": 18}, {"counter": 20}]) 1059 1060 conn.execute(stmt) 1061 1062:ticket:`4075` 1063 1064Key Behavioral Changes - ORM 1065============================ 1066 1067.. _change_3934: 1068 1069The after_rollback() Session event now emits before the expiration of objects 1070----------------------------------------------------------------------------- 1071 1072The :meth:`.SessionEvents.after_rollback` event now has access to the attribute 1073state of objects before their state has been expired (e.g. the "snapshot 1074removal"). This allows the event to be consistent with the behavior 1075of the :meth:`.SessionEvents.after_commit` event which also emits before the 1076"snapshot" has been removed:: 1077 1078 sess = Session() 1079 1080 user = sess.query(User).filter_by(name='x').first() 1081 1082 @event.listens_for(sess, "after_rollback") 1083 def after_rollback(session): 1084 # 'user.name' is now present, assuming it was already 1085 # loaded. previously this would raise upon trying 1086 # to emit a lazy load. 1087 print("user name: %s" % user.name) 1088 1089 @event.listens_for(sess, "after_commit") 1090 def after_commit(session): 1091 # 'user.name' is present, assuming it was already 1092 # loaded. this is the existing behavior. 1093 print("user name: %s" % user.name) 1094 1095 if should_rollback: 1096 sess.rollback() 1097 else: 1098 sess.commit() 1099 1100Note that the :class:`.Session` will still disallow SQL from being emitted 1101within this event; meaning that unloaded attributes will still not be 1102able to load within the scope of the event. 1103 1104:ticket:`3934` 1105 1106.. _change_3891: 1107 1108Fixed issue involving single-table inheritance with ``select_from()`` 1109--------------------------------------------------------------------- 1110 1111The :meth:`_query.Query.select_from` method now honors the single-table inheritance 1112column discriminator when generating SQL; previously, only the expressions 1113in the query column list would be taken into account. 1114 1115Supposing ``Manager`` is a subclass of ``Employee``. A query like the following:: 1116 1117 sess.query(Manager.id) 1118 1119Would generate SQL as:: 1120 1121 SELECT employee.id FROM employee WHERE employee.type IN ('manager') 1122 1123However, if ``Manager`` were only specified by :meth:`_query.Query.select_from` 1124and not in the columns list, the discriminator would not be added:: 1125 1126 sess.query(func.count(1)).select_from(Manager) 1127 1128would generate:: 1129 1130 SELECT count(1) FROM employee 1131 1132With the fix, :meth:`_query.Query.select_from` now works correctly and we get:: 1133 1134 SELECT count(1) FROM employee WHERE employee.type IN ('manager') 1135 1136Applications that may have been working around this by supplying the 1137WHERE clause manually may need to be adjusted. 1138 1139:ticket:`3891` 1140 1141.. _change_3913: 1142 1143Previous collection is no longer mutated upon replacement 1144--------------------------------------------------------- 1145 1146The ORM emits events whenever the members of a mapped collection change. 1147In the case of assigning a collection to an attribute that would replace 1148the previous collection, a side effect of this was that the collection 1149being replaced would also be mutated, which is misleading and unnecessary:: 1150 1151 >>> a1, a2, a3 = Address('a1'), Address('a2'), Address('a3') 1152 >>> user.addresses = [a1, a2] 1153 1154 >>> previous_collection = user.addresses 1155 1156 # replace the collection with a new one 1157 >>> user.addresses = [a2, a3] 1158 1159 >>> previous_collection 1160 [Address('a1'), Address('a2')] 1161 1162Above, prior to the change, the ``previous_collection`` would have had the 1163"a1" member removed, corresponding to the member that's no longer in the 1164new collection. 1165 1166:ticket:`3913` 1167 1168.. _change_3896_validates: 1169 1170A @validates method receives all values on bulk-collection set before comparison 1171-------------------------------------------------------------------------------- 1172 1173A method that uses ``@validates`` will now receive all members of a collection 1174during a "bulk set" operation, before comparison is applied against the 1175existing collection. 1176 1177Given a mapping as:: 1178 1179 class A(Base): 1180 __tablename__ = 'a' 1181 id = Column(Integer, primary_key=True) 1182 bs = relationship("B") 1183 1184 @validates('bs') 1185 def convert_dict_to_b(self, key, value): 1186 return B(data=value['data']) 1187 1188 class B(Base): 1189 __tablename__ = 'b' 1190 id = Column(Integer, primary_key=True) 1191 a_id = Column(ForeignKey('a.id')) 1192 data = Column(String) 1193 1194Above, we could use the validator as follows, to convert from an incoming 1195dictionary to an instance of ``B`` upon collection append:: 1196 1197 a1 = A() 1198 a1.bs.append({"data": "b1"}) 1199 1200However, a collection assignment would fail, since the ORM would assume 1201incoming objects are already instances of ``B`` as it attempts to compare them 1202to the existing members of the collection, before doing collection appends 1203which actually invoke the validator. This would make it impossible for bulk 1204set operations to accommodate non-ORM objects like dictionaries that needed 1205up-front modification:: 1206 1207 a1 = A() 1208 a1.bs = [{"data": "b1"}] 1209 1210The new logic uses the new :meth:`.AttributeEvents.bulk_replace` event to ensure 1211that all values are sent to the ``@validates`` function up front. 1212 1213As part of this change, this means that validators will now receive 1214**all** members of a collection upon bulk set, not just the members that 1215are new. Supposing a simple validator such as:: 1216 1217 class A(Base): 1218 # ... 1219 1220 @validates('bs') 1221 def validate_b(self, key, value): 1222 assert value.data is not None 1223 return value 1224 1225Above, if we began with a collection as:: 1226 1227 a1 = A() 1228 1229 b1, b2 = B(data="one"), B(data="two") 1230 a1.bs = [b1, b2] 1231 1232And then, replaced the collection with one that overlaps the first:: 1233 1234 b3 = B(data="three") 1235 a1.bs = [b2, b3] 1236 1237Previously, the second assignment would trigger the ``A.validate_b`` 1238method only once, for the ``b3`` object. The ``b2`` object would be seen 1239as being already present in the collection and not validated. With the new 1240behavior, both ``b2`` and ``b3`` are passed to ``A.validate_b`` before passing 1241onto the collection. It is thus important that validation methods employ 1242idempotent behavior to suit such a case. 1243 1244.. seealso:: 1245 1246 :ref:`change_3896_event` 1247 1248:ticket:`3896` 1249 1250.. _change_3753: 1251 1252Use flag_dirty() to mark an object as "dirty" without any attribute changing 1253---------------------------------------------------------------------------- 1254 1255An exception is now raised if the :func:`.attributes.flag_modified` function 1256is used to mark an attribute as modified that isn't actually loaded:: 1257 1258 a1 = A(data='adf') 1259 s.add(a1) 1260 1261 s.flush() 1262 1263 # expire, similarly as though we said s.commit() 1264 s.expire(a1, 'data') 1265 1266 # will raise InvalidRequestError 1267 attributes.flag_modified(a1, 'data') 1268 1269This because the flush process will most likely fail in any case if the 1270attribute remains un-present by the time flush occurs. To mark an object 1271as "modified" without referring to any attribute specifically, so that it 1272is considered within the flush process for the purpose of custom event handlers 1273such as :meth:`.SessionEvents.before_flush`, use the new 1274:func:`.attributes.flag_dirty` function:: 1275 1276 from sqlalchemy.orm import attributes 1277 1278 attributes.flag_dirty(a1) 1279 1280:ticket:`3753` 1281 1282.. _change_3796: 1283 1284"scope" keyword removed from scoped_session 1285------------------------------------------- 1286 1287A very old and undocumented keyword argument ``scope`` has been removed:: 1288 1289 from sqlalchemy.orm import scoped_session 1290 Session = scoped_session(sessionmaker()) 1291 1292 session = Session(scope=None) 1293 1294The purpose of this keyword was an attempt to allow for variable 1295"scopes", where ``None`` indicated "no scope" and would therefore return 1296a new :class:`.Session`. The keyword has never been documented and will 1297now raise ``TypeError`` if encountered. It is not anticipated that this 1298keyword is in use, however if users report issues related to this during 1299beta testing, it can be restored with a deprecation. 1300 1301:ticket:`3796` 1302 1303.. _change_3471: 1304 1305Refinements to post_update in conjunction with onupdate 1306------------------------------------------------------- 1307 1308A relationship that uses the :paramref:`_orm.relationship.post_update` feature 1309will now interact better with a column that has an :paramref:`_schema.Column.onupdate` 1310value set. If an object is inserted with an explicit value for the column, 1311it is re-stated during the UPDATE so that the "onupdate" rule does not 1312overwrite it:: 1313 1314 class A(Base): 1315 __tablename__ = 'a' 1316 id = Column(Integer, primary_key=True) 1317 favorite_b_id = Column(ForeignKey('b.id', name="favorite_b_fk")) 1318 bs = relationship("B", primaryjoin="A.id == B.a_id") 1319 favorite_b = relationship( 1320 "B", primaryjoin="A.favorite_b_id == B.id", post_update=True) 1321 updated = Column(Integer, onupdate=my_onupdate_function) 1322 1323 class B(Base): 1324 __tablename__ = 'b' 1325 id = Column(Integer, primary_key=True) 1326 a_id = Column(ForeignKey('a.id', name="a_fk")) 1327 1328 a1 = A() 1329 b1 = B() 1330 1331 a1.bs.append(b1) 1332 a1.favorite_b = b1 1333 a1.updated = 5 1334 s.add(a1) 1335 s.flush() 1336 1337Above, the previous behavior would be that an UPDATE would emit after the 1338INSERT, thus triggering the "onupdate" and overwriting the value 1339"5". The SQL now looks like:: 1340 1341 INSERT INTO a (favorite_b_id, updated) VALUES (?, ?) 1342 (None, 5) 1343 INSERT INTO b (a_id) VALUES (?) 1344 (1,) 1345 UPDATE a SET favorite_b_id=?, updated=? WHERE a.id = ? 1346 (1, 5, 1) 1347 1348Additionally, if the value of "updated" is *not* set, then we correctly 1349get back the newly generated value on ``a1.updated``; previously, the logic 1350that refreshes or expires the attribute to allow the generated value 1351to be present would not fire off for a post-update. The 1352:meth:`.InstanceEvents.refresh_flush` event is also emitted when a refresh 1353within flush occurs in this case. 1354 1355:ticket:`3471` 1356 1357:ticket:`3472` 1358 1359.. _change_3496: 1360 1361post_update integrates with ORM versioning 1362------------------------------------------ 1363 1364The post_update feature, documented at :ref:`post_update`, involves that an 1365UPDATE statement is emitted in response to changes to a particular 1366relationship-bound foreign key, in addition to the INSERT/UPDATE/DELETE that 1367would normally be emitted for the target row. This UPDATE statement 1368now participates in the versioning feature, documented at 1369:ref:`mapper_version_counter`. 1370 1371Given a mapping:: 1372 1373 class Node(Base): 1374 __tablename__ = 'node' 1375 id = Column(Integer, primary_key=True) 1376 version_id = Column(Integer, default=0) 1377 parent_id = Column(ForeignKey('node.id')) 1378 favorite_node_id = Column(ForeignKey('node.id')) 1379 1380 nodes = relationship("Node", primaryjoin=remote(parent_id) == id) 1381 favorite_node = relationship( 1382 "Node", primaryjoin=favorite_node_id == remote(id), 1383 post_update=True 1384 ) 1385 1386 __mapper_args__ = { 1387 'version_id_col': version_id 1388 } 1389 1390An UPDATE of a node that associates another node as "favorite" will 1391now increment the version counter as well as match the current version:: 1392 1393 node = Node() 1394 session.add(node) 1395 session.commit() # node is now version #1 1396 1397 node = session.query(Node).get(node.id) 1398 node.favorite_node = Node() 1399 session.commit() # node is now version #2 1400 1401Note that this means an object that receives an UPDATE in response to 1402other attributes changing, and a second UPDATE due to a post_update 1403relationship change, will now receive 1404**two version counter updates for one flush**. However, if the object 1405is subject to an INSERT within the current flush, the version counter 1406**will not** be incremented an additional time, unless a server-side 1407versioning scheme is in place. 1408 1409The reason post_update emits an UPDATE even for an UPDATE is now discussed at 1410:ref:`faq_post_update_update`. 1411 1412.. seealso:: 1413 1414 :ref:`post_update` 1415 1416 :ref:`faq_post_update_update` 1417 1418 1419:ticket:`3496` 1420 1421Key Behavioral Changes - Core 1422============================= 1423 1424.. _change_4063: 1425 1426The typing behavior of custom operators has been made consistent 1427---------------------------------------------------------------- 1428 1429User defined operators can be made on the fly using the 1430:meth:`.Operators.op` function. Previously, the typing behavior of 1431an expression against such an operator was inconsistent and also not 1432controllable. 1433 1434Whereas in 1.1, an expression such as the following would produce 1435a result with no return type (assume ``-%>`` is some special operator 1436supported by the database):: 1437 1438 >>> column('x', types.DateTime).op('-%>')(None).type 1439 NullType() 1440 1441Other types would use the default behavior of using the left-hand type 1442as the return type:: 1443 1444 >>> column('x', types.String(50)).op('-%>')(None).type 1445 String(length=50) 1446 1447These behaviors were mostly by accident, so the behavior has been made 1448consistent with the second form, that is the default return type is the 1449same as the left-hand expression:: 1450 1451 >>> column('x', types.DateTime).op('-%>')(None).type 1452 DateTime() 1453 1454As most user-defined operators tend to be "comparison" operators, often 1455one of the many special operators defined by PostgreSQL, the 1456:paramref:`.Operators.op.is_comparison` flag has been repaired to follow 1457its documented behavior of allowing the return type to be :class:`.Boolean` 1458in all cases, including for :class:`_types.ARRAY` and :class:`_types.JSON`:: 1459 1460 >>> column('x', types.String(50)).op('-%>', is_comparison=True)(None).type 1461 Boolean() 1462 >>> column('x', types.ARRAY(types.Integer)).op('-%>', is_comparison=True)(None).type 1463 Boolean() 1464 >>> column('x', types.JSON()).op('-%>', is_comparison=True)(None).type 1465 Boolean() 1466 1467To assist with boolean comparison operators, a new shorthand method 1468:meth:`.Operators.bool_op` has been added. This method should be preferred 1469for on-the-fly boolean operators:: 1470 1471 >>> print(column('x', types.Integer).bool_op('-%>')(5)) 1472 x -%> :x_1 1473 1474 1475.. _change_3740: 1476 1477Percent signs in literal_column() now conditionally escaped 1478----------------------------------------------------------- 1479 1480The :obj:`_expression.literal_column` construct now escapes percent sign characters 1481conditionally, based on whether or not the DBAPI in use makes use of a 1482percent-sign-sensitive paramstyle or not (e.g. 'format' or 'pyformat'). 1483 1484Previously, it was not possible to produce a :obj:`_expression.literal_column` 1485construct that stated a single percent sign:: 1486 1487 >>> from sqlalchemy import literal_column 1488 >>> print(literal_column('some%symbol')) 1489 some%%symbol 1490 1491The percent sign is now unaffected for dialects that are not set to 1492use the 'format' or 'pyformat' paramstyles; dialects such most MySQL 1493dialects which do state one of these paramstyles will continue to escape 1494as is appropriate:: 1495 1496 >>> from sqlalchemy import literal_column 1497 >>> print(literal_column('some%symbol')) 1498 some%symbol 1499 >>> from sqlalchemy.dialects import mysql 1500 >>> print(literal_column('some%symbol').compile(dialect=mysql.dialect())) 1501 some%%symbol 1502 1503As part of this change, the doubling that has been present when using 1504operators like :meth:`.ColumnOperators.contains`, 1505:meth:`.ColumnOperators.startswith` and :meth:`.ColumnOperators.endswith` 1506is also refined to only occur when appropriate. 1507 1508:ticket:`3740` 1509 1510 1511.. _change_3785: 1512 1513The column-level COLLATE keyword now quotes the collation name 1514-------------------------------------------------------------- 1515 1516A bug in the :func:`_expression.collate` and :meth:`.ColumnOperators.collate` 1517functions, used to supply ad-hoc column collations at the statement level, 1518is fixed, where a case sensitive name would not be quoted:: 1519 1520 stmt = select([mytable.c.x, mytable.c.y]).\ 1521 order_by(mytable.c.somecolumn.collate("fr_FR")) 1522 1523now renders:: 1524 1525 SELECT mytable.x, mytable.y, 1526 FROM mytable ORDER BY mytable.somecolumn COLLATE "fr_FR" 1527 1528Previously, the case sensitive name `"fr_FR"` would not be quoted. Currently, 1529manual quoting of the "fr_FR" name is **not** detected, so applications that 1530are manually quoting the identifier should be adjusted. Note that this change 1531does not impact the use of collations at the type level (e.g. specified 1532on the datatype like :class:`.String` at the table level), where quoting 1533is already applied. 1534 1535:ticket:`3785` 1536 1537Dialect Improvements and Changes - PostgreSQL 1538============================================= 1539 1540.. _change_4109: 1541 1542Support for Batch Mode / Fast Execution Helpers 1543------------------------------------------------ 1544 1545The psycopg2 ``cursor.executemany()`` method has been identified as performing 1546poorly, particularly with INSERT statements. To alleviate this, psycopg2 1547has added `Fast Execution Helpers <https://initd.org/psycopg/docs/extras.html#fast-execution-helpers>`_ 1548which rework statements into fewer server round trips by sending multiple 1549DML statements in batch. SQLAlchemy 1.2 now includes support for these 1550helpers to be used transparently whenever the :class:`_engine.Engine` makes use 1551of ``cursor.executemany()`` to invoke a statement against multiple parameter 1552sets. The feature is off by default and can be enabled using the 1553``use_batch_mode`` argument on :func:`_sa.create_engine`:: 1554 1555 engine = create_engine( 1556 "postgresql+psycopg2://scott:tiger@host/dbname", 1557 use_batch_mode=True) 1558 1559The feature is considered to be experimental for the moment but may become 1560on by default in a future release. 1561 1562.. seealso:: 1563 1564 :ref:`psycopg2_batch_mode` 1565 1566:ticket:`4109` 1567 1568.. _change_3959: 1569 1570Support for fields specification in INTERVAL, including full reflection 1571----------------------------------------------------------------------- 1572 1573The "fields" specifier in PostgreSQL's INTERVAL datatype allows specification 1574of which fields of the interval to store, including such values as "YEAR", 1575"MONTH", "YEAR TO MONTH", etc. The :class:`_postgresql.INTERVAL` datatype 1576now allows these values to be specified:: 1577 1578 from sqlalchemy.dialects.postgresql import INTERVAL 1579 1580 Table( 1581 'my_table', metadata, 1582 Column("some_interval", INTERVAL(fields="DAY TO SECOND")) 1583 ) 1584 1585Additionally, all INTERVAL datatypes can now be reflected independently 1586of the "fields" specifier present; the "fields" parameter in the datatype 1587itself will also be present:: 1588 1589 >>> inspect(engine).get_columns("my_table") 1590 [{'comment': None, 1591 'name': u'some_interval', 'nullable': True, 1592 'default': None, 'autoincrement': False, 1593 'type': INTERVAL(fields=u'day to second')}] 1594 1595:ticket:`3959` 1596 1597Dialect Improvements and Changes - MySQL 1598======================================== 1599 1600.. _change_4009: 1601 1602Support for INSERT..ON DUPLICATE KEY UPDATE 1603------------------------------------------- 1604 1605The ``ON DUPLICATE KEY UPDATE`` clause of ``INSERT`` supported by MySQL 1606is now supported using a MySQL-specific version of the 1607:class:`_expression.Insert` object, via :func:`sqlalchemy.dialects.mysql.dml.insert`. 1608This :class:`_expression.Insert` subclass adds a new method 1609:meth:`~.mysql.dml.Insert.on_duplicate_key_update` that implements MySQL's syntax:: 1610 1611 from sqlalchemy.dialects.mysql import insert 1612 1613 insert_stmt = insert(my_table). \ 1614 values(id='some_id', data='some data to insert') 1615 1616 on_conflict_stmt = insert_stmt.on_duplicate_key_update( 1617 data=insert_stmt.inserted.data, 1618 status='U' 1619 ) 1620 1621 conn.execute(on_conflict_stmt) 1622 1623The above will render:: 1624 1625 INSERT INTO my_table (id, data) 1626 VALUES (:id, :data) 1627 ON DUPLICATE KEY UPDATE data=VALUES(data), status=:status_1 1628 1629.. seealso:: 1630 1631 :ref:`mysql_insert_on_duplicate_key_update` 1632 1633:ticket:`4009` 1634 1635 1636Dialect Improvements and Changes - Oracle 1637========================================= 1638 1639.. _change_cxoracle_12: 1640 1641Major Refactor to cx_Oracle Dialect, Typing System 1642-------------------------------------------------- 1643 1644With the introduction of the 6.x series of the cx_Oracle DBAPI, SQLAlchemy's 1645cx_Oracle dialect has been reworked and simplified to take advantage of recent 1646improvements in cx_Oracle as well as dropping support for patterns that were 1647more relevant before the 5.x series of cx_Oracle. 1648 1649* The minimum cx_Oracle version supported is now 5.1.3; 5.3 or the most recent 1650 6.x series are recommended. 1651 1652* The handling of datatypes has been refactored. The ``cursor.setinputsizes()`` 1653 method is no longer used for any datatype except LOB types, per advice from 1654 cx_Oracle's developers. As a result, the parameters ``auto_setinputsizes`` 1655 and ``exclude_setinputsizes`` are deprecated and no longer have any effect. 1656 1657* The ``coerce_to_decimal`` flag, when set to False to indicate that coercion 1658 of numeric types with precision and scale to ``Decimal`` should not occur, 1659 only impacts untyped (e.g. plain string with no :class:`.TypeEngine` objects) 1660 statements. A Core expression that includes a :class:`.Numeric` type or 1661 subtype will now follow the decimal coercion rules of that type. 1662 1663* The "two phase" transaction support in the dialect, already dropped for the 1664 6.x series of cx_Oracle, has now been removed entirely as this feature has 1665 never worked correctly and is unlikely to have been in production use. 1666 As a result, the ``allow_twophase`` dialect flag is deprecated and also has no 1667 effect. 1668 1669* Fixed a bug involving the column keys present with RETURNING. Given 1670 a statement as follows:: 1671 1672 result = conn.execute(table.insert().values(x=5).returning(table.c.a, table.c.b)) 1673 1674 Previously, the keys in each row of the result would be ``ret_0`` and ``ret_1``, 1675 which are identifiers internal to the cx_Oracle RETURNING implementation. 1676 The keys will now be ``a`` and ``b`` as is expected for other dialects. 1677 1678* cx_Oracle's LOB datatype represents return values as a ``cx_Oracle.LOB`` 1679 object, which is a cursor-associated proxy that returns the ultimate data 1680 value via a ``.read()`` method. Historically, if more rows were read before 1681 these LOB objects were consumed (specifically, more rows than the value of 1682 cursor.arraysize which causes a new batch of rows to be read), these LOB 1683 objects would raise the error "LOB variable no longer valid after subsequent 1684 fetch". SQLAlchemy worked around this by both automatically calling 1685 ``.read()`` upon these LOBs within its typing system, as well as using a 1686 special ``BufferedColumnResultSet`` which would ensure this data was buffered 1687 in case a call like ``cursor.fetchmany()`` or ``cursor.fetchall()`` were 1688 used. 1689 1690 The dialect now makes use of a cx_Oracle outputtypehandler to handle these 1691 ``.read()`` calls, so that they are always called up front regardless of how 1692 many rows are being fetched, so that this error can no longer occur. As a 1693 result, the use of the ``BufferedColumnResultSet``, as well as some other 1694 internals to the Core ``ResultSet`` that were specific to this use case, 1695 have been removed. The type objects are also simplified as they no longer 1696 need to process a binary column result. 1697 1698 Additionally, cx_Oracle 6.x has removed the conditions under which this error 1699 occurs in any case, so the error is no longer possible. The error 1700 can occur on SQLAlchemy in the case that the seldom (if ever) used 1701 ``auto_convert_lobs=False`` option is in use, in conjunction with the 1702 previous 5.x series of cx_Oracle, and more rows are read before the LOB 1703 objects can be consumed. Upgrading to cx_Oracle 6.x will resolve that issue. 1704 1705.. _change_4003: 1706 1707Oracle Unique, Check constraints now reflected 1708---------------------------------------------- 1709 1710UNIQUE and CHECK constraints now reflect via 1711:meth:`_reflection.Inspector.get_unique_constraints` and 1712:meth:`_reflection.Inspector.get_check_constraints`. A :class:`_schema.Table` object that's 1713reflected will now include :class:`.CheckConstraint` objects as well. 1714See the notes at :ref:`oracle_constraint_reflection` for information 1715on behavioral quirks here, including that most :class:`_schema.Table` objects 1716will still not include any :class:`.UniqueConstraint` objects as these 1717usually represent via :class:`.Index`. 1718 1719.. seealso:: 1720 1721 :ref:`oracle_constraint_reflection` 1722 1723 1724:ticket:`4003` 1725 1726.. _change_3276: 1727 1728Oracle foreign key constraint names are now "name normalized" 1729------------------------------------------------------------- 1730 1731The names of foreign key constraints as delivered to a 1732:class:`_schema.ForeignKeyConstraint` object during table reflection as well as 1733within the :meth:`_reflection.Inspector.get_foreign_keys` method will now be 1734"name normalized", that is, expressed as lower case for a case insensitive 1735name, rather than the raw UPPERCASE format that Oracle uses:: 1736 1737 >>> insp.get_indexes("addresses") 1738 [{'unique': False, 'column_names': [u'user_id'], 1739 'name': u'address_idx', 'dialect_options': {}}] 1740 1741 >>> insp.get_pk_constraint("addresses") 1742 {'name': u'pk_cons', 'constrained_columns': [u'id']} 1743 1744 >>> insp.get_foreign_keys("addresses") 1745 [{'referred_table': u'users', 'referred_columns': [u'id'], 1746 'referred_schema': None, 'name': u'user_id_fk', 1747 'constrained_columns': [u'user_id']}] 1748 1749Previously, the foreign keys result would look like:: 1750 1751 [{'referred_table': u'users', 'referred_columns': [u'id'], 1752 'referred_schema': None, 'name': 'USER_ID_FK', 1753 'constrained_columns': [u'user_id']}] 1754 1755Where the above could create problems particularly with Alembic autogenerate. 1756 1757:ticket:`3276` 1758 1759 1760Dialect Improvements and Changes - SQL Server 1761============================================= 1762 1763.. _change_2626: 1764 1765SQL Server schema names with embedded dots supported 1766---------------------------------------------------- 1767 1768The SQL Server dialect has a behavior such that a schema name with a dot inside 1769of it is assumed to be a "database"."owner" identifier pair, which is 1770necessarily split up into these separate components during table and component 1771reflection operations, as well as when rendering quoting for the schema name so 1772that the two symbols are quoted separately. The schema argument can 1773now be passed using brackets to manually specify where this split 1774occurs, allowing database and/or owner names that themselves contain one 1775or more dots:: 1776 1777 Table( 1778 "some_table", metadata, 1779 Column("q", String(50)), 1780 schema="[MyDataBase.dbo]" 1781 ) 1782 1783The above table will consider the "owner" to be ``MyDataBase.dbo``, which 1784will also be quoted upon render, and the "database" as None. To individually 1785refer to database name and owner, use two pairs of brackets:: 1786 1787 Table( 1788 "some_table", metadata, 1789 Column("q", String(50)), 1790 schema="[MyDataBase.SomeDB].[MyDB.owner]" 1791 ) 1792 1793Additionally, the :class:`.quoted_name` construct is now honored when 1794passed to "schema" by the SQL Server dialect; the given symbol will 1795not be split on the dot if the quote flag is True and will be interpreted 1796as the "owner". 1797 1798.. seealso:: 1799 1800 :ref:`multipart_schema_names` 1801 1802:ticket:`2626` 1803 1804AUTOCOMMIT isolation level support 1805---------------------------------- 1806 1807Both the PyODBC and pymssql dialects now support the "AUTOCOMMIT" isolation 1808level as set by :meth:`_engine.Connection.execution_options` which will establish 1809the correct flags on the DBAPI connection object. 1810