1.. _migration_14_toplevel: 2 3============================= 4What's New in SQLAlchemy 1.4? 5============================= 6 7.. admonition:: About this Document 8 9 This document describes changes between SQLAlchemy version 1.3 10 and SQLAlchemy version 1.4. 11 12 Version 1.4 is taking on a different focus than other SQLAlchemy releases 13 in that it is in many ways attempting to serve as a potential migration 14 point for a more dramatic series of API changes currently planned for 15 release 2.0 of SQLAlchemy. The focus of SQLAlchemy 2.0 is a modernized 16 and slimmed down API that removes lots of usage patterns that have long 17 been discouraged, as well as mainstreams the best ideas in SQLAlchemy as 18 first class API features, with the goal being that there is much less 19 ambiguity in how the API is to be used, as well as that a series of 20 implicit behaviors and rarely-used API flags that complicate the internals 21 and hinder performance will be removed. 22 23 For the current status of SQLAlchemy 2.0, see :ref:`migration_20_toplevel`. 24 25Major API changes and features - General 26========================================= 27 28.. _change_5634: 29 30Python 3.6 is the minimum Python 3 version; Python 2.7 still supported 31---------------------------------------------------------------------- 32 33As Python 3.5 reached EOL in September of 2020, SQLAlchemy 1.4 now places 34version 3.6 as the minimum Python 3 version. Python 2.7 is still supported, 35however the SQLAlchemy 1.4 series will be the last series to support Python 2. 36 37 38.. _change_5159: 39 40ORM Query is internally unified with select, update, delete; 2.0 style execution available 41------------------------------------------------------------------------------------------ 42 43The biggest conceptual change to SQLAlchemy for version 2.0 and essentially 44in 1.4 as well is that the great separation between the :class:`_sql.Select` 45construct in Core and the :class:`_orm.Query` object in the ORM has been removed, 46as well as between the :meth:`_orm.Query.update` and :meth:`_orm.Query.delete` 47methods in how they relate to :class:`_dml.Update` and :class:`_dml.Delete`. 48 49With regards to :class:`_sql.Select` and :class:`_orm.Query`, these two objects 50have for many versions had similar, largely overlapping APIs and even some 51ability to change between one and the other, while remaining very different in 52their usage patterns and behaviors. The historical background for this was 53that the :class:`_orm.Query` object was introduced to overcome shortcomings in 54the :class:`_sql.Select` object which used to be at the core of how ORM objects 55were queried, except that they had to be queried in terms of 56:class:`_schema.Table` metadata only. However :class:`_orm.Query` had only a 57simplistic interface for loading objects, and only over the course of many 58major releases did it eventually gain most of the flexibility of the 59:class:`_sql.Select` object, which then led to the ongoing awkwardness that 60these two objects became highly similar yet still largely incompatible with 61each other. 62 63In version 1.4, all Core and ORM SELECT statements are rendered from a 64:class:`_sql.Select` object directly; when the :class:`_orm.Query` object 65is used, at statement invocation time it copies its state to a :class:`_sql.Select` 66which is then invoked internally using :term:`2.0 style` execution. Going forward, 67the :class:`_orm.Query` object will become legacy only, and applications will 68be encouraged to move to :term:`2.0 style` execution which allows Core constructs 69to be used freely against ORM entities:: 70 71 with Session(engine, future=True) as sess: 72 73 stmt = select(User).where( 74 User.name == 'sandy' 75 ).join(User.addresses).where(Address.email_address.like("%gmail%")) 76 77 result = sess.execute(stmt) 78 79 for user in result.scalars(): 80 print(user) 81 82Things to note about the above example: 83 84* The :class:`_orm.Session` and :class:`_orm.sessionmaker` objects now feature 85 full context manager (i.e. the ``with:`` statement) capability; 86 see the revised documentation at :ref:`session_getting` for an example. 87 88* Within the 1.4 series, all :term:`2.0 style` ORM invocation uses a 89 :class:`_orm.Session` that includes the :paramref:`_orm.Session.future` 90 flag set to ``True``; this flag indicates the :class:`_orm.Session` should 91 have 2.0-style behaviors, which include that ORM queries can be invoked 92 from :class:`_orm.Session.execute` as well as some changes in transactional 93 features. In version 2.0 this flag will always be ``True``. 94 95* The :func:`_sql.select` construct no longer needs brackets around the 96 columns clause; see :ref:`change_5284` for background on this improvement. 97 98* The :func:`_sql.select` / :class:`_sql.Select` object has a :meth:`_sql.Select.join` 99 method that acts like that of the :class:`_orm.Query` and even accommodates 100 an ORM relationship attribute (without breaking the separation between 101 Core and ORM!) - see :ref:`change_select_join` for background on this. 102 103* Statements that work with ORM entities and are expected to return ORM 104 results are invoked using :meth:`.orm.Session.execute`. See 105 :ref:`session_querying_20` for a primer. 106 107* a :class:`_engine.Result` object is returned, rather than a plain list, which 108 itself is a much more sophisticated version of the previous ``ResultProxy`` 109 object; this object is now used both for Core and ORM results. See 110 :ref:`change_result_14_core`, 111 :ref:`change_4710_core`, and :ref:`change_4710_orm` for information on this. 112 113Throughout SQLAlchemy's documentation, there will be many references to 114:term:`1.x style` and :term:`2.0 style` execution. This is to distinguish 115between the two querying styles and to attempt to forwards-document the new 116calling style going forward. In SQLAlchemy 2.0, while the :class:`_orm.Query` 117object may remain as a legacy construct, it will no longer be featured in 118most documentation. 119 120Similar adjustments have been made to "bulk updates and deletes" such that 121Core :func:`_sql.update` and :func:`_sql.delete` can be used for bulk 122operations. A bulk update like the following:: 123 124 session.query(User).filter(User.name == 'sandy').update({"password": "foobar"}, synchronize_session="fetch") 125 126can now be achieved in :term:`2.0 style` (and indeed the above runs internally 127in this way) as follows:: 128 129 with Session(engine, future=True) as sess: 130 stmt = update(User).where( 131 User.name == 'sandy' 132 ).values(password="foobar").execution_options( 133 synchronize_session="fetch" 134 ) 135 136 sess.execute(stmt) 137 138Note the use of the :meth:`_sql.Executable.execution_options` method to pass 139ORM-related options. The use of "execution options" is now much more prevalent 140within both Core and ORM, and many ORM-related methods from :class:`_orm.Query` 141are now implemented as execution options (see :meth:`_orm.Query.execution_options` 142for some examples). 143 144.. seealso:: 145 146 :ref:`migration_20_toplevel` 147 148:ticket:`5159` 149 150.. _change_4639: 151 152Transparent SQL Compilation Caching added to All DQL, DML Statements in Core, ORM 153---------------------------------------------------------------------------------- 154 155One of the most broadly encompassing changes to ever land in a single 156SQLAlchemy version, a many-month reorganization and refactoring of all querying 157systems from the base of Core all the way through ORM now allows the 158majority of Python computation involved producing SQL strings and related 159statement metadata from a user-constructed statement to be cached in memory, 160such that subsequent invocations of an identical statement construct will use 16135-60% fewer CPU resources. 162 163This caching goes beyond the construction of the SQL string to also include the 164construction of result fetching structures that link the SQL construct to the 165result set, and in the ORM it includes the accommodation of ORM-enabled 166attribute loaders, relationship eager loaders and other options, and object 167construction routines that must be built up each time an ORM query seeks to run 168and construct ORM objects from result sets. 169 170To introduce the general idea of the feature, given code from the 171:ref:`examples_performance` suite as follows, which will invoke 172a very simple query "n" times, for a default value of n=10000. The 173query returns only a single row, as the overhead we are looking to decrease 174is that of **many small queries**. The optimization is not as significant 175for queries that return many rows:: 176 177 session = Session(bind=engine) 178 for id_ in random.sample(ids, n): 179 result = session.query(Customer).filter(Customer.id == id_).one() 180 181This example in the 1.3 release of SQLAlchemy on a Dell XPS13 running Linux 182completes as follows:: 183 184 test_orm_query : (10000 iterations); total time 3.440652 sec 185 186In 1.4, the code above without modification completes:: 187 188 test_orm_query : (10000 iterations); total time 2.367934 sec 189 190This first test indicates that regular ORM queries when using caching can run 191over many iterations in the range of **30% faster**. 192 193A second variant of the feature is the optional use of Python lambdas to defer 194the construction of the query itself. This is a more sophisticated variant of 195the approach used by the "Baked Query" extension, which was introduced in 196version 1.0.0. The "lambda" feature may be used in a style very similar to 197that of baked queries, except that it is available in an ad-hoc way for any SQL 198construct. It additionally includes the ability to scan each invocation of the 199lambda for bound literal values that change on every invocation, as well as 200changes to other constructs, such as querying from a different entity or column 201each time, while still not having to run the actual code each time. 202 203Using this API looks as follows:: 204 205 session = Session(bind=engine) 206 for id_ in random.sample(ids, n): 207 stmt = lambda_stmt(lambda: future_select(Customer)) 208 stmt += lambda s: s.where(Customer.id == id_) 209 session.execute(stmt).scalar_one() 210 211The code above completes:: 212 213 test_orm_query_newstyle_w_lambdas : (10000 iterations); total time 1.247092 sec 214 215This test indicates that using the newer "select()" style of ORM querying, 216in conjunction with a full "baked" style invocation that caches the entire 217construction, can run over many iterations in the range of **60% faster** and 218grants performance about the same as the baked query system which is now superseded 219by the native caching system. 220 221The new system makes use of the existing 222:paramref:`_engine.Connection.execution_options.compiled_cache` execution 223option and also adds a cache to the :class:`_engine.Engine` directly, which is 224configured using the :paramref:`_engine.Engine.query_cache_size` parameter. 225 226A significant portion of API and behavioral changes throughout 1.4 were 227driven in order to support this new feature. 228 229.. seealso:: 230 231 :ref:`sql_caching` 232 233:ticket:`4639` 234:ticket:`5380` 235:ticket:`4645` 236:ticket:`4808` 237:ticket:`5004` 238 239.. _change_5508: 240 241Declarative is now integrated into the ORM with new features 242------------------------------------------------------------- 243 244After ten years or so of popularity, the ``sqlalchemy.ext.declarative`` 245package is now integrated into the ``sqlalchemy.orm`` namespace, with the 246exception of the declarative "extension" classes which remain as Declarative 247extensions. 248 249The new classes added to ``sqlalchemy.orm`` include: 250 251* :class:`_orm.registry` - a new class that supersedes the role of the 252 "declarative base" class, serving as a registry of mapped classes which 253 can be referenced via string name within :func:`_orm.relationship` calls 254 and is agnostic of the style in which any particular class was mapped. 255 256* :func:`_orm.declarative_base` - this is the same declarative base class that 257 has been in use throughout the span of the declarative system, except it now 258 references a :class:`_orm.registry` object internally and is implemented 259 by the :meth:`_orm.registry.generate_base` method which can be invoked 260 from a :class:`_orm.registry` directly. The :func:`_orm.declarative_base` 261 function creates this registry automatically so there is no impact on 262 existing code. The ``sqlalchemy.ext.declarative.declarative_base`` name 263 is still present, emitting a 2.0 deprecation warning when 264 :ref:`2.0 deprecations mode <deprecation_20_mode>` is enabled. 265 266* :func:`_orm.declared_attr` - the same "declared attr" function call now 267 part of ``sqlalchemy.orm``. The ``sqlalchemy.ext.declarative.declared_attr`` 268 name is still present, emitting a 2.0 deprecation warning when 269 :ref:`2.0 deprecations mode <deprecation_20_mode>` is enabled. 270 271* Other names moved into ``sqlalchemy.orm`` include :func:`_orm.has_inherited_table`, 272 :func:`_orm.synonym_for`, :class:`_orm.DeclarativeMeta`, :func:`_orm.as_declarative`. 273 274In addition, The :func:`_declarative.instrument_declarative` function is 275deprecated, superseded by :meth:`_orm.registry.map_declaratively`. The 276:class:`_declarative.ConcreteBase`, :class:`_declarative.AbstractConcreteBase`, 277and :class:`_declarative.DeferredReflection` classes remain as extensions in the 278:ref:`declarative_toplevel` package. 279 280Mapping styles have now been organized such that they all extend from 281the :class:`_orm.registry` object, and fall into these categories: 282 283* :ref:`orm_declarative_mapping` 284 * Using :func:`_orm.declarative_base` Base class w/ metaclass 285 * :ref:`orm_declarative_table` 286 * :ref:`Imperative Table (a.k.a. "hybrid table") <orm_imperative_table_configuration>` 287 * Using :meth:`_orm.registry.mapped` Declarative Decorator 288 * Declarative Table 289 * Imperative Table (Hybrid) 290 * :ref:`orm_declarative_dataclasses` 291* :ref:`Imperative (a.k.a. "classical" mapping) <classical_mapping>` 292 * Using :meth:`_orm.registry.map_imperatively` 293 * :ref:`orm_imperative_dataclasses` 294 295The existing classical mapping function :func:`_orm.mapper` remains, however 296it is deprecated to call upon :func:`_orm.mapper` directly; the new 297:meth:`_orm.registry.map_imperatively` method now routes the request through 298the :meth:`_orm.registry` so that it integrates with other declarative mappings 299unambiguously. 300 301The new approach interoperates with 3rd party class instrumentation systems 302which necessarily must take place on the class before the mapping process 303does, allowing declarative mapping to work via a decorator instead of a 304declarative base so that packages like dataclasses_ and attrs_ can be 305used with declarative mappings, in addition to working with classical 306mappings. 307 308Declarative documentation has now been fully integrated into the ORM mapper 309configuration documentation and includes examples for all styles of mappings 310organized into one place. See the section 311:ref:`orm_mapping_classes_toplevel` for the start of the newly reorganized 312documentation. 313 314.. _dataclasses: https://docs.python.org/3/library/dataclasses.html 315.. _attrs: https://pypi.org/project/attrs/ 316 317.. seealso:: 318 319 :ref:`orm_mapping_classes_toplevel` 320 321 :ref:`change_5027` 322 323:ticket:`5508` 324 325 326.. _change_5027: 327 328Python Dataclasses, attrs Supported w/ Declarative, Imperative Mappings 329----------------------------------------------------------------------- 330 331Along with the new declarative decorator styles introduced in :ref:`change_5508`, 332the :class:`_orm.Mapper` is now explicitly aware of the Python ``dataclasses`` 333module and will recognize attributes that are configured in this way, and 334proceed to map them without skipping them as was the case previously. In the 335case of the ``attrs`` module, ``attrs`` already removes its own attributes 336from the class so was already compatible with SQLAlchemy classical mappings. 337With the addition of the :meth:`_orm.registry.mapped` decorator, both 338attribute systems can now interoperate with Declarative mappings as well. 339 340.. seealso:: 341 342 :ref:`orm_declarative_dataclasses` 343 344 :ref:`orm_imperative_dataclasses` 345 346 347:ticket:`5027` 348 349 350.. _change_3414: 351 352Asynchronous IO Support for Core and ORM 353------------------------------------------ 354 355SQLAlchemy now supports Python ``asyncio``-compatible database drivers using an 356all-new asyncio front-end interface to :class:`_engine.Connection` for Core 357usage as well as :class:`_orm.Session` for ORM use, using the 358:class:`_asyncio.AsyncConnection` and :class:`_asyncio.AsyncSession` objects. 359 360.. note:: The new asyncio feature should be considered **alpha level** for 361 the initial releases of SQLAlchemy 1.4. This is super new stuff that uses 362 some previously unfamiliar programming techniques. 363 364The initial database API supported is the :ref:`dialect-postgresql-asyncpg` 365asyncio driver for PostgreSQL. 366 367The internal features of SQLAlchemy are fully integrated by making use of 368the `greenlet <https://greenlet.readthedocs.io/en/latest/>`_ library in order 369to adapt the flow of execution within SQLAlchemy's internals to propagate 370asyncio ``await`` keywords outwards from the database driver to the end-user 371API, which features ``async`` methods. Using this approach, the asyncpg 372driver is fully operational within SQLAlchemy's own test suite and features 373compatibility with most psycopg2 features. The approach was vetted and 374improved upon by developers of the greenlet project for which SQLAlchemy 375is appreciative. 376 377.. sidebar:: greenlets are good 378 379 Don't confuse the greenlet_ library with event-based IO libraries that build 380 on top of it such as ``gevent`` and ``eventlet``; while the use of these 381 libraries with SQLAlchemy is common, SQLAlchemy's asyncio integration 382 **does not** make use of these event based systems in any way. The asyncio 383 API integrates with the user-provided event loop, typically Python's own 384 asyncio event loop, without the use of additional threads or event systems. 385 The approach involves a single greenlet context switch per ``await`` call, 386 and the extension which makes it possible is less than 20 lines of code. 387 388The user facing ``async`` API itself is focused around IO-oriented methods such 389as :meth:`_asyncio.AsyncEngine.connect` and 390:meth:`_asyncio.AsyncConnection.execute`. The new Core constructs strictly 391support :term:`2.0 style` usage only; which means all statements must be 392invoked given a connection object, in this case 393:class:`_asyncio.AsyncConnection`. 394 395Within the ORM, :term:`2.0 style` query execution is 396supported, using :func:`_sql.select` constructs in conjunction with 397:meth:`_asyncio.AsyncSession.execute`; the legacy :class:`_orm.Query` 398object itself is not supported by the :class:`_asyncio.AsyncSession` class. 399 400ORM features such as lazy loading of related attributes as well as unexpiry of 401expired attributes are by definition disallowed in the traditional asyncio 402programming model, as they indicate IO operations that would run implicitly 403within the scope of a Python ``getattr()`` operation. To overcome this, the 404**traditional** asyncio application should make judicious use of :ref:`eager 405loading <loading_toplevel>` techniques as well as forego the use of features 406such as :ref:`expire on commit <session_committing>` so that such loads are not 407needed. 408 409For the asyncio application developer who **chooses to break** with 410tradition, the new API provides a **strictly optional 411feature** such that applications that wish to make use of such ORM features 412can opt to organize database-related code into functions which can then be 413run within greenlets using the :meth:`_asyncio.AsyncSession.run_sync` 414method. See the ``greenlet_orm.py`` example at :ref:`examples_asyncio` 415for a demonstration. 416 417Support for asynchronous cursors is also provided using new methods 418:meth:`_asyncio.AsyncConnection.stream` and 419:meth:`_asyncio.AsyncSession.stream`, which support a new 420:class:`_asyncio.AsyncResult` object that itself provides awaitable 421versions of common methods like 422:meth:`_asyncio.AsyncResult.all` and 423:meth:`_asyncio.AsyncResult.fetchmany`. Both Core and ORM are integrated 424with the feature which corresponds to the use of "server side cursors" 425in traditional SQLAlchemy. 426 427.. seealso:: 428 429 :ref:`asyncio_toplevel` 430 431 :ref:`examples_asyncio` 432 433 434 435:ticket:`3414` 436 437.. _change_deferred_construction: 438 439 440Many Core and ORM statement objects now perform much of their construction and validation in the compile phase 441-------------------------------------------------------------------------------------------------------------- 442 443A major initiative in the 1.4 series is to approach the model of both Core SQL 444statements as well as the ORM Query to allow for an efficient, cacheable model 445of statement creation and compilation, where the compilation step would be 446cached, based on a cache key generated by the created statement object, which 447itself is newly created for each use. Towards this goal, much of the Python 448computation which occurs within the construction of statements, particularly 449that of the ORM :class:`_query.Query` as well as the :func:`_sql.select` 450construct when used to invoke ORM queries, is being moved to occur within 451the compilation phase of the statement which only occurs after the statement 452has been invoked, and only if the statement's compiled form was not yet 453cached. 454 455From an end-user perspective, this means that some of the error messages which 456can arise based on arguments passed to the object will no longer be raised 457immediately, and instead will occur only when the statement is invoked for 458the first time. These conditions are always structural and not data driven, 459so there is no risk of such a condition being missed due to a cached statement. 460 461Error conditions which fall under this category include: 462 463* when a :class:`_selectable.CompoundSelect` is constructed (e.g. a UNION, EXCEPT, etc.) 464 and the SELECT statements passed do not have the same number of columns, a 465 :class:`.CompileError` is now raised to this effect; previously, an 466 :class:`.ArgumentError` would be raised immediately upon statement 467 construction. 468 469* Various error conditions which may arise when calling upon :meth:`.Query.join` 470 will be evaluated at statement compilation time rather than when the method 471 is first called. 472 473Other things that may change involve the :class:`_orm.Query` object directly: 474 475* Behaviors may be slightly different when calling upon the 476 :attr:`_orm.Query.statement` accessor. The :class:`_sql.Select` object 477 returned is now a direct copy of the same state that was present in the 478 :class:`_orm.Query`, without any ORM-specific compilation being performed 479 (which means it's dramatically faster). However, the :class:`_sql.Select` 480 will not have the same internal state as it had in 1.3, including things like 481 the FROM clauses being explicitly spelled out if they were not explicitly 482 stated in the :class:`_orm.Query`. This means code that relies upon 483 manipulating this :class:`_sql.Select` statement such as calling methods like 484 :meth:`_sql.Select.with_only_columns` may need to accommodate for the FROM 485 clause. 486 487.. seealso:: 488 489 :ref:`change_4639` 490 491.. _change_4656: 492 493Repaired internal importing conventions such that code linters may work correctly 494--------------------------------------------------------------------------------- 495 496SQLAlchemy has for a long time used a parameter-injecting decorator to help resolve 497mutually-dependent module imports, like this:: 498 499 @util.dependency_for("sqlalchemy.sql.dml") 500 def insert(self, dml, *args, **kw): 501 502Where the above function would be rewritten to no longer have the ``dml`` parameter 503on the outside. This would confuse code-linting tools into seeing a missing parameter 504to functions. A new approach has been implemented internally such that the function's 505signature is no longer modified and the module object is procured inside the function 506instead. 507 508 509:ticket:`4656` 510 511:ticket:`4689` 512 513 514.. _change_1390: 515 516Support for SQL Regular Expression operators 517-------------------------------------------- 518 519A long awaited feature to add rudimentary support for database regular 520expression operators, to complement the :meth:`_sql.ColumnOperators.like` and 521:meth:`_sql.ColumnOperators.match` suites of operations. The new features 522include :meth:`_sql.ColumnOperators.regexp_match` implementing a regular 523expression match like function, and :meth:`_sql.ColumnOperators.regexp_replace` 524implementing a regular expression string replace function. 525 526Supported backends include SQLite, PostgreSQL, MySQL / MariaDB, and Oracle. 527The SQLite backend only supports "regexp_match" but not "regexp_replace". 528 529The regular expression syntaxes and flags are **not backend agnostic**. 530A future feature will allow multiple regular expression syntaxes to be 531specified at once to switch between different backends on the fly. 532 533For SQLite, Python's ``re.search()`` function with no additional arguments 534is established as the implementation. 535 536.. seealso:: 537 538 539 :meth:`_sql.ColumnOperators.regexp_match` 540 541 :meth:`_sql.ColumnOperators.regexp_replace` 542 543 :ref:`pysqlite_regexp` - SQLite implementation notes 544 545 546:ticket:`1390` 547 548 549.. _deprecation_20_mode: 550 551SQLAlchemy 2.0 Deprecations Mode 552--------------------------------- 553 554One of the primary goals of the 1.4 release is to provide a "transitional" 555release so that applications may migrate to SQLAlchemy 2.0 gradually. Towards 556this end, a primary feature in release 1.4 is "2.0 deprecations mode", which is 557a series of deprecation warnings that emit against every detectable API pattern 558which will work differently in version 2.0. The warnings all make use of the 559:class:`_exc.RemovedIn20Warning` class. As these warnings affect foundational 560patterns including the :func:`_sql.select` and :class:`_engine.Engine` constructs, even 561simple applications can generate a lot of warnings until appropriate API 562changes are made. The warning mode is therefore turned off by default until 563the developer enables the environment variable ``SQLALCHEMY_WARN_20=1``. 564 565For a full walkthrough of using 2.0 Deprecations mode, see :ref:`migration_20_deprecations_mode`. 566 567.. seealso:: 568 569 :ref:`migration_20_toplevel` 570 571 :ref:`migration_20_deprecations_mode` 572 573 574 575API and Behavioral Changes - Core 576================================== 577 578.. _change_4617: 579 580A SELECT statement is no longer implicitly considered to be a FROM clause 581-------------------------------------------------------------------------- 582 583This change is one of the larger conceptual changes in SQLAlchemy in many years, 584however it is hoped that the end user impact is relatively small, as the change 585more closely matches what databases like MySQL and PostgreSQL require in any case. 586 587The most immediate noticeable impact is that a :func:`_expression.select` can no longer 588be embedded inside of another :func:`_expression.select` directly, without explicitly 589turning the inner :func:`_expression.select` into a subquery first. This is historically 590performed by using the :meth:`_expression.SelectBase.alias` method, which remains, however 591is more explicitly suited by using a new method :meth:`_expression.SelectBase.subquery`; 592both methods do the same thing. The object returned is now :class:`.Subquery`, 593which is very similar to the :class:`_expression.Alias` object and shares a common 594base :class:`.AliasedReturnsRows`. 595 596That is, this will now raise:: 597 598 stmt1 = select(user.c.id, user.c.name) 599 stmt2 = select(addresses, stmt1).select_from(addresses.join(stmt1)) 600 601Raising:: 602 603 sqlalchemy.exc.ArgumentError: Column expression or FROM clause expected, 604 got <...Select object ...>. To create a FROM clause from a <class 605 'sqlalchemy.sql.selectable.Select'> object, use the .subquery() method. 606 607The correct calling form is instead (noting also that :ref:`brackets are no 608longer required for select() <change_5284>`):: 609 610 sq1 = select(user.c.id, user.c.name).subquery() 611 stmt2 = select(addresses, sq1).select_from(addresses.join(sq1)) 612 613Noting above that the :meth:`_expression.SelectBase.subquery` method is essentially 614equivalent to using the :meth:`_expression.SelectBase.alias` method. 615 616 617The rationale for this change is based on the following: 618 619* In order to support the unification of :class:`_sql.Select` with 620 :class:`_orm.Query`, the :class:`_sql.Select` object needs to have 621 :meth:`_sql.Select.join` and :meth:`_sql.Select.outerjoin` methods that 622 actually add JOIN criteria to the existing FROM clause, as is what users have 623 always expected it to do in any case. The previous behavior, having to 624 align with what a :class:`.FromClause` would do, was that it would generate 625 an unnamed subquery and then JOIN to it, which was a completely useless 626 feature that only confused those users unfortunate enough to try this. This 627 change is discussed at :ref:`change_select_join`. 628 629* The behavior of including a SELECT in the FROM clause of another SELECT 630 without first creating an alias or subquery would be that it creates an 631 unnamed subquery. While standard SQL does support this syntax, in practice 632 it is rejected by most databases. For example, both the MySQL and PostgreSQL 633 outright reject the usage of unnamed subqueries:: 634 635 # MySQL / MariaDB: 636 637 MariaDB [(none)]> select * from (select 1); 638 ERROR 1248 (42000): Every derived table must have its own alias 639 640 641 # PostgreSQL: 642 643 test=> select * from (select 1); 644 ERROR: subquery in FROM must have an alias 645 LINE 1: select * from (select 1); 646 ^ 647 HINT: For example, FROM (SELECT ...) [AS] foo. 648 649 A database like SQLite accepts them, however it is still often the case that 650 the names produced from such a subquery are too ambiguous to be useful:: 651 652 sqlite> CREATE TABLE a(id integer); 653 sqlite> CREATE TABLE b(id integer); 654 sqlite> SELECT * FROM a JOIN (SELECT * FROM b) ON a.id=id; 655 Error: ambiguous column name: id 656 sqlite> SELECT * FROM a JOIN (SELECT * FROM b) ON a.id=b.id; 657 Error: no such column: b.id 658 659 # use a name 660 sqlite> SELECT * FROM a JOIN (SELECT * FROM b) AS anon_1 ON a.id=anon_1.id; 661 662 .. 663 664As :class:`_expression.SelectBase` objects are no longer 665:class:`_expression.FromClause` objects, attributes like the ``.c`` attribute 666as well as methods like ``.select()`` is now deprecated, as they imply implicit 667production of a subquery. The ``.join()`` and ``.outerjoin()`` methods are now 668:ref:`repurposed to append JOIN criteria to the existing query <change_select_join>` in a similar 669way as that of :meth:`_orm.Query.join`, which is what users have always 670expected these methods to do in any case. 671 672In place of the ``.c`` attribute, a new attribute :attr:`_expression.SelectBase.selected_columns` 673is added. This attribute resolves to a column collection that is what most 674people hope that ``.c`` does (but does not), which is to reference the columns 675that are in the columns clause of the SELECT statement. A common beginner mistake 676is code such as the following:: 677 678 stmt = select(users) 679 stmt = stmt.where(stmt.c.name == 'foo') 680 681The above code appears intuitive and that it would generate 682"SELECT * FROM users WHERE name='foo'", however veteran SQLAlchemy users will 683recognize that it in fact generates a useless subquery resembling 684"SELECT * FROM (SELECT * FROM users) WHERE name='foo'". 685 686The new :attr:`_expression.SelectBase.selected_columns` attribute however **does** suit 687the use case above, as in a case like the above it links directly to the columns 688present in the ``users.c`` collection:: 689 690 stmt = select(users) 691 stmt = stmt.where(stmt.selected_columns.name == 'foo') 692 693 694:ticket:`4617` 695 696 697.. _change_select_join: 698 699select().join() and outerjoin() add JOIN criteria to the current query, rather than creating a subquery 700------------------------------------------------------------------------------------------------------- 701 702Towards the goal of unifying :class:`_orm.Query` and :class:`_sql.Select`, 703particularly for :term:`2.0 style` use of :class:`_sql.Select`, it was critical 704that there be a working :meth:`_sql.Select.join` method that behaves like the 705:meth:`_orm.Query.join` method, adding additional entries to the FROM clause of 706the existing SELECT and then returning the new :class:`_sql.Select` object for 707further modification, instead of wrapping the object inside of an unnamed 708subquery and returning a JOIN from that subquery, a behavior that has always 709been virtually useless and completely misleading to users. 710 711To allow this to be the case, :ref:`change_4617` was first implemented which 712splits off :class:`_sql.Select` from having to be a :class:`_sql.FromClause`; 713this removed the requirement that :meth:`_sql.Select.join` would need to 714return a :class:`_sql.Join` object rather than a new version of that 715:class:`_sql.Select` object that includes a new JOIN in its FROM clause. 716 717From that point on, as the :meth:`_sql.Select.join` and :meth:`_sql.Select.outerjoin` 718did have an existing behavior, the original plan was that these 719methods would be deprecated, and the new "useful" version of 720the methods would be available on an alternate, "future" :class:`_sql.Select` 721object available as a separate import. 722 723However, after some time working with this particular codebase, it was decided 724that having two different kinds of :class:`_sql.Select` objects floating 725around, each with 95% the same behavior except for some subtle difference 726in how some of the methods behave was going to be more misleading and inconvenient 727than simply making a hard change in how these two methods behave, given 728that the existing behavior of :meth:`_sql.Select.join` and :meth:`_sql.Select.outerjoin` 729is essentially never used and only causes confusion. 730 731So it was decided, given how very useless the current behavior is, and how 732extremely useful and important and useful the new behavior would be, to make a 733**hard behavioral change** in this one area, rather than waiting another year 734and having a more awkward API in the interim. SQLAlchemy developers do not 735take it lightly to make a completely breaking change like this, however this is 736a very special case and it is extremely unlikely that the previous 737implementation of these methods was being used; as noted in 738:ref:`change_4617`, major databases such as MySQL and PostgreSQL don't allow 739for unnamed subqueries in any case and from a syntactical point of view it's 740nearly impossible for a JOIN from an unnamed subquery to be useful since it's 741very difficult to refer to the columns within it unambiguously. 742 743With the new implementation, :meth:`_sql.Select.join` and 744:meth:`_sql.Select.outerjoin` now behave very similarly to that of 745:meth:`_orm.Query.join`, adding JOIN criteria to the existing statement by 746matching to the left entity:: 747 748 stmt = select(user_table).join(addresses_table, user_table.c.id == addresses_table.c.user_id) 749 750producing:: 751 752 SELECT user.id, user.name FROM user JOIN address ON user.id=address.user_id 753 754As is the case for :class:`_sql.Join`, the ON clause is automatically determined 755if feasible:: 756 757 stmt = select(user_table).join(addresses_table) 758 759When ORM entities are used in the statement, this is essentially how ORM 760queries are built up using :term:`2.0 style` invocation. ORM entities will 761assign a "plugin" to the statement internally such that ORM-related compilation 762rules will take place when the statement is compiled into a SQL string. More 763directly, the :meth:`_sql.Select.join` method can accommodate ORM 764relationships, without breaking the hard separation between Core and ORM 765internals:: 766 767 stmt = select(User).join(User.addresses) 768 769Another new method :meth:`_sql.Select.join_from` is also added, which 770allows easier specification of the left and right side of a join at once:: 771 772 stmt = select(Address.email_address, User.name).join_from(User, Address) 773 774producing:: 775 776 SELECT address.email_address, user.name FROM user JOIN address ON user.id == address.user_id 777 778 779.. _change_5526: 780 781The URL object is now immutable 782------------------------------- 783 784The :class:`_engine.URL` object has been formalized such that it now presents 785itself as a ``namedtuple`` with a fixed number of fields that are immutable. In 786addition, the dictionary represented by the :attr:`_engine.URL.query` attribute 787is also an immutable mapping. Mutation of the :class:`_engine.URL` object was 788not a formally supported or documented use case which led to some open-ended 789use cases that made it very difficult to intercept incorrect usages, most 790commonly mutation of the :attr:`_engine.URL.query` dictionary to include non-string elements. 791It also led to all the common problems of allowing mutability in a fundamental 792data object, namely unwanted mutations elsewhere leaking into code that didn't 793expect the URL to change. Finally, the namedtuple design is inspired by that 794of Python's ``urllib.parse.urlparse()`` which returns the parsed object as a 795named tuple. 796 797The decision to change the API outright is based on a calculus weighing the 798infeasibility of a deprecation path (which would involve changing the 799:attr:`_engine.URL.query` dictionary to be a special dictionary that emits deprecation 800warnings when any kind of standard library mutation methods are invoked, in 801addition that when the dictionary would hold any kind of list of elements, the 802list would also have to emit deprecation warnings on mutation) against the 803unlikely use case of projects already mutating :class:`_engine.URL` objects in 804the first place, as well as that small changes such as that of :ticket:`5341` 805were creating backwards-incompatibility in any case. The primary case for 806mutation of a 807:class:`_engine.URL` object is that of parsing plugin arguments within the 808:class:`_engine.CreateEnginePlugin` extension point, itself a fairly recent 809addition that based on Github code search is in use by two repositories, 810neither of which are actually mutating the URL object. 811 812The :class:`_engine.URL` object now provides a rich interface inspecting 813and generating new :class:`_engine.URL` objects. The 814existing mechanism to create a :class:`_engine.URL` object, the 815:func:`_engine.make_url` function, remains unchanged:: 816 817 >>> from sqlalchemy.engine import make_url 818 >>> url = make_url("postgresql+psycopg2://user:pass@host/dbname") 819 820For programmatic construction, code that may have been using the 821:class:`_engine.URL` constructor or ``__init__`` method directly will 822receive a deprecation warning if arguments are passed as keyword arguments 823and not an exact 7-tuple. The keyword-style constructor is now available 824via the :meth:`_engine.URL.create` method:: 825 826 >>> from sqlalchemy.engine import URL 827 >>> url = URL.create("postgresql", "user", "pass", host="host", database="dbname") 828 >>> str(url) 829 'postgresql://user:pass@host/dbname' 830 831 832Fields can be altered typically using the :meth:`_engine.URL.set` method, which 833returns a new :class:`_engine.URL` object with changes applied:: 834 835 >>> mysql_url = url.set(drivername="mysql+pymysql") 836 >>> str(mysql_url) 837 'mysql+pymysql://user:pass@host/dbname' 838 839To alter the contents of the :attr:`_engine.URL.query` dictionary, methods 840such as :meth:`_engine.URL.update_query_dict` may be used:: 841 842 >>> url.update_query_dict({"sslcert": '/path/to/crt'}) 843 postgresql://user:***@host/dbname?sslcert=%2Fpath%2Fto%2Fcrt 844 845To upgrade code that is mutating these fields directly, a **backwards and 846forwards compatible approach** is to use a duck-typing, as in the following 847style:: 848 849 def set_url_drivername(some_url, some_drivername): 850 # check for 1.4 851 if hasattr(some_url, "set"): 852 return some_url.set(drivername=some_drivername) 853 else: 854 # SQLAlchemy 1.3 or earlier, mutate in place 855 some_url.drivername = some_drivername 856 return some_url 857 858 def set_ssl_cert(some_url, ssl_cert): 859 # check for 1.4 860 if hasattr(some_url, "update_query_dict"): 861 return some_url.update_query_dict({"sslcert": ssl_cert}) 862 else: 863 # SQLAlchemy 1.3 or earlier, mutate in place 864 some_url.query["sslcert"] = ssl_cert 865 return some_url 866 867The query string retains its existing format as a dictionary of strings 868to strings, using sequences of strings to represent multiple parameters. 869For example:: 870 871 >>> from sqlalchemy.engine import make_url 872 >>> url = make_url("postgresql://user:pass@host/dbname?alt_host=host1&alt_host=host2&sslcert=%2Fpath%2Fto%2Fcrt") 873 >>> url.query 874 immutabledict({'alt_host': ('host1', 'host2'), 'sslcert': '/path/to/crt'}) 875 876To work with the contents of the :attr:`_engine.URL.query` attribute such that all values are 877normalized into sequences, use the :attr:`_engine.URL.normalized_query` attribute:: 878 879 >>> url.normalized_query 880 immutabledict({'alt_host': ('host1', 'host2'), 'sslcert': ('/path/to/crt',)}) 881 882The query string can be appended to via methods such as :meth:`_engine.URL.update_query_dict`, 883:meth:`_engine.URL.update_query_pairs`, :meth:`_engine.URL.update_query_string`:: 884 885 >>> url.update_query_dict({"alt_host": "host3"}, append=True) 886 postgresql://user:***@host/dbname?alt_host=host1&alt_host=host2&alt_host=host3&sslcert=%2Fpath%2Fto%2Fcrt 887 888.. seealso:: 889 890 :class:`_engine.URL` 891 892 893Changes to CreateEnginePlugin 894^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 895 896The :class:`_engine.CreateEnginePlugin` is also impacted by this change, 897as the documentation for custom plugins indicated that the ``dict.pop()`` 898method should be used to remove consumed arguments from the URL object. This 899should now be achieved using the :meth:`_engine.CreateEnginePlugin.update_url` 900method. A backwards compatible approach would look like:: 901 902 from sqlalchemy.engine import CreateEnginePlugin 903 904 class MyPlugin(CreateEnginePlugin): 905 def __init__(self, url, kwargs): 906 # check for 1.4 style 907 if hasattr(CreateEnginePlugin, "update_url"): 908 self.my_argument_one = url.query['my_argument_one'] 909 self.my_argument_two = url.query['my_argument_two'] 910 else: 911 # legacy 912 self.my_argument_one = url.query.pop('my_argument_one') 913 self.my_argument_two = url.query.pop('my_argument_two') 914 915 self.my_argument_three = kwargs.pop('my_argument_three', None) 916 917 def update_url(self, url): 918 # this method runs in 1.4 only and should be used to consume 919 # plugin-specific arguments 920 return url.difference_update_query( 921 ["my_argument_one", "my_argument_two"] 922 ) 923 924See the docstring at :class:`_engine.CreateEnginePlugin` for complete details 925on how this class is used. 926 927:ticket:`5526` 928 929 930.. _change_5284: 931 932select(), case() now accept positional expressions 933--------------------------------------------------- 934 935As it may be seen elsewhere in this document, the :func:`_sql.select` construct will 936now accept "columns clause" arguments positionally, rather than requiring they 937be passed as a list:: 938 939 # new way, supports 2.0 940 stmt = select(table.c.col1, table.c.col2, ...) 941 942When sending the arguments positionally, no other keyword arguments are permitted. 943In SQLAlchemy 2.0, the above calling style will be the only calling style 944supported. 945 946For the duration of 1.4, the previous calling style will still continue 947to function, which passes the list of columns or other expressions as a list:: 948 949 # old way, still works in 1.4 950 stmt = select([table.c.col1, table.c.col2, ...]) 951 952The above legacy calling style also accepts the old keyword arguments that have 953since been removed from most narrative documentation. The existence of these 954keyword arguments is why the columns clause was passed as a list in the first place:: 955 956 # very much the old way, but still works in 1.4 957 stmt = select([table.c.col1, table.c.col2, ...], whereclause=table.c.col1 == 5) 958 959The detection between the two styles is based on whether or not the first 960positional argument is a list. There are unfortunately still likely some 961usages that look like the following, where the keyword for the "whereclause" 962is omitted:: 963 964 # very much the old way, but still works in 1.4 965 stmt = select([table.c.col1, table.c.col2, ...], table.c.col1 == 5) 966 967As part of this change, the :class:`.Select` construct also gains the 2.0-style 968"future" API which includes an updated :meth:`.Select.join` method as well 969as methods like :meth:`.Select.filter_by` and :meth:`.Select.join_from`. 970 971In a related change, the :func:`_sql.case` construct has also been modified 972to accept its list of WHEN clauses positionally, with a similar deprecation 973track for the old calling style:: 974 975 stmt = select(users_table).where( 976 case( 977 (users_table.c.name == 'wendy', 'W'), 978 (users_table.c.name == 'jack', 'J'), 979 else_='E' 980 ) 981 ) 982 983The convention for SQLAlchemy constructs accepting ``*args`` vs. a list of 984values, as is the latter case for a construct like 985:meth:`_sql.ColumnOperators.in_`, is that **positional arguments are used for 986structural specification, lists are used for data specification**. 987 988 989.. seealso:: 990 991 :ref:`migration_20_5284` 992 993 :ref:`error_c9ae` 994 995 996:ticket:`5284` 997 998.. _change_4645: 999 1000All IN expressions render parameters for each value in the list on the fly (e.g. expanding parameters) 1001------------------------------------------------------------------------------------------------------ 1002 1003The "expanding IN" feature, first introduced in :ref:`change_3953`, has matured 1004enough such that it is clearly superior to the previous method of rendering IN 1005expressions. As the approach was improved to handle empty lists of values, it 1006is now the only means that Core / ORM will use to render lists of IN 1007parameters. 1008 1009The previous approach which has been present in SQLAlchemy since its first 1010release was that when a list of values were passed to the 1011:meth:`.ColumnOperators.in_` method, the list would be expanded into a series 1012of individual :class:`.BindParameter` objects at statement construction time. 1013This suffered from the limitation that it was not possible to vary the 1014parameter list at statement execution time based on the parameter dictionary, 1015which meant that string SQL statements could not be cached independently of 1016their parameters, nor could the parameter dictionary be fully used for 1017statements that included IN expressions generally. 1018 1019In order to service the "baked query" feature described at 1020:ref:`baked_toplevel`, a cacheable version of IN was needed, which is what 1021brought about the "expanding IN" feature. In contrast to the existing behavior 1022whereby the parameter list is expanded at statement construction time into 1023individual :class:`.BindParameter` objects, the feature instead uses a single 1024:class:`.BindParameter` that stores the list of values at once; when the 1025statement is executed by the :class:`_engine.Engine`, it is "expanded" on the fly into 1026individual bound parameter positions based on the parameters passed to the call 1027to :meth:`_engine.Connection.execute`, and the existing SQL string which may have been 1028retrieved from a previous execution is modified using a regular expression to 1029suit the current parameter set. This allows for the same :class:`.Compiled` 1030object, which stores the rendered string statement, to be invoked multiple 1031times against different parameter sets that modify the list contents passed to 1032IN expressions, while still maintaining the behavior of individual scalar 1033parameters being passed to the DBAPI. While some DBAPIs do support this 1034functionality directly, it is not generally available; the "expanding IN" 1035feature now supports the behavior consistently for all backends. 1036 1037As a major focus of 1.4 is to allow for true statement caching in Core and ORM 1038without the awkwardness of the "baked" system, and since the "expanding IN" 1039feature represents a simpler approach to building expressions in any case, 1040it's now invoked automatically whenever a list of values is passed to 1041an IN expression:: 1042 1043 stmt = select(A.id, A.data).where(A.id.in_([1, 2, 3])) 1044 1045The pre-execution string representation is:: 1046 1047 >>> print(stmt) 1048 SELECT a.id, a.data 1049 FROM a 1050 WHERE a.id IN ([POSTCOMPILE_id_1]) 1051 1052To render the values directly, use ``literal_binds`` as was the case previously:: 1053 1054 >>> print(stmt.compile(compile_kwargs={"literal_binds": True})) 1055 SELECT a.id, a.data 1056 FROM a 1057 WHERE a.id IN (1, 2, 3) 1058 1059A new flag, "render_postcompile", is added as a helper to allow the current 1060bound value to be rendered as it would be passed to the database:: 1061 1062 >>> print(stmt.compile(compile_kwargs={"render_postcompile": True})) 1063 SELECT a.id, a.data 1064 FROM a 1065 WHERE a.id IN (:id_1_1, :id_1_2, :id_1_3) 1066 1067Engine logging output shows the ultimate rendered statement as well:: 1068 1069 INFO sqlalchemy.engine.base.Engine SELECT a.id, a.data 1070 FROM a 1071 WHERE a.id IN (?, ?, ?) 1072 INFO sqlalchemy.engine.base.Engine (1, 2, 3) 1073 1074As part of this change, the behavior of "empty IN" expressions, where the list 1075parameter is empty, is now standardized on use of the IN operator against a 1076so-called "empty set". As there is no standard SQL syntax for empty sets, a 1077SELECT that returns no rows is used, tailored in specific ways for each backend 1078so that the database treats it as an empty set; this feature was first 1079introduced in version 1.3 and is described at :ref:`change_4271`. The 1080:paramref:`_sa.create_engine.empty_in_strategy` parameter, introduced in version 10811.2 as a means for migrating for how this case was treated for the previous IN 1082system, is now deprecated and this flag no longer has an effect; as described 1083in :ref:`change_3907`, this flag allowed a dialect to switch between the 1084original system of comparing a column against itself, which turned out to be a 1085huge performance issue, and a newer system of comparing "1 != 1" in 1086order to produce a "false" expression. The 1.3 introduced behavior which 1087now takes place in all cases is more correct than both approaches as the IN 1088operator is still used, and does not have the performance issue of the original 1089system. 1090 1091In addition, the "expanding" parameter system has been generalized so that it 1092also services other dialect-specific use cases where a parameter cannot be 1093accommodated by the DBAPI or backing database; see :ref:`change_4808` for 1094details. 1095 1096.. seealso:: 1097 1098 :ref:`change_4808` 1099 1100 :ref:`change_4271` 1101 1102 :class:`.BindParameter` 1103 1104:ticket:`4645` 1105 1106.. _change_4737: 1107 1108 1109Built-in FROM linting will warn for any potential cartesian products in a SELECT statement 1110------------------------------------------------------------------------------------------ 1111 1112As the Core expression language as well as the ORM are built on an "implicit 1113FROMs" model where a particular FROM clause is automatically added if any part 1114of the query refers to it, a common issue is the case where a SELECT statement, 1115either a top level statement or an embedded subquery, contains FROM elements 1116that are not joined to the rest of the FROM elements in the query, causing 1117what's referred to as a "cartesian product" in the result set, i.e. every 1118possible combination of rows from each FROM element not otherwise joined. In 1119relational databases, this is nearly always an undesirable outcome as it 1120produces an enormous result set full of duplicated, uncorrelated data. 1121 1122SQLAlchemy, for all of its great features, is particularly prone to this sort 1123of issue happening as a SELECT statement will have elements added to its FROM 1124clause automatically from any table seen in the other clauses. A typical 1125scenario looks like the following, where two tables are JOINed together, 1126however an additional entry in the WHERE clause that perhaps inadvertently does 1127not line up with these two tables will create an additional FROM entry:: 1128 1129 address_alias = aliased(Address) 1130 1131 q = session.query(User).\ 1132 join(address_alias, User.addresses).\ 1133 filter(Address.email_address == 'foo') 1134 1135The above query selects from a JOIN of ``User`` and ``address_alias``, the 1136latter of which is an alias of the ``Address`` entity. However, the 1137``Address`` entity is used within the WHERE clause directly, so the above would 1138result in the SQL:: 1139 1140 SELECT 1141 users.id AS users_id, users.name AS users_name, 1142 users.fullname AS users_fullname, 1143 users.nickname AS users_nickname 1144 FROM addresses, users JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id 1145 WHERE addresses.email_address = :email_address_1 1146 1147In the above SQL, we can see what SQLAlchemy developers term "the dreaded 1148comma", as we see "FROM addresses, users JOIN addresses" in the FROM clause 1149which is the classic sign of a cartesian product; where a query is making use 1150of JOIN in order to join FROM clauses together, however because one of them is 1151not joined, it uses a comma. The above query will return a full set of 1152rows that join the "user" and "addresses" table together on the "id / user_id" 1153column, and will then apply all those rows into a cartesian product against 1154every row in the "addresses" table directly. That is, if there are ten user 1155rows and 100 rows in addresses, the above query will return its expected result 1156rows, likely to be 100 as all address rows would be selected, multiplied by 100 1157again, so that the total result size would be 10000 rows. 1158 1159The "table1, table2 JOIN table3" pattern is one that also occurs quite 1160frequently within the SQLAlchemy ORM due to either subtle mis-application of 1161ORM features particularly those related to joined eager loading or joined table 1162inheritance, as well as a result of SQLAlchemy ORM bugs within those same 1163systems. Similar issues apply to SELECT statements that use "implicit joins", 1164where the JOIN keyword is not used and instead each FROM element is linked with 1165another one via the WHERE clause. 1166 1167For some years there has been a recipe on the Wiki that applies a graph 1168algorithm to a :func:`_expression.select` construct at query execution time and inspects 1169the structure of the query for these un-linked FROM clauses, parsing through 1170the WHERE clause and all JOIN clauses to determine how FROM elements are linked 1171together and ensuring that all the FROM elements are connected in a single 1172graph. This recipe has now been adapted to be part of the :class:`.SQLCompiler` 1173itself where it now optionally emits a warning for a statement if this 1174condition is detected. The warning is enabled using the 1175:paramref:`_sa.create_engine.enable_from_linting` flag and is enabled by default. 1176The computational overhead of the linter is very low, and additionally it only 1177occurs during statement compilation which means for a cached SQL statement it 1178only occurs once. 1179 1180Using this feature, our ORM query above will emit a warning:: 1181 1182 >>> q.all() 1183 SAWarning: SELECT statement has a cartesian product between FROM 1184 element(s) "addresses_1", "users" and FROM element "addresses". 1185 Apply join condition(s) between each element to resolve. 1186 1187The linter feature accommodates not just for tables linked together through the 1188JOIN clauses but also through the WHERE clause Above, we can add a WHERE 1189clause to link the new ``Address`` entity with the previous ``address_alias`` 1190entity and that will remove the warning:: 1191 1192 q = session.query(User).\ 1193 join(address_alias, User.addresses).\ 1194 filter(Address.email_address == 'foo').\ 1195 filter(Address.id == address_alias.id) # resolve cartesian products, 1196 # will no longer warn 1197 1198The cartesian product warning considers **any** kind of link between two 1199FROM clauses to be a resolution, even if the end result set is still 1200wasteful, as the linter is intended only to detect the common case of a 1201FROM clause that is completely unexpected. If the FROM clause is referred 1202to explicitly elsewhere and linked to the other FROMs, no warning is emitted:: 1203 1204 q = session.query(User).\ 1205 join(address_alias, User.addresses).\ 1206 filter(Address.email_address == 'foo').\ 1207 filter(Address.id > address_alias.id) # will generate a lot of rows, 1208 # but no warning 1209 1210Full cartesian products are also allowed if they are explicitly stated; if we 1211wanted for example the cartesian product of ``User`` and ``Address``, we can 1212JOIN on :func:`.true` so that every row will match with every other; the 1213following query will return all rows and produce no warnings:: 1214 1215 from sqlalchemy import true 1216 1217 # intentional cartesian product 1218 q = session.query(User).join(Address, true()) # intentional cartesian product 1219 1220The warning is only generated by default when the statement is compiled by the 1221:class:`_engine.Connection` for execution; calling the :meth:`_expression.ClauseElement.compile` 1222method will not emit a warning unless the linting flag is supplied:: 1223 1224 >>> from sqlalchemy.sql import FROM_LINTING 1225 >>> print(q.statement.compile(linting=FROM_LINTING)) 1226 SAWarning: SELECT statement has a cartesian product between FROM element(s) "addresses" and FROM element "users". Apply join condition(s) between each element to resolve. 1227 SELECT users.id, users.name, users.fullname, users.nickname 1228 FROM addresses, users JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id 1229 WHERE addresses.email_address = :email_address_1 1230 1231:ticket:`4737` 1232 1233 1234.. _change_result_14_core: 1235 1236New Result object 1237----------------- 1238 1239A major goal of SQLAlchemy 2.0 is to unify how "results" are handled between 1240the ORM and Core. Towards this goal, version 1.4 introduces new versions 1241of both the ``ResultProxy`` and ``RowProxy`` objects that have been part 1242of SQLAlchemy since the beginning. 1243 1244The new objects are documented at :class:`_engine.Result` and :class:`_engine.Row`, 1245and are used not only for Core result sets but for :term:`2.0 style` results 1246within the ORM as well. 1247 1248This result object is fully compatible with ``ResultProxy`` and includes many 1249new features, that are now applied to both Core and ORM results equally, 1250including methods such as: 1251 1252:meth:`_engine.Result.one` - returns exactly a single row, or raises: 1253 1254.. sourcecode:: 1255 1256 with engine.connect() as conn: 1257 row = conn.execute(table.select().where(table.c.id == 5)).one() 1258 1259 1260:meth:`_engine.Result.one_or_none` - same, but also returns None for no rows 1261 1262:meth:`_engine.Result.all` - returns all rows 1263 1264:meth:`_engine.Result.partitions` - fetches rows in chunks: 1265 1266.. sourcecode:: 1267 1268 with engine.connect() as conn: 1269 result = conn.execute( 1270 table.select().order_by(table.c.id), 1271 execution_options={"stream_results": True} 1272 ) 1273 for chunk in result.partitions(500): 1274 # process up to 500 records 1275 1276:meth:`_engine.Result.columns` - allows slicing and reorganizing of rows: 1277 1278.. sourcecode:: 1279 1280 with engine.connect() as conn: 1281 # requests x, y, z 1282 result = conn.execute(select(table.c.x, table.c.y, table.c.z)) 1283 1284 # iterate rows as y, x 1285 for y, x in result.columns("y", "x"): 1286 print("Y: %s X: %s" % (y, x)) 1287 1288:meth:`_engine.Result.scalars` - returns lists of scalar objects, from the 1289first column by default but can also be selected: 1290 1291.. sourcecode:: 1292 1293 result = session.execute(select(User).order_by(User.id)) 1294 for user_obj in result.scalars(): 1295 # ... 1296 1297:meth:`_engine.Result.mappings` - instead of named-tuple rows, returns 1298dictionaries: 1299 1300.. sourcecode:: 1301 1302 with engine.connect() as conn: 1303 result = conn.execute(select(table.c.x, table.c.y, table.c.z)) 1304 1305 for map_ in result.mappings(): 1306 print("Y: %(y)s X: %(x)s" % map_) 1307 1308When using Core, the object returned by :meth:`_engine.Connection.execute` is 1309an instance of :class:`.CursorResult`, which continues to feature the same API 1310features as ``ResultProxy`` regarding inserted primary keys, defaults, 1311rowcounts, etc. For ORM, a :class:`_result.Result` subclass will be returned 1312that performs translation of Core rows into ORM rows, and then allows all the 1313same operations to take place. 1314 1315.. seealso:: 1316 1317 :ref:`migration_20_unify_select` - in the 2.0 migration documentation 1318 1319:ticket:`5087` 1320 1321:ticket:`4395` 1322 1323:ticket:`4959` 1324 1325 1326.. _change_4710_core: 1327 1328RowProxy is no longer a "proxy"; is now called Row and behaves like an enhanced named tuple 1329------------------------------------------------------------------------------------------- 1330 1331The :class:`.RowProxy` class, which represents individual database result rows 1332in a Core result set, is now called :class:`.Row` and is no longer a "proxy" 1333object; what this means is that when the :class:`.Row` object is returned, the 1334row is a simple tuple that contains the data in its final form, already having 1335been processed by result-row handling functions associated with datatypes 1336(examples include turning a date string from the database into a ``datetime`` 1337object, a JSON string into a Python ``json.loads()`` result, etc.). 1338 1339The immediate rationale for this is so that the row can act more like a Python 1340named tuple, rather than a mapping, where the values in the tuple are the 1341subject of the ``__contains__`` operator on the tuple, rather than the keys. 1342With :class:`.Row` acting like a named tuple, it is then suitable for use as as 1343replacement for the ORM's :class:`.KeyedTuple` object, leading to an eventual 1344API where both the ORM and Core deliver result sets that behave identically. 1345Unification of major patterns within ORM and Core is a major goal of SQLAlchemy 13462.0, and release 1.4 aims to have most or all of the underlying architectural 1347patterns in place in order to support this process. The note in 1348:ref:`change_4710_orm` describes the ORM's use of the :class:`.Row` class. 1349 1350For release 1.4, the :class:`.Row` class provides an additional subclass 1351:class:`.LegacyRow`, which is used by Core and provides a backwards-compatible 1352version of :class:`.RowProxy` while emitting deprecation warnings for those API 1353features and behaviors that will be moved. ORM :class:`_query.Query` now makes use 1354of :class:`.Row` directly as a replacement for :class:`.KeyedTuple`. 1355 1356The :class:`.LegacyRow` class is a transitional class where the 1357``__contains__`` method is still testing against the keys, not the values, 1358while emitting a deprecation warning when the operation succeeds. 1359Additionally, all the other mapping-like methods on the previous 1360:class:`.RowProxy` are deprecated, including :meth:`.LegacyRow.keys`, 1361:meth:`.LegacyRow.items`, etc. For mapping-like behaviors from a :class:`.Row` 1362object, including support for these methods as well as a key-oriented 1363``__contains__`` operator, the API going forward will be to first access a 1364special attribute :attr:`.Row._mapping`, which will then provide a complete 1365mapping interface to the row, rather than a tuple interface. 1366 1367Rationale: To behave more like a named tuple rather than a mapping 1368^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 1369 1370The difference between a named tuple and a mapping as far as boolean operators 1371can be summarized. Given a "named tuple" in pseudo code as:: 1372 1373 row = (id: 5, name: 'some name') 1374 1375The biggest cross-incompatible difference is the behavior of ``__contains__``:: 1376 1377 "id" in row # True for a mapping, False for a named tuple 1378 "some name" in row # False for a mapping, True for a named tuple 1379 1380In 1.4, when a :class:`.LegacyRow` is returned by a Core result set, the above 1381``"id" in row`` comparison will continue to succeed, however a deprecation 1382warning will be emitted. To use the "in" operator as a mapping, use the 1383:attr:`.Row._mapping` attribute:: 1384 1385 "id" in row._mapping 1386 1387SQLAlchemy 2.0's result object will feature a ``.mappings()`` modifier so that 1388these mappings can be received directly:: 1389 1390 # using sqlalchemy.future package 1391 for row in result.mappings(): 1392 row["id"] 1393 1394Proxying behavior goes away, was also unnecessary in modern usage 1395^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 1396 1397The refactor of :class:`.Row` to behave like a tuple requires that all 1398data values be fully available up front. This is an internal behavior change 1399from that of :class:`.RowProxy`, where result-row processing functions would 1400be invoked at the point of accessing an element of the row, instead of 1401when the row was first fetched. This means for example when retrieving 1402a datetime value from SQLite, the data for the row as present in the 1403:class:`.RowProxy` object would previously have looked like:: 1404 1405 row_proxy = (1, '2019-12-31 19:56:58.272106') 1406 1407and then upon access via ``__getitem__``, the ``datetime.strptime()`` function 1408would be used on the fly to convert the above string date into a ``datetime`` 1409object. With the new architecture, the ``datetime()`` object is present 1410in the tuple when it is returned, the ``datetime.strptime()`` function 1411having been called just once up front:: 1412 1413 row = (1, datetime.datetime(2019, 12, 31, 19, 56, 58, 272106)) 1414 1415The :class:`.RowProxy` and :class:`.Row` objects in SQLAlchemy are where the 1416majority of SQLAlchemy's C extension code takes place. This code has been 1417highly refactored to provide the new behavior in an efficient manner, and 1418overall performance has been improved as the design of :class:`.Row` is now 1419considerably simpler. 1420 1421The rationale behind the previous behavior assumed a usage model where a 1422result row might have dozens or hundreds of columns present, where most of 1423those columns would not be accessed, and for which a majority of those columns 1424would require some result-value processing function. By invoking the 1425processing function only when needed, the goal was that lots of result 1426processing functions would not be necessary, thus increasing performance. 1427 1428There are many reasons why the above assumptions do not hold: 1429 14301. the vast majority of row-processing functions called were to Unicode decode 1431 a bytestring into a Python Unicode string under Python 2. This was right 1432 as Python Unicode was beginning to see use and before Python 3 existed. 1433 Once Python 3 was introduced, within a few years, all Python DBAPIs took 1434 on the proper role of supporting the delivering of Python Unicode objects directly, under 1435 both Python 2 and Python 3, as an option in the former case and as the only 1436 way forward in the latter case. Eventually, in most cases it became 1437 the default for Python 2 as well. SQLAlchemy's Python 2 support still 1438 enables explicit string-to-Unicode conversion for some DBAPIs such as 1439 cx_Oracle, however it is now performed at the DBAPI level rather than 1440 as a standard SQLAlchemy result row processing function. 1441 14422. The above string conversion, when it is used, was made to be extremely 1443 performant via the C extensions, so much so that even in 1.4, SQLAlchemy's 1444 byte-to-Unicode codec hook is plugged into cx_Oracle where it has been 1445 observed to be more performant than cx_Oracle's own hook; this meant that 1446 the overhead for converting all strings in a row was not as significant 1447 as it originally was in any case. 1448 14493. Row processing functions are not used in most other cases; the 1450 exceptions are SQLite's datetime support, JSON support for some backends, 1451 some numeric handlers such as string to ``Decimal``. In the case of 1452 ``Decimal``, Python 3 also standardized on the highly performant ``cdecimal`` 1453 implementation, which is not the case in Python 2 which continues to use 1454 the much less performant pure Python version. 1455 14564. Fetching full rows where only a few columns are needed is not common within 1457 real-world use cases In the early days of SQLAlchemy, database code from other 1458 languages of the form "row = fetch('SELECT * FROM table')" was common; 1459 using SQLAlchemy's expression language however, code observed in the wild 1460 typically makes use of the specific columns needed. 1461 1462.. seealso:: 1463 1464 :ref:`change_4710_orm` 1465 1466:ticket:`4710` 1467 1468.. _change_4753: 1469 1470SELECT objects and derived FROM clauses allow for duplicate columns and column labels 1471------------------------------------------------------------------------------------- 1472 1473This change allows that the :func:`_expression.select` construct now allows for duplicate 1474column labels as well as duplicate column objects themselves, so that result 1475tuples are organized and ordered in the identical way in that the columns were 1476selected. The ORM :class:`_query.Query` already works this way, so this change 1477allows for greater cross-compatibility between the two, which is a key goal of 1478the 2.0 transition:: 1479 1480 >>> from sqlalchemy import column, select 1481 >>> c1, c2, c3, c4 = column('c1'), column('c2'), column('c3'), column('c4') 1482 >>> stmt = select(c1, c2, c3.label('c2'), c2, c4) 1483 >>> print(stmt) 1484 SELECT c1, c2, c3 AS c2, c2, c4 1485 1486To support this change, the :class:`_expression.ColumnCollection` used by 1487:class:`_expression.SelectBase` as well as for derived FROM clauses such as subqueries 1488also support duplicate columns; this includes the new 1489:attr:`_expression.SelectBase.selected_columns` attribute, the deprecated ``SelectBase.c`` 1490attribute, as well as the :attr:`_expression.FromClause.c` attribute seen on constructs 1491such as :class:`.Subquery` and :class:`_expression.Alias`:: 1492 1493 >>> list(stmt.selected_columns) 1494 [ 1495 <sqlalchemy.sql.elements.ColumnClause at 0x7fa540bcca20; c1>, 1496 <sqlalchemy.sql.elements.ColumnClause at 0x7fa540bcc9e8; c2>, 1497 <sqlalchemy.sql.elements.Label object at 0x7fa540b3e2e8>, 1498 <sqlalchemy.sql.elements.ColumnClause at 0x7fa540bcc9e8; c2>, 1499 <sqlalchemy.sql.elements.ColumnClause at 0x7fa540897048; c4> 1500 ] 1501 1502 >>> print(stmt.subquery().select()) 1503 SELECT anon_1.c1, anon_1.c2, anon_1.c2, anon_1.c2, anon_1.c4 1504 FROM (SELECT c1, c2, c3 AS c2, c2, c4) AS anon_1 1505 1506:class:`_expression.ColumnCollection` also allows access by integer index to support 1507when the string "key" is ambiguous:: 1508 1509 >>> stmt.selected_columns[2] 1510 <sqlalchemy.sql.elements.Label object at 0x7fa540b3e2e8> 1511 1512To suit the use of :class:`_expression.ColumnCollection` in objects such as 1513:class:`_schema.Table` and :class:`.PrimaryKeyConstraint`, the old "deduplicating" 1514behavior which is more critical for these objects is preserved in a new class 1515:class:`.DedupeColumnCollection`. 1516 1517The change includes that the familiar warning ``"Column %r on table %r being 1518replaced by %r, which has the same key. Consider use_labels for select() 1519statements."`` is **removed**; the :meth:`_expression.Select.apply_labels` is still 1520available and is still used by the ORM for all SELECT operations, however it 1521does not imply deduplication of column objects, although it does imply 1522deduplication of implicitly generated labels:: 1523 1524 >>> from sqlalchemy import table 1525 >>> user = table('user', column('id'), column('name')) 1526 >>> stmt = select(user.c.id, user.c.name, user.c.id).apply_labels() 1527 >>> print(stmt) 1528 SELECT "user".id AS user_id, "user".name AS user_name, "user".id AS id_1 1529 FROM "user" 1530 1531Finally, the change makes it easier to create UNION and other 1532:class:`_selectable.CompoundSelect` objects, by ensuring that the number and position 1533of columns in a SELECT statement mirrors what was given, in a use case such 1534as:: 1535 1536 >>> s1 = select(user, user.c.id) 1537 >>> s2 = select(c1, c2, c3) 1538 >>> from sqlalchemy import union 1539 >>> u = union(s1, s2) 1540 >>> print(u) 1541 SELECT "user".id, "user".name, "user".id 1542 FROM "user" UNION SELECT c1, c2, c3 1543 1544 1545 1546:ticket:`4753` 1547 1548 1549 1550.. _change_4449: 1551 1552Improved column labeling for simple column expressions using CAST or similar 1553---------------------------------------------------------------------------- 1554 1555A user pointed out that the PostgreSQL database has a convenient behavior when 1556using functions like CAST against a named column, in that the result column name 1557is named the same as the inner expression:: 1558 1559 test=> SELECT CAST(data AS VARCHAR) FROM foo; 1560 1561 data 1562 ------ 1563 5 1564 (1 row) 1565 1566This allows one to apply CAST to table columns while not losing the column 1567name (above using the name ``"data"``) in the result row. Compare to 1568databases such as MySQL/MariaDB, as well as most others, where the column 1569name is taken from the full SQL expression and is not very portable:: 1570 1571 MariaDB [test]> SELECT CAST(data AS CHAR) FROM foo; 1572 +--------------------+ 1573 | CAST(data AS CHAR) | 1574 +--------------------+ 1575 | 5 | 1576 +--------------------+ 1577 1 row in set (0.003 sec) 1578 1579 1580In SQLAlchemy Core expressions, we never deal with a raw generated name like 1581the above, as SQLAlchemy applies auto-labeling to expressions like these, which 1582are up until now always a so-called "anonymous" expression:: 1583 1584 >>> print(select(cast(foo.c.data, String))) 1585 SELECT CAST(foo.data AS VARCHAR) AS anon_1 # old behavior 1586 FROM foo 1587 1588These anonymous expressions were necessary as SQLAlchemy's 1589:class:`_engine.ResultProxy` made heavy use of result column names in order to match 1590up datatypes, such as the :class:`.String` datatype which used to have 1591result-row-processing behavior, to the correct column, so most importantly the 1592names had to be both easy to determine in a database-agnostic manner as well as 1593unique in all cases. In SQLAlchemy 1.0 as part of :ticket:`918`, this 1594reliance on named columns in result rows (specifically the 1595``cursor.description`` element of the PEP-249 cursor) was scaled back to not be 1596necessary for most Core SELECT constructs; in release 1.4, the system overall 1597is becoming more comfortable with SELECT statements that have duplicate column 1598or label names such as in :ref:`change_4753`. So we now emulate PostgreSQL's 1599reasonable behavior for simple modifications to a single column, most 1600prominently with CAST:: 1601 1602 >>> print(select(cast(foo.c.data, String))) 1603 SELECT CAST(foo.data AS VARCHAR) AS data 1604 FROM foo 1605 1606For CAST against expressions that don't have a name, the previous logic is used 1607to generate the usual "anonymous" labels:: 1608 1609 >>> print(select(cast('hi there,' + foo.c.data, String))) 1610 SELECT CAST(:data_1 + foo.data AS VARCHAR) AS anon_1 1611 FROM foo 1612 1613A :func:`.cast` against a :class:`.Label`, despite having to omit the label 1614expression as these don't render inside of a CAST, will nonetheless make use of 1615the given name:: 1616 1617 >>> print(select(cast(('hi there,' + foo.c.data).label('hello_data'), String))) 1618 SELECT CAST(:data_1 + foo.data AS VARCHAR) AS hello_data 1619 FROM foo 1620 1621And of course as was always the case, :class:`.Label` can be applied to the 1622expression on the outside to apply an "AS <name>" label directly:: 1623 1624 >>> print(select(cast(('hi there,' + foo.c.data), String).label('hello_data'))) 1625 SELECT CAST(:data_1 + foo.data AS VARCHAR) AS hello_data 1626 FROM foo 1627 1628 1629:ticket:`4449` 1630 1631.. _change_4808: 1632 1633New "post compile" bound parameters used for LIMIT/OFFSET in Oracle, SQL Server 1634------------------------------------------------------------------------------- 1635 1636A major goal of the 1.4 series is to establish that all Core SQL constructs 1637are completely cacheable, meaning that a particular :class:`.Compiled` 1638structure will produce an identical SQL string regardless of any SQL parameters 1639used with it, which notably includes those used to specify the LIMIT and 1640OFFSET values, typically used for pagination and "top N" style results. 1641 1642While SQLAlchemy has used bound parameters for LIMIT/OFFSET schemes for many 1643years, a few outliers remained where such parameters were not allowed, including 1644a SQL Server "TOP N" statement, such as:: 1645 1646 SELECT TOP 5 mytable.id, mytable.data FROM mytable 1647 1648as well as with Oracle, where the FIRST_ROWS() hint (which SQLAlchemy will 1649use if the ``optimize_limits=True`` parameter is passed to 1650:func:`_sa.create_engine` with an Oracle URL) does not allow them, 1651but also that using bound parameters with ROWNUM comparisons has been reported 1652as producing slower query plans:: 1653 1654 SELECT anon_1.id, anon_1.data FROM ( 1655 SELECT /*+ FIRST_ROWS(5) */ 1656 anon_2.id AS id, 1657 anon_2.data AS data, 1658 ROWNUM AS ora_rn FROM ( 1659 SELECT mytable.id, mytable.data FROM mytable 1660 ) anon_2 1661 WHERE ROWNUM <= :param_1 1662 ) anon_1 WHERE ora_rn > :param_2 1663 1664In order to allow for all statements to be unconditionally cacheable at the 1665compilation level, a new form of bound parameter called a "post compile" 1666parameter has been added, which makes use of the same mechanism as that 1667of "expanding IN parameters". This is a :func:`.bindparam` that behaves 1668identically to any other bound parameter except that parameter value will 1669be rendered literally into the SQL string before sending it to the DBAPI 1670``cursor.execute()`` method. The new parameter is used internally by the 1671SQL Server and Oracle dialects, so that the drivers receive the literal 1672rendered value but the rest of SQLAlchemy can still consider this as a 1673bound parameter. The above two statements when stringified using 1674``str(statement.compile(dialect=<dialect>))`` now look like:: 1675 1676 SELECT TOP [POSTCOMPILE_param_1] mytable.id, mytable.data FROM mytable 1677 1678and:: 1679 1680 SELECT anon_1.id, anon_1.data FROM ( 1681 SELECT /*+ FIRST_ROWS([POSTCOMPILE__ora_frow_1]) */ 1682 anon_2.id AS id, 1683 anon_2.data AS data, 1684 ROWNUM AS ora_rn FROM ( 1685 SELECT mytable.id, mytable.data FROM mytable 1686 ) anon_2 1687 WHERE ROWNUM <= [POSTCOMPILE_param_1] 1688 ) anon_1 WHERE ora_rn > [POSTCOMPILE_param_2] 1689 1690The ``[POSTCOMPILE_<param>]`` format is also what is seen when an 1691"expanding IN" is used. 1692 1693When viewing the SQL logging output, the final form of the statement will 1694be seen:: 1695 1696 SELECT anon_1.id, anon_1.data FROM ( 1697 SELECT /*+ FIRST_ROWS(5) */ 1698 anon_2.id AS id, 1699 anon_2.data AS data, 1700 ROWNUM AS ora_rn FROM ( 1701 SELECT mytable.id AS id, mytable.data AS data FROM mytable 1702 ) anon_2 1703 WHERE ROWNUM <= 8 1704 ) anon_1 WHERE ora_rn > 3 1705 1706 1707The "post compile parameter" feature is exposed as public API through the 1708:paramref:`.bindparam.literal_execute` parameter, however is currently not 1709intended for general use. The literal values are rendered using the 1710:meth:`.TypeEngine.literal_processor` of the underlying datatype, which in 1711SQLAlchemy has **extremely limited** scope, supporting only integers and simple 1712string values. 1713 1714:ticket:`4808` 1715 1716.. _change_4712: 1717 1718Connection-level transactions can now be inactive based on subtransaction 1719------------------------------------------------------------------------- 1720 1721A :class:`_engine.Connection` now includes the behavior where a :class:`.Transaction` 1722can be made inactive due to a rollback on an inner transaction, however the 1723:class:`.Transaction` will not clear until it is itself rolled back. 1724 1725This is essentially a new error condition which will disallow statement 1726executions to proceed on a :class:`_engine.Connection` if an inner "sub" transaction 1727has been rolled back. The behavior works very similarly to that of the 1728ORM :class:`.Session`, where if an outer transaction has been begun, it needs 1729to be rolled back to clear the invalid transaction; this behavior is described 1730in :ref:`faq_session_rollback`. 1731 1732While the :class:`_engine.Connection` has had a less strict behavioral pattern than 1733the :class:`.Session`, this change was made as it helps to identify when 1734a subtransaction has rolled back the DBAPI transaction, however the external 1735code isn't aware of this and attempts to continue proceeding, which in fact 1736runs operations on a new transaction. The "test harness" pattern described 1737at :ref:`session_external_transaction` is the common place for this to occur. 1738 1739The "subtransaction" feature of Core and ORM is itself deprecated and will 1740no longer be present in version 2.0. As a result, this new error condition 1741is itself temporary as it will no longer apply once subtransactions are removed. 1742 1743In order to work with the 2.0 style behavior that does not include 1744subtransactions, use the :paramref:`_sa.create_engine.future` parameter 1745on :func:`_sa.create_engine`. 1746 1747The error message is described in the errors page at :ref:`error_8s2a`. 1748 1749.. _change_5367: 1750 1751Enum and Boolean datatypes no longer default to "create constraint" 1752------------------------------------------------------------------- 1753 1754The :paramref:`.Enum.create_constraint` and 1755:paramref:`.Boolean.create_constraint` parameters now default to False, 1756indicating when a so-called "non-native" version of these two datatypes is 1757created, a CHECK constraint will **not** be generated by default. These 1758CHECK constraints present schema-management maintenance complexities that 1759should be opted in to, rather than being turned on by default. 1760 1761 1762To ensure that a CREATE CONSTRAINT is emitted for these types, set these 1763flags to ``True``:: 1764 1765 class Spam(Base): 1766 __tablename__ = "spam" 1767 id = Column(Integer, primary_key=True) 1768 boolean = Column(Boolean(create_constraint=True)) 1769 enum = Column(Enum("a", "b", "c", create_constraint=True)) 1770 1771 1772:ticket:`5367` 1773 1774New Features - ORM 1775================== 1776 1777.. _change_4826: 1778 1779Raiseload for Columns 1780--------------------- 1781 1782The "raiseload" feature, which raises :class:`.InvalidRequestError` when an 1783unloaded attribute is accessed, is now available for column-oriented attributes 1784using the :paramref:`.orm.defer.raiseload` parameter of :func:`.defer`. This 1785works in the same manner as that of the :func:`.raiseload` option used by 1786relationship loading:: 1787 1788 book = session.query(Book).options(defer(Book.summary, raiseload=True)).first() 1789 1790 # would raise an exception 1791 book.summary 1792 1793To configure column-level raiseload on a mapping, the 1794:paramref:`.deferred.raiseload` parameter of :func:`.deferred` may be used. The 1795:func:`.undefer` option may then be used at query time to eagerly load 1796the attribute:: 1797 1798 class Book(Base): 1799 __tablename__ = 'book' 1800 1801 book_id = Column(Integer, primary_key=True) 1802 title = Column(String(200), nullable=False) 1803 summary = deferred(Column(String(2000)), raiseload=True) 1804 excerpt = deferred(Column(Text), raiseload=True) 1805 1806 book_w_excerpt = session.query(Book).options(undefer(Book.excerpt)).first() 1807 1808It was originally considered that the existing :func:`.raiseload` option that 1809works for :func:`_orm.relationship` attributes be expanded to also support column-oriented 1810attributes. However, this would break the "wildcard" behavior of :func:`.raiseload`, 1811which is documented as allowing one to prevent all relationships from loading:: 1812 1813 session.query(Order).options( 1814 joinedload(Order.items), raiseload('*')) 1815 1816Above, if we had expanded :func:`.raiseload` to accommodate for columns as 1817well, the wildcard would also prevent columns from loading and thus be a 1818backwards incompatible change; additionally, it's not clear if 1819:func:`.raiseload` covered both column expressions and relationships, how one 1820would achieve the effect above of only blocking relationship loads, without 1821new API being added. So to keep things simple, the option for columns 1822remains on :func:`.defer`: 1823 1824 :func:`.raiseload` - query option to raise for relationship loads 1825 1826 :paramref:`.orm.defer.raiseload` - query option to raise for column expression loads 1827 1828 1829As part of this change, the behavior of "deferred" in conjunction with 1830attribute expiration has changed. Previously, when an object would be marked 1831as expired, and then unexpired via the access of one of the expired attributes, 1832attributes which were mapped as "deferred" at the mapper level would also load. 1833This has been changed such that an attribute that is deferred in the mapping 1834will never "unexpire", it only loads when accessed as part of the deferral 1835loader. 1836 1837An attribute that is not mapped as "deferred", however was deferred at query 1838time via the :func:`.defer` option, will be reset when the object or attribute 1839is expired; that is, the deferred option is removed. This is the same behavior 1840as was present previously. 1841 1842 1843.. seealso:: 1844 1845 :ref:`deferred_raiseload` 1846 1847:ticket:`4826` 1848 1849.. _change_5263: 1850 1851ORM Batch inserts with psycopg2 now batch statements with RETURNING in most cases 1852--------------------------------------------------------------------------------- 1853 1854The change in :ref:`change_5401` adds support for "executemany" + "RETURNING" 1855at the same time in Core, which is now enabled for the psycopg2 dialect 1856by default using the psycopg2 ``execute_values()`` extension. The ORM flush 1857process now makes use of this feature such that the retrieval of newly generated 1858primary key values and server defaults can be achieved while not losing the 1859performance benefits of being able to batch INSERT statements together. Additionally, 1860psycopg2's ``execute_values()`` extension itself provides a five-fold performance 1861improvement over psycopg2's default "executemany" implementation, by rewriting 1862an INSERT statement to include many "VALUES" expressions all in one statement 1863rather than invoking the same statement repeatedly, as psycopg2 lacks the ability 1864to PREPARE the statement ahead of time as would normally be expected for this 1865approach to be performant. 1866 1867SQLAlchemy includes a :ref:`performance suite <examples_performance>` within 1868its examples, where we can compare the times generated for the "batch_inserts" 1869runner against 1.3 and 1.4, revealing a 3x-5x speedup for most flavors 1870of batch insert:: 1871 1872 # 1.3 1873 $ python -m examples.performance bulk_inserts --dburl postgresql://scott:tiger@localhost/test 1874 test_flush_no_pk : (100000 iterations); total time 14.051527 sec 1875 test_bulk_save_return_pks : (100000 iterations); total time 15.002470 sec 1876 test_flush_pk_given : (100000 iterations); total time 7.863680 sec 1877 test_bulk_save : (100000 iterations); total time 6.780378 sec 1878 test_bulk_insert_mappings : (100000 iterations); total time 5.363070 sec 1879 test_core_insert : (100000 iterations); total time 5.362647 sec 1880 1881 # 1.4 with enhancement 1882 $ python -m examples.performance bulk_inserts --dburl postgresql://scott:tiger@localhost/test 1883 test_flush_no_pk : (100000 iterations); total time 3.820807 sec 1884 test_bulk_save_return_pks : (100000 iterations); total time 3.176378 sec 1885 test_flush_pk_given : (100000 iterations); total time 4.037789 sec 1886 test_bulk_save : (100000 iterations); total time 2.604446 sec 1887 test_bulk_insert_mappings : (100000 iterations); total time 1.204897 sec 1888 test_core_insert : (100000 iterations); total time 0.958976 sec 1889 1890Note that the ``execute_values()`` extension modifies the INSERT statement in the psycopg2 1891layer, **after** it's been logged by SQLAlchemy. So with SQL logging, one will see the 1892parameter sets batched together, but the joining of multiple "values" will not be visible 1893on the application side:: 1894 1895 2020-06-27 19:08:18,166 INFO sqlalchemy.engine.Engine INSERT INTO a (data) VALUES (%(data)s) RETURNING a.id 1896 2020-06-27 19:08:18,166 INFO sqlalchemy.engine.Engine [generated in 0.00698s] ({'data': 'data 1'}, {'data': 'data 2'}, {'data': 'data 3'}, {'data': 'data 4'}, {'data': 'data 5'}, {'data': 'data 6'}, {'data': 'data 7'}, {'data': 'data 8'} ... displaying 10 of 4999 total bound parameter sets ... {'data': 'data 4998'}, {'data': 'data 4999'}) 1897 2020-06-27 19:08:18,254 INFO sqlalchemy.engine.Engine COMMIT 1898 1899The ultimate INSERT statement can be seen by enabling statement logging on the PostgreSQL side:: 1900 1901 2020-06-27 19:08:18.169 EDT [26960] LOG: statement: INSERT INTO a (data) 1902 VALUES ('data 1'),('data 2'),('data 3'),('data 4'),('data 5'),('data 6'),('data 1903 7'),('data 8'),('data 9'),('data 10'),('data 11'),('data 12'), 1904 ... ('data 999'),('data 1000') RETURNING a.id 1905 1906 2020-06-27 19:08:18.175 EDT 1907 [26960] LOG: statement: INSERT INTO a (data) VALUES ('data 1001'),('data 1908 1002'),('data 1003'),('data 1004'),('data 1005 '),('data 1006'),('data 1909 1007'),('data 1008'),('data 1009'),('data 1010'),('data 1011'), ... 1910 1911The feature batches rows into groups of 1000 by default which can be affected 1912using the ``executemany_values_page_size`` argument documented at 1913:ref:`psycopg2_executemany_mode`. 1914 1915:ticket:`5263` 1916 1917 1918.. _change_orm_update_returning_14: 1919 1920ORM Bulk Update and Delete use RETURNING for "fetch" strategy when available 1921---------------------------------------------------------------------------- 1922 1923An ORM bulk update or delete that uses the "fetch" strategy:: 1924 1925 sess.query(User).filter(User.age > 29).update( 1926 {"age": User.age - 10}, synchronize_session="fetch" 1927 ) 1928 1929Will now use RETURNING if the backend database supports it; this currently 1930includes PostgreSQL and SQL Server (the Oracle dialect does not support RETURNING 1931of multiple rows):: 1932 1933 UPDATE users SET age_int=(users.age_int - %(age_int_1)s) WHERE users.age_int > %(age_int_2)s RETURNING users.id 1934 [generated in 0.00060s] {'age_int_1': 10, 'age_int_2': 29} 1935 Col ('id',) 1936 Row (2,) 1937 Row (4,) 1938 1939For backends that do not support RETURNING of multiple rows, the previous approach 1940of emitting SELECT for the primary keys beforehand is still used:: 1941 1942 SELECT users.id FROM users WHERE users.age_int > %(age_int_1)s 1943 [generated in 0.00043s] {'age_int_1': 29} 1944 Col ('id',) 1945 Row (2,) 1946 Row (4,) 1947 UPDATE users SET age_int=(users.age_int - %(age_int_1)s) WHERE users.age_int > %(age_int_2)s 1948 [generated in 0.00102s] {'age_int_1': 10, 'age_int_2': 29} 1949 1950One of the intricate challenges of this change is to support cases such as the 1951horizontal sharding extension, where a single bulk update or delete may be 1952multiplexed among backends some of which support RETURNING and some don't. The 1953new 1.4 execution architecture supports this case so that the "fetch" strategy 1954can be left intact with a graceful degrade to using a SELECT, rather than having 1955to add a new "returning" strategy that would not be backend-agnostic. 1956 1957As part of this change, the "fetch" strategy is also made much more efficient 1958in that it will no longer expire the objects located which match the rows, 1959for Python expressions used in the SET clause which can be evaluated in 1960Python; these are instead assigned 1961directly onto the object in the same way as the "evaluate" strategy. Only 1962for SQL expressions that can't be evaluated does it fall back to expiring 1963the attributes. The "evaluate" strategy has also been enhanced to fall back 1964to "expire" for a value that cannot be evaluated. 1965 1966 1967Behavioral Changes - ORM 1968======================== 1969 1970.. _change_4710_orm: 1971 1972The "KeyedTuple" object returned by Query is replaced by Row 1973------------------------------------------------------------- 1974 1975As discussed at :ref:`change_4710_core`, the Core :class:`.RowProxy` object 1976is now replaced by a class called :class:`.Row`. The base :class:`.Row` 1977object now behaves more fully like a named tuple, and as such it is now 1978used as the basis for tuple-like results returned by the :class:`_query.Query` 1979object, rather than the previous "KeyedTuple" class. 1980 1981The rationale is so that by SQLAlchemy 2.0, both Core and ORM SELECT statements 1982will return result rows using the same :class:`.Row` object which behaves like 1983a named tuple. Dictionary-like functionality is available from :class:`.Row` 1984via the :attr:`.Row._mapping` attribute. In the interim, Core result sets 1985will make use of a :class:`.Row` subclass :class:`.LegacyRow` which maintains 1986the previous dict/tuple hybrid behavior for backwards compatibility while the 1987:class:`.Row` class will be used directly for ORM tuple results returned 1988by the :class:`_query.Query` object. 1989 1990Effort has been made to get most of the featureset of :class:`.Row` to be 1991available within the ORM, meaning that access by string name as well 1992as entity / column should work:: 1993 1994 row = s.query(User, Address).join(User.addresses).first() 1995 1996 row._mapping[User] # same as row[0] 1997 row._mapping[Address] # same as row[1] 1998 row._mapping["User"] # same as row[0] 1999 row._mapping["Address"] # same as row[1] 2000 2001 u1 = aliased(User) 2002 row = s.query(u1).only_return_tuples(True).first() 2003 row._mapping[u1] # same as row[0] 2004 2005 2006 row = ( 2007 s.query(User.id, Address.email_address) 2008 .join(User.addresses) 2009 .first() 2010 ) 2011 2012 row._mapping[User.id] # same as row[0] 2013 row._mapping["id"] # same as row[0] 2014 row._mapping[users.c.id] # same as row[0] 2015 2016.. seealso:: 2017 2018 :ref:`change_4710_core` 2019 2020:ticket:`4710`. 2021 2022.. _change_5074: 2023 2024Session features new "autobegin" behavior 2025----------------------------------------- 2026 2027Previously, the :class:`.Session` in its default mode of ``autocommit=False`` 2028would internally begin a :class:`.SessionTransaction` object immediately 2029on construction, and additionally would create a new one after each call to 2030:meth:`.Session.rollback` or :meth:`.Session.commit`. 2031 2032The new behavior is that this :class:`.SessionTransaction` object is now 2033created on demand only, when methods such as :meth:`.Session.add` or 2034:meth:`.Session.execute` are called. However it is also now possible 2035to call :meth:`.Session.begin` explicitly in order to begin the transaction, 2036even in ``autocommit=False`` mode, thus matching the behavior of the 2037future-style :class:`_base.Connection`. 2038 2039The behavioral changes this indicates are: 2040 2041* The :class:`.Session` can now be in the state where no transaction is begun, 2042 even in ``autocommit=False`` mode. Previously, this state was only available 2043 in "autocommit" mode. 2044* Within this state, the :meth:`.Session.commit` and :meth:`.Session.rollback` 2045 methods are no-ops. Code that relies upon these methods to expire all objects 2046 should make explicit use of either :meth:`.Session.begin` or 2047 :meth:`.Session.expire_all` to suit their use case. 2048* The :meth:`.SessionEvents.after_transaction_create` event hook is not emitted 2049 immediately when the :class:`.Session` is created, or after a 2050 :meth:`.Session.rollback` or :meth:`.Session.commit` completes. 2051* The :meth:`.Session.close` method also does not imply implicit begin of a new 2052 :class:`.SessionTransaction`. 2053 2054.. seealso:: 2055 2056 :ref:`session_autobegin` 2057 2058Rationale 2059^^^^^^^^^ 2060 2061The :class:`.Session` object's default behavior of ``autocommit=False`` 2062historically has meant that there is always a :class:`.SessionTransaction` 2063object in play, associated with the :class:`.Session` via the 2064:attr:`.Session.transaction` attribute. When the given 2065:class:`.SessionTransaction` was complete, due to a commit, rollback, or close, 2066it was immediately replaced with a new one. The :class:`.SessionTransaction` 2067by itself does not imply the usage of any connection-oriented resources, so 2068this long-standing behavior has a particular elegance to it in that the state 2069of :attr:`.Session.transaction` is always predictable as non-None. 2070 2071However, as part of the initiative in :ticket:`5056` to greatly reduce 2072reference cycles, this assumption means that calling upon 2073:meth:`.Session.close` results in a :class:`.Session` object that still has 2074reference cycles and is more expensive to clean up, not to mention that there 2075is a small overhead in constructing the :class:`.SessionTransaction` 2076object, which meant that there would be unnecessary overhead created 2077for a :class:`.Session` that for example invoked :meth:`.Session.commit` 2078and then :meth:`.Session.close`. 2079 2080As such, it was decided that :meth:`.Session.close` should leave the internal 2081state of ``self.transaction``, now referred to internally as 2082``self._transaction``, as None, and that a new :class:`.SessionTransaction` 2083should only be created when needed. For consistency and code coverage, this 2084behavior was also expanded to include all the points at which "autobegin" is 2085expected, not just when :meth:`.Session.close` were called. 2086 2087In particular, this causes a behavioral change for applications which 2088subscribe to the :meth:`.SessionEvents.after_transaction_create` event hook; 2089previously, this event would be emitted when the :class:`.Session` were first 2090constructed, as well as for most actions that closed the previous transaction 2091and would emit :meth:`.SessionEvents.after_transaction_end`. The new behavior 2092is that :meth:`.SessionEvents.after_transaction_create` is emitted on demand, 2093when the :class:`.Session` has not yet created a new 2094:class:`.SessionTransaction` object and mapped objects are associated with the 2095:class:`.Session` through methods like :meth:`.Session.add` and 2096:meth:`.Session.delete`, when the :attr:`.Session.transaction` attribute is 2097called upon, when the :meth:`.Session.flush` method has tasks to complete, etc. 2098 2099In addition, code which relies upon the :meth:`.Session.commit` or 2100:meth:`.Session.rollback` method to unconditionally expire all objects can no 2101longer do so. Code which needs to expire all objects when no change that has 2102occurred should be calling :meth:`.Session.expire_all` for this case. 2103 2104Besides the change in when the :meth:`.SessionEvents.after_transaction_create` 2105event is emitted as well as the no-op nature of :meth:`.Session.commit` or 2106:meth:`.Session.rollback`, the change should have no other user-visible impact 2107on the :class:`.Session` object's behavior; the :class:`.Session` will continue 2108to have the behavior that it remains usable for new operations after 2109:meth:`.Session.close` is called, and the sequencing of how the 2110:class:`.Session` interacts with the :class:`_engine.Engine` and the database 2111itself should also remain unaffected, since these operations were already 2112operating in an on-demand fashion. 2113 2114:ticket:`5074` 2115 2116.. _change_5237_14: 2117 2118Viewonly relationships don't synchronize backrefs 2119------------------------------------------------- 2120 2121In :ticket:`5149` in 1.3.14, SQLAlchemy began emitting a warning when the 2122:paramref:`_orm.relationship.backref` or :paramref:`_orm.relationship.back_populates` 2123keywords would be used at the same time as the :paramref:`_orm.relationship.viewonly` 2124flag on the target relationship. This was because a "viewonly" relationship does 2125not actually persist changes made to it, which could cause some misleading 2126behaviors to occur. However, in :ticket:`5237`, we sought to refine this 2127behavior as there are legitimate use cases to have backrefs set up on 2128viewonly relationships, including that back populates attributes are used 2129in some cases by the relationship lazy loaders to determine that an additional 2130eager load in the other direction is not necessary, as well as that back 2131populates can be used for mapper introspection and that :func:`_orm.backref` 2132can be a convenient way to set up bi-directional relationships. 2133 2134The solution then was to make the "mutation" that occurs from a backref 2135an optional thing, using the :paramref:`_orm.relationship.sync_backref` 2136flag. In 1.4 the value of :paramref:`_orm.relationship.sync_backref` defaults 2137to False for a relationship target that also sets :paramref:`_orm.relationship.viewonly`. 2138This indicates that any changes made to a relationship with 2139viewonly will not impact the state of the other side or of the :class:`_orm.Session` 2140in any way:: 2141 2142 2143 class User(Base): 2144 # ... 2145 2146 addresses = relationship(Address, backref=backref("user", viewonly=True)) 2147 2148 class Address(Base): 2149 # ... 2150 2151 2152 u1 = session.query(User).filter_by(name="x").first() 2153 2154 a1 = Address() 2155 a1.user = u1 2156 2157Above, the ``a1`` object will **not** be added to the ``u1.addresses`` 2158collection, nor will the ``a1`` object be added to the session. Previously, 2159both of these things would be true. The warning that 2160:paramref:`.relationship.sync_backref` should be set to ``False`` when 2161:paramref:`.relationship.viewonly` is ``False`` is no longer emitted as this is 2162now the default behavior. 2163 2164:ticket:`5237` 2165 2166.. _change_5150: 2167 2168cascade_backrefs behavior deprecated for removal in 2.0 2169------------------------------------------------------- 2170 2171SQLAlchemy has long had a behavior of cascading objects into the 2172:class:`_orm.Session` based on backref assignment. Given ``User`` below 2173already in a :class:`_orm.Session`, assigning it to the ``Address.user`` 2174attribute of an ``Address`` object, assuming a bidirectional relationship 2175is set up, would mean that the ``Address`` also gets put into the 2176:class:`_orm.Session` at that point:: 2177 2178 u1 = User() 2179 session.add(u1) 2180 2181 a1 = Address() 2182 a1.user = u1 # <--- adds "a1" to the Session 2183 2184The above behavior was an unintended side effect of backref behavior, in that 2185since ``a1.user`` implies ``u1.addresses.append(a1)``, ``a1`` would get 2186cascaded into the :class:`_orm.Session`. This remains the default behavior 2187throughout 1.4. At some point, a new flag :paramref:`_orm.relationship.cascade_backrefs` 2188was added to disable to above behavior, along with :paramref:`_orm.backref.cascade_backrefs` 2189to set this when the relationship is specified by ``relationship.backref``, as it can be 2190surprising and also gets in the way of some operations where the object would be placed in 2191the :class:`_orm.Session` too early and get prematurely flushed. 2192 2193In 2.0, the default behavior will be that "cascade_backrefs" is False, and 2194additionally there will be no "True" behavior as this is not generally a desirable 2195behavior. When 2.0 deprecation warnings are enabled, a warning will be emitted 2196when a "backref cascade" actually takes place. To get the new behavior, either 2197set :paramref:`_orm.relationship.cascade_backrefs` and 2198:paramref:`_orm.backref.cascade_backrefs` to ``False`` on any target 2199relationships, as is already supported in 1.3 and earlier, or alternatively make 2200use of the :paramref:`_orm.Session.future` flag to :term:`2.0-style` mode:: 2201 2202 Session = sessionmaker(engine, future=True) 2203 2204 with Session() as session: 2205 u1 = User() 2206 session.add(u1) 2207 2208 a1 = Address() 2209 a1.user = u1 # <--- will not add "a1" to the Session 2210 2211 2212 2213:ticket:`5150` 2214 2215.. _change_1763: 2216 2217Eager loaders emit during unexpire operations 2218--------------------------------------------- 2219 2220A long sought behavior was that when an expired object is accessed, configured 2221eager loaders will run in order to eagerly load relationships on the expired 2222object when the object is refreshed or otherwise unexpired. This behavior has 2223now been added, so that joinedloaders will add inline JOINs as usual, and 2224selectin/subquery loaders will run an "immediateload" operation for a given 2225relationship, when an expired object is unexpired or an object is refreshed:: 2226 2227 >>> a1 = session.query(A).options(joinedload(A.bs)).first() 2228 >>> a1.data = 'new data' 2229 >>> session.commit() 2230 2231Above, the ``A`` object was loaded with a ``joinedload()`` option associated 2232with it in order to eagerly load the ``bs`` collection. After the 2233``session.commit()``, the state of the object is expired. Upon accessing 2234the ``.data`` column attribute, the object is refreshed and this will now 2235include the joinedload operation as well:: 2236 2237 >>> a1.data 2238 SELECT a.id AS a_id, a.data AS a_data, b_1.id AS b_1_id, b_1.a_id AS b_1_a_id 2239 FROM a LEFT OUTER JOIN b AS b_1 ON a.id = b_1.a_id 2240 WHERE a.id = ? 2241 2242The behavior applies both to loader strategies applied to the 2243:func:`_orm.relationship` directly, as well as with options used with 2244:meth:`_query.Query.options`, provided that the object was originally loaded by that 2245query. 2246 2247For the "secondary" eager loaders "selectinload" and "subqueryload", the SQL 2248strategy for these loaders is not necessary in order to eagerly load attributes 2249on a single object; so they will instead invoke the "immediateload" strategy in 2250a refresh scenario, which resembles the query emitted by "lazyload", emitted as 2251an additional query:: 2252 2253 >>> a1 = session.query(A).options(selectinload(A.bs)).first() 2254 >>> a1.data = 'new data' 2255 >>> session.commit() 2256 >>> a1.data 2257 SELECT a.id AS a_id, a.data AS a_data 2258 FROM a 2259 WHERE a.id = ? 2260 (1,) 2261 SELECT b.id AS b_id, b.a_id AS b_a_id 2262 FROM b 2263 WHERE ? = b.a_id 2264 (1,) 2265 2266Note that a loader option does not apply to an object that was introduced 2267into the :class:`.Session` in a different way. That is, if the ``a1`` object 2268were just persisted in this :class:`.Session`, or was loaded with a different 2269query before the eager option had been applied, then the object doesn't have 2270an eager load option associated with it. This is not a new concept, however 2271users who are looking for the eagerload on refresh behavior may find this 2272to be more noticeable. 2273 2274:ticket:`1763` 2275 2276.. _change_4519: 2277 2278Accessing an uninitialized collection attribute on a transient object no longer mutates __dict__ 2279------------------------------------------------------------------------------------------------- 2280 2281It has always been SQLAlchemy's behavior that accessing mapped attributes on a 2282newly created object returns an implicitly generated value, rather than raising 2283``AttributeError``, such as ``None`` for scalar attributes or ``[]`` for a 2284list-holding relationship:: 2285 2286 >>> u1 = User() 2287 >>> u1.name 2288 None 2289 >>> u1.addresses 2290 [] 2291 2292The rationale for the above behavior was originally to make ORM objects easier 2293to work with. Since an ORM object represents an empty row when first created 2294without any state, it is intuitive that its un-accessed attributes would 2295resolve to ``None`` (or SQL NULL) for scalars and to empty collections for 2296relationships. In particular, it makes possible an extremely common pattern 2297of being able to mutate the new collection without manually creating and 2298assigning an empty collection first:: 2299 2300 >>> u1 = User() 2301 >>> u1.addresses.append(Address()) # no need to assign u1.addresses = [] 2302 2303Up until version 1.0 of SQLAlchemy, the behavior of this initialization system 2304for both scalar attributes as well as collections would be that the ``None`` or 2305empty collection would be *populated* into the object's state, e.g. 2306``__dict__``. This meant that the following two operations were equivalent:: 2307 2308 >>> u1 = User() 2309 >>> u1.name = None # explicit assignment 2310 2311 >>> u2 = User() 2312 >>> u2.name # implicit assignment just by accessing it 2313 None 2314 2315Where above, both ``u1`` and ``u2`` would have the value ``None`` populated 2316in the value of the ``name`` attribute. Since this is a SQL NULL, the ORM 2317would skip including these values within an INSERT so that SQL-level defaults 2318take place, if any, else the value defaults to NULL on the database side. 2319 2320In version 1.0 as part of :ref:`migration_3061`, this behavior was refined so 2321that the ``None`` value was no longer populated into ``__dict__``, only 2322returned. Besides removing the mutating side effect of a getter operation, 2323this change also made it possible to set columns that did have server defaults 2324to the value NULL by actually assigning ``None``, which was now distinguished 2325from just reading it. 2326 2327The change however did not accommodate for collections, where returning an 2328empty collection that is not assigned meant that this mutable collection would 2329be different each time and also would not be able to correctly accommodate for 2330mutating operations (e.g. append, add, etc.) called upon it. While the 2331behavior continued to generally not get in anyone's way, an edge case was 2332eventually identified in :ticket:`4519` where this empty collection could be 2333harmful, which is when the object is merged into a session:: 2334 2335 >>> u1 = User(id=1) # create an empty User to merge with id=1 in the database 2336 >>> merged1 = session.merge(u1) # value of merged1.addresses is unchanged from that of the DB 2337 2338 >>> u2 = User(id=2) # create an empty User to merge with id=2 in the database 2339 >>> u2.addresses 2340 [] 2341 >>> merged2 = session.merge(u2) # value of merged2.addresses has been emptied in the DB 2342 2343Above, the ``.addresses`` collection on ``merged1`` will contain all the 2344``Address()`` objects that were already in the database. ``merged2`` will 2345not; because it has an empty list implicitly assigned, the ``.addresses`` 2346collection will be erased. This is an example of where this mutating side 2347effect can actually mutate the database itself. 2348 2349While it was considered that perhaps the attribute system should begin using 2350strict "plain Python" behavior, raising ``AttributeError`` in all cases for 2351non-existent attributes on non-persistent objects and requiring that all 2352collections be explicitly assigned, such a change would likely be too extreme 2353for the vast number of applications that have relied upon this behavior for 2354many years, leading to a complex rollout / backwards compatibility problem as 2355well as the likelihood that workarounds to restore the old behavior would 2356become prevalent, thus rendering the whole change ineffective in any case. 2357 2358The change then is to keep the default producing behavior, but to finally make 2359the non-mutating behavior of scalars a reality for collections as well, via the 2360addition of additional mechanics in the collection system. When accessing the 2361empty attribute, the new collection is created and associated with the state, 2362however is not added to ``__dict__`` until it is actually mutated:: 2363 2364 >>> u1 = User() 2365 >>> l1 = u1.addresses # new list is created, associated with the state 2366 >>> assert u1.addresses is l1 # you get the same list each time you access it 2367 >>> assert "addresses" not in u1.__dict__ # but it won't go into __dict__ until it's mutated 2368 >>> from sqlalchemy import inspect 2369 >>> inspect(u1).attrs.addresses.history 2370 History(added=None, unchanged=None, deleted=None) 2371 2372When the list is changed, then it becomes part of the tracked changes to 2373be persisted to the database:: 2374 2375 >>> l1.append(Address()) 2376 >>> assert "addresses" in u1.__dict__ 2377 >>> inspect(u1).attrs.addresses.history 2378 History(added=[<__main__.Address object at 0x7f49b725eda0>], unchanged=[], deleted=[]) 2379 2380This change is expected to have *nearly* no impact on existing applications 2381in any way, except that it has been observed that some applications may be 2382relying upon the implicit assignment of this collection, such as to assert that 2383the object contains certain values based on its ``__dict__``:: 2384 2385 >>> u1 = User() 2386 >>> u1.addresses 2387 [] 2388 # this will now fail, would pass before 2389 >>> assert {k: v for k, v in u1.__dict__.items() if not k.startswith("_")} == {"addresses": []} 2390 2391or to ensure that the collection won't require a lazy load to proceed, the 2392(admittedly awkward) code below will now also fail:: 2393 2394 >>> u1 = User() 2395 >>> u1.addresses 2396 [] 2397 >>> s.add(u1) 2398 >>> s.flush() 2399 >>> s.close() 2400 >>> u1.addresses # <-- will fail, .addresses is not loaded and object is detached 2401 2402Applications that rely upon the implicit mutating behavior of collections will 2403need to be changed so that they assign the desired collection explicitly:: 2404 2405 >>> u1.addresses = [] 2406 2407:ticket:`4519` 2408 2409.. _change_4662: 2410 2411The "New instance conflicts with existing identity" error is now a warning 2412--------------------------------------------------------------------------- 2413 2414SQLAlchemy has always had logic to detect when an object in the :class:`.Session` 2415to be inserted has the same primary key as an object that is already present:: 2416 2417 class Product(Base): 2418 __tablename__ = 'product' 2419 2420 id = Column(Integer, primary_key=True) 2421 2422 session = Session(engine) 2423 2424 # add Product with primary key 1 2425 session.add(Product(id=1)) 2426 session.flush() 2427 2428 # add another Product with same primary key 2429 session.add(Product(id=1)) 2430 s.commit() # <-- will raise FlushError 2431 2432The change is that the :class:`.FlushError` is altered to be only a warning:: 2433 2434 sqlalchemy/orm/persistence.py:408: SAWarning: New instance <Product at 0x7f1ff65e0ba8> with identity key (<class '__main__.Product'>, (1,), None) conflicts with persistent instance <Product at 0x7f1ff60a4550> 2435 2436 2437Subsequent to that, the condition will attempt to insert the row into the 2438database which will emit :class:`.IntegrityError`, which is the same error that 2439would be raised if the primary key identity was not already present in the 2440:class:`.Session`:: 2441 2442 sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: product.id 2443 2444The rationale is to allow code that is using :class:`.IntegrityError` to catch 2445duplicates to function regardless of the existing state of the 2446:class:`.Session`, as is often done using savepoints:: 2447 2448 2449 # add another Product with same primary key 2450 try: 2451 with session.begin_nested(): 2452 session.add(Product(id=1)) 2453 except exc.IntegrityError: 2454 print("row already exists") 2455 2456The above logic was not fully feasible earlier, as in the case that the 2457``Product`` object with the existing identity were already in the 2458:class:`.Session`, the code would also have to catch :class:`.FlushError`, 2459which additionally is not filtered for the specific condition of integrity 2460issues. With the change, the above block behaves consistently with the 2461exception of the warning also being emitted. 2462 2463Since the logic in question deals with the primary key, all databases emit an 2464integrity error in the case of primary key conflicts on INSERT. The case 2465where an error would not be raised, that would have earlier, is the extremely 2466unusual scenario of a mapping that defines a primary key on the mapped 2467selectable that is more restrictive than what is actually configured in the 2468database schema, such as when mapping to joins of tables or when defining 2469additional columns as part of a composite primary key that is not actually 2470constrained in the database schema. However, these situations also work more 2471consistently in that the INSERT would theoretically proceed whether or not the 2472existing identity were still in the database. The warning can also be 2473configured to raise an exception using the Python warnings filter. 2474 2475 2476:ticket:`4662` 2477 2478.. _change_4994: 2479 2480Persistence-related cascade operations disallowed with viewonly=True 2481--------------------------------------------------------------------- 2482 2483When a :func:`_orm.relationship` is set as ``viewonly=True`` using the 2484:paramref:`_orm.relationship.viewonly` flag, it indicates this relationship should 2485only be used to load data from the database, and should not be mutated 2486or involved in a persistence operation. In order to ensure this contract 2487works successfully, the relationship can no longer specify 2488:paramref:`_orm.relationship.cascade` settings that make no sense in terms of 2489"viewonly". 2490 2491The primary targets here are the "delete, delete-orphan" cascades, which 2492through 1.3 continued to impact persistence even if viewonly were True, which 2493is a bug; even if viewonly were True, an object would still cascade these 2494two operations onto the related object if the parent were deleted or the 2495object were detached. Rather than modify the cascade operations to check 2496for viewonly, the configuration of both of these together is simply 2497disallowed:: 2498 2499 class User(Base): 2500 # ... 2501 2502 # this is now an error 2503 addresses = relationship( 2504 "Address", viewonly=True, cascade="all, delete-orphan") 2505 2506The above will raise:: 2507 2508 sqlalchemy.exc.ArgumentError: Cascade settings 2509 "delete, delete-orphan, merge, save-update" apply to persistence 2510 operations and should not be combined with a viewonly=True relationship. 2511 2512Applications that have this issue should be emitting a warning as of 2513SQLAlchemy 1.3.12, and for the above error the solution is to remove 2514the cascade settings for a viewonly relationship. 2515 2516 2517:ticket:`4993` 2518:ticket:`4994` 2519 2520.. _change_5122: 2521 2522Stricter behavior when querying inheritance mappings using custom queries 2523------------------------------------------------------------------------- 2524 2525This change applies to the scenario where a joined- or single- table 2526inheritance subclass entity is being queried, given a completed SELECT subquery 2527to select from. If the given subquery returns rows that do not correspond to 2528the requested polymorphic identity or identities, an error is raised. 2529Previously, this condition would pass silently under joined table inheritance, 2530returning an invalid subclass, and under single table inheritance, the 2531:class:`_query.Query` would be adding additional criteria against the subquery to 2532limit the results which could inappropriately interfere with the intent of the 2533query. 2534 2535Given the example mapping of ``Employee``, ``Engineer(Employee)``, ``Manager(Employee)``, 2536in the 1.3 series if we were to emit the following query against a joined 2537inheritance mapping:: 2538 2539 s = Session(e) 2540 2541 s.add_all([Engineer(), Manager()]) 2542 2543 s.commit() 2544 2545 print( 2546 s.query(Manager).select_entity_from(s.query(Employee).subquery()).all() 2547 ) 2548 2549 2550The subquery selects both the ``Engineer`` and the ``Manager`` rows, and 2551even though the outer query is against ``Manager``, we get a non ``Manager`` 2552object back:: 2553 2554 SELECT anon_1.type AS anon_1_type, anon_1.id AS anon_1_id 2555 FROM (SELECT employee.type AS type, employee.id AS id 2556 FROM employee) AS anon_1 2557 2020-01-29 18:04:13,524 INFO sqlalchemy.engine.base.Engine () 2558 [<__main__.Engineer object at 0x7f7f5b9a9810>, <__main__.Manager object at 0x7f7f5b9a9750>] 2559 2560The new behavior is that this condition raises an error:: 2561 2562 sqlalchemy.exc.InvalidRequestError: Row with identity key 2563 (<class '__main__.Employee'>, (1,), None) can't be loaded into an object; 2564 the polymorphic discriminator column '%(140205120401296 anon)s.type' 2565 refers to mapped class Engineer->engineer, which is not a sub-mapper of 2566 the requested mapped class Manager->manager 2567 2568The above error only raises if the primary key columns of that entity are 2569non-NULL. If there's no primary key for a given entity in a row, no attempt 2570to construct an entity is made. 2571 2572In the case of single inheritance mapping, the change in behavior is slightly 2573more involved; if ``Engineer`` and ``Manager`` above are mapped with 2574single table inheritance, in 1.3 the following query would be emitted and 2575only a ``Manager`` object is returned:: 2576 2577 SELECT anon_1.type AS anon_1_type, anon_1.id AS anon_1_id 2578 FROM (SELECT employee.type AS type, employee.id AS id 2579 FROM employee) AS anon_1 2580 WHERE anon_1.type IN (?) 2581 2020-01-29 18:08:32,975 INFO sqlalchemy.engine.base.Engine ('manager',) 2582 [<__main__.Manager object at 0x7ff1b0200d50>] 2583 2584The :class:`_query.Query` added the "single table inheritance" criteria to the 2585subquery, editorializing on the intent that was originally set up by it. 2586This behavior was added in version 1.0 in :ticket:`3891`, and creates a 2587behavioral inconsistency between "joined" and "single" table inheritance, 2588and additionally modifies the intent of the given query, which may intend 2589to return additional rows where the columns that correspond to the inheriting 2590entity are NULL, which is a valid use case. The behavior is now equivalent 2591to that of joined table inheritance, where it is assumed that the subquery 2592returns the correct rows and an error is raised if an unexpected polymorphic 2593identity is encountered:: 2594 2595 SELECT anon_1.type AS anon_1_type, anon_1.id AS anon_1_id 2596 FROM (SELECT employee.type AS type, employee.id AS id 2597 FROM employee) AS anon_1 2598 2020-01-29 18:13:10,554 INFO sqlalchemy.engine.base.Engine () 2599 Traceback (most recent call last): 2600 # ... 2601 sqlalchemy.exc.InvalidRequestError: Row with identity key 2602 (<class '__main__.Employee'>, (1,), None) can't be loaded into an object; 2603 the polymorphic discriminator column '%(140700085268432 anon)s.type' 2604 refers to mapped class Engineer->employee, which is not a sub-mapper of 2605 the requested mapped class Manager->employee 2606 2607The correct adjustment to the situation as presented above which worked on 1.3 2608is to adjust the given subquery to correctly filter the rows based on the 2609discriminator column:: 2610 2611 print( 2612 s.query(Manager).select_entity_from( 2613 s.query(Employee).filter(Employee.discriminator == 'manager'). 2614 subquery()).all() 2615 ) 2616 2617 SELECT anon_1.type AS anon_1_type, anon_1.id AS anon_1_id 2618 FROM (SELECT employee.type AS type, employee.id AS id 2619 FROM employee 2620 WHERE employee.type = ?) AS anon_1 2621 2020-01-29 18:14:49,770 INFO sqlalchemy.engine.base.Engine ('manager',) 2622 [<__main__.Manager object at 0x7f70e13fca90>] 2623 2624 2625:ticket:`5122` 2626 2627Dialect Changes 2628=============== 2629 2630pg8000 minimum version is 1.16.6, supports Python 3 only 2631-------------------------------------------------------- 2632 2633Support for the pg8000 dialect has been dramatically improved, with help from 2634the project's maintainer. 2635 2636Due to API changes, the pg8000 dialect now requires 2637version 1.16.6 or greater. The pg8000 series has dropped Python 2 support as of 2638the 1.13 series. Python 2 users who require pg8000 should ensure their 2639requirements are pinned at ``SQLAlchemy<1.4``. 2640 2641:ticket:`5451` 2642 2643psycopg2 version 2.7 or higher is required for the PostgreSQL psycopg2 dialect 2644------------------------------------------------------------------------------ 2645 2646The psycopg2 dialect relies upon many features of psycopg2 released 2647in the past few years. To simplify the dialect, version 2.7, released 2648in March, 2017 is now the minimum version required. 2649 2650.. _change_5941: 2651 2652psycopg2 dialect no longer has limitations regarding bound parameter names 2653-------------------------------------------------------------------------- 2654 2655SQLAlchemy 1.3 was not able to accommodate bound parameter names that included 2656percent signs or parenthesis under the psycopg2 dialect. This in turn meant 2657that column names which included these characters were also problematic as 2658INSERT and other DML statements would generate parameter names that matched 2659that of the column, which would then cause failures. The workaround was to make 2660use of the :paramref:`_schema.Column.key` parameter so that an alternate name 2661that would be used to generate the parameter, or otherwise the parameter style 2662of the dialect had to be changed at the :func:`_sa.create_engine` level. As of 2663SQLAlchemy 1.4.0beta3 all naming limitations have been removed and parameters 2664are fully escaped in all scenarios, so these workarounds are no longer 2665necessary. 2666 2667 2668:ticket:`5941` 2669 2670:ticket:`5653` 2671 2672 2673.. _change_5401: 2674 2675psycopg2 dialect features "execute_values" with RETURNING for INSERT statements by default 2676------------------------------------------------------------------------------------------ 2677 2678The first half of a significant performance enhancement for PostgreSQL when 2679using both Core and ORM, the psycopg2 dialect now uses 2680``psycopg2.extras.execute_values()`` by default for compiled INSERT statements 2681and also implements RETURNING support in this mode. The other half of this 2682change is :ref:`change_5263` which allows the ORM to take advantage of 2683RETURNING with executemany (i.e. batching of INSERT statements) so that ORM 2684bulk inserts with psycopg2 are up to 400% faster depending on specifics. 2685 2686This extension method allows many rows to be INSERTed within a single 2687statement, using an extended VALUES clause for the statement. While 2688SQLAlchemy's :func:`_sql.insert` construct already supports this syntax via 2689the :meth:`_sql.Insert.values` method, the extension method allows the 2690construction of the VALUES clause to occur dynamically when the statement 2691is executed as an "executemany" execution, which is what occurs when one 2692passes a list of parameter dictionaries to :meth:`_engine.Connection.execute`. 2693It also occurs beyond the cache boundary so that the INSERT statement may 2694be cached before the VALUES are rendered. 2695 2696A quick test of the ``execute_values()`` approach using the 2697``bulk_inserts.py`` script in the :ref:`examples_performance` example 2698suite reveals an approximate **fivefold performance increase**:: 2699 2700 $ python -m examples.performance bulk_inserts --test test_core_insert --num 100000 --dburl postgresql://scott:tiger@localhost/test 2701 2702 # 1.3 2703 test_core_insert : A single Core INSERT construct inserting mappings in bulk. (100000 iterations); total time 5.229326 sec 2704 2705 # 1.4 2706 test_core_insert : A single Core INSERT construct inserting mappings in bulk. (100000 iterations); total time 0.944007 sec 2707 2708Support for the "batch" extension was added in version 1.2 in 2709:ref:`change_4109`, and enhanced to include support for the ``execute_values`` 2710extension in 1.3 in :ticket:`4623`. In 1.4 the ``execute_values`` extension is 2711now being turned on by default for INSERT statements; the "batch" extension 2712for UPDATE and DELETE remains off by default. 2713 2714In addition, the ``execute_values`` extension function supports returning the 2715rows that are generated by RETURNING as an aggregated list. The psycopg2 2716dialect will now retrieve this list if the given :func:`_sql.insert` construct 2717requests returning via the :meth:`.Insert.returning` method or similar methods 2718intended to return generated defaults; the rows are then installed in the 2719result so that they are retrieved as though they came from the cursor 2720directly. This allows tools like the ORM to use batched inserts in all cases, 2721which is expected to provide a dramatic performance improvement. 2722 2723 2724The ``executemany_mode`` feature of the psycopg2 dialect has been revised 2725with the following changes: 2726 2727* A new mode ``"values_only"`` is added. This mode uses the very performant 2728 ``psycopg2.extras.execute_values()`` extension method for compiled INSERT 2729 statements run with executemany(), but does not use ``execute_batch()`` for 2730 UPDATE and DELETE statements. This new mode is now the default setting for 2731 the psycopg2 dialect. 2732 2733* The existing ``"values"`` mode is now named ``"values_plus_batch"``. This mode 2734 will use ``execute_values`` for INSERT statements and ``execute_batch`` 2735 for UPDATE and DELETE statements. The mode is not enabled by default 2736 because it disables the proper functioning of ``cursor.rowcount`` with 2737 UPDATE and DELETE statements executed with ``executemany()``. 2738 2739* RETURNING support is enabled for ``"values_only"`` and ``"values"`` for 2740 INSERT statements. The psycopg2 dialect will receive the rows back 2741 from psycopg2 using the fetch=True flag and install them into the result 2742 set as though they came directly from the cursor (which they ultimately did, 2743 however psycopg2's extension function has aggregated multiple batches into 2744 one list). 2745 2746* The default "page_size" setting for ``execute_values`` has been increased 2747 from 100 to 1000. The default remains at 100 for the ``execute_batch`` 2748 function. These parameters may both be modified as was the case before. 2749 2750* The ``use_batch_mode`` flag that was part of the 1.2 version of the feature 2751 is removed; the behavior remains controllable via the ``executemany_mode`` 2752 flag added in 1.3. 2753 2754* The Core engine and dialect has been enhanced to support executemany 2755 plus returning mode, currently only available with psycopg2, by providing 2756 new :attr:`_engine.CursorResult.inserted_primary_key_rows` and 2757 :attr:`_engine.CursorResult.returned_default_rows` accessors. 2758 2759.. seealso:: 2760 2761 :ref:`psycopg2_executemany_mode` 2762 2763 2764:ticket:`5401` 2765 2766 2767.. _change_4895: 2768 2769Removed "join rewriting" logic from SQLite dialect; updated imports 2770------------------------------------------------------------------- 2771 2772Dropped support for right-nested join rewriting to support old SQLite 2773versions prior to 3.7.16, released in 2013. It is not expected that 2774any modern Python versions rely upon this limitation. 2775 2776The behavior was first introduced in 0.9 and was part of the larger change of 2777allowing for right nested joins as described at :ref:`feature_joins_09`. 2778However the SQLite workaround produced many regressions in the 2013-2014 2779period due to its complexity. In 2016, the dialect was modified so that the 2780join rewriting logic would only occur for SQLite versions prior to 3.7.16 after 2781bisection was used to identify where SQLite fixed its support for this 2782construct, and no further issues were reported against the behavior (even 2783though some bugs were found internally). It is now anticipated that there 2784are little to no Python builds for Python 2.7 or 3.5 and above (the supported 2785Python versions) which would include a SQLite version prior to 3.7.17, and 2786the behavior is only necessary only in more complex ORM joining scenarios. 2787A warning is now emitted if the installed SQLite version is older than 27883.7.16. 2789 2790In related changes, the module imports for SQLite no longer attempt to 2791import the "pysqlite2" driver on Python 3 as this driver does not exist 2792on Python 3; a very old warning for old pysqlite2 versions is also dropped. 2793 2794:ticket:`4895` 2795 2796 2797.. _change_4976: 2798 2799Added Sequence support for MariaDB 10.3 2800---------------------------------------- 2801 2802The MariaDB database as of 10.3 supports sequences. SQLAlchemy's MySQL 2803dialect now implements support for the :class:`.Sequence` object against this 2804database, meaning "CREATE SEQUENCE" DDL will be emitted for a 2805:class:`.Sequence` that is present in a :class:`_schema.Table` or :class:`_schema.MetaData` 2806collection in the same way as it works for backends such as PostgreSQL, Oracle, 2807when the dialect's server version check has confirmed the database is MariaDB 280810.3 or greater. Additionally, the :class:`.Sequence` will act as a 2809column default and primary key generation object when used in these ways. 2810 2811Since this change will impact the assumptions both for DDL as well as the 2812behavior of INSERT statements for an application that is currently deployed 2813against MariaDB 10.3 which also happens to make explicit use the 2814:class:`.Sequence` construct within its table definitions, it is important to 2815note that :class:`.Sequence` supports a flag :paramref:`.Sequence.optional` 2816which is used to limit the scenarios in which the :class:`.Sequence` to take 2817effect. When "optional" is used on a :class:`.Sequence` that is present in the 2818integer primary key column of a table:: 2819 2820 Table( 2821 "some_table", metadata, 2822 Column("id", Integer, Sequence("some_seq", optional=True), primary_key=True) 2823 ) 2824 2825The above :class:`.Sequence` is only used for DDL and INSERT statements if the 2826target database does not support any other means of generating integer primary 2827key values for the column. That is, the Oracle database above would use the 2828sequence, however the PostgreSQL and MariaDB 10.3 databases would not. This may 2829be important for an existing application that is upgrading to SQLAlchemy 1.4 2830which may not have emitted DDL for this :class:`.Sequence` against its backing 2831database, as an INSERT statement will fail if it seeks to use a sequence that 2832was not created. 2833 2834 2835.. seealso:: 2836 2837 :ref:`defaults_sequences` 2838 2839:ticket:`4976` 2840 2841 2842.. _change_4235: 2843 2844Added Sequence support distinct from IDENTITY to SQL Server 2845----------------------------------------------------------- 2846 2847The :class:`.Sequence` construct is now fully functional with Microsoft 2848SQL Server. When applied to a :class:`.Column`, the DDL for the table will 2849no longer include IDENTITY keywords and instead will rely upon "CREATE SEQUENCE" 2850to ensure a sequence is present which will then be used for INSERT statements 2851on the table. 2852 2853The :class:`.Sequence` prior to version 1.3 was used to control parameters for 2854the IDENTITY column in SQL Server; this usage emitted deprecation warnings 2855throughout 1.3 and is now removed in 1.4. For control of parameters for an 2856IDENTITY column, the ``mssql_identity_start`` and ``mssql_identity_increment`` 2857parameters should be used; see the MSSQL dialect documentation linked below. 2858 2859 2860.. seealso:: 2861 2862 :ref:`mssql_identity` 2863 2864:ticket:`4235` 2865 2866:ticket:`4633` 2867