1============================= 2What's New in SQLAlchemy 0.7? 3============================= 4 5.. admonition:: About this Document 6 7 This document describes changes between SQLAlchemy version 0.6, 8 last released May 5, 2012, and SQLAlchemy version 0.7, 9 undergoing maintenance releases as of October, 2012. 10 11 Document date: July 27, 2011 12 13Introduction 14============ 15 16This guide introduces what's new in SQLAlchemy version 0.7, 17and also documents changes which affect users migrating 18their applications from the 0.6 series of SQLAlchemy to 0.7. 19 20To as great a degree as possible, changes are made in such a 21way as to not break compatibility with applications built 22for 0.6. The changes that are necessarily not backwards 23compatible are very few, and all but one, the change to 24mutable attribute defaults, should affect an exceedingly 25small portion of applications - many of the changes regard 26non-public APIs and undocumented hacks some users may have 27been attempting to use. 28 29A second, even smaller class of non-backwards-compatible 30changes is also documented. This class of change regards 31those features and behaviors that have been deprecated at 32least since version 0.5 and have been raising warnings since 33their deprecation. These changes would only affect 34applications that are still using 0.4- or early 0.5-style 35APIs. As the project matures, we have fewer and fewer of 36these kinds of changes with 0.x level releases, which is a 37product of our API having ever fewer features that are less 38than ideal for the use cases they were meant to solve. 39 40An array of existing functionalities have been superseded in 41SQLAlchemy 0.7. There's not much difference between the 42terms "superseded" and "deprecated", except that the former 43has a much weaker suggestion of the old feature would ever 44be removed. In 0.7, features like ``synonym`` and 45``comparable_property``, as well as all the ``Extension`` 46and other event classes, have been superseded. But these 47"superseded" features have been re-implemented such that 48their implementations live mostly outside of core ORM code, 49so their continued "hanging around" doesn't impact 50SQLAlchemy's ability to further streamline and refine its 51internals, and we expect them to remain within the API for 52the foreseeable future. 53 54New Features 55============ 56 57New Event System 58---------------- 59 60SQLAlchemy started early with the ``MapperExtension`` class, 61which provided hooks into the persistence cycle of mappers. 62As SQLAlchemy quickly became more componentized, pushing 63mappers into a more focused configurational role, many more 64"extension", "listener", and "proxy" classes popped up to 65solve various activity-interception use cases in an ad-hoc 66fashion. Part of this was driven by the divergence of 67activities; ``ConnectionProxy`` objects wanted to provide a 68system of rewriting statements and parameters; 69``AttributeExtension`` provided a system of replacing 70incoming values, and ``DDL`` objects had events that could 71be switched off of dialect-sensitive callables. 72 730.7 re-implements virtually all of these plugin points with 74a new, unified approach, which retains all the 75functionalities of the different systems, provides more 76flexibility and less boilerplate, performs better, and 77eliminates the need to learn radically different APIs for 78each event subsystem. The pre-existing classes 79``MapperExtension``, ``SessionExtension``, 80``AttributeExtension``, ``ConnectionProxy``, 81``PoolListener`` as well as the ``DDLElement.execute_at`` 82method are deprecated and now implemented in terms of the 83new system - these APIs remain fully functional and are 84expected to remain in place for the foreseeable future. 85 86The new approach uses named events and user-defined 87callables to associate activities with events. The API's 88look and feel was driven by such diverse sources as JQuery, 89Blinker, and Hibernate, and was also modified further on 90several occasions during conferences with dozens of users on 91Twitter, which appears to have a much higher response rate 92than the mailing list for such questions. 93 94It also features an open-ended system of target 95specification that allows events to be associated with API 96classes, such as for all ``Session`` or ``Engine`` objects, 97with specific instances of API classes, such as for a 98specific ``Pool`` or ``Mapper``, as well as for related 99objects like a user- defined class that's mapped, or 100something as specific as a certain attribute on instances of 101a particular subclass of a mapped parent class. Individual 102listener subsystems can apply wrappers to incoming user- 103defined listener functions which modify how they are called 104- an mapper event can receive either the instance of the 105object being operated upon, or its underlying 106``InstanceState`` object. An attribute event can opt whether 107or not to have the responsibility of returning a new value. 108 109Several systems now build upon the new event API, including 110the new "mutable attributes" API as well as composite 111attributes. The greater emphasis on events has also led to 112the introduction of a handful of new events, including 113attribute expiration and refresh operations, pickle 114loads/dumps operations, completed mapper construction 115operations. 116 117.. seealso:: 118 119 :ref:`event_toplevel` 120 121:ticket:`1902` 122 123Hybrid Attributes, implements/supersedes synonym(), comparable_property() 124------------------------------------------------------------------------- 125 126The "derived attributes" example has now been turned into an 127official extension. The typical use case for ``synonym()`` 128is to provide descriptor access to a mapped column; the use 129case for ``comparable_property()`` is to be able to return a 130``PropComparator`` from any descriptor. In practice, the 131approach of "derived" is easier to use, more extensible, is 132implemented in a few dozen lines of pure Python with almost 133no imports, and doesn't require the ORM core to even be 134aware of it. The feature is now known as the "Hybrid 135Attributes" extension. 136 137``synonym()`` and ``comparable_property()`` are still part 138of the ORM, though their implementations have been moved 139outwards, building on an approach that is similar to that of 140the hybrid extension, so that the core ORM 141mapper/query/property modules aren't really aware of them 142otherwise. 143 144.. seealso:: 145 146 :ref:`hybrids_toplevel` 147 148:ticket:`1903` 149 150Speed Enhancements 151------------------ 152 153As is customary with all major SQLA releases, a wide pass 154through the internals to reduce overhead and callcounts has 155been made which further reduces the work needed in common 156scenarios. Highlights of this release include: 157 158* The flush process will now bundle INSERT statements into 159 batches fed to ``cursor.executemany()``, for rows where 160 the primary key is already present. In particular this 161 usually applies to the "child" table on a joined table 162 inheritance configuration, meaning the number of calls to 163 ``cursor.execute`` for a large bulk insert of joined- 164 table objects can be cut in half, allowing native DBAPI 165 optimizations to take place for those statements passed 166 to ``cursor.executemany()`` (such as re-using a prepared 167 statement). 168 169* The codepath invoked when accessing a many-to-one 170 reference to a related object that's already loaded has 171 been greatly simplified. The identity map is checked 172 directly without the need to generate a new ``Query`` 173 object first, which is expensive in the context of 174 thousands of in-memory many-to-ones being accessed. The 175 usage of constructed-per-call "loader" objects is also no 176 longer used for the majority of lazy attribute loads. 177 178* The rewrite of composites allows a shorter codepath when 179 mapper internals access mapped attributes within a 180 flush. 181 182* New inlined attribute access functions replace the 183 previous usage of "history" when the "save-update" and 184 other cascade operations need to cascade among the full 185 scope of datamembers associated with an attribute. This 186 reduces the overhead of generating a new ``History`` 187 object for this speed-critical operation. 188 189* The internals of the ``ExecutionContext``, the object 190 corresponding to a statement execution, have been 191 inlined and simplified. 192 193* The ``bind_processor()`` and ``result_processor()`` 194 callables generated by types for each statement 195 execution are now cached (carefully, so as to avoid memory 196 leaks for ad-hoc types and dialects) for the lifespan of 197 that type, further reducing per-statement call overhead. 198 199* The collection of "bind processors" for a particular 200 ``Compiled`` instance of a statement is also cached on 201 the ``Compiled`` object, taking further advantage of the 202 "compiled cache" used by the flush process to re-use the 203 same compiled form of INSERT, UPDATE, DELETE statements. 204 205A demonstration of callcount reduction including a sample 206benchmark script is at 207https://techspot.zzzeek.org/2010/12/12/a-tale-of-three- 208profiles/ 209 210Composites Rewritten 211-------------------- 212 213The "composite" feature has been rewritten, like 214``synonym()`` and ``comparable_property()``, to use a 215lighter weight implementation based on descriptors and 216events, rather than building into the ORM internals. This 217allowed the removal of some latency from the mapper/unit of 218work internals, and simplifies the workings of composite. 219The composite attribute now no longer conceals the 220underlying columns it builds upon, which now remain as 221regular attributes. Composites can also act as a proxy for 222``relationship()`` as well as ``Column()`` attributes. 223 224The major backwards-incompatible change of composites is 225that they no longer use the ``mutable=True`` system to 226detect in-place mutations. Please use the `Mutation 227Tracking <https://www.sqlalchemy.org/docs/07/orm/extensions/m 228utable.html>`_ extension to establish in-place change events 229to existing composite usage. 230 231.. seealso:: 232 233 :ref:`mapper_composite` 234 235 :ref:`mutable_toplevel` 236 237:ticket:`2008` :ticket:`2024` 238 239More succinct form of query.join(target, onclause) 240-------------------------------------------------- 241 242The default method of issuing ``query.join()`` to a target 243with an explicit onclause is now: 244 245:: 246 247 query.join(SomeClass, SomeClass.id==ParentClass.some_id) 248 249In 0.6, this usage was considered to be an error, because 250``join()`` accepts multiple arguments corresponding to 251multiple JOIN clauses - the two-argument form needed to be 252in a tuple to disambiguate between single-argument and two- 253argument join targets. In the middle of 0.6 we added 254detection and an error message for this specific calling 255style, since it was so common. In 0.7, since we are 256detecting the exact pattern anyway, and since having to type 257out a tuple for no reason is extremely annoying, the non- 258tuple method now becomes the "normal" way to do it. The 259"multiple JOIN" use case is exceedingly rare compared to the 260single join case, and multiple joins these days are more 261clearly represented by multiple calls to ``join()``. 262 263The tuple form will remain for backwards compatibility. 264 265Note that all the other forms of ``query.join()`` remain 266unchanged: 267 268:: 269 270 query.join(MyClass.somerelation) 271 query.join("somerelation") 272 query.join(MyTarget) 273 # ... etc 274 275`Querying with Joins 276<https://www.sqlalchemy.org/docs/07/orm/tutorial.html 277#querying-with-joins>`_ 278 279:ticket:`1923` 280 281.. _07_migration_mutation_extension: 282 283Mutation event extension, supersedes "mutable=True" 284--------------------------------------------------- 285 286A new extension, :ref:`mutable_toplevel`, provides a 287mechanism by which user-defined datatypes can provide change 288events back to the owning parent or parents. The extension 289includes an approach for scalar database values, such as 290those managed by :class:`.PickleType`, ``postgresql.ARRAY``, or 291other custom ``MutableType`` classes, as well as an approach 292for ORM "composites", those configured using :func:`~.sqlalchemy.orm.composite`. 293 294.. seealso:: 295 296 :ref:`mutable_toplevel` 297 298NULLS FIRST / NULLS LAST operators 299---------------------------------- 300 301These are implemented as an extension to the ``asc()`` and 302``desc()`` operators, called ``nullsfirst()`` and 303``nullslast()``. 304 305.. seealso:: 306 307 :func:`.nullsfirst` 308 309 :func:`.nullslast` 310 311:ticket:`723` 312 313select.distinct(), query.distinct() accepts \*args for PostgreSQL DISTINCT ON 314----------------------------------------------------------------------------- 315 316This was already available by passing a list of expressions 317to the ``distinct`` keyword argument of ``select()``, the 318``distinct()`` method of ``select()`` and ``Query`` now 319accept positional arguments which are rendered as DISTINCT 320ON when a PostgreSQL backend is used. 321 322`distinct() <https://www.sqlalchemy.org/docs/07/core/expressi 323on_api.html#sqlalchemy.sql.expression.Select.distinct>`_ 324 325`Query.distinct() <https://www.sqlalchemy.org/docs/07/orm/que 326ry.html#sqlalchemy.orm.query.Query.distinct>`_ 327 328:ticket:`1069` 329 330``Index()`` can be placed inline inside of ``Table``, ``__table_args__`` 331------------------------------------------------------------------------ 332 333The Index() construct can be created inline with a Table 334definition, using strings as column names, as an alternative 335to the creation of the index outside of the Table. That is: 336 337:: 338 339 Table('mytable', metadata, 340 Column('id',Integer, primary_key=True), 341 Column('name', String(50), nullable=False), 342 Index('idx_name', 'name') 343 ) 344 345The primary rationale here is for the benefit of declarative 346``__table_args__``, particularly when used with mixins: 347 348:: 349 350 class HasNameMixin(object): 351 name = Column('name', String(50), nullable=False) 352 @declared_attr 353 def __table_args__(cls): 354 return (Index('name'), {}) 355 356 class User(HasNameMixin, Base): 357 __tablename__ = 'user' 358 id = Column('id', Integer, primary_key=True) 359 360`Indexes <https://www.sqlalchemy.org/docs/07/core/schema.html 361#indexes>`_ 362 363Window Function SQL Construct 364----------------------------- 365 366A "window function" provides to a statement information 367about the result set as it's produced. This allows criteria 368against various things like "row number", "rank" and so 369forth. They are known to be supported at least by 370PostgreSQL, SQL Server and Oracle, possibly others. 371 372The best introduction to window functions is on PostgreSQL's 373site, where window functions have been supported since 374version 8.4: 375 376https://www.postgresql.org/docs/9.0/static/tutorial- 377window.html 378 379SQLAlchemy provides a simple construct typically invoked via 380an existing function clause, using the ``over()`` method, 381which accepts ``order_by`` and ``partition_by`` keyword 382arguments. Below we replicate the first example in PG's 383tutorial: 384 385:: 386 387 from sqlalchemy.sql import table, column, select, func 388 389 empsalary = table('empsalary', 390 column('depname'), 391 column('empno'), 392 column('salary')) 393 394 s = select([ 395 empsalary, 396 func.avg(empsalary.c.salary). 397 over(partition_by=empsalary.c.depname). 398 label('avg') 399 ]) 400 401 print(s) 402 403SQL: 404 405:: 406 407 SELECT empsalary.depname, empsalary.empno, empsalary.salary, 408 avg(empsalary.salary) OVER (PARTITION BY empsalary.depname) AS avg 409 FROM empsalary 410 411`sqlalchemy.sql.expression.over <https://www.sqlalchemy.org/d 412ocs/07/core/expression_api.html#sqlalchemy.sql.expression.ov 413er>`_ 414 415:ticket:`1844` 416 417execution_options() on Connection accepts "isolation_level" argument 418-------------------------------------------------------------------- 419 420This sets the transaction isolation level for a single 421``Connection``, until that ``Connection`` is closed and its 422underlying DBAPI resource returned to the connection pool, 423upon which the isolation level is reset back to the default. 424The default isolation level is set using the 425``isolation_level`` argument to ``create_engine()``. 426 427Transaction isolation support is currently only supported by 428the PostgreSQL and SQLite backends. 429 430`execution_options() <https://www.sqlalchemy.org/docs/07/core 431/connections.html#sqlalchemy.engine.base.Connection.executio 432n_options>`_ 433 434:ticket:`2001` 435 436``TypeDecorator`` works with integer primary key columns 437-------------------------------------------------------- 438 439A ``TypeDecorator`` which extends the behavior of 440``Integer`` can be used with a primary key column. The 441"autoincrement" feature of ``Column`` will now recognize 442that the underlying database column is still an integer so 443that lastrowid mechanisms continue to function. The 444``TypeDecorator`` itself will have its result value 445processor applied to newly generated primary keys, including 446those received by the DBAPI ``cursor.lastrowid`` accessor. 447 448:ticket:`2005` :ticket:`2006` 449 450``TypeDecorator`` is present in the "sqlalchemy" import space 451------------------------------------------------------------- 452 453No longer need to import this from ``sqlalchemy.types``, 454it's now mirrored in ``sqlalchemy``. 455 456New Dialects 457------------ 458 459Dialects have been added: 460 461* a MySQLdb driver for the Drizzle database: 462 463 464 `Drizzle <https://www.sqlalchemy.org/docs/07/dialects/drizz 465 le.html>`_ 466 467* support for the pymysql DBAPI: 468 469 470 `pymsql Notes 471 <https://www.sqlalchemy.org/docs/07/dialects/mysql.html 472 #module-sqlalchemy.dialects.mysql.pymysql>`_ 473 474* psycopg2 now works with Python 3 475 476 477Behavioral Changes (Backwards Compatible) 478========================================= 479 480C Extensions Build by Default 481----------------------------- 482 483This is as of 0.7b4. The exts will build if cPython 2.xx 484is detected. If the build fails, such as on a windows 485install, that condition is caught and the non-C install 486proceeds. The C exts won't build if Python 3 or PyPy is 487used. 488 489Query.count() simplified, should work virtually always 490------------------------------------------------------ 491 492The very old guesswork which occurred within 493``Query.count()`` has been modernized to use 494``.from_self()``. That is, ``query.count()`` is now 495equivalent to: 496 497:: 498 499 query.from_self(func.count(literal_column('1'))).scalar() 500 501Previously, internal logic attempted to rewrite the columns 502clause of the query itself, and upon detection of a 503"subquery" condition, such as a column-based query that 504might have aggregates in it, or a query with DISTINCT, would 505go through a convoluted process of rewriting the columns 506clause. This logic failed in complex conditions, 507particularly those involving joined table inheritance, and 508was long obsolete by the more comprehensive ``.from_self()`` 509call. 510 511The SQL emitted by ``query.count()`` is now always of the 512form: 513 514:: 515 516 SELECT count(1) AS count_1 FROM ( 517 SELECT user.id AS user_id, user.name AS user_name from user 518 ) AS anon_1 519 520that is, the original query is preserved entirely inside of 521a subquery, with no more guessing as to how count should be 522applied. 523 524:ticket:`2093` 525 526To emit a non-subquery form of count() 527^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 528 529MySQL users have already reported that the MyISAM engine not 530surprisingly falls over completely with this simple change. 531Note that for a simple ``count()`` that optimizes for DBs 532that can't handle simple subqueries, ``func.count()`` should 533be used: 534 535:: 536 537 from sqlalchemy import func 538 session.query(func.count(MyClass.id)).scalar() 539 540or for ``count(*)``: 541 542:: 543 544 from sqlalchemy import func, literal_column 545 session.query(func.count(literal_column('*'))).select_from(MyClass).scalar() 546 547LIMIT/OFFSET clauses now use bind parameters 548-------------------------------------------- 549 550The LIMIT and OFFSET clauses, or their backend equivalents 551(i.e. TOP, ROW NUMBER OVER, etc.), use bind parameters for 552the actual values, for all backends which support it (most 553except for Sybase). This allows better query optimizer 554performance as the textual string for multiple statements 555with differing LIMIT/OFFSET are now identical. 556 557:ticket:`805` 558 559Logging enhancements 560-------------------- 561 562Vinay Sajip has provided a patch to our logging system such 563that the "hex string" embedded in logging statements for 564engines and pools is no longer needed to allow the ``echo`` 565flag to work correctly. A new system that uses filtered 566logging objects allows us to maintain our current behavior 567of ``echo`` being local to individual engines without the 568need for additional identifying strings local to those 569engines. 570 571:ticket:`1926` 572 573Simplified polymorphic_on assignment 574------------------------------------ 575 576The population of the ``polymorphic_on`` column-mapped 577attribute, when used in an inheritance scenario, now occurs 578when the object is constructed, i.e. its ``__init__`` method 579is called, using the init event. The attribute then behaves 580the same as any other column-mapped attribute. Previously, 581special logic would fire off during flush to populate this 582column, which prevented any user code from modifying its 583behavior. The new approach improves upon this in three 584ways: 1. the polymorphic identity is now present on the 585object as soon as its constructed; 2. the polymorphic 586identity can be changed by user code without any difference 587in behavior from any other column-mapped attribute; 3. the 588internals of the mapper during flush are simplified and no 589longer need to make special checks for this column. 590 591:ticket:`1895` 592 593contains_eager() chains across multiple paths (i.e. "all()") 594------------------------------------------------------------ 595 596The ```contains_eager()```` modifier now will chain itself 597for a longer path without the need to emit individual 598````contains_eager()``` calls. Instead of: 599 600:: 601 602 session.query(A).options(contains_eager(A.b), contains_eager(A.b, B.c)) 603 604you can say: 605 606:: 607 608 session.query(A).options(contains_eager(A.b, B.c)) 609 610:ticket:`2032` 611 612Flushing of orphans that have no parent is allowed 613-------------------------------------------------- 614 615We've had a long standing behavior that checks for a so- 616called "orphan" during flush, that is, an object which is 617associated with a ``relationship()`` that specifies "delete- 618orphan" cascade, has been newly added to the session for an 619INSERT, and no parent relationship has been established. 620This check was added years ago to accommodate some test 621cases which tested the orphan behavior for consistency. In 622modern SQLA, this check is no longer needed on the Python 623side. The equivalent behavior of the "orphan check" is 624accomplished by making the foreign key reference to the 625object's parent row NOT NULL, where the database does its 626job of establishing data consistency in the same way SQLA 627allows most other operations to do. If the object's parent 628foreign key is nullable, then the row can be inserted. The 629"orphan" behavior runs when the object was persisted with a 630particular parent, and is then disassociated with that 631parent, leading to a DELETE statement emitted for it. 632 633:ticket:`1912` 634 635Warnings generated when collection members, scalar referents not part of the flush 636---------------------------------------------------------------------------------- 637 638Warnings are now emitted when related objects referenced via 639a loaded ``relationship()`` on a parent object marked as 640"dirty" are not present in the current ``Session``. 641 642The ``save-update`` cascade takes effect when objects are 643added to the ``Session``, or when objects are first 644associated with a parent, so that an object and everything 645related to it are usually all present in the same 646``Session``. However, if ``save-update`` cascade is 647disabled for a particular ``relationship()``, then this 648behavior does not occur, and the flush process does not try 649to correct for it, instead staying consistent to the 650configured cascade behavior. Previously, when such objects 651were detected during the flush, they were silently skipped. 652The new behavior is that a warning is emitted, for the 653purposes of alerting to a situation that more often than not 654is the source of unexpected behavior. 655 656:ticket:`1973` 657 658Setup no longer installs a Nose plugin 659-------------------------------------- 660 661Since we moved to nose we've used a plugin that installs via 662setuptools, so that the ``nosetests`` script would 663automatically run SQLA's plugin code, necessary for our 664tests to have a full environment. In the middle of 0.6, we 665realized that the import pattern here meant that Nose's 666"coverage" plugin would break, since "coverage" requires 667that it be started before any modules to be covered are 668imported; so in the middle of 0.6 we made the situation 669worse by adding a separate ``sqlalchemy-nose`` package to 670the build to overcome this. 671 672In 0.7 we've done away with trying to get ``nosetests`` to 673work automatically, since the SQLAlchemy module would 674produce a large number of nose configuration options for all 675usages of ``nosetests``, not just the SQLAlchemy unit tests 676themselves, and the additional ``sqlalchemy-nose`` install 677was an even worse idea, producing an extra package in Python 678environments. The ``sqla_nose.py`` script in 0.7 is now 679the only way to run the tests with nose. 680 681:ticket:`1949` 682 683Non-``Table``-derived constructs can be mapped 684---------------------------------------------- 685 686A construct that isn't against any ``Table`` at all, like a 687function, can be mapped. 688 689:: 690 691 from sqlalchemy import select, func 692 from sqlalchemy.orm import mapper 693 694 class Subset(object): 695 pass 696 selectable = select(["x", "y", "z"]).select_from(func.some_db_function()).alias() 697 mapper(Subset, selectable, primary_key=[selectable.c.x]) 698 699:ticket:`1876` 700 701aliased() accepts ``FromClause`` elements 702----------------------------------------- 703 704This is a convenience helper such that in the case a plain 705``FromClause``, such as a ``select``, ``Table`` or ``join`` 706is passed to the ``orm.aliased()`` construct, it passes 707through to the ``.alias()`` method of that from construct 708rather than constructing an ORM level ``AliasedClass``. 709 710:ticket:`2018` 711 712Session.connection(), Session.execute() accept 'bind' 713----------------------------------------------------- 714 715This is to allow execute/connection operations to 716participate in the open transaction of an engine explicitly. 717It also allows custom subclasses of ``Session`` that 718implement their own ``get_bind()`` method and arguments to 719use those custom arguments with both the ``execute()`` and 720``connection()`` methods equally. 721 722`Session.connection <https://www.sqlalchemy.org/docs/07/orm/s 723ession.html#sqlalchemy.orm.session.Session.connection>`_ 724`Session.execute <https://www.sqlalchemy.org/docs/07/orm/sess 725ion.html#sqlalchemy.orm.session.Session.execute>`_ 726 727:ticket:`1996` 728 729Standalone bind parameters in columns clause auto-labeled. 730---------------------------------------------------------- 731 732Bind parameters present in the "columns clause" of a select 733are now auto-labeled like other "anonymous" clauses, which 734among other things allows their "type" to be meaningful when 735the row is fetched, as in result row processors. 736 737SQLite - relative file paths are normalized through os.path.abspath() 738--------------------------------------------------------------------- 739 740This so that a script that changes the current directory 741will continue to target the same location as subsequent 742SQLite connections are established. 743 744:ticket:`2036` 745 746MS-SQL - ``String``/``Unicode``/``VARCHAR``/``NVARCHAR``/``VARBINARY`` emit "max" for no length 747----------------------------------------------------------------------------------------------- 748 749On the MS-SQL backend, the String/Unicode types, and their 750counterparts VARCHAR/ NVARCHAR, as well as VARBINARY 751(:ticket:`1833`) emit "max" as the length when no length is 752specified. This makes it more compatible with PostgreSQL's 753VARCHAR type which is similarly unbounded when no length 754specified. SQL Server defaults the length on these types 755to '1' when no length is specified. 756 757Behavioral Changes (Backwards Incompatible) 758=========================================== 759 760Note again, aside from the default mutability change, most 761of these changes are \*extremely minor* and will not affect 762most users. 763 764``PickleType`` and ARRAY mutability turned off by default 765--------------------------------------------------------- 766 767This change refers to the default behavior of the ORM when 768mapping columns that have either the ``PickleType`` or 769``postgresql.ARRAY`` datatypes. The ``mutable`` flag is now 770set to ``False`` by default. If an existing application uses 771these types and depends upon detection of in-place 772mutations, the type object must be constructed with 773``mutable=True`` to restore the 0.6 behavior: 774 775:: 776 777 Table('mytable', metadata, 778 # .... 779 780 Column('pickled_data', PickleType(mutable=True)) 781 ) 782 783The ``mutable=True`` flag is being phased out, in favor of 784the new `Mutation Tracking <https://www.sqlalchemy.org/docs/0 7857/orm/extensions/mutable.html>`_ extension. This extension 786provides a mechanism by which user-defined datatypes can 787provide change events back to the owning parent or parents. 788 789The previous approach of using ``mutable=True`` does not 790provide for change events - instead, the ORM must scan 791through all mutable values present in a session and compare 792them against their original value for changes every time 793``flush()`` is called, which is a very time consuming event. 794This is a holdover from the very early days of SQLAlchemy 795when ``flush()`` was not automatic and the history tracking 796system was not nearly as sophisticated as it is now. 797 798Existing applications which use ``PickleType``, 799``postgresql.ARRAY`` or other ``MutableType`` subclasses, 800and require in-place mutation detection, should migrate to 801the new mutation tracking system, as ``mutable=True`` is 802likely to be deprecated in the future. 803 804:ticket:`1980` 805 806Mutability detection of ``composite()`` requires the Mutation Tracking Extension 807-------------------------------------------------------------------------------- 808 809So-called "composite" mapped attributes, those configured 810using the technique described at `Composite Column Types 811<https://www.sqlalchemy.org/docs/07/orm/mapper_config.html 812#composite-column-types>`_, have been re-implemented such 813that the ORM internals are no longer aware of them (leading 814to shorter and more efficient codepaths in critical 815sections). While composite types are generally intended to 816be treated as immutable value objects, this was never 817enforced. For applications that use composites with 818mutability, the `Mutation Tracking <https://www.sqlalchemy.or 819g/docs/07/orm/extensions/mutable.html>`_ extension offers a 820base class which establishes a mechanism for user-defined 821composite types to send change event messages back to the 822owning parent or parents of each object. 823 824Applications which use composite types and rely upon in- 825place mutation detection of these objects should either 826migrate to the "mutation tracking" extension, or change the 827usage of the composite types such that in-place changes are 828no longer needed (i.e., treat them as immutable value 829objects). 830 831SQLite - the SQLite dialect now uses ``NullPool`` for file-based databases 832-------------------------------------------------------------------------- 833 834This change is **99.999% backwards compatible**, unless you 835are using temporary tables across connection pool 836connections. 837 838A file-based SQLite connection is blazingly fast, and using 839``NullPool`` means that each call to ``Engine.connect`` 840creates a new pysqlite connection. 841 842Previously, the ``SingletonThreadPool`` was used, which 843meant that all connections to a certain engine in a thread 844would be the same connection. It's intended that the new 845approach is more intuitive, particularly when multiple 846connections are used. 847 848``SingletonThreadPool`` is still the default engine when a 849``:memory:`` database is used. 850 851Note that this change **breaks temporary tables used across 852Session commits**, due to the way SQLite handles temp 853tables. See the note at 854https://www.sqlalchemy.org/docs/dialects/sqlite.html#using- 855temporary-tables-with-sqlite if temporary tables beyond the 856scope of one pool connection are desired. 857 858:ticket:`1921` 859 860``Session.merge()`` checks version ids for versioned mappers 861------------------------------------------------------------ 862 863Session.merge() will check the version id of the incoming 864state against that of the database, assuming the mapping 865uses version ids and incoming state has a version_id 866assigned, and raise StaleDataError if they don't match. 867This is the correct behavior, in that if incoming state 868contains a stale version id, it should be assumed the state 869is stale. 870 871If merging data into a versioned state, the version id 872attribute can be left undefined, and no version check will 873take place. 874 875This check was confirmed by examining what Hibernate does - 876both the ``merge()`` and the versioning features were 877originally adapted from Hibernate. 878 879:ticket:`2027` 880 881Tuple label names in Query Improved 882----------------------------------- 883 884This improvement is potentially slightly backwards 885incompatible for an application that relied upon the old 886behavior. 887 888Given two mapped classes ``Foo`` and ``Bar`` each with a 889column ``spam``: 890 891:: 892 893 894 qa = session.query(Foo.spam) 895 qb = session.query(Bar.spam) 896 897 qu = qa.union(qb) 898 899The name given to the single column yielded by ``qu`` will 900be ``spam``. Previously it would be something like 901``foo_spam`` due to the way the ``union`` would combine 902things, which is inconsistent with the name ``spam`` in the 903case of a non-unioned query. 904 905:ticket:`1942` 906 907Mapped column attributes reference the most specific column first 908----------------------------------------------------------------- 909 910This is a change to the behavior involved when a mapped 911column attribute references multiple columns, specifically 912when dealing with an attribute on a joined-table subclass 913that has the same name as that of an attribute on the 914superclass. 915 916Using declarative, the scenario is this: 917 918:: 919 920 class Parent(Base): 921 __tablename__ = 'parent' 922 id = Column(Integer, primary_key=True) 923 924 class Child(Parent): 925 __tablename__ = 'child' 926 id = Column(Integer, ForeignKey('parent.id'), primary_key=True) 927 928Above, the attribute ``Child.id`` refers to both the 929``child.id`` column as well as ``parent.id`` - this due to 930the name of the attribute. If it were named differently on 931the class, such as ``Child.child_id``, it then maps 932distinctly to ``child.id``, with ``Child.id`` being the same 933attribute as ``Parent.id``. 934 935When the ``id`` attribute is made to reference both 936``parent.id`` and ``child.id``, it stores them in an ordered 937list. An expression such as ``Child.id`` then refers to 938just *one* of those columns when rendered. Up until 0.6, 939this column would be ``parent.id``. In 0.7, it is the less 940surprising ``child.id``. 941 942The legacy of this behavior deals with behaviors and 943restrictions of the ORM that don't really apply anymore; all 944that was needed was to reverse the order. 945 946A primary advantage of this approach is that it's now easier 947to construct ``primaryjoin`` expressions that refer to the 948local column: 949 950:: 951 952 class Child(Parent): 953 __tablename__ = 'child' 954 id = Column(Integer, ForeignKey('parent.id'), primary_key=True) 955 some_related = relationship("SomeRelated", 956 primaryjoin="Child.id==SomeRelated.child_id") 957 958 class SomeRelated(Base): 959 __tablename__ = 'some_related' 960 id = Column(Integer, primary_key=True) 961 child_id = Column(Integer, ForeignKey('child.id')) 962 963Prior to 0.7 the ``Child.id`` expression would reference 964``Parent.id``, and it would be necessary to map ``child.id`` 965to a distinct attribute. 966 967It also means that a query like this one changes its 968behavior: 969 970:: 971 972 session.query(Parent).filter(Child.id > 7) 973 974In 0.6, this would render: 975 976:: 977 978 SELECT parent.id AS parent_id 979 FROM parent 980 WHERE parent.id > :id_1 981 982in 0.7, you get: 983 984:: 985 986 SELECT parent.id AS parent_id 987 FROM parent, child 988 WHERE child.id > :id_1 989 990which you'll note is a cartesian product - this behavior is 991now equivalent to that of any other attribute that is local 992to ``Child``. The ``with_polymorphic()`` method, or a 993similar strategy of explicitly joining the underlying 994``Table`` objects, is used to render a query against all 995``Parent`` objects with criteria against ``Child``, in the 996same manner as that of 0.5 and 0.6: 997 998:: 999 1000 print(s.query(Parent).with_polymorphic([Child]).filter(Child.id > 7)) 1001 1002Which on both 0.6 and 0.7 renders: 1003 1004:: 1005 1006 SELECT parent.id AS parent_id, child.id AS child_id 1007 FROM parent LEFT OUTER JOIN child ON parent.id = child.id 1008 WHERE child.id > :id_1 1009 1010Another effect of this change is that a joined-inheritance 1011load across two tables will populate from the child table's 1012value, not that of the parent table. An unusual case is that 1013a query against "Parent" using ``with_polymorphic="*"`` 1014issues a query against "parent", with a LEFT OUTER JOIN to 1015"child". The row is located in "Parent", sees the 1016polymorphic identity corresponds to "Child", but suppose the 1017actual row in "child" has been *deleted*. Due to this 1018corruption, the row comes in with all the columns 1019corresponding to "child" set to NULL - this is now the value 1020that gets populated, not the one in the parent table. 1021 1022:ticket:`1892` 1023 1024Mapping to joins with two or more same-named columns requires explicit declaration 1025---------------------------------------------------------------------------------- 1026 1027This is somewhat related to the previous change in 1028:ticket:`1892`. When mapping to a join, same-named columns 1029must be explicitly linked to mapped attributes, i.e. as 1030described in `Mapping a Class Against Multiple Tables <http: 1031//www.sqlalchemy.org/docs/07/orm/mapper_config.html#mapping- 1032a-class-against-multiple-tables>`_. 1033 1034Given two tables ``foo`` and ``bar``, each with a primary 1035key column ``id``, the following now produces an error: 1036 1037:: 1038 1039 1040 foobar = foo.join(bar, foo.c.id==bar.c.foo_id) 1041 mapper(FooBar, foobar) 1042 1043This because the ``mapper()`` refuses to guess what column 1044is the primary representation of ``FooBar.id`` - is it 1045``foo.c.id`` or is it ``bar.c.id`` ? The attribute must be 1046explicit: 1047 1048:: 1049 1050 1051 foobar = foo.join(bar, foo.c.id==bar.c.foo_id) 1052 mapper(FooBar, foobar, properties={ 1053 'id':[foo.c.id, bar.c.id] 1054 }) 1055 1056:ticket:`1896` 1057 1058Mapper requires that polymorphic_on column be present in the mapped selectable 1059------------------------------------------------------------------------------ 1060 1061This is a warning in 0.6, now an error in 0.7. The column 1062given for ``polymorphic_on`` must be in the mapped 1063selectable. This to prevent some occasional user errors 1064such as: 1065 1066:: 1067 1068 mapper(SomeClass, sometable, polymorphic_on=some_lookup_table.c.id) 1069 1070where above the polymorphic_on needs to be on a 1071``sometable`` column, in this case perhaps 1072``sometable.c.some_lookup_id``. There are also some 1073"polymorphic union" scenarios where similar mistakes 1074sometimes occur. 1075 1076Such a configuration error has always been "wrong", and the 1077above mapping doesn't work as specified - the column would 1078be ignored. It is however potentially backwards 1079incompatible in the rare case that an application has been 1080unknowingly relying upon this behavior. 1081 1082:ticket:`1875` 1083 1084``DDL()`` constructs now escape percent signs 1085--------------------------------------------- 1086 1087Previously, percent signs in ``DDL()`` strings would have to 1088be escaped, i.e. ``%%`` depending on DBAPI, for those DBAPIs 1089that accept ``pyformat`` or ``format`` binds (i.e. psycopg2, 1090mysql-python), which was inconsistent versus ``text()`` 1091constructs which did this automatically. The same escaping 1092now occurs for ``DDL()`` as for ``text()``. 1093 1094:ticket:`1897` 1095 1096``Table.c`` / ``MetaData.tables`` refined a bit, don't allow direct mutation 1097---------------------------------------------------------------------------- 1098 1099Another area where some users were tinkering around in such 1100a way that doesn't actually work as expected, but still left 1101an exceedingly small chance that some application was 1102relying upon this behavior, the construct returned by the 1103``.c`` attribute on ``Table`` and the ``.tables`` attribute 1104on ``MetaData`` is explicitly non-mutable. The "mutable" 1105version of the construct is now private. Adding columns to 1106``.c`` involves using the ``append_column()`` method of 1107``Table``, which ensures things are associated with the 1108parent ``Table`` in the appropriate way; similarly, 1109``MetaData.tables`` has a contract with the ``Table`` 1110objects stored in this dictionary, as well as a little bit 1111of new bookkeeping in that a ``set()`` of all schema names 1112is tracked, which is satisfied only by using the public 1113``Table`` constructor as well as ``Table.tometadata()``. 1114 1115It is of course possible that the ``ColumnCollection`` and 1116``dict`` collections consulted by these attributes could 1117someday implement events on all of their mutational methods 1118such that the appropriate bookkeeping occurred upon direct 1119mutation of the collections, but until someone has the 1120motivation to implement all that along with dozens of new 1121unit tests, narrowing the paths to mutation of these 1122collections will ensure no application is attempting to rely 1123upon usages that are currently not supported. 1124 1125:ticket:`1893` :ticket:`1917` 1126 1127server_default consistently returns None for all inserted_primary_key values 1128---------------------------------------------------------------------------- 1129 1130Established consistency when server_default is present on an 1131Integer PK column. SQLA doesn't pre-fetch these, nor do they 1132come back in cursor.lastrowid (DBAPI). Ensured all backends 1133consistently return None in result.inserted_primary_key for 1134these - some backends may have returned a value previously. 1135Using a server_default on a primary key column is extremely 1136unusual. If a special function or SQL expression is used 1137to generate primary key defaults, this should be established 1138as a Python-side "default" instead of server_default. 1139 1140Regarding reflection for this case, reflection of an int PK 1141col with a server_default sets the "autoincrement" flag to 1142False, except in the case of a PG SERIAL col where we 1143detected a sequence default. 1144 1145:ticket:`2020` :ticket:`2021` 1146 1147The ``sqlalchemy.exceptions`` alias in sys.modules is removed 1148------------------------------------------------------------- 1149 1150For a few years we've added the string 1151``sqlalchemy.exceptions`` to ``sys.modules``, so that a 1152statement like "``import sqlalchemy.exceptions``" would 1153work. The name of the core exceptions module has been 1154``exc`` for a long time now, so the recommended import for 1155this module is: 1156 1157:: 1158 1159 from sqlalchemy import exc 1160 1161The ``exceptions`` name is still present in "``sqlalchemy``" 1162for applications which might have said ``from sqlalchemy 1163import exceptions``, but they should also start using the 1164``exc`` name. 1165 1166Query Timing Recipe Changes 1167--------------------------- 1168 1169While not part of SQLAlchemy itself, it's worth mentioning 1170that the rework of the ``ConnectionProxy`` into the new 1171event system means it is no longer appropriate for the 1172"Timing all Queries" recipe. Please adjust query-timers to 1173use the ``before_cursor_execute()`` and 1174``after_cursor_execute()`` events, demonstrated in the 1175updated recipe UsageRecipes/Profiling. 1176 1177Deprecated API 1178============== 1179 1180Default constructor on types will not accept arguments 1181------------------------------------------------------ 1182 1183Simple types like ``Integer``, ``Date`` etc. in the core 1184types module don't accept arguments. The default 1185constructor that accepts/ignores a catchall ``\*args, 1186\**kwargs`` is restored as of 0.7b4/0.7.0, but emits a 1187deprecation warning. 1188 1189If arguments are being used with a core type like 1190``Integer``, it may be that you intended to use a dialect 1191specific type, such as ``sqlalchemy.dialects.mysql.INTEGER`` 1192which does accept a "display_width" argument for example. 1193 1194compile_mappers() renamed configure_mappers(), simplified configuration internals 1195--------------------------------------------------------------------------------- 1196 1197This system slowly morphed from something small, implemented 1198local to an individual mapper, and poorly named into 1199something that's more of a global "registry-" level function 1200and poorly named, so we've fixed both by moving the 1201implementation out of ``Mapper`` altogether and renaming it 1202to ``configure_mappers()``. It is of course normally not 1203needed for an application to call ``configure_mappers()`` as 1204this process occurs on an as-needed basis, as soon as the 1205mappings are needed via attribute or query access. 1206 1207:ticket:`1966` 1208 1209Core listener/proxy superseded by event listeners 1210------------------------------------------------- 1211 1212``PoolListener``, ``ConnectionProxy``, 1213``DDLElement.execute_at`` are superseded by 1214``event.listen()``, using the ``PoolEvents``, 1215``EngineEvents``, ``DDLEvents`` dispatch targets, 1216respectively. 1217 1218ORM extensions superseded by event listeners 1219-------------------------------------------- 1220 1221``MapperExtension``, ``AttributeExtension``, 1222``SessionExtension`` are superseded by ``event.listen()``, 1223using the ``MapperEvents``/``InstanceEvents``, 1224``AttributeEvents``, ``SessionEvents``, dispatch targets, 1225respectively. 1226 1227Sending a string to 'distinct' in select() for MySQL should be done via prefixes 1228-------------------------------------------------------------------------------- 1229 1230This obscure feature allows this pattern with the MySQL 1231backend: 1232 1233:: 1234 1235 select([mytable], distinct='ALL', prefixes=['HIGH_PRIORITY']) 1236 1237The ``prefixes`` keyword or ``prefix_with()`` method should 1238be used for non-standard or unusual prefixes: 1239 1240:: 1241 1242 select([mytable]).prefix_with('HIGH_PRIORITY', 'ALL') 1243 1244``useexisting`` superseded by ``extend_existing`` and ``keep_existing`` 1245----------------------------------------------------------------------- 1246 1247The ``useexisting`` flag on Table has been superseded by a 1248new pair of flags ``keep_existing`` and ``extend_existing``. 1249``extend_existing`` is equivalent to ``useexisting`` - the 1250existing Table is returned, and additional constructor 1251elements are added. With ``keep_existing``, the existing 1252Table is returned, but additional constructor elements are 1253not added - these elements are only applied when the Table 1254is newly created. 1255 1256Backwards Incompatible API Changes 1257================================== 1258 1259Callables passed to ``bindparam()`` don't get evaluated - affects the Beaker example 1260------------------------------------------------------------------------------------ 1261 1262:ticket:`1950` 1263 1264Note this affects the Beaker caching example, where the 1265workings of the ``_params_from_query()`` function needed a 1266slight adjustment. If you're using code from the Beaker 1267example, this change should be applied. 1268 1269types.type_map is now private, types._type_map 1270---------------------------------------------- 1271 1272We noticed some users tapping into this dictionary inside of 1273``sqlalchemy.types`` as a shortcut to associating Python 1274types with SQL types. We can't guarantee the contents or 1275format of this dictionary, and additionally the business of 1276associating Python types in a one-to-one fashion has some 1277grey areas that should are best decided by individual 1278applications, so we've underscored this attribute. 1279 1280:ticket:`1870` 1281 1282Renamed the ``alias`` keyword arg of standalone ``alias()`` function to ``name`` 1283-------------------------------------------------------------------------------- 1284 1285This so that the keyword argument ``name`` matches that of 1286the ``alias()`` methods on all ``FromClause`` objects as 1287well as the ``name`` argument on ``Query.subquery()``. 1288 1289Only code that uses the standalone ``alias()`` function, and 1290not the method bound functions, and passes the alias name 1291using the explicit keyword name ``alias``, and not 1292positionally, would need modification here. 1293 1294Non-public ``Pool`` methods underscored 1295--------------------------------------- 1296 1297All methods of ``Pool`` and subclasses which are not 1298intended for public use have been renamed with underscores. 1299That they were not named this way previously was a bug. 1300 1301Pooling methods now underscored or removed: 1302 1303``Pool.create_connection()`` -> 1304``Pool._create_connection()`` 1305 1306``Pool.do_get()`` -> ``Pool._do_get()`` 1307 1308``Pool.do_return_conn()`` -> ``Pool._do_return_conn()`` 1309 1310``Pool.do_return_invalid()`` -> removed, was not used 1311 1312``Pool.return_conn()`` -> ``Pool._return_conn()`` 1313 1314``Pool.get()`` -> ``Pool._get()``, public API is 1315``Pool.connect()`` 1316 1317``SingletonThreadPool.cleanup()`` -> ``_cleanup()`` 1318 1319``SingletonThreadPool.dispose_local()`` -> removed, use 1320``conn.invalidate()`` 1321 1322:ticket:`1982` 1323 1324Previously Deprecated, Now Removed 1325================================== 1326 1327Query.join(), Query.outerjoin(), eagerload(), eagerload_all(), others no longer allow lists of attributes as arguments 1328---------------------------------------------------------------------------------------------------------------------- 1329 1330Passing a list of attributes or attribute names to 1331``Query.join``, ``eagerload()``, and similar has been 1332deprecated since 0.5: 1333 1334:: 1335 1336 # old way, deprecated since 0.5 1337 session.query(Houses).join([Houses.rooms, Room.closets]) 1338 session.query(Houses).options(eagerload_all([Houses.rooms, Room.closets])) 1339 1340These methods all accept \*args as of the 0.5 series: 1341 1342:: 1343 1344 # current way, in place since 0.5 1345 session.query(Houses).join(Houses.rooms, Room.closets) 1346 session.query(Houses).options(eagerload_all(Houses.rooms, Room.closets)) 1347 1348``ScopedSession.mapper`` is removed 1349----------------------------------- 1350 1351This feature provided a mapper extension which linked class- 1352based functionality with a particular ``ScopedSession``, in 1353particular providing the behavior such that new object 1354instances would be automatically associated with that 1355session. The feature was overused by tutorials and 1356frameworks which led to great user confusion due to its 1357implicit behavior, and was deprecated in 0.5.5. Techniques 1358for replicating its functionality are at 1359[wiki:UsageRecipes/SessionAwareMapper] 1360 1361