1============= 21.4 Changelog 3============= 4 5This document details individual issue-level changes made throughout 61.4 releases. For a narrative overview of what's new in 1.4, see 7:ref:`migration_14_toplevel`. 8 9 10.. changelog_imports:: 11 12 .. include:: changelog_13.rst 13 :start-line: 5 14 15 16.. changelog:: 17 :version: 1.4.27 18 :released: November 11, 2021 19 20 .. change:: 21 :tags: bug, engine 22 :tickets: 7291 23 24 Fixed issue in future :class:`_future.Connection` object where the 25 :meth:`_future.Connection.execute` method would not accept a non-dict 26 mapping object, such as SQLAlchemy's own :class:`.RowMapping` or other 27 ``abc.collections.Mapping`` object as a parameter dictionary. 28 29 .. change:: 30 :tags: bug, mysql, mariadb 31 :tickets: 7167 32 33 Reorganized the list of reserved words into two separate lists, one for 34 MySQL and one for MariaDB, so that these diverging sets of words can be 35 managed more accurately; adjusted the MySQL/MariaDB dialect to switch among 36 these lists based on either explicitly configured or 37 server-version-detected "MySQL" or "MariaDB" backend. Added all current 38 reserved words through MySQL 8 and current MariaDB versions including 39 recently added keywords like "lead" . Pull request courtesy Kevin Kirsche. 40 41 .. change:: 42 :tags: bug, orm 43 :tickets: 7224 44 45 Fixed bug in "relationship to aliased class" feature introduced at 46 :ref:`relationship_aliased_class` where it was not possible to create a 47 loader strategy option targeting an attribute on the target using the 48 :func:`_orm.aliased` construct directly in a second loader option, such as 49 ``selectinload(A.aliased_bs).joinedload(aliased_b.cs)``, without explicitly 50 qualifying using :meth:`_orm.PropComparator.of_type` on the preceding 51 element of the path. Additionally, targeting the non-aliased class directly 52 would be accepted (inappropriately), but would silently fail, such as 53 ``selectinload(A.aliased_bs).joinedload(B.cs)``; this now raises an error 54 referring to the typing mismatch. 55 56 57 .. change:: 58 :tags: bug, schema 59 :tickets: 7295 60 61 Fixed issue in :class:`.Table` where the 62 :paramref:`.Table.implicit_returning` parameter would not be 63 accommodated correctly when passed along with 64 :paramref:`.Table.extend_existing` to augment an existing 65 :class:`.Table`. 66 67 .. change:: 68 :tags: bug, postgresql, asyncpg 69 :tickets: 7283 70 71 Changed the asyncpg dialect to bind the :class:`.Float` type to the "float" 72 PostgreSQL type instead of "numeric" so that the value ``float(inf)`` can 73 be accommodated. Added test suite support for persistence of the "inf" 74 value. 75 76 77 .. change:: 78 :tags: bug, engine, regression 79 :tickets: 7274 80 :versions: 2.0.0b1 81 82 Fixed regression where the :meth:`_engine.CursorResult.fetchmany` method 83 would fail to autoclose a server-side cursor (i.e. when ``stream_results`` 84 or ``yield_per`` is in use, either Core or ORM oriented results) when the 85 results were fully exhausted. 86 87 .. change:: 88 :tags: bug, orm 89 :tickets: 7274 90 :versions: 2.0.0b1 91 92 All :class:`_result.Result` objects will now consistently raise 93 :class:`_exc.ResourceClosedError` if they are used after a hard close, 94 which includes the "hard close" that occurs after calling "single row or 95 value" methods like :meth:`_result.Result.first` and 96 :meth:`_result.Result.scalar`. This was already the behavior of the most 97 common class of result objects returned for Core statement executions, i.e. 98 those based on :class:`_engine.CursorResult`, so this behavior is not new. 99 However, the change has been extended to properly accommodate for the ORM 100 "filtering" result objects returned when using 2.0 style ORM queries, 101 which would previously behave in "soft closed" style of returning empty 102 results, or wouldn't actually "soft close" at all and would continue 103 yielding from the underlying cursor. 104 105 As part of this change, also added :meth:`_result.Result.close` to the base 106 :class:`_result.Result` class and implemented it for the filtered result 107 implementations that are used by the ORM, so that it is possible to call 108 the :meth:`_engine.CursorResult.close` method on the underlying 109 :class:`_engine.CursorResult` when the the ``yield_per`` execution option 110 is in use to close a server side cursor before remaining ORM results have 111 been fetched. This was again already available for Core result sets but the 112 change makes it available for 2.0 style ORM results as well. 113 114 115 .. change:: 116 :tags: bug, mysql 117 :tickets: 7281 118 :versions: 2.0.0b1 119 120 Fixed issue in MySQL :meth:`_mysql.Insert.on_duplicate_key_update` which 121 would render the wrong column name when an expression were used in a VALUES 122 expression. Pull request courtesy Cristian Sabaila. 123 124 .. change:: 125 :tags: bug, sql, regression 126 :tickets: 7292 127 128 Fixed regression where the row objects returned for ORM queries, which are 129 now the normal :class:`_sql.Row` objects, would not be interpreted by the 130 :meth:`_sql.ColumnOperators.in_` operator as tuple values to be broken out 131 into individual bound parameters, and would instead pass them as single 132 values to the driver leading to failures. The change to the "expanding IN" 133 system now accommodates for the expression already being of type 134 :class:`.TupleType` and treats values accordingly if so. In the uncommon 135 case of using "tuple-in" with an untyped statement such as a textual 136 statement with no typing information, a tuple value is detected for values 137 that implement ``collections.abc.Sequence``, but that are not ``str`` or 138 ``bytes``, as always when testing for ``Sequence``. 139 140 .. change:: 141 :tags: usecase, sql 142 143 Added :class:`.TupleType` to the top level ``sqlalchemy`` import namespace. 144 145 .. change:: 146 :tags: bug, sql 147 :tickets: 7269 148 149 Fixed issue where using the feature of using a string label for ordering or 150 grouping described at :ref:`tutorial_order_by_label` would fail to function 151 correctly if used on a :class:`.CTE` construct, when the CTE were embedded 152 inside of an enclosing :class:`_sql.Select` statement that itself was set 153 up as a scalar subquery. 154 155 156 157 .. change:: 158 :tags: bug, orm, regression 159 :tickets: 7239 160 161 Fixed 1.4 regression where :meth:`_orm.Query.filter_by` would not function 162 correctly on a :class:`_orm.Query` that was produced from 163 :meth:`_orm.Query.union`, :meth:`_orm.Query.from_self` or similar. 164 165 .. change:: 166 :tags: bug, orm 167 :tickets: 7304 168 169 Fixed issue where deferred polymorphic loading of attributes from a 170 joined-table inheritance subclass would fail to populate the attribute 171 correctly if the :func:`_orm.load_only` option were used to originally 172 exclude that attribute, in the case where the load_only were descending 173 from a relationship loader option. The fix allows that other valid options 174 such as ``defer(..., raiseload=True)`` etc. still function as expected. 175 176 .. change:: 177 :tags: postgresql, usecase, asyncpg 178 :tickets: 7284 179 :versions: 2.0.0b1 180 181 Added overridable methods ``PGDialect_asyncpg.setup_asyncpg_json_codec`` 182 and ``PGDialect_asyncpg.setup_asyncpg_jsonb_codec`` codec, which handle the 183 required task of registering JSON/JSONB codecs for these datatypes when 184 using asyncpg. The change is that methods are broken out as individual, 185 overridable methods to support third party dialects that need to alter or 186 disable how these particular codecs are set up. 187 188 189 190 .. change:: 191 :tags: bug, engine 192 :tickets: 7272 193 :versions: 2.0.0b1 194 195 Fixed issue in future :class:`_future.Engine` where calling upon 196 :meth:`_future.Engine.begin` and entering the context manager would not 197 close the connection if the actual BEGIN operation failed for some reason, 198 such as an event handler raising an exception; this use case failed to be 199 tested for the future version of the engine. Note that the "future" context 200 managers which handle ``begin()`` blocks in Core and ORM don't actually run 201 the "BEGIN" operation until the context managers are actually entered. This 202 is different from the legacy version which runs the "BEGIN" operation up 203 front. 204 205 .. change:: 206 :tags: mssql, bug 207 :tickets: 7300 208 209 Adjusted the compiler's generation of "post compile" symbols including 210 those used for "expanding IN" as well as for the "schema translate map" to 211 not be based directly on plain bracketed strings with underscores, as this 212 conflicts directly with SQL Server's quoting format of also using brackets, 213 which produces false matches when the compiler replaces "post compile" and 214 "schema translate" symbols. The issue created easy to reproduce examples 215 both with the :meth:`.Inspector.get_schema_names` method when used in 216 conjunction with the 217 :paramref:`_engine.Connection.execution_options.schema_translate_map` 218 feature, as well in the unlikely case that a symbol overlapping with the 219 internal name "POSTCOMPILE" would be used with a feature like "expanding 220 in". 221 222 223 .. change:: 224 :tags: postgresql, pg8000 225 :tickets: 7167 226 227 Improve array handling when using PostgreSQL with the 228 pg8000 dialect. 229 230 .. change:: 231 :tags: bug, orm, regression 232 :tickets: 7244 233 234 Fixed 1.4 regression where :meth:`_orm.Query.filter_by` would not function 235 correctly when :meth:`_orm.Query.join` were joined to an entity which made 236 use of :meth:`_orm.PropComparator.of_type` to specify an aliased version of 237 the target entity. The issue also applies to future style ORM queries 238 constructed with :func:`_sql.select`. 239 240 241 .. change:: 242 :tags: bug, sql, regression 243 :tickets: 7287 244 245 Fixed regression where the :func:`_sql.text` construct would no longer be 246 accepted as a target case in the "whens" list within a :func:`_sql.case` 247 construct. The regression appears related to an attempt to guard against 248 some forms of literal values that were considered to be ambiguous when 249 passed here; however, there's no reason the target cases shouldn't be 250 interpreted as open-ended SQL expressions just like anywhere else, and a 251 literal string or tuple will be converted to a bound parameter as would be 252 the case elsewhere. 253 254.. changelog:: 255 :version: 1.4.26 256 :released: October 19, 2021 257 258 .. change:: 259 :tags: orm 260 :tickets: 6284 261 262 Passing a :class:`.Query` object to :meth:`_orm.Session.execute` is not 263 the intended use of this object, and will now raise a deprecation warning. 264 265 .. change:: 266 :tags: bug, postgresql 267 :tickets: 5387 268 269 Added a "disconnect" condition for the "SSL SYSCALL error: Bad address" 270 error message as reported by psycopg2. Pull request courtesy Zeke Brechtel. 271 272 .. change:: 273 :tags: bug, orm 274 275 Improved the exception message generated when configuring a mapping with 276 joined table inheritance where the two tables either have no foreign key 277 relationships set up, or where they have multiple foreign key relationships 278 set up. The message is now ORM specific and includes context that the 279 :paramref:`_orm.Mapper.inherit_condition` parameter may be needed 280 particularly for the ambiguous foreign keys case. 281 282 283 .. change:: 284 :tags: bug, sql 285 :tickets: 6520 286 287 Fixed issue where SQL queries using the 288 :meth:`_functions.FunctionElement.within_group` construct could not be 289 pickled, typically when using the ``sqlalchemy.ext.serializer`` extension 290 but also for general generic pickling. 291 292 .. change:: 293 :tags: bug, orm 294 :tickets: 7189 295 296 Fixed issue with :func:`_orm.with_loader_criteria` feature where ON 297 criteria would not be added to a JOIN for a query of the form 298 ``select(A).join(B)``, stating a target while making use of an implicit 299 ON clause. 300 301 .. change:: 302 :tags: bug, orm 303 :tickets: 7205 304 305 Fixed bug where the ORM "plugin", necessary for features such as 306 :func:`_orm.with_loader_criteria` to work correctly, would not be applied 307 to a :func:`_sql.select` which queried from an ORM column expression if it 308 made use of the :meth:`_sql.ColumnElement.label` modifier. 309 310 311 312 .. change:: 313 :tags: bug, mypy 314 :tickets: 6435 315 316 Fixed issue in mypy plugin to improve upon some issues detecting ``Enum()`` 317 SQL types containing custom Python enumeration classes. Pull request 318 courtesy Hiroshi Ogawa. 319 320 .. change:: 321 :tags: bug, mysql 322 :tickets: 7144 323 324 Fixed issue in MySQL :func:`_mysql.match` construct where passing a clause 325 expression such as :func:`_sql.bindparam` or other SQL expression for the 326 "against" parameter would fail. Pull request courtesy Anton Kovalevich. 327 328 329 .. change:: 330 :tags: bug, mssql 331 :tickets: 7160 332 333 Fixed issue with :meth:`.Inspector.get_foreign_keys` where foreign 334 keys were omitted if they were established against a unique 335 index instead of a unique constraint. 336 337 338 .. change:: 339 :tags: usecase, mssql 340 341 Added reflection support for SQL Server foreign key options, including 342 "ON UPDATE" and "ON DELETE" values of "CASCADE" and "SET NULL". 343 344 .. change:: 345 :tags: bug, sql 346 :tickets: 4123 347 348 Repaired issue in new :paramref:`_sql.HasCTE.cte.nesting` parameter 349 introduced with :ticket:`4123` where a recursive :class:`_sql.CTE` using 350 :paramref:`_sql.HasCTE.cte.recursive` in typical conjunction with UNION 351 would not compile correctly. Additionally makes some adjustments so that 352 the :class:`_sql.CTE` construct creates a correct cache key. 353 Pull request courtesy Eric Masseran. 354 355 .. change:: 356 :tags: bug, engine 357 :tickets: 7130 358 359 Fixed issue where the deprecation warning for the :class:`.URL` constructor 360 which indicates that the :meth:`.URL.create` method should be used would 361 not emit if a full positional argument list of seven arguments were passed; 362 additionally, validation of URL arguments will now occur if the constructor 363 is called in this way, which was being skipped previously. 364 365 .. change:: 366 :tags: bug, orm 367 :tickets: 7103 368 369 Add missing methods added in :ticket:`6991` to 370 :class:`_scoping.scoped_session` and :func:`_asyncio.async_scoped_session`. 371 372 .. change:: 373 :tags: bug, examples 374 :tickets: 7169 375 376 Repaired the examples in examples/versioned_rows to use SQLAlchemy 1.4 APIs 377 correctly; these examples had been missed when API changes like removing 378 "passive" from :meth:`_orm.Session.is_modified` were made as well as the 379 :meth:`_ormevents.SessionEvents.do_orm_execute()` event hook were added. 380 381 .. change:: 382 :tags: bug, orm 383 :tickets: 6974, 6972 384 385 An extra layer of warning messages has been added to the functionality 386 of :meth:`_orm.Query.join` and the ORM version of 387 :meth:`_sql.Select.join`, where a few places where "automatic aliasing" 388 continues to occur will now be called out as a pattern to avoid, mostly 389 specific to the area of joined table inheritance where classes that share 390 common base tables are being joined together without using explicit aliases. 391 One case emits a legacy warning for a pattern that's not recommended, 392 the other case is fully deprecated. 393 394 The automatic aliasing within ORM join() which occurs for overlapping 395 mapped tables does not work consistently with all APIs such as 396 :func:`_orm.contains_eager()`, and rather than continue to try to make 397 these use cases work everywhere, replacing with a more user-explicit 398 pattern is clearer, less prone to bugs and simplifies SQLAlchemy's 399 internals further. 400 401 The warnings include links to the errors.rst page where each pattern is 402 demonstrated along with the recommended pattern to fix. 403 404 .. seealso:: 405 406 :ref:`error_xaj1` 407 408 :ref:`error_xaj2` 409 410 .. change:: 411 :tags: bug, sql 412 :tickets: 7061 413 414 Account for the :paramref:`_sql.table.schema` parameter passed to 415 the :func:`_sql.table` construct, such that it is taken into account 416 when accessing the :attr:`_sql.TableClause.fullname` attribute. 417 418 .. change:: 419 :tags: bug, sql 420 :tickets: 7140 421 422 Fixed an inconsistency in the :meth:`_sql.ColumnOperators.any_` / 423 :meth:`_sql.ColumnOperators.all_` functions / methods where the special 424 behavior these functions have of "flipping" the expression such that the 425 "ANY" / "ALL" expression is always on the right side would not function if 426 the comparison were against the None value, that is, "column.any_() == 427 None" should produce the same SQL expression as "null() == column.any_()". 428 Added more docs to clarify this as well, plus mentions that any_() / all_() 429 generally supersede the ARRAY version "any()" / "all()". 430 431 .. change:: 432 :tags: engine, bug, postgresql 433 :tickets: 3247 434 435 The :meth:`_reflection.Inspector.reflect_table` method now supports 436 reflecting tables that do not have user defined columns. This allows 437 :meth:`_schema.MetaData.reflect` to properly complete reflection on 438 databases that contain such tables. Currently, only PostgreSQL is known to 439 support such a construct among the common database backends. 440 441 .. change:: 442 :tags: sql, bug, regression 443 :tickets: 7177 444 445 Fixed issue where "expanding IN" would fail to function correctly with 446 datatypes that use the :meth:`_types.TypeEngine.bind_expression` method, 447 where the method would need to be applied to each element of the 448 IN expression rather than the overall IN expression itself. 449 450 .. change:: 451 :tags: postgresql, bug, regression 452 :tickets: 7177 453 454 Fixed issue where IN expressions against a series of array elements, as can 455 be done with PostgreSQL, would fail to function correctly due to multiple 456 issues within the "expanding IN" feature of SQLAlchemy Core that was 457 standardized in version 1.4. The psycopg2 dialect now makes use of the 458 :meth:`_types.TypeEngine.bind_expression` method with :class:`_types.ARRAY` 459 to portably apply the correct casts to elements. The asyncpg dialect was 460 not affected by this issue as it applies bind-level casts at the driver 461 level rather than at the compiler level. 462 463 464 .. change:: 465 :tags: bug, mysql 466 :tickets: 7204 467 468 Fixed installation issue where the ``sqlalchemy.dialects.mysql`` module 469 would not be importable if "greenlet" were not installed. 470 471 .. change:: 472 :tags: bug, mssql 473 :tickets: 7168 474 475 Fixed issue with :meth:`.Inspector.has_table` where it would return False 476 if a local temp table with the same name from a different session happened 477 to be returned first when querying tempdb. This is a continuation of 478 :ticket:`6910` which accounted for the temp table existing only in the 479 alternate session and not the current one. 480 481 .. change:: 482 :tags: bug, orm 483 :tickets: 7128 484 485 Fixed bug where iterating a :class:`.Result` from a :class:`_orm.Session` 486 after that :class:`_orm.Session` were closed would partially attach objects 487 to that session in an essentially invalid state. It now raises an exception 488 with a link to new documentation if an **un-buffered** result is iterated 489 from a :class:`_orm.Session` that was closed or otherwise had the 490 :meth:`_orm.Session.expunge_all` method called after that :class:`.Result` 491 was generated. The ``prebuffer_rows`` execution option, as is used 492 automatically by the asyncio extension for client-side result sets, may be 493 used to produce a :class:`.Result` where the ORM objects are prebuffered, 494 and in this case iterating the result will produce a series of detached 495 objects. 496 497 .. seealso:: 498 499 :ref:`error_lkrp` 500 501 .. change:: 502 :tags: bug, mssql, regression 503 :tickets: 7129 504 505 Fixed bug in SQL Server :class:`_mssql.DATETIMEOFFSET` datatype where the 506 ODBC implementation would not generate the correct DDL, for cases where the 507 type were converted using the ``dialect.type_descriptor()`` method, the 508 usage of which is illustrated in some documented examples for 509 :class:`.TypeDecorator`, though not necessary for most datatypes. 510 Regression was introduced by :ticket:`6366`. As part of this change, the 511 full list of SQL Server date types have been amended to return a "dialect 512 impl" that generates the same DDL name as the supertype. 513 514 .. change:: 515 :tags: bug, sql 516 :tickets: 7153 517 518 Adjusted the "column disambiguation" logic that's new in 1.4, where the 519 same expression repeated gets an "extra anonymous" label, so that the logic 520 more aggressively deduplicates those labels when the repeated element 521 is the same Python expression object each time, as occurs in cases like 522 when using "singleton" values like :func:`_sql.null`. This is based on 523 the observation that at least some databases (e.g. MySQL, but not SQLite) 524 will raise an error if the same label is repeated inside of a subquery. 525 526 .. change:: 527 :tags: bug, orm 528 :tickets: 7154 529 530 Related to :ticket:`7153`, fixed an issue where result column lookups would 531 fail for "adapted" SELECT statements that selected for "constant" value 532 expressions most typically the NULL expression, as would occur in such 533 places as joined eager loading in conjunction with limit/offset. This was 534 overall a regression due to issue :ticket:`6259` which removed all 535 "adaption" for constants like NULL, "true", and "false" when rewriting 536 expressions in a SQL statement, but this broke the case where the same 537 adaption logic were used to resolve the constant to a labeled expression 538 for the purposes of result set targeting. 539 540 .. change:: 541 :tags: bug, orm, regression 542 :tickets: 7134 543 544 Fixed regression where ORM loaded objects could not be pickled in cases 545 where loader options making use of ``"*"`` were used in certain 546 combinations, such as combining the :func:`_orm.joinedload` loader strategy 547 with ``raiseload('*')`` of sub-elements. 548 549 550 .. change:: 551 :tags: bug, engine 552 :tickets: 7077 553 554 Implemented proper ``__reduce__()`` methods for all SQLAlchemy exception 555 objects to ensure they all support clean round trips when pickling, as 556 exception objects are often serialized for the purposes of various 557 debugging tools. 558 559 .. change:: 560 :tags: bug, orm, regression 561 :tickets: 7209 562 563 Fixed regression where the use of a :class:`_hybrid.hybrid_property` 564 attribute or a mapped :func:`_orm.composite` attribute as a key passed to 565 the :meth:`_dml.Update.values` method for an ORM-enabled 566 :class:`_dml.Update` statement, as well as when using it via the legacy 567 :meth:`_orm.Query.update` method, would be processed for incoming 568 ORM/hybrid/composite values within the compilation stage of the UPDATE 569 statement, which meant that in those cases where caching occurred, 570 subsequent invocations of the same statement would no longer receive the 571 correct values. This would include not only hybrids that use the 572 :meth:`_hybrid.hybrid_property.update_expression` method, but any use of a 573 plain hybrid attribute as well. For composites, the issue instead caused a 574 non-repeatable cache key to be generated, which would break caching and 575 could fill up the statement cache with repeated statements. 576 577 The :class:`_dml.Update` construct now handles the processing of key/value 578 pairs passed to :meth:`_dml.Update.values` and 579 :meth:`_dml.Update.ordered_values` up front when the construct is first 580 generated, before the cache key has been generated so that the key/value 581 pairs are processed each time, and so that the cache key is generated 582 against the individual column/value pairs that will ultimately be 583 used in the statement. 584 585 586.. changelog:: 587 :version: 1.4.25 588 :released: September 22, 2021 589 590 .. change:: 591 :tags: bug, platform, regression 592 :tickets: 7024 593 594 Fixed regression due to :ticket:`7024` where the reorganization of the 595 "platform machine" names used by the ``greenlet`` dependency mis-spelled 596 "aarch64" and additionally omitted uppercase "AMD64" as is needed for 597 Windows machines. Pull request courtesy James Dow. 598 599.. changelog:: 600 :version: 1.4.24 601 :released: September 22, 2021 602 603 .. change:: 604 :tags: bug, asyncio 605 :tickets: 6943 606 607 Fixed a bug in :meth:`_asyncio.AsyncSession.execute` and 608 :meth:`_asyncio.AsyncSession.stream` that required ``execution_options`` 609 to be an instance of ``immutabledict`` when defined. It now 610 correctly accepts any mapping. 611 612 .. change:: 613 :tags: engine, asyncio, usecase 614 :tickets: 6832 615 616 Improve the interface used by adapted drivers, like the asyncio ones, 617 to access the actual connection object returned by the driver. 618 619 The :class:`._ConnectionFairy` object has two new attributes: 620 621 * :attr:`._ConnectionFairy.dbapi_connection` always represents a DBAPI 622 compatible object. For pep-249 drivers, this is the DBAPI connection as 623 it always has been, previously accessed under the ``.connection`` 624 attribute. For asyncio drivers that SQLAlchemy adapts into a pep-249 625 interface, the returned object will normally be a SQLAlchemy adaption 626 object called :class:`_engine.AdaptedConnection`. 627 * :attr:`._ConnectionFairy.driver_connection` always represents the actual 628 connection object maintained by the third party pep-249 DBAPI or async 629 driver in use. For standard pep-249 DBAPIs, this will always be the same 630 object as that of the ``dbapi_connection``. For an asyncio driver, it 631 will be the underlying asyncio-only connection object. 632 633 The ``.connection`` attribute remains available and is now a legacy alias 634 of ``.dbapi_connection``. 635 636 .. seealso:: 637 638 :ref:`faq_dbapi_connection` 639 640 641 .. change:: 642 :tags: bug, sql 643 :tickets: 7052 644 645 Implemented missing methods in :class:`_functions.FunctionElement` which, 646 while unused, would lead pylint to report them as unimplemented abstract 647 methods. 648 649 .. change:: 650 :tags: bug, mssql, reflection 651 :tickets: 6910 652 653 Fixed an issue where :meth:`_reflection.has_table` returned 654 ``True`` for local temporary tables that actually belonged to a 655 different SQL Server session (connection). An extra check is now 656 performed to ensure that the temp table detected is in fact owned 657 by the current session. 658 659 .. change:: 660 :tags: bug, engine, regression 661 :tickets: 6913 662 663 Fixed issue where the ability of the 664 :meth:`_events.ConnectionEvents.before_execute` method to alter the SQL 665 statement object passed, returning the new object to be invoked, was 666 inadvertently removed. This behavior has been restored. 667 668 669 .. change:: 670 :tags: bug, engine 671 :tickets: 6958 672 673 Ensure that ``str()`` is called on the an 674 :paramref:`_url.URL.create.password` argument, allowing usage of objects 675 that implement the ``__str__()`` method as password attributes. Also 676 clarified that one such object is not appropriate to dynamically change the 677 password for each database connection; the approaches at 678 :ref:`engines_dynamic_tokens` should be used instead. 679 680 .. change:: 681 :tags: bug, orm, regression 682 :tickets: 6979 683 684 Fixed ORM issue where column expressions passed to ``query()`` or 685 ORM-enabled ``select()`` would be deduplicated on the identity of the 686 object, such as a phrase like ``select(A.id, null(), null())`` would 687 produce only one "NULL" expression, which previously was not the case in 688 1.3. However, the change also allows for ORM expressions to render as given 689 as well, such as ``select(A.data, A.data)`` will produce a result row with 690 two columns. 691 692 .. change:: 693 :tags: bug, engine 694 :tickets: 6983 695 696 Fixed issue in :class:`_engine.URL` where validation of "drivername" would 697 not appropriately respond to the ``None`` value where a string were 698 expected. 699 700 .. change:: 701 :tags: bug, mypy 702 :tickets: 6950 703 704 Fixed issue where mypy plugin would crash when interpreting a 705 ``query_expression()`` construct. 706 707 .. change:: 708 :tags: usecase, sql 709 :tickets: 4123 710 711 Added new parameter :paramref:`_sql.HasCTE.cte.nesting` to the 712 :class:`_sql.CTE` constructor and :meth:`_sql.HasCTE.cte` method, which 713 flags the CTE as one which should remain nested within an enclosing CTE, 714 rather than being moved to the top level of the outermost SELECT. While in 715 the vast majority of cases there is no difference in SQL functionality, 716 users have identified various edge-cases where true nesting of CTE 717 constructs is desirable. Much thanks to Eric Masseran for lots of work on 718 this intricate feature. 719 720 .. change:: 721 :tags: usecase, engine, orm 722 :tickets: 6990 723 724 Added new methods :meth:`_orm.Session.scalars`, 725 :meth:`_engine.Connection.scalars`, :meth:`_asyncio.AsyncSession.scalars` 726 and :meth:`_asyncio.AsyncSession.stream_scalars`, which provide a short cut 727 to the use case of receiving a row-oriented :class:`_result.Result` object 728 and converting it to a :class:`_result.ScalarResult` object via the 729 :meth:`_engine.Result.scalars` method, to return a list of values rather 730 than a list of rows. The new methods are analogous to the long existing 731 :meth:`_orm.Session.scalar` and :meth:`_engine.Connection.scalar` methods 732 used to return a single value from the first row only. Pull request 733 courtesy Miguel Grinberg. 734 735 .. change:: 736 :tags: usecase, orm 737 :tickets: 6955 738 739 Added loader options to :meth:`_orm.Session.merge` and 740 :meth:`_asyncio.AsyncSession.merge` via a new 741 :paramref:`_orm.Session.merge.options` parameter, which will apply the 742 given loader options to the ``get()`` used internally by merge, allowing 743 eager loading of relationships etc. to be applied when the merge process 744 loads a new object. Pull request courtesy Daniel Stone. 745 746 .. change:: 747 :tags: feature, asyncio, mysql 748 :tickets: 6993 749 750 Added initial support for the ``asyncmy`` asyncio database driver for MySQL 751 and MariaDB. This driver is very new, however appears to be the only 752 current alternative to the ``aiomysql`` driver which currently appears to 753 be unmaintained and is not working with current Python versions. Much 754 thanks to long2ice for the pull request for this dialect. 755 756 .. seealso:: 757 758 :ref:`asyncmy` 759 760 .. change:: 761 :tags: bug, asyncio 762 763 Added missing ``**kw`` arguments to the 764 :meth:`_asyncio.AsyncSession.connection` method. 765 766 .. change:: 767 :tags: bug, sql 768 :tickets: 7055 769 770 Fixed a two issues where combinations of ``select()`` and ``join()`` when 771 adapted to form a copy of the element would not completely copy the state 772 of all column objects associated with subqueries. A key problem this caused 773 is that usage of the :meth:`_sql.ClauseElement.params` method (which should 774 probably be moved into a legacy category as it is inefficient and error 775 prone) would leave copies of the old :class:`_sql.BindParameter` objects 776 around, leading to issues in correctly setting the parameters at execution 777 time. 778 779 780 781 .. change:: 782 :tags: bug, orm, regression 783 :tickets: 6924 784 785 Fixed issue in recently repaired ``Query.with_entities()`` method where the 786 flag that determines automatic uniquing for legacy ORM ``Query`` objects 787 only would be set to ``True`` inappropriately in cases where the 788 ``with_entities()`` call would be setting the ``Query`` to return 789 column-only rows, which are not uniqued. 790 791 .. change:: 792 :tags: bug, postgresql 793 :tickets: 6912 794 795 Qualify ``version()`` call to avoid shadowing issues if a different 796 search path is configured by the user. 797 798 .. change:: 799 :tags: bug, engine, postgresql 800 :tickets: 6963 801 802 Fixed issue where an engine that had 803 :paramref:`_sa.create_engine.implicit_returning` set to False would fail to 804 function when PostgreSQL's "fast insertmany" feature were used in 805 conjunction with a ``Sequence``, as well as if any kind of "executemany" 806 with "return_defaults()" were used in conjunction with a ``Sequence``. Note 807 that PostgreSQL "fast insertmany" uses "RETURNING" by definition, when the 808 SQL statement is passed to the driver; overall, the 809 :paramref:`_sa.create_engine.implicit_returning` flag is legacy and has no 810 real use in modern SQLAlchemy, and will be deprecated in a separate change. 811 812 .. change:: 813 :tags: bug, mypy 814 :tickets: 6937 815 816 Fixed issue in mypy plugin where columns on a mixin would not be correctly 817 interpreted if the mapped class relied upon a ``__tablename__`` routine 818 that came from a superclass. 819 820 .. change:: 821 :tags: bug, postgresql 822 :tickets: 6106 823 824 The :class:`_postgresql.ENUM` datatype is PostgreSQL-native and therefore 825 should not be used with the ``native_enum=False`` flag. This flag is now 826 ignored if passed to the :class:`_postgresql.ENUM` datatype and a warning 827 is emitted; previously the flag would cause the type object to fail to 828 function correctly. 829 830 831 .. change:: 832 :tags: bug, sql 833 :tickets: 7036 834 835 Fixed issue related to new :meth:`_sql.HasCTE.add_cte` feature where 836 pairing two "INSERT..FROM SELECT" statements simultaneously would lose 837 track of the two independent SELECT statements, leading to the wrong SQL. 838 839 .. change:: 840 :tags: asyncio, bug 841 :tickets: 6746 842 843 Deprecate usage of :class:`_orm.scoped_session` with asyncio drivers. When 844 using Asyncio the :class:`_asyncio.async_scoped_session` should be used 845 instead. 846 847 .. change:: 848 :tags: bug, platform 849 :tickets: 7024 850 851 Further adjusted the "greenlet" package specifier in setup.cfg to use a 852 long chain of "or" expressions, so that the comparison of 853 ``platform_machine`` to a specific identifier matches only the complete 854 string. 855 856 .. change:: 857 :tags: bug, sqlite 858 859 Fixed bug where the error message for SQLite invalid isolation level on the 860 pysqlite driver would fail to indicate that "AUTOCOMMIT" is one of the 861 valid isolation levels. 862 863 .. change:: 864 :tags: bug, sql 865 :tickets: 7060 866 867 Fixed issue where using ORM column expressions as keys in the list of 868 dictionaries passed to :meth:`_sql.Insert.values` for "multi-valued insert" 869 would not be processed correctly into the correct column expressions. 870 871 .. change:: 872 :tags: asyncio, usecase 873 :tickets: 6746 874 875 The :class:`_asyncio.AsyncSession` now supports overriding which 876 :class:`_orm.Session` it uses as the proxied instance. A custom ``Session`` 877 class can be passed using the :paramref:`.AsyncSession.sync_session_class` 878 parameter or by subclassing the ``AsyncSession`` and specifying a custom 879 :attr:`.AsyncSession.sync_session_class`. 880 881 .. change:: 882 :tags: bug, oracle, performance 883 :tickets: 4486 884 885 Added a CAST(VARCHAR2(128)) to the "table name", "owner", and other 886 DDL-name parameters as used in reflection queries against Oracle system 887 views such as ALL_TABLES, ALL_TAB_CONSTRAINTS, etc to better enable 888 indexing to take place against these columns, as they previously would be 889 implicitly handled as NVARCHAR2 due to Python's use of Unicode for strings; 890 these columns are documented in all Oracle versions as being VARCHAR2 with 891 lengths varying from 30 to 128 characters depending on server version. 892 Additionally, test support has been enabled for Unicode-named DDL 893 structures against Oracle databases. 894 895.. changelog:: 896 :version: 1.4.23 897 :released: August 18, 2021 898 899 .. change:: 900 :tags: bug, sql 901 :tickets: 6752 902 903 Fix issue in :class:`_sql.CTE` where new :meth:`_sql.HasCTE.add_cte` method 904 added in version 1.4.21 / :ticket:`6752` failed to function correctly for 905 "compound select" structures such as :func:`_sql.union`, 906 :func:`_sql.union_all`, :func:`_sql.except`, etc. Pull request courtesy 907 Eric Masseran. 908 909 .. change:: 910 :tags: orm, usecase 911 :tickets: 6808 912 913 Added new attribute :attr:`_sql.Select.columns_clause_froms` that will 914 retrieve the FROM list implied by the columns clause of the 915 :class:`_sql.Select` statement. This differs from the old 916 :attr:`_sql.Select.froms` collection in that it does not perform any ORM 917 compilation steps, which necessarily deannotate the FROM elements and do 918 things like compute joinedloads etc., which makes it not an appropriate 919 candidate for the :meth:`_sql.Select.select_from` method. Additionally adds 920 a new parameter 921 :paramref:`_sql.Select.with_only_columns.maintain_column_froms` that 922 transfers this collection to :meth:`_sql.Select.select_from` before 923 replacing the columns collection. 924 925 In addition, the :attr:`_sql.Select.froms` is renamed to 926 :meth:`_sql.Select.get_final_froms`, to stress that this collection is not 927 a simple accessor and is instead calculated given the full state of the 928 object, which can be an expensive call when used in an ORM context. 929 930 Additionally fixes a regression involving the 931 :func:`_orm.with_only_columns` function to support applying criteria to 932 column elements that were replaced with either 933 :meth:`_sql.Select.with_only_columns` or :meth:`_orm.Query.with_entities` , 934 which had broken as part of :ticket:`6503` released in 1.4.19. 935 936 .. change:: 937 :tags: bug, orm, sql 938 :tickets: 6824 939 940 Fixed issue where a bound parameter object that was "cloned" would cause a 941 name conflict in the compiler, if more than one clone of this parameter 942 were used at the same time in a single statement. This could occur in 943 particular with things like ORM single table inheritance queries that 944 indicated the same "discriminator" value multiple times in one query. 945 946 947 .. change:: 948 :tags: bug, mssql, sql 949 :tickets: 6863 950 951 Fixed issue where the ``literal_binds`` compiler flag, as used externally 952 to render bound parameters inline, would fail to work when used with a 953 certain class of parameters known as "literal_execute", which covers things 954 like LIMIT and OFFSET values for dialects where the drivers don't allow a 955 bound parameter, such as SQL Server's "TOP" clause. The issue locally 956 seemed to affect only the MSSQL dialect. 957 958 .. change:: 959 :tags: bug, orm 960 :tickets: 6869 961 962 Fixed issue in loader strategies where the use of the 963 :meth:`_orm.Load.options` method, particularly when nesting multiple calls, 964 would generate an overly long and more importantly non-deterministic cache 965 key, leading to very large cache keys which were also not allowing 966 efficient cache usage, both in terms of total memory used as well as number 967 of entries used in the cache itself. 968 969 .. change:: 970 :tags: bug, sql 971 :tickets: 6858 972 973 Fixed an issue in the ``CacheKey.to_offline_string()`` method used by the 974 dogpile.caching example where attempting to create a proper cache key from 975 the special "lambda" query generated by the lazy loader would fail to 976 include the parameter values, leading to an incorrect cache key. 977 978 979 .. change:: 980 :tags: bug, orm 981 :tickets: 6887 982 983 Revised the means by which the 984 :attr:`_orm.ORMExecuteState.user_defined_options` accessor receives 985 :class:`_orm.UserDefinedOption` and related option objects from the 986 context, with particular emphasis on the "selectinload" on the loader 987 strategy where this previously was not working; other strategies did not 988 have this problem. The objects that are associated with the current query 989 being executed, and not that of a query being cached, are now propagated 990 unconditionally. This essentially separates them out from the "loader 991 strategy" options which are explicitly associated with the compiled state 992 of a query and need to be used in relation to the cached query. 993 994 The effect of this fix is that a user-defined option, such as those used 995 by the dogpile.caching example as well as for other recipes such as 996 defining a "shard id" for the horizontal sharing extension, will be 997 correctly propagated to eager and lazy loaders regardless of whether 998 a cached query was ultimately invoked. 999 1000 1001 .. change:: 1002 :tags: bug, sql 1003 :tickets: 6886 1004 1005 Adjusted the "from linter" warning feature to accommodate for a chain of 1006 joins more than one level deep where the ON clauses don't explicitly match 1007 up the targets, such as an expression such as "ON TRUE". This mode of use 1008 is intended to cancel the cartesian product warning simply by the fact that 1009 there's a JOIN from "a to b", which was not working for the case where the 1010 chain of joins had more than one element. 1011 1012 .. change:: 1013 :tags: bug, postgresql 1014 :tickets: 6886 1015 1016 Added the "is_comparison" flag to the PostgreSQL "overlaps", 1017 "contained_by", "contains" operators, so that they work in relevant ORM 1018 contexts as well as in conjunction with the "from linter" feature. 1019 1020 .. change:: 1021 :tags: bug, orm 1022 :tickets: 6812 1023 1024 Fixed issue where the unit of work would internally use a 2.0-deprecated 1025 SQL expression form, emitting a deprecation warning when SQLALCHEMY_WARN_20 1026 were enabled. 1027 1028 1029 .. change:: 1030 :tags: bug, orm 1031 :tickets: 6881 1032 1033 Fixed issue in :func:`_orm.selectinload` where use of the new 1034 :meth:`_orm.PropComparator.and_` feature within options that were nested 1035 more than one level deep would fail to update bound parameter values that 1036 were in the nested criteria, as a side effect of SQL statement caching. 1037 1038 1039 .. change:: 1040 :tags: bug, general 1041 :tickets: 6136 1042 1043 The setup requirements have been modified such ``greenlet`` is a default 1044 requirement only for those platforms that are well known for ``greenlet`` 1045 to be installable and for which there is already a pre-built binary on 1046 pypi; the current list is ``x86_64 aarch64 ppc64le amd64 win32``. For other 1047 platforms, greenlet will not install by default, which should enable 1048 installation and test suite running of SQLAlchemy 1.4 on platforms that 1049 don't support ``greenlet``, excluding any asyncio features. In order to 1050 install with the ``greenlet`` dependency included on a machine architecture 1051 outside of the above list, the ``[asyncio]`` extra may be included by 1052 running ``pip install sqlalchemy[asyncio]`` which will then attempt to 1053 install ``greenlet``. 1054 1055 Additionally, the test suite has been repaired so that tests can complete 1056 fully when greenlet is not installed, with appropriate skips for 1057 asyncio-related tests. 1058 1059 .. change:: 1060 :tags: enum, schema 1061 :tickets: 6146 1062 1063 Unify behaviour :class:`_schema.Enum` in native and non-native 1064 implementations regarding the accepted values for an enum with 1065 aliased elements. 1066 When :paramref:`_schema.Enum.omit_aliases` is ``False`` all values, 1067 alias included, are accepted as valid values. 1068 When :paramref:`_schema.Enum.omit_aliases` is ``True`` only non aliased values 1069 are accepted as valid values. 1070 1071 .. change:: 1072 :tags: bug, ext 1073 :tickets: 6816 1074 1075 Fixed issue where the horizontal sharding extension would not correctly 1076 accommodate for a plain textual SQL statement passed to 1077 :meth:`_orm.Session.execute`. 1078 1079 .. change:: 1080 :tags: bug, orm 1081 :tickets: 6889, 6079 1082 1083 Adjusted ORM loader internals to no longer use the "lambda caching" system 1084 that was added in 1.4, as well as repaired one location that was still 1085 using the previous "baked query" system for a query. The lambda caching 1086 system remains an effective way to reduce the overhead of building up 1087 queries that have relatively fixed usage patterns. In the case of loader 1088 strategies, the queries used are responsible for moving through lots of 1089 arbitrary options and criteria, which is both generated and sometimes 1090 consumed by end-user code, that make the lambda cache concept not any more 1091 efficient than not using it, at the cost of more complexity. In particular 1092 the problems noted by :ticket:`6881` and :ticket:`6887` are made are made 1093 considerably less complicated by removing this feature internally. 1094 1095 1096 1097 .. change:: 1098 :tags: bug, orm 1099 :tickets: 6889 1100 1101 Fixed an issue where the :class:`_orm.Bundle` construct would not create 1102 proper cache keys, leading to inefficient use of the query cache. This 1103 had some impact on the "selectinload" strategy and was identified as 1104 part of :ticket:`6889`. 1105 1106 .. change:: 1107 :tags: usecase, mypy 1108 :tickets: 6804, 6759 1109 1110 Added support for SQLAlchemy classes to be defined in user code using 1111 "generic class" syntax as defined by ``sqlalchemy2-stubs``, e.g. 1112 ``Column[String]``, without the need for qualifying these constructs within 1113 a ``TYPE_CHECKING`` block by implementing the Python special method 1114 ``__class_getitem__()``, which allows this syntax to pass without error at 1115 runtime. 1116 1117 .. change:: 1118 :tags: bug, sql 1119 1120 Fixed issue in lambda caching system where an element of a query that 1121 produces no cache key, like a custom option or clause element, would still 1122 populate the expression in the "lambda cache" inappropriately. 1123 1124.. changelog:: 1125 :version: 1.4.22 1126 :released: July 21, 2021 1127 1128 .. change:: 1129 :tags: bug, sql 1130 :tickets: 6786 1131 1132 Fixed issue where use of the :paramref:`_sql.case.whens` parameter passing 1133 a dictionary positionally and not as a keyword argument would emit a 2.0 1134 deprecation warning, referring to the deprecation of passing a list 1135 positionally. The dictionary format of "whens", passed positionally, is 1136 still supported and was accidentally marked as deprecated. 1137 1138 1139 .. change:: 1140 :tags: bug, orm 1141 :tickets: 6775 1142 1143 Fixed issue in new :meth:`_schema.Table.table_valued` method where the 1144 resulting :class:`_sql.TableValuedColumn` construct would not respond 1145 correctly to alias adaptation as is used throughout the ORM, such as for 1146 eager loading, polymorphic loading, etc. 1147 1148 1149 .. change:: 1150 :tags: bug, orm 1151 :tickets: 6769 1152 1153 Fixed issue where usage of the :meth:`_result.Result.unique` method with an 1154 ORM result that included column expressions with unhashable types, such as 1155 ``JSON`` or ``ARRAY`` using non-tuples would silently fall back to using 1156 the ``id()`` function, rather than raising an error. This now raises an 1157 error when the :meth:`_result.Result.unique` method is used in a 2.0 style 1158 ORM query. Additionally, hashability is assumed to be True for result 1159 values of unknown type, such as often happens when using SQL functions of 1160 unknown return type; if values are truly not hashable then the ``hash()`` 1161 itself will raise. 1162 1163 For legacy ORM queries, since the legacy :class:`_orm.Query` object 1164 uniquifies in all cases, the old rules remain in place, which is to use 1165 ``id()`` for result values of unknown type as this legacy uniquing is 1166 mostly for the purpose of uniquing ORM entities and not column values. 1167 1168 .. change:: 1169 :tags: orm, bug 1170 :tickets: 6771 1171 1172 Fixed an issue where clearing of mappers during things like test suite 1173 teardowns could cause a "dictionary changed size" warning during garbage 1174 collection, due to iteration of a weak-referencing dictionary. A ``list()`` 1175 has been applied to prevent concurrent GC from affecting this operation. 1176 1177 .. change:: 1178 :tags: bug, sql 1179 :tickets: 6770 1180 1181 Fixed issue where type-specific bound parameter handlers would not be 1182 called upon in the case of using the :meth:`_sql.Insert.values` method with 1183 the Python ``None`` value; in particular, this would be noticed when using 1184 the :class:`_types.JSON` datatype as well as related PostgreSQL specific 1185 types such as :class:`_postgresql.JSONB` which would fail to encode the 1186 Python ``None`` value into JSON null, however the issue was generalized to 1187 any bound parameter handler in conjunction with this specific method of 1188 :class:`_sql.Insert`. 1189 1190 1191 .. change:: 1192 :tags: bug, engine 1193 :tickets: 6740 1194 1195 Added some guards against ``KeyError`` in the event system to accommodate 1196 the case that the interpreter is shutting down at the same time 1197 :meth:`_engine.Engine.dispose` is being called, which would cause stack 1198 trace warnings. 1199 1200 1201 .. change:: 1202 :tags: bug, orm, regression 1203 :tickets: 6793 1204 1205 Fixed critical caching issue where the ORM's persistence feature using 1206 INSERT..RETURNING would cache an incorrect query when mixing the "bulk 1207 save" and standard "flush" forms of INSERT. 1208 1209.. changelog:: 1210 :version: 1.4.21 1211 :released: July 14, 2021 1212 1213 .. change:: 1214 :tags: usecase, orm 1215 :tickets: 6708 1216 1217 Modified the approach used for history tracking of scalar object 1218 relationships that are not many-to-one, i.e. one-to-one relationships that 1219 would otherwise be one-to-many. When replacing a one-to-one value, the 1220 "old" value that would be replaced is no longer loaded immediately, and is 1221 instead handled during the flush process. This eliminates an historically 1222 troublesome lazy load that otherwise often occurs when assigning to a 1223 one-to-one attribute, and is particularly troublesome when using 1224 "lazy='raise'" as well as asyncio use cases. 1225 1226 This change does cause a behavioral change within the 1227 :meth:`_orm.AttributeEvents.set` event, which is nonetheless currently 1228 documented, which is that the event applied to such a one-to-one attribute 1229 will no longer receive the "old" parameter if it is unloaded and the 1230 :paramref:`_orm.relationship.active_history` flag is not set. As is 1231 documented in :meth:`_orm.AttributeEvents.set`, if the event handler needs 1232 to receive the "old" value when the event fires off, the active_history 1233 flag must be established either with the event listener or with the 1234 relationship. This is already the behavior with other kinds of attributes 1235 such as many-to-one and column value references. 1236 1237 The change additionally will defer updating a backref on the "old" value 1238 in the less common case that the "old" value is locally present in the 1239 session, but isn't loaded on the relationship in question, until the 1240 next flush occurs. If this causes an issue, again the normal 1241 :paramref:`_orm.relationship.active_history` flag can be set to ``True`` 1242 on the relationship. 1243 1244 .. change:: 1245 :tags: usecase, sql 1246 :tickets: 6752 1247 1248 Added new method :meth:`_sql.HasCTE.add_cte` to each of the 1249 :func:`_sql.select`, :func:`_sql.insert`, :func:`_sql.update` and 1250 :func:`_sql.delete` constructs. This method will add the given 1251 :class:`_sql.CTE` as an "independent" CTE of the statement, meaning it 1252 renders in the WITH clause above the statement unconditionally even if it 1253 is not otherwise referenced in the primary statement. This is a popular use 1254 case on the PostgreSQL database where a CTE is used for a DML statement 1255 that runs against database rows independently of the primary statement. 1256 1257 .. change:: 1258 :tags: bug, postgresql 1259 :tickets: 6755 1260 1261 Fixed issue in :meth:`_postgresql.Insert.on_conflict_do_nothing` and 1262 :meth:`_postgresql.Insert.on_conflict_do_update` where the name of a unique 1263 constraint passed as the ``constraint`` parameter would not be properly 1264 truncated for length if it were based on a naming convention that generated 1265 a too-long name for the PostgreSQL max identifier length of 63 characters, 1266 in the same way which occurs within a CREATE TABLE statement. 1267 1268 .. change:: 1269 :tags: bug, sql 1270 :tickets: 6710 1271 1272 Fixed issue in CTE constructs where a recursive CTE that referred to a 1273 SELECT that has duplicate column names, which are typically deduplicated 1274 using labeling logic in 1.4, would fail to refer to the deduplicated label 1275 name correctly within the WITH clause. 1276 1277 .. change:: 1278 :tags: bug, regression, mssql 1279 :tickets: 6697 1280 1281 Fixed regression where the special dotted-schema name handling for the SQL 1282 Server dialect would not function correctly if the dotted schema name were 1283 used within the ``schema_translate_map`` feature. 1284 1285 .. change:: 1286 :tags: orm, regression 1287 :tickets: 6718 1288 1289 Fixed ORM regression where ad-hoc label names generated for hybrid 1290 properties and potentially other similar types of ORM-enabled expressions 1291 would usually be propagated outwards through subqueries, allowing the name 1292 to be retained in the final keys of the result set even when selecting from 1293 subqueries. Additional state is now tracked in this case that isn't lost 1294 when a hybrid is selected out of a Core select / subquery. 1295 1296 1297 .. change:: 1298 :tags: bug, postgresql 1299 :tickets: 6739 1300 1301 Fixed issue where the PostgreSQL ``ENUM`` datatype as embedded in the 1302 ``ARRAY`` datatype would fail to emit correctly in create/drop when the 1303 ``schema_translate_map`` feature were also in use. Additionally repairs a 1304 related issue where the same ``schema_translate_map`` feature would not 1305 work for the ``ENUM`` datatype in combination with a ``CAST``, that's also 1306 intrinsic to how the ``ARRAY(ENUM)`` combination works on the PostgreSQL 1307 dialect. 1308 1309 1310 .. change:: 1311 :tags: bug, sql, regression 1312 :tickets: 6735 1313 1314 Fixed regression where the :func:`_sql.tablesample` construct would fail to 1315 be executable when constructed given a floating-point sampling value not 1316 embedded within a SQL function. 1317 1318 .. change:: 1319 :tags: bug, postgresql 1320 :tickets: 6696 1321 1322 Fixed issue in :meth:`_postgresql.Insert.on_conflict_do_nothing` and 1323 :meth:`_postgresql.Insert.on_conflict_do_update` where the name of a unique 1324 constraint passed as the ``constraint`` parameter would not be properly 1325 quoted if it contained characters which required quoting. 1326 1327 1328 .. change:: 1329 :tags: bug, regression, orm 1330 :tickets: 6698 1331 1332 Fixed regression caused in 1.4.19 due to :ticket:`6503` and related 1333 involving :meth:`_orm.Query.with_entities` where the new structure used 1334 would be inappropriately transferred to an enclosing :class:`_orm.Query` 1335 when making use of set operations such as :meth:`_orm.Query.union`, causing 1336 the JOIN instructions within to be applied to the outside query as well. 1337 1338 .. change:: 1339 :tags: bug, orm, regression 1340 :tickets: 6762 1341 1342 Fixed regression which appeared in version 1.4.3 due to :ticket:`6060` 1343 where rules that limit ORM adaptation of derived selectables interfered 1344 with other ORM-adaptation based cases, in this case when applying 1345 adaptations for a :func:`_orm.with_polymorphic` against a mapping which 1346 uses a :func:`_orm.column_property` which in turn makes use of a scalar 1347 select that includes a :func:`_orm.aliased` object of the mapped table. 1348 1349.. changelog:: 1350 :version: 1.4.20 1351 :released: June 28, 2021 1352 1353 .. change:: 1354 :tags: bug, regression, orm 1355 :tickets: 6680 1356 1357 Fixed regression in ORM regarding an internal reconstitution step for the 1358 :func:`_orm.with_polymorphic` construct, when the user-facing object is 1359 garbage collected as the query is processed. The reconstitution was not 1360 ensuring the sub-entities for the "polymorphic" case were handled, leading 1361 to an ``AttributeError``. 1362 1363 .. change:: 1364 :tags: usecase, sql 1365 :tickets: 6646 1366 1367 Add a impl parameter to :class:`_types.PickleType` constructor, allowing 1368 any arbitrary type to be used in place of the default implementation of 1369 :class:`_types.LargeBinary`. Pull request courtesy jason3gb. 1370 1371 .. change:: 1372 :tags: bug, engine 1373 :tickets: 5348 1374 1375 Fixed an issue in the C extension for the :class:`_result.Row` class which 1376 could lead to a memory leak in the unlikely case of a :class:`_result.Row` 1377 object which referred to an ORM object that then was mutated to refer back 1378 to the ``Row`` itself, creating a cycle. The Python C APIs for tracking GC 1379 cycles has been added to the native :class:`_result.Row` implementation to 1380 accommodate for this case. 1381 1382 1383 .. change:: 1384 :tags: bug, engine 1385 :tickets: 6665 1386 1387 Fixed old issue where a :func:`_sql.select()` made against the token "*", 1388 which then yielded exactly one column, would fail to correctly organize the 1389 ``cursor.description`` column name into the keys of the result object. 1390 1391 1392 1393 .. change:: 1394 :tags: usecase, mysql 1395 :tickets: 6659 1396 1397 Made a small adjustment in the table reflection feature of the MySQL 1398 dialect to accommodate for alternate MySQL-oriented databases such as TiDB 1399 which include their own "comment" directives at the end of a constraint 1400 directive within "CREATE TABLE" where the format doesn't have the 1401 additional space character after the comment, in this case the TiDB 1402 "clustered index" feature. Pull request courtesy Daniël van Eeden. 1403 1404 .. change:: 1405 :tags: bug, schema 1406 :tickets: 6685 1407 1408 Fixed issue where passing ``None`` for the value of 1409 :paramref:`_schema.Table.prefixes` would not store an empty list, but 1410 rather the constant ``None``, which may be unexpected by third party 1411 dialects. The issue is revealed by a usage in recent versions of Alembic 1412 that are passing ``None`` for this value. Pull request courtesy Kai 1413 Mueller. 1414 1415 .. change:: 1416 :tags: bug, regression, ext 1417 :tickets: 6679 1418 1419 Fixed regression in :mod:`sqlalchemy.ext.automap` extension such that the 1420 use case of creating an explicit mapped class to a table that is also the 1421 :paramref:`_orm.relationship.secondary` element of a 1422 :func:`_orm.relationship` that automap will be generating would emit the 1423 "overlaps" warnings introduced in 1.4 and discussed at :ref:`error_qzyx`. 1424 While generating this case from automap is still subject to the same 1425 caveats that the "overlaps" warning refers towards, as automap is intended 1426 for more ad-hoc use cases, the condition which produces the warning is 1427 disabled when a many-to-many relationship with this particular pattern is 1428 generated. 1429 1430 1431 1432 .. change:: 1433 :tags: bug, regression, orm 1434 :tickets: 6678 1435 1436 Adjusted :meth:`_orm.Query.union` and similar set operations to be 1437 correctly compatible with the new capabilities just added in 1438 :ticket:`6661`, with SQLAlchemy 1.4.19, such that the SELECT statements 1439 rendered as elements of the UNION or other set operation will include 1440 directly mapped columns that are mapped as deferred; this both fixes a 1441 regression involving unions with multiple levels of nesting that would 1442 produce a column mismatch, and also allows the :func:`_orm.undefer` option 1443 to be used at the top level of such a :class:`_orm.Query` without having to 1444 apply the option to each of the elements within the UNION. 1445 1446 .. change:: 1447 :tags: bug, sql, orm 1448 :tickets: 6668 1449 1450 Fixed the class hierarchy for the :class:`_schema.Sequence` and the more 1451 general :class:`_schema.DefaultGenerator` base, as these are "executable" 1452 as statements they need to include :class:`_sql.Executable` in their 1453 hierarchy, not just :class:`_roles.StatementRole` as was applied 1454 arbitrarily to :class:`_schema.Sequence` previously. The fix allows 1455 :class:`_schema.Sequence` to work in all ``.execute()`` methods including 1456 with :meth:`_orm.Session.execute` which was not working in the case that a 1457 :meth:`_orm.SessionEvents.do_orm_execute` handler was also established. 1458 1459 1460 .. change:: 1461 :tags: bug, orm 1462 :tickets: 6538 1463 1464 Adjusted the check in the mapper for a callable object that is used as a 1465 ``@validates`` validator function or a ``@reconstructor`` reconstruction 1466 function, to check for "callable" more liberally such as to accommodate 1467 objects based on fundamental attributes like ``__func__`` and 1468 ``__call___``, rather than testing for ``MethodType`` / ``FunctionType``, 1469 allowing things like cython functions to work properly. Pull request 1470 courtesy Miłosz Stypiński. 1471 1472.. changelog:: 1473 :version: 1.4.19 1474 :released: June 22, 2021 1475 1476 .. change:: 1477 :tags: bug, mssql 1478 :tickets: 6658 1479 1480 Fixed bug where the "schema_translate_map" feature would fail to function 1481 correctly in conjunction with an INSERT into a table that has an IDENTITY 1482 column, where the value of the IDENTITY column were specified in the values 1483 of the INSERT thus triggering SQLAlchemy's feature of setting IDENTITY 1484 INSERT to "on"; it's in this directive where the schema translate map would 1485 fail to be honored. 1486 1487 1488 .. change:: 1489 :tags: bug, sql 1490 :tickets: 6663 1491 1492 Fixed issue in CTE constructs mostly relevant to ORM use cases where a 1493 recursive CTE against "anonymous" labels such as those seen in ORM 1494 ``column_property()`` mappings would render in the 1495 ``WITH RECURSIVE xyz(...)`` section as their raw internal label and not a 1496 cleanly anonymized name. 1497 1498 .. change:: 1499 :tags: mssql, change 1500 :tickets: 6503, 6253 1501 1502 Made improvements to the server version regexp used by the pymssql dialect 1503 to prevent a regexp overflow in case of an invalid version string. 1504 1505 .. change:: 1506 :tags: bug, orm, regression 1507 :tickets: 6503, 6253 1508 1509 Fixed further regressions in the same area as that of :ticket:`6052` where 1510 loader options as well as invocations of methods like 1511 :meth:`_orm.Query.join` would fail if the left side of the statement for 1512 which the option/join depends upon were replaced by using the 1513 :meth:`_orm.Query.with_entities` method, or when using 2.0 style queries 1514 when using the :meth:`_sql.Select.with_only_columns` method. A new set of 1515 state has been added to the objects which tracks the "left" entities that 1516 the options / join were made against which is memoized when the lead 1517 entities are changed. 1518 1519 .. change:: 1520 :tags: bug, asyncio, postgresql 1521 :tickets: 6652 1522 1523 Fixed bug in asyncio implementation where the greenlet adaptation system 1524 failed to propagate ``BaseException`` subclasses, most notably including 1525 ``asyncio.CancelledError``, to the exception handling logic used by the 1526 engine to invalidate and clean up the connection, thus preventing 1527 connections from being correctly disposed when a task was cancelled. 1528 1529 1530 1531 .. change:: 1532 :tags: usecase, asyncio 1533 :tickets: 6583 1534 1535 Implemented :class:`_asyncio.async_scoped_session` to address some 1536 asyncio-related incompatibilities between :class:`_orm.scoped_session` and 1537 :class:`_asyncio.AsyncSession`, in which some methods (notably the 1538 :meth:`_asyncio.async_scoped_session.remove` method) should be used with 1539 the ``await`` keyword. 1540 1541 .. seealso:: 1542 1543 :ref:`asyncio_scoped_session` 1544 1545 .. change:: 1546 :tags: usecase, mysql 1547 :tickets: 6132 1548 1549 Added new construct :class:`_mysql.match`, which provides for the full 1550 range of MySQL's MATCH operator including multiple column support and 1551 modifiers. Pull request courtesy Anton Kovalevich. 1552 1553 .. seealso:: 1554 1555 :class:`_mysql.match` 1556 1557 .. change:: 1558 :tags: bug, postgresql, oracle 1559 :tickets: 6649 1560 1561 Fixed issue where the ``INTERVAL`` datatype on PostgreSQL and Oracle would 1562 produce an ``AttributeError`` when used in the context of a comparison 1563 operation against a ``timedelta()`` object. Pull request courtesy 1564 MajorDallas. 1565 1566 .. change:: 1567 :tags: bug, mypy 1568 :tickets: 6476 1569 1570 Fixed issue in mypy plugin where class info for a custom declarative base 1571 would not be handled correctly on a cached mypy pass, leading to an 1572 AssertionError being raised. 1573 1574 .. change:: 1575 :tags: bug, orm 1576 :tickets: 6661 1577 1578 Refined the behavior of ORM subquery rendering with regards to deferred 1579 columns and column properties to be more compatible with that of 1.3 while 1580 also providing for 1.4's newer features. As a subquery in 1.4 does not make 1581 use of loader options, including :func:`_orm.undefer`, a subquery that is 1582 against an ORM entity with deferred attributes will now render those 1583 deferred attributes that refer directly to mapped table columns, as these 1584 are needed in the outer SELECT if that outer SELECT makes use of these 1585 columns; however a deferred attribute that refers to a composed SQL 1586 expression as we normally do with :func:`_orm.column_property` will not be 1587 part of the subquery, as these can be selected explicitly if needed in the 1588 subquery. If the entity is being SELECTed from this subquery, the column 1589 expression can still render on "the outside" in terms of the derived 1590 subquery columns. This produces essentially the same behavior as when 1591 working with 1.3. However in this case the fix has to also make sure that 1592 the ``.selected_columns`` collection of an ORM-enabled :func:`_sql.select` 1593 also follows these rules, which in particular allows recursive CTEs to 1594 render correctly in this scenario, which were previously failing to render 1595 correctly due to this issue. 1596 1597 .. change:: 1598 :tags: bug, postgresql 1599 :tickets: 6621 1600 1601 Fixed issue where the pool "pre ping" feature would implicitly start a 1602 transaction, which would then interfere with custom transactional flags 1603 such as PostgreSQL's "read only" mode when used with the psycopg2 driver. 1604 1605 1606.. changelog:: 1607 :version: 1.4.18 1608 :released: June 10, 2021 1609 1610 .. change:: 1611 :tags: bug, orm 1612 :tickets: 6072, 6487 1613 1614 Clarified the current purpose of the 1615 :paramref:`_orm.relationship.bake_queries` flag, which in 1.4 is to enable 1616 or disable "lambda caching" of statements within the "lazyload" and 1617 "selectinload" loader strategies; this is separate from the more 1618 foundational SQL query cache that is used for most statements. 1619 Additionally, the lazy loader no longer uses its own cache for many-to-one 1620 SQL queries, which was an implementation quirk that doesn't exist for any 1621 other loader scenario. Finally, the "lru cache" warning that the lazyloader 1622 and selectinloader strategies could emit when handling a wide array of 1623 class/relationship combinations has been removed; based on analysis of some 1624 end-user cases, this warning doesn't suggest any significant issue. While 1625 setting ``bake_queries=False`` for such a relationship will remove this 1626 cache from being used, there's no particular performance gain in this case 1627 as using no caching vs. using a cache that needs to refresh often likely 1628 still wins out on the caching being used side. 1629 1630 1631 .. change:: 1632 :tags: bug, asyncio 1633 :tickets: 6575 1634 1635 Fixed an issue that presented itself when using the :class:`_pool.NullPool` 1636 or the :class:`_pool.StaticPool` with an async engine. This mostly affected 1637 the aiosqlite dialect. 1638 1639 .. change:: 1640 :tags: bug, sqlite, regression 1641 :tickets: 6586 1642 1643 The fix for pysqlcipher released in version 1.4.3 :ticket:`5848` was 1644 unfortunately non-working, in that the new ``on_connect_url`` hook was 1645 erroneously not receiving a ``URL`` object under normal usage of 1646 :func:`_sa.create_engine` and instead received a string that was unhandled; 1647 the test suite failed to fully set up the actual conditions under which 1648 this hook is called. This has been fixed. 1649 1650 .. change:: 1651 :tags: bug, postgresql, regression 1652 :tickets: 6581 1653 1654 Fixed regression where using the PostgreSQL "INSERT..ON CONFLICT" structure 1655 would fail to work with the psycopg2 driver if it were used in an 1656 "executemany" context along with bound parameters in the "SET" clause, due 1657 to the implicit use of the psycopg2 fast execution helpers which are not 1658 appropriate for this style of INSERT statement; as these helpers are the 1659 default in 1.4 this is effectively a regression. Additional checks to 1660 exclude this kind of statement from that particular extension have been 1661 added. 1662 1663 .. change:: 1664 :tags: bug, orm, regression 1665 :tickets: 6285 1666 1667 Adjusted the means by which classes such as :class:`_orm.scoped_session` 1668 and :class:`_asyncio.AsyncSession` are generated from the base 1669 :class:`_orm.Session` class, such that custom :class:`_orm.Session` 1670 subclasses such as that used by Flask-SQLAlchemy don't need to implement 1671 positional arguments when they call into the superclass method, and can 1672 continue using the same argument styles as in previous releases. 1673 1674 .. change:: 1675 :tags: bug, orm, regression 1676 :tickets: 6595 1677 1678 Fixed issue where query production for joinedload against a complex left 1679 hand side involving joined-table inheritance could fail to produce a 1680 correct query, due to a clause adaption issue. 1681 1682 .. change:: 1683 :tags: bug, orm, regression, performance 1684 :tickets: 6596 1685 1686 Fixed regression involving how the ORM would resolve a given mapped column 1687 to a result row, where under cases such as joined eager loading, a slightly 1688 more expensive "fallback" could take place to set up this resolution due to 1689 some logic that was removed since 1.3. The issue could also cause 1690 deprecation warnings involving column resolution to be emitted when using a 1691 1.4 style query with joined eager loading. 1692 1693 .. change:: 1694 :tags: bug, orm 1695 :tickets: 6591 1696 1697 Fixed issue in experimental "select ORM objects from INSERT/UPDATE" use 1698 case where an error was raised if the statement were against a 1699 single-table-inheritance subclass. 1700 1701 .. change:: 1702 :tags: bug, asyncio 1703 :tickets: 6592 1704 1705 Added ``asyncio.exceptions.TimeoutError``, 1706 ``asyncio.exceptions.CancelledError`` as so-called "exit exceptions", a 1707 class of exceptions that include things like ``GreenletExit`` and 1708 ``KeyboardInterrupt``, which are considered to be events that warrant 1709 considering a DBAPI connection to be in an unusable state where it should 1710 be recycled. 1711 1712 .. change:: 1713 :tags: bug, orm 1714 :tickets: 6400 1715 1716 The warning that's emitted for :func:`_orm.relationship` when multiple 1717 relationships would overlap with each other as far as foreign key 1718 attributes written towards, now includes the specific "overlaps" argument 1719 to use for each warning in order to silence the warning without changing 1720 the mapping. 1721 1722 .. change:: 1723 :tags: usecase, asyncio 1724 :tickets: 6319 1725 1726 Implemented a new registry architecture that allows the ``Async`` version 1727 of an object, like ``AsyncSession``, ``AsyncConnection``, etc., to be 1728 locatable given the proxied "sync" object, i.e. ``Session``, 1729 ``Connection``. Previously, to the degree such lookup functions were used, 1730 an ``Async`` object would be re-created each time, which was less than 1731 ideal as the identity and state of the "async" object would not be 1732 preserved across calls. 1733 1734 From there, new helper functions :func:`_asyncio.async_object_session`, 1735 :func:`_asyncio.async_session` as well as a new :class:`_orm.InstanceState` 1736 attribute :attr:`_orm.InstanceState.async_session` have been added, which 1737 are used to retrieve the original :class:`_asyncio.AsyncSession` associated 1738 with an ORM mapped object, a :class:`_orm.Session` associated with an 1739 :class:`_asyncio.AsyncSession`, and an :class:`_asyncio.AsyncSession` 1740 associated with an :class:`_orm.InstanceState`, respectively. 1741 1742 This patch also implements new methods 1743 :meth:`_asyncio.AsyncSession.in_nested_transaction`, 1744 :meth:`_asyncio.AsyncSession.get_transaction`, 1745 :meth:`_asyncio.AsyncSession.get_nested_transaction`. 1746 1747.. changelog:: 1748 :version: 1.4.17 1749 :released: May 29, 2021 1750 1751 .. change:: 1752 :tags: bug, orm, regression 1753 :tickets: 6558 1754 1755 Fixed regression caused by just-released performance fix mentioned in #6550 1756 where a query.join() to a relationship could produce an AttributeError if 1757 the query were made against non-ORM structures only, a fairly unusual 1758 calling pattern. 1759 1760.. changelog:: 1761 :version: 1.4.16 1762 :released: May 28, 2021 1763 1764 .. change:: 1765 :tags: bug, engine 1766 :tickets: 6482 1767 1768 Fixed issue where an ``@`` sign in the database portion of a URL would not 1769 be interpreted correctly if the URL also had a username:password section. 1770 1771 1772 .. change:: 1773 :tags: bug, ext 1774 :tickets: 6529 1775 1776 Fixed a deprecation warning that was emitted when using 1777 :func:`_automap.automap_base` without passing an existing 1778 ``Base``. 1779 1780 1781 .. change:: 1782 :tags: bug, pep484 1783 :tickets: 6461 1784 1785 Remove pep484 types from the code. 1786 Current effort is around the stub package, and having typing in 1787 two places makes thing worse, since the types in the SQLAlchemy 1788 source were usually outdated compared to the version in the stubs. 1789 1790 .. change:: 1791 :tags: usecase, mssql 1792 :tickets: 6464 1793 1794 Implemented support for a :class:`_sql.CTE` construct to be used directly 1795 as the target of a :func:`_sql.delete` construct, i.e. "WITH ... AS cte 1796 DELETE FROM cte". This appears to be a useful feature of SQL Server. 1797 1798 .. change:: 1799 :tags: bug, general 1800 :tickets: 6540, 6543 1801 1802 Resolved various deprecation warnings which were appearing as of Python 1803 version 3.10.0b1. 1804 1805 .. change:: 1806 :tags: bug, orm 1807 :tickets: 6471 1808 1809 Fixed issue when using :paramref:`_orm.relationship.cascade_backrefs` 1810 parameter set to ``False``, which per :ref:`change_5150` is set to become 1811 the standard behavior in SQLAlchemy 2.0, where adding the item to a 1812 collection that uniquifies, such as ``set`` or ``dict`` would fail to fire 1813 a cascade event if the object were already associated in that collection 1814 via the backref. This fix represents a fundamental change in the collection 1815 mechanics by introducing a new event state which can fire off for a 1816 collection mutation even if there is no net change on the collection; the 1817 action is now suited using a new event hook 1818 :meth:`_orm.AttributeEvents.append_wo_mutation`. 1819 1820 1821 1822 .. change:: 1823 :tags: bug, orm, regression 1824 :tickets: 6550 1825 1826 Fixed regression involving clause adaption of labeled ORM compound 1827 elements, such as single-table inheritance discriminator expressions with 1828 conditionals or CASE expressions, which could cause aliased expressions 1829 such as those used in ORM join / joinedload operations to not be adapted 1830 correctly, such as referring to the wrong table in the ON clause in a join. 1831 1832 This change also improves a performance bump that was located within the 1833 process of invoking :meth:`_sql.Select.join` given an ORM attribute 1834 as a target. 1835 1836 .. change:: 1837 :tags: bug, orm, regression 1838 :tickets: 6495 1839 1840 Fixed regression where the full combination of joined inheritance, global 1841 with_polymorphic, self-referential relationship and joined loading would 1842 fail to be able to produce a query with the scope of lazy loads and object 1843 refresh operations that also attempted to render the joined loader. 1844 1845 .. change:: 1846 :tags: bug, engine 1847 :tickets: 6329 1848 1849 Fixed a long-standing issue with :class:`.URL` where query parameters 1850 following the question mark would not be parsed correctly if the URL did 1851 not contain a database portion with a backslash. 1852 1853 .. change:: 1854 :tags: bug, sql, regression 1855 :tickets: 6549 1856 1857 Fixed regression in dynamic loader strategy and :func:`_orm.relationship` 1858 overall where the :paramref:`_orm.relationship.order_by` parameter were 1859 stored as a mutable list, which could then be mutated when combined with 1860 additional "order_by" methods used against the dynamic query object, 1861 causing the ORDER BY criteria to continue to grow repetitively. 1862 1863 .. change:: 1864 :tags: bug, orm 1865 :tickets: 6484 1866 1867 Enhanced the bind resolution rules for :meth:`_orm.Session.execute` so that 1868 when a non-ORM statement such as an :func:`_sql.insert` construct 1869 nonetheless is built against ORM objects, to the greatest degree possible 1870 the ORM entity will be used to resolve the bind, such as for a 1871 :class:`_orm.Session` that has a bind map set up on a common superclass 1872 without specific mappers or tables named in the map. 1873 1874 .. change:: 1875 :tags: bug, regression, ext 1876 :tickets: 6390 1877 1878 Fixed regression in the ``sqlalchemy.ext.instrumentation`` extension that 1879 prevented instrumentation disposal from working completely. This fix 1880 includes both a 1.4 regression fix as well as a fix for a related issue 1881 that existed in 1.3 also. As part of this change, the 1882 :class:`sqlalchemy.ext.instrumentation.InstrumentationManager` class now 1883 has a new method ``unregister()``, which replaces the previous method 1884 ``dispose()``, which was not called as of version 1.4. 1885 1886 1887.. changelog:: 1888 :version: 1.4.15 1889 :released: May 11, 2021 1890 1891 .. change:: 1892 :tags: bug, documentation, mysql 1893 :tickets: 5397 1894 1895 Added support for the ``ssl_check_hostname=`` parameter in mysql connection 1896 URIs and updated the mysql dialect documentation regarding secure 1897 connections. Original pull request courtesy of Jerry Zhao. 1898 1899 .. change:: 1900 :tags: bug, orm, regression 1901 :tickets: 6449 1902 1903 Fixed additional regression caused by "eager loaders run on unexpire" 1904 feature :ticket:`1763` where the feature would run for a 1905 ``contains_eager()`` eagerload option in the case that the 1906 ``contains_eager()`` were chained to an additional eager loader option, 1907 which would then produce an incorrect query as the original query-bound 1908 join criteria were no longer present. 1909 1910 .. change:: 1911 :tags: feature, general 1912 :tickets: 6241 1913 1914 A new approach has been applied to the warnings system in SQLAlchemy to 1915 accurately predict the appropriate stack level for each warning 1916 dynamically. This allows evaluating the source of SQLAlchemy-generated 1917 warnings and deprecation warnings to be more straightforward as the warning 1918 will indicate the source line within end-user code, rather than from an 1919 arbitrary level within SQLAlchemy's own source code. 1920 1921 .. change:: 1922 :tags: bug, orm 1923 :tickets: 6459 1924 1925 Fixed issue in subquery loader strategy which prevented caching from 1926 working correctly. This would have been seen in the logs as a "generated" 1927 message instead of "cached" for all subqueryload SQL emitted, which by 1928 saturating the cache with new keys would degrade overall performance; it 1929 also would produce "LRU size alert" warnings. 1930 1931 1932 .. change:: 1933 :tags: bug, sql 1934 :tickets: 6460 1935 1936 Adjusted the logic added as part of :ticket:`6397` in 1.4.12 so that 1937 internal mutation of the :class:`.BindParameter` object occurs within the 1938 clause construction phase as it did before, rather than in the compilation 1939 phase. In the latter case, the mutation still produced side effects against 1940 the incoming construct and additionally could potentially interfere with 1941 other internal mutation routines. 1942 1943.. changelog:: 1944 :version: 1.4.14 1945 :released: May 6, 2021 1946 1947 .. change:: 1948 :tags: bug, regression, orm 1949 :tickets: 6426 1950 1951 Fixed regression involving ``lazy='dynamic'`` loader in conjunction with a 1952 detached object. The previous behavior was that the dynamic loader upon 1953 calling methods like ``.all()`` returns empty lists for detached objects 1954 without error, this has been restored; however a warning is now emitted as 1955 this is not the correct result. Other dynamic loader scenarios correctly 1956 raise ``DetachedInstanceError``. 1957 1958 .. change:: 1959 :tags: bug, regression, sql 1960 :tickets: 6428 1961 1962 Fixed regression caused by the "empty in" change just made in 1963 :ticket:`6397` 1.4.12 where the expression needs to be parenthesized for 1964 the "not in" use case, otherwise the condition will interfere with the 1965 other filtering criteria. 1966 1967 1968 .. change:: 1969 :tags: bug, sql, regression 1970 :tickets: 6436 1971 1972 The :class:`.TypeDecorator` class will now emit a warning when used in SQL 1973 compilation with caching unless the ``.cache_ok`` flag is set to ``True`` 1974 or ``False``. A new class-level attribute :attr:`.TypeDecorator.cache_ok` 1975 may be set which will be used as an indication that all the parameters 1976 passed to the object are safe to be used as a cache key if set to ``True``, 1977 ``False`` means they are not. 1978 1979 .. change:: 1980 :tags: engine, bug, regression 1981 :tickets: 6427 1982 1983 Established a deprecation path for calling upon the 1984 :meth:`_cursor.CursorResult.keys` method for a statement that returns no 1985 rows to provide support for legacy patterns used by the "records" package 1986 as well as any other non-migrated applications. Previously, this would 1987 raise :class:`.ResourceClosedException` unconditionally in the same way as 1988 it does when attempting to fetch rows. While this is the correct behavior 1989 going forward, the :class:`_cursor.LegacyCursorResult` object will now in 1990 this case return an empty list for ``.keys()`` as it did in 1.3, while also 1991 emitting a 2.0 deprecation warning. The :class:`_cursor.CursorResult`, used 1992 when using a 2.0-style "future" engine, will continue to raise as it does 1993 now. 1994 1995 .. change:: 1996 :tags: usecase, engine, orm 1997 :tickets: 6288 1998 1999 Applied consistent behavior to the use case of 2000 calling ``.commit()`` or ``.rollback()`` inside of an existing 2001 ``.begin()`` context manager, with the addition of potentially 2002 emitting SQL within the block subsequent to the commit or rollback. 2003 This change continues upon the change first added in 2004 :ticket:`6155` where the use case of calling "rollback" inside of 2005 a ``.begin()`` contextmanager block was proposed: 2006 2007 * calling ``.commit()`` or ``.rollback()`` will now be allowed 2008 without error or warning within all scopes, including 2009 that of legacy and future :class:`_engine.Engine`, ORM 2010 :class:`_orm.Session`, asyncio :class:`.AsyncEngine`. Previously, 2011 the :class:`_orm.Session` disallowed this. 2012 2013 * The remaining scope of the context manager is then closed; 2014 when the block ends, a check is emitted to see if the transaction 2015 was already ended, and if so the block returns without action. 2016 2017 * It will now raise **an error** if subsequent SQL of any kind 2018 is emitted within the block, **after** ``.commit()`` or 2019 ``.rollback()`` is called. The block should be closed as 2020 the state of the executable object would otherwise be undefined 2021 in this state. 2022 2023.. changelog:: 2024 :version: 1.4.13 2025 :released: May 3, 2021 2026 2027 .. change:: 2028 :tags: bug, regression, orm 2029 :tickets: 6410 2030 2031 Fixed regression in ``selectinload`` loader strategy that would cause it to 2032 cache its internal state incorrectly when handling relationships that join 2033 across more than one column, such as when using a composite foreign key. 2034 The invalid caching would then cause other unrelated loader operations to 2035 fail. 2036 2037 2038 .. change:: 2039 :tags: bug, orm, regression 2040 :tickets: 6414 2041 2042 Fixed regression where :meth:`_orm.Query.filter_by` would not work if the 2043 lead entity were a SQL function or other expression derived from the 2044 primary entity in question, rather than a simple entity or column of that 2045 entity. Additionally, improved the behavior of 2046 :meth:`_sql.Select.filter_by` overall to work with column expressions even 2047 in a non-ORM context. 2048 2049 .. change:: 2050 :tags: bug, engine, regression 2051 :tickets: 6408 2052 2053 Restored a legacy transactional behavior that was inadvertently removed 2054 from the :class:`_engine.Connection` as it was never tested as a known use 2055 case in previous versions, where calling upon the 2056 :meth:`_engine.Connection.begin_nested` method, when no transaction is 2057 present, does not create a SAVEPOINT at all and instead starts an outer 2058 transaction, returning a :class:`.RootTransaction` object instead of a 2059 :class:`.NestedTransaction` object. This :class:`.RootTransaction` then 2060 will emit a real COMMIT on the database connection when committed. 2061 Previously, the 2.0 style behavior was present in all cases that would 2062 autobegin a transaction but not commit it, which is a behavioral change. 2063 2064 When using a :term:`2.0 style` connection object, the behavior is unchanged 2065 from previous 1.4 versions; calling :meth:`_future.Connection.begin_nested` 2066 will "autobegin" the outer transaction if not already present, and then as 2067 instructed emit a SAVEPOINT, returning the :class:`.NestedTransaction` 2068 object. The outer transaction is committed by calling upon 2069 :meth:`_future.Connection.commit`, as is "commit-as-you-go" style usage. 2070 2071 In non-"future" mode, while the old behavior is restored, it also 2072 emits a 2.0 deprecation warning as this is a legacy behavior. 2073 2074 2075 .. change:: 2076 :tags: bug, asyncio, regression 2077 :tickets: 6409 2078 2079 Fixed a regression introduced by :ticket:`6337` that would create an 2080 ``asyncio.Lock`` which could be attached to the wrong loop when 2081 instantiating the async engine before any asyncio loop was started, leading 2082 to an asyncio error message when attempting to use the engine under certain 2083 circumstances. 2084 2085 .. change:: 2086 :tags: bug, orm, regression 2087 :tickets: 6419 2088 2089 Fixed regression where using :func:`_orm.selectinload` and 2090 :func:`_orm.subqueryload` to load a two-level-deep path would lead to an 2091 attribute error. 2092 2093 .. change:: 2094 :tags: bug, orm, regression 2095 :tickets: 6420 2096 2097 Fixed regression where using the :func:`_orm.noload` loader strategy in 2098 conjunction with a "dynamic" relationship would lead to an attribute error 2099 as the noload strategy would attempt to apply itself to the dynamic loader. 2100 2101 .. change:: 2102 :tags: usecase, postgresql 2103 :tickets: 6198 2104 2105 Add support for server side cursors in the pg8000 dialect for PostgreSQL. 2106 This allows use of the 2107 :paramref:`.Connection.execution_options.stream_results` option. 2108 2109.. changelog:: 2110 :version: 1.4.12 2111 :released: April 29, 2021 2112 2113 .. change:: 2114 :tags: bug, orm, regression, caching 2115 :tickets: 6391 2116 2117 Fixed critical regression where bound parameter tracking as used in the SQL 2118 caching system could fail to track all parameters for the case where the 2119 same SQL expression containing a parameter were used in an ORM-related 2120 query using a feature such as class inheritance, which was then embedded in 2121 an enclosing expression which would make use of that same expression 2122 multiple times, such as a UNION. The ORM would individually copy the 2123 individual SELECT statements as part of compilation with class inheritance, 2124 which then embedded in the enclosing statement would fail to accommodate 2125 for all parameters. The logic that tracks this condition has been adjusted 2126 to work for multiple copies of a parameter. 2127 2128 .. change:: 2129 :tags: bug, sql 2130 :tickets: 6258, 6397 2131 2132 Revised the "EMPTY IN" expression to no longer rely upon using a subquery, 2133 as this was causing some compatibility and performance problems. The new 2134 approach for selected databases takes advantage of using a NULL-returning 2135 IN expression combined with the usual "1 != 1" or "1 = 1" expression 2136 appended by AND or OR. The expression is now the default for all backends 2137 other than SQLite, which still had some compatibility issues regarding 2138 tuple "IN" for older SQLite versions. 2139 2140 Third party dialects can still override how the "empty set" expression 2141 renders by implementing a new compiler method 2142 ``def visit_empty_set_op_expr(self, type_, expand_op)``, which takes 2143 precedence over the existing 2144 ``def visit_empty_set_expr(self, element_types)`` which remains in place. 2145 2146 2147 .. change:: 2148 :tags: bug, orm 2149 :tickets: 6350 2150 2151 Fixed two distinct issues mostly affecting 2152 :class:`_hybrid.hybrid_property`, which would come into play under common 2153 mis-configuration scenarios that were silently ignored in 1.3, and now 2154 failed in 1.4, where the "expression" implementation would return a non 2155 :class:`_sql.ClauseElement` such as a boolean value. For both issues, 1.3's 2156 behavior was to silently ignore the mis-configuration and ultimately 2157 attempt to interpret the value as a SQL expression, which would lead to an 2158 incorrect query. 2159 2160 * Fixed issue regarding interaction of the attribute system with 2161 hybrid_property, where if the ``__clause_element__()`` method of the 2162 attribute returned a non-:class:`_sql.ClauseElement` object, an internal 2163 ``AttributeError`` would lead the attribute to return the ``expression`` 2164 function on the hybrid_property itself, as the attribute error was 2165 against the name ``.expression`` which would invoke the ``__getattr__()`` 2166 method as a fallback. This now raises explicitly. In 1.3 the 2167 non-:class:`_sql.ClauseElement` was returned directly. 2168 2169 * Fixed issue in SQL argument coercions system where passing the wrong 2170 kind of object to methods that expect column expressions would fail if 2171 the object were altogether not a SQLAlchemy object, such as a Python 2172 function, in cases where the object were not just coerced into a bound 2173 value. Again 1.3 did not have a comprehensive argument coercion system 2174 so this case would also pass silently. 2175 2176 2177 .. change:: 2178 :tags: bug, orm 2179 :tickets: 6378 2180 2181 Fixed issue where using a :class:`_sql.Select` as a subquery in an ORM 2182 context would modify the :class:`_sql.Select` in place to disable 2183 eagerloads on that object, which would then cause that same 2184 :class:`_sql.Select` to not eagerload if it were then re-used in a 2185 top-level execution context. 2186 2187 2188 .. change:: 2189 :tags: bug, regression, sql 2190 :tickets: 6343 2191 2192 Fixed regression where usage of the :func:`_sql.text` construct inside the 2193 columns clause of a :class:`_sql.Select` construct, which is better handled 2194 by using a :func:`_sql.literal_column` construct, would nonetheless prevent 2195 constructs like :func:`_sql.union` from working correctly. Other use cases, 2196 such as constructing subuqeries, continue to work the same as in prior 2197 versions where the :func:`_sql.text` construct is silently omitted from the 2198 collection of exported columns. Also repairs similar use within the 2199 ORM. 2200 2201 2202 .. change:: 2203 :tags: bug, regression, sql 2204 :tickets: 6261 2205 2206 Fixed regression involving legacy methods such as 2207 :meth:`_sql.Select.append_column` where internal assertions would fail. 2208 2209 .. change:: 2210 :tags: usecase, sqlite 2211 :tickets: 6379 2212 2213 Default to using ``SingletonThreadPool`` for in-memory SQLite databases 2214 created using URI filenames. Previously the default pool used was the 2215 ``NullPool`` that precented sharing the same database between multiple 2216 engines. 2217 2218 .. change:: 2219 :tags: bug, regression, sql 2220 :tickets: 6300 2221 2222 Fixed regression caused by :ticket:`5395` where tuning back the check for 2223 sequences in :func:`_sql.select` now caused failures when doing 2.0-style 2224 querying with a mapped class that also happens to have an ``__iter__()`` 2225 method. Tuned the check some more to accommodate this as well as some other 2226 interesting ``__iter__()`` scenarios. 2227 2228 2229 .. change:: 2230 :tags: bug, mssql, schema 2231 :tickets: 6345 2232 2233 Add :meth:`_types.TypeEngine.as_generic` support for 2234 :class:`sqlalchemy.dialects.mysql.BIT` columns, mapping 2235 them to :class:`_sql.sqltypes.Boolean`. 2236 2237 .. change:: 2238 :tags: bug, orm, regression 2239 :tickets: 6360, 6359 2240 2241 Fixed issue where the new :ref:`autobegin <session_autobegin>` behavior 2242 failed to "autobegin" in the case where an existing persistent object has 2243 an attribute change, which would then impact the behavior of 2244 :meth:`_orm.Session.rollback` in that no snapshot was created to be rolled 2245 back. The "attribute modify" mechanics have been updated to ensure 2246 "autobegin", which does not perform any database work, does occur when 2247 persistent attributes change in the same manner as when 2248 :meth:`_orm.Session.add` is called. This is a regression as in 1.3, the 2249 rollback() method always had a transaction to roll back and would expire 2250 every time. 2251 2252 .. change:: 2253 :tags: bug, mssql, regression 2254 :tickets: 6366 2255 2256 Fixed regression caused by :ticket:`6306` which added support for 2257 ``DateTime(timezone=True)``, where the previous behavior of the pyodbc 2258 driver of implicitly dropping the tzinfo from a timezone-aware date when 2259 INSERTing into a timezone-naive DATETIME column were lost, leading to a SQL 2260 Server error when inserting timezone-aware datetime objects into 2261 timezone-native database columns. 2262 2263 .. change:: 2264 :tags: orm, bug, regression 2265 :tickets: 6386 2266 2267 Fixed regression in ORM where using hybrid property to indicate an 2268 expression from a different entity would confuse the column-labeling logic 2269 in the ORM and attempt to derive the name of the hybrid from that other 2270 class, leading to an attribute error. The owning class of the hybrid 2271 attribute is now tracked along with the name. 2272 2273 .. change:: 2274 :tags: orm, bug, regression 2275 :tickets: 6401 2276 2277 Fixed regression in hybrid_property where a hybrid against a SQL function 2278 would generate an ``AttributeError`` when attempting to generate an entry 2279 for the ``.c`` collection of a subquery in some cases; among other things 2280 this would impact its use in cases like that of ``Query.count()``. 2281 2282 2283 .. change:: 2284 :tags: bug, postgresql 2285 :tickets: 6373 2286 2287 Fixed very old issue where the :class:`_types.Enum` datatype would not 2288 inherit the :paramref:`_schema.MetaData.schema` parameter of a 2289 :class:`_schema.MetaData` object when that object were passed to the 2290 :class:`_types.Enum` using :paramref:`_types.Enum.metadata`. 2291 2292 .. change:: 2293 :tags: bug, orm, dataclasses 2294 :tickets: 6346 2295 2296 Adjusted the declarative scan for dataclasses so that the inheritance 2297 behavior of :func:`_orm.declared_attr` established on a mixin, when using 2298 the new form of having it inside of a ``dataclasses.field()`` construct and 2299 not actually a descriptor attribute on the class, correctly accommodates 2300 the case when the target class to be mapped is a subclass of an existing 2301 mapped class which has already mapped that :func:`_orm.declared_attr`, and 2302 therefore should not be re-applied to this class. 2303 2304 2305 .. change:: 2306 :tags: bug, schema, mysql, mariadb, oracle, postgresql 2307 :tickets: 6338 2308 2309 Ensure that the MySQL and MariaDB dialect ignore the 2310 :class:`_sql.Identity` construct while rendering the ``AUTO_INCREMENT`` 2311 keyword in a create table. 2312 2313 The Oracle and PostgreSQL compiler was updated to not render 2314 :class:`_sql.Identity` if the database version does not support it 2315 (Oracle < 12 and PostgreSQL < 10). Previously it was rendered regardless 2316 of the database version. 2317 2318 .. change:: 2319 :tags: bug, orm 2320 :tickets: 6353 2321 2322 Fixed an issue with the (deprecated in 1.4) 2323 :meth:`_schema.ForeignKeyConstraint.copy` method that caused an error when 2324 invoked with the ``schema`` argument. 2325 2326 .. change:: 2327 :tags: bug, engine 2328 :tickets: 6361 2329 2330 Fixed issue where usage of an explicit :class:`.Sequence` would produce 2331 inconsistent "inline" behavior for an :class:`.Insert` construct that 2332 includes multiple values phrases; the first seq would be inline but 2333 subsequent ones would be "pre-execute", leading to inconsistent sequence 2334 ordering. The sequence expressions are now fully inline. 2335 2336.. changelog:: 2337 :version: 1.4.11 2338 :released: April 21, 2021 2339 2340 .. change:: 2341 :tags: bug, engine, regression 2342 :tickets: 6337 2343 2344 Fixed critical regression caused by the change in :ticket:`5497` where the 2345 connection pool "init" phase no longer occurred within mutexed isolation, 2346 allowing other threads to proceed with the dialect uninitialized, which 2347 could then impact the compilation of SQL statements. 2348 2349 2350 .. change:: 2351 :tags: bug, orm, regression, declarative 2352 :tickets: 6331 2353 2354 Fixed regression where recent changes to support Python dataclasses had the 2355 inadvertent effect that an ORM mapped class could not successfully override 2356 the ``__new__()`` method. 2357 2358.. changelog:: 2359 :version: 1.4.10 2360 :released: April 20, 2021 2361 2362 .. change:: 2363 :tags: bug, declarative, regression 2364 :tickets: 6291 2365 2366 Fixed :func:`_declarative.instrument_declarative` that called 2367 a non existing registry method. 2368 2369 .. change:: 2370 :tags: bug, orm 2371 :tickets: 6320 2372 2373 Fixed bug in new :func:`_orm.with_loader_criteria` feature where using a 2374 mixin class with :func:`_orm.declared_attr` on an attribute that were 2375 accessed inside the custom lambda would emit a warning regarding using an 2376 unmapped declared attr, when the lambda callable were first initialized. 2377 This warning is now prevented using special instrumentation for this 2378 lambda initialization step. 2379 2380 2381 .. change:: 2382 :tags: usecase, mssql 2383 :tickets: 6306 2384 2385 The :paramref:`_types.DateTime.timezone` parameter when set to ``True`` 2386 will now make use of the ``DATETIMEOFFSET`` column type with SQL Server 2387 when used to emit DDL, rather than ``DATETIME`` where the flag was silently 2388 ignored. 2389 2390 .. change:: 2391 :tags: orm, bug, regression 2392 :tickets: 6326 2393 2394 Fixed additional regression caused by the "eagerloaders on refresh" feature 2395 added in :ticket:`1763` where the refresh operation historically would set 2396 ``populate_existing``, which given the new feature now overwrites pending 2397 changes on eagerly loaded objects when autoflush is false. The 2398 populate_existing flag has been turned off for this case and a more 2399 specific method used to ensure the correct attributes refreshed. 2400 2401 .. change:: 2402 :tags: bug, orm, result 2403 :tickets: 6299 2404 2405 Fixed an issue when using 2.0 style execution that prevented using 2406 :meth:`_result.Result.scalar_one` or 2407 :meth:`_result.Result.scalar_one_or_none` after calling 2408 :meth:`_result.Result.unique`, for the case where the ORM is returning a 2409 single-element row in any case. 2410 2411 .. change:: 2412 :tags: bug, sql 2413 :tickets: 6327 2414 2415 Fixed issue in SQL compiler where the bound parameters set up for a 2416 :class:`.Values` construct wouldn't be positionally tracked correctly if 2417 inside of a :class:`_sql.CTE`, affecting database drivers that support 2418 VALUES + ctes and use positional parameters such as SQL Server in 2419 particular as well as asyncpg. The fix also repairs support for 2420 compiler flags such as ``literal_binds``. 2421 2422 .. change:: 2423 :tags: bug, schema 2424 :tickets: 6287 2425 2426 Fixed issue where :func:`_functions.next_value` was not deriving its type 2427 from the corresponding :class:`_schema.Sequence`, instead hardcoded to 2428 :class:`_types.Integer`. The specific numeric type is now used. 2429 2430 .. change:: 2431 :tags: bug, mypy 2432 :tickets: 6255 2433 2434 Fixed issue where mypy plugin would not correctly interpret an explicit 2435 :class:`_orm.Mapped` annotation in conjunction with a 2436 :func:`_orm.relationship` that refers to a class by string name; the 2437 correct annotation would be downgraded to a less specific one leading to 2438 typing errors. 2439 2440 .. change:: 2441 :tags: bug, sql 2442 :tickets: 6256 2443 2444 Repaired and solidified issues regarding custom functions and other 2445 arbitrary expression constructs which within SQLAlchemy's column labeling 2446 mechanics would seek to use ``str(obj)`` to get a string representation to 2447 use as an anonymous column name in the ``.c`` collection of a subquery. 2448 This is a very legacy behavior that performs poorly and leads to lots of 2449 issues, so has been revised to no longer perform any compilation by 2450 establishing specific methods on :class:`.FunctionElement` to handle this 2451 case, as SQL functions are the only use case that it came into play. An 2452 effect of this behavior is that an unlabeled column expression with no 2453 derivable name will be given an arbitrary label starting with the prefix 2454 ``"_no_label"`` in the ``.c`` collection of a subquery; these were 2455 previously being represented either as the generic stringification of that 2456 expression, or as an internal symbol. 2457 2458 .. change:: 2459 :tags: usecase, orm 2460 :ticketS: 6301 2461 2462 Altered some of the behavior repaired in :ticket:`6232` where the 2463 ``immediateload`` loader strategy no longer goes into recursive loops; the 2464 modification is that an eager load (joinedload, selectinload, or 2465 subqueryload) from A->bs->B which then states ``immediateload`` for a 2466 simple manytoone B->a->A that's in the identity map will populate the B->A, 2467 so that this attribute is back-populated when the collection of A/A.bs are 2468 loaded. This allows the objects to be functional when detached. 2469 2470 2471.. changelog:: 2472 :version: 1.4.9 2473 :released: April 17, 2021 2474 2475 .. change:: 2476 :tags: bug, sql, regression 2477 :tickets: 6290 2478 2479 Fixed regression where an empty in statement on a tuple would result 2480 in an error when compiled with the option ``literal_binds=True``. 2481 2482 .. change:: 2483 :tags: bug, regression, orm, performance, sql 2484 :tickets: 6304 2485 2486 Fixed a critical performance issue where the traversal of a 2487 :func:`_sql.select` construct would traverse a repetitive product of the 2488 represented FROM clauses as they were each referred towards by columns in 2489 the columns clause; for a series of nested subqueries with lots of columns 2490 this could cause a large delay and significant memory growth. This 2491 traversal is used by a wide variety of SQL and ORM functions, including by 2492 the ORM :class:`_orm.Session` when it's configured to have 2493 "table-per-bind", which while this is not a common use case, it seems to be 2494 what Flask-SQLAlchemy is hardcoded as using, so the issue impacts 2495 Flask-SQLAlchemy users. The traversal has been repaired to uniqify on FROM 2496 clauses which was effectively what would happen implicitly with the pre-1.4 2497 architecture. 2498 2499 .. change:: 2500 :tags: bug, postgresql, sql, regression 2501 :tickets: 6303 2502 2503 Fixed an argument error in the default and PostgreSQL compilers that 2504 would interfere with an UPDATE..FROM or DELETE..FROM..USING statement 2505 that was then SELECTed from as a CTE. 2506 2507 .. change:: 2508 :tags: bug, orm, regression 2509 :tickets: 6272 2510 2511 Fixed regression where an attribute that is mapped to a 2512 :func:`_orm.synonym` could not be used in column loader options such as 2513 :func:`_orm.load_only`. 2514 2515 .. change:: 2516 :tags: usecase, orm 2517 :tickets: 6267 2518 2519 Established support for :func:`_orm.synoynm` in conjunction with 2520 hybrid property, assocaitionproxy is set up completely, including that 2521 synonyms can be established linking to these constructs which work 2522 fully. This is a behavior that was semi-explicitly disallowed previously, 2523 however since it did not fail in every scenario, explicit support 2524 for assoc proxy and hybrids has been added. 2525 2526 2527.. changelog:: 2528 :version: 1.4.8 2529 :released: April 15, 2021 2530 2531 .. change:: 2532 :tags: change, mypy 2533 2534 Updated Mypy plugin to only use the public plugin interface of the 2535 semantic analyzer. 2536 2537 .. change:: 2538 :tags: bug, mssql, regression 2539 :tickets: 6265 2540 2541 Fixed an additional regression in the same area as that of :ticket:`6173`, 2542 :ticket:`6184`, where using a value of 0 for OFFSET in conjunction with 2543 LIMIT with SQL Server would create a statement using "TOP", as was the 2544 behavior in 1.3, however due to caching would then fail to respond 2545 accordingly to other values of OFFSET. If the "0" wasn't first, then it 2546 would be fine. For the fix, the "TOP" syntax is now only emitted if the 2547 OFFSET value is omitted entirely, that is, :meth:`_sql.Select.offset` is 2548 not used. Note that this change now requires that if the "with_ties" or 2549 "percent" modifiers are used, the statement can't specify an OFFSET of 2550 zero, it now needs to be omitted entirely. 2551 2552 .. change:: 2553 :tags: bug, engine 2554 2555 The :meth:`_engine.Dialect.has_table` method now raises an informative 2556 exception if a non-Connection is passed to it, as this incorrect behavior 2557 seems to be common. This method is not intended for external use outside 2558 of a dialect. Please use the :meth:`.Inspector.has_table` method 2559 or for cross-compatibility with older SQLAlchemy versions, the 2560 :meth:`_engine.Engine.has_table` method. 2561 2562 2563 .. change:: 2564 :tags: bug, regression, sql 2565 :tickets: 6249 2566 2567 Fixed regression where the :class:`_sql.BindParameter` object would not 2568 properly render for an IN expression (i.e. using the "post compile" feature 2569 in 1.4) if the object were copied from either an internal cloning 2570 operation, or from a pickle operation, and the parameter name contained 2571 spaces or other special characters. 2572 2573 .. change:: 2574 :tags: bug, mypy 2575 :tickets: 6205 2576 2577 Revised the fix for ``OrderingList`` from version 1.4.7 which was testing 2578 against the incorrect API. 2579 2580 .. change:: 2581 :tags: bug, asyncio 2582 :tickets: 6220 2583 2584 Fix typo that prevented setting the ``bind`` attribute of an 2585 :class:`_asyncio.AsyncSession` to the correct value. 2586 2587 .. change:: 2588 :tags: feature, sql 2589 :tickets: 3314 2590 2591 The tuple returned by :attr:`.CursorResult.inserted_primary_key` is now a 2592 :class:`_result.Row` object with a named tuple interface on top of the 2593 existing tuple interface. 2594 2595 2596 2597 2598 .. change:: 2599 :tags: bug, regression, sql, sqlite 2600 :tickets: 6254 2601 2602 Fixed regression where the introduction of the INSERT syntax "INSERT... 2603 VALUES (DEFAULT)" was not supported on some backends that do however 2604 support "INSERT..DEFAULT VALUES", including SQLite. The two syntaxes are 2605 now each individually supported or non-supported for each dialect, for 2606 example MySQL supports "VALUES (DEFAULT)" but not "DEFAULT VALUES". 2607 Support for Oracle has also been enabled. 2608 2609 .. change:: 2610 :tags: bug, regression, orm 2611 :tickets: 6259 2612 2613 Fixed a cache leak involving the :func:`_orm.with_expression` loader 2614 option, where the given SQL expression would not be correctly considered as 2615 part of the cache key. 2616 2617 Additionally, fixed regression involving the corresponding 2618 :func:`_orm.query_expression` feature. While the bug technically exists in 2619 1.3 as well, it was not exposed until 1.4. The "default expr" value of 2620 ``null()`` would be rendered when not needed, and additionally was also not 2621 adapted correctly when the ORM rewrites statements such as when using 2622 joined eager loading. The fix ensures "singleton" expressions like ``NULL`` 2623 and ``true`` aren't "adapted" to refer to columns in ORM statements, and 2624 additionally ensures that a :func:`_orm.query_expression` with no default 2625 expression doesn't render in the statement if a 2626 :func:`_orm.with_expression` isn't used. 2627 2628 .. change:: 2629 :tags: bug, orm 2630 :tickets: 6252 2631 2632 Fixed issue in the new feature of :meth:`_orm.Session.refresh` introduced 2633 by :ticket:`1763` where eagerly loaded relationships are also refreshed, 2634 where the ``lazy="raise"`` and ``lazy="raise_on_sql"`` loader strategies 2635 would interfere with the :func:`_orm.immediateload` loader strategy, thus 2636 breaking the feature for relationships that were loaded with 2637 :func:`_orm.selectinload`, :func:`_orm.subqueryload` as well. 2638 2639.. changelog:: 2640 :version: 1.4.7 2641 :released: April 9, 2021 2642 2643 .. change:: 2644 :tags: bug, sql, regression 2645 :tickets: 6222 2646 2647 Enhanced the "expanding" feature used for :meth:`_sql.ColumnOperators.in_` 2648 operations to infer the type of expression from the right hand list of 2649 elements, if the left hand side does not have any explicit type set up. 2650 This allows the expression to support stringification among other things. 2651 In 1.3, "expanding" was not automatically used for 2652 :meth:`_sql.ColumnOperators.in_` expressions, so in that sense this change 2653 fixes a behavioral regression. 2654 2655 2656 .. change:: 2657 :tags: bug, mypy 2658 2659 Fixed issue in Mypy plugin where the plugin wasn’t inferring the correct 2660 type for columns of subclasses that don’t directly descend from 2661 ``TypeEngine``, in particular that of ``TypeDecorator`` and 2662 ``UserDefinedType``. 2663 2664 .. change:: 2665 :tags: bug, orm, regression 2666 :tickets: 6221 2667 2668 Fixed regression where the :func:`_orm.subqueryload` loader strategy would 2669 fail to correctly accommodate sub-options, such as a :func:`_orm.defer` 2670 option on a column, if the "path" of the subqueryload were more than one 2671 level deep. 2672 2673 2674 .. change:: 2675 :tags: bug, sql 2676 2677 Fixed the "stringify" compiler to support a basic stringification 2678 of a "multirow" INSERT statement, i.e. one with multiple tuples 2679 following the VALUES keyword. 2680 2681 2682 .. change:: 2683 :tags: bug, orm, regression 2684 :tickets: 6211 2685 2686 Fixed regression where the :func:`_orm.merge_frozen_result` function relied 2687 upon by the dogpile.caching example was not included in tests and began 2688 failing due to incorrect internal arguments. 2689 2690 .. change:: 2691 :tags: bug, engine, regression 2692 :tickets: 6218 2693 2694 Fixed up the behavior of the :class:`_result.Row` object when dictionary 2695 access is used upon it, meaning converting to a dict via ``dict(row)`` or 2696 accessing members using strings or other objects i.e. ``row["some_key"]`` 2697 works as it would with a dictionary, rather than raising ``TypeError`` as 2698 would be the case with a tuple, whether or not the C extensions are in 2699 place. This was originally supposed to emit a 2.0 deprecation warning for 2700 the "non-future" case using :class:`_result.LegacyRow`, and was to raise 2701 ``TypeError`` for the "future" :class:`_result.Row` class. However, the C 2702 version of :class:`_result.Row` was failing to raise this ``TypeError``, 2703 and to complicate matters, the :meth:`_orm.Session.execute` method now 2704 returns :class:`_result.Row` in all cases to maintain consistency with the 2705 ORM result case, so users who didn't have C extensions installed would 2706 see different behavior in this one case for existing pre-1.4 style 2707 code. 2708 2709 Therefore, in order to soften the overall upgrade scheme as most users have 2710 not been exposed to the more strict behavior of :class:`_result.Row` up 2711 through 1.4.6, :class:`_result.LegacyRow` and :class:`_result.Row` both 2712 provide for string-key access as well as support for ``dict(row)``, in all 2713 cases emitting the 2.0 deprecation warning when ``SQLALCHEMY_WARN_20`` is 2714 enabled. The :class:`_result.Row` object still uses tuple-like behavior for 2715 ``__contains__``, which is probably the only noticeable behavioral change 2716 compared to :class:`_result.LegacyRow`, other than the removal of 2717 dictionary-style methods ``values()`` and ``items()``. 2718 2719 .. change:: 2720 :tags: bug, regression, orm 2721 :tickets: 6233 2722 2723 Fixed critical regression where the :class:`_orm.Session` could fail to 2724 "autobegin" a new transaction when a flush occurred without an existing 2725 transaction in place, implicitly placing the :class:`_orm.Session` into 2726 legacy autocommit mode which commit the transaction. The 2727 :class:`_orm.Session` now has a check that will prevent this condition from 2728 occurring, in addition to repairing the flush issue. 2729 2730 Additionally, scaled back part of the change made as part of :ticket:`5226` 2731 which can run autoflush during an unexpire operation, to not actually 2732 do this in the case of a :class:`_orm.Session` using legacy 2733 :paramref:`_orm.Session.autocommit` mode, as this incurs a commit within 2734 a refresh operation. 2735 2736 .. change:: 2737 :tags: change, tests 2738 2739 Added a new flag to :class:`.DefaultDialect` called ``supports_schemas``; 2740 third party dialects may set this flag to ``False`` to disable SQLAlchemy's 2741 schema-level tests when running the test suite for a third party dialect. 2742 2743 .. change:: 2744 :tags: bug, regression, schema 2745 :tickets: 6216 2746 2747 Fixed regression where usage of a token in the 2748 :paramref:`_engine.Connection.execution_options.schema_translate_map` 2749 dictionary which contained special characters such as braces would fail to 2750 be substituted properly. Use of square bracket characters ``[]`` is now 2751 explicitly disallowed as these are used as a delimiter character in the 2752 current implementation. 2753 2754 .. change:: 2755 :tags: bug, regression, orm 2756 :tickets: 6215 2757 2758 Fixed regression where the ORM compilation scheme would assume the function 2759 name of a hybrid property would be the same as the attribute name in such a 2760 way that an ``AttributeError`` would be raised, when it would attempt to 2761 determine the correct name for each element in a result tuple. A similar 2762 issue exists in 1.3 but only impacts the names of tuple rows. The fix here 2763 adds a check that the hybrid's function name is actually present in the 2764 ``__dict__`` of the class or its superclasses before assigning this name; 2765 otherwise, the hybrid is considered to be "unnamed" and ORM result tuples 2766 will use the naming scheme of the underlying expression. 2767 2768 .. change:: 2769 :tags: bug, orm, regression 2770 :tickets: 6232 2771 2772 Fixed critical regression caused by the new feature added as part of 2773 :ticket:`1763`, eager loaders are invoked on unexpire operations. The new 2774 feature makes use of the "immediateload" eager loader strategy as a 2775 substitute for a collection loading strategy, which unlike the other 2776 "post-load" strategies was not accommodating for recursive invocations 2777 between mutually-dependent relationships, leading to recursion overflow 2778 errors. 2779 2780 2781.. changelog:: 2782 :version: 1.4.6 2783 :released: April 6, 2021 2784 2785 .. change:: 2786 :tags: bug, sql, regression, oracle, mssql 2787 :tickets: 6202 2788 2789 Fixed further regressions in the same area as that of :ticket:`6173` released in 2790 1.4.5, where a "postcompile" parameter, again most typically those used for 2791 LIMIT/OFFSET rendering in Oracle and SQL Server, would fail to be processed 2792 correctly if the same parameter rendered in multiple places in the 2793 statement. 2794 2795 2796 2797 .. change:: 2798 :tags: bug, orm, regression 2799 :tickets: 6203 2800 2801 Fixed regression where a deprecated form of :meth:`_orm.Query.join` were 2802 used, passing a series of entities to join from without any ON clause in a 2803 single :meth:`_orm.Query.join` call, would fail to function correctly. 2804 2805 .. change:: 2806 :tags: bug, mypy 2807 :tickets: 6147 2808 2809 Applied a series of refactorings and fixes to accommodate for Mypy 2810 "incremental" mode across multiple files, which previously was not taken 2811 into account. In this mode the Mypy plugin has to accommodate Python 2812 datatypes expressed in other files coming in with less information than 2813 they have on a direct run. 2814 2815 Additionally, a new decorator :func:`_orm.declarative_mixin` is added, 2816 which is necessary for the Mypy plugin to be able to definifitely identify 2817 a Declarative mixin class that is otherwise not used inside a particular 2818 Python file. 2819 2820 .. seealso:: 2821 2822 :ref:`mypy_declarative_mixins` 2823 2824 2825 .. change:: 2826 :tags: bug, mypy 2827 :tickets: 6205 2828 2829 Fixed issue where the Mypy plugin would fail to interpret the 2830 "collection_class" of a relationship if it were a callable and not a class. 2831 Also improved type matching and error reporting for collection-oriented 2832 relationships. 2833 2834 2835 .. change:: 2836 :tags: bug, sql 2837 :tickets: 6204 2838 2839 Executing a :class:`_sql.Subquery` using :meth:`_engine.Connection.execute` 2840 is deprecated and will emit a deprecation warning; this use case was an 2841 oversight that should have been removed from 1.4. The operation will now 2842 execute the underlying :class:`_sql.Select` object directly for backwards 2843 compatibility. Similarly, the :class:`_sql.CTE` class is also not 2844 appropriate for execution. In 1.3, attempting to execute a CTE would result 2845 in an invalid "blank" SQL statement being executed; since this use case was 2846 not working it now raises :class:`_exc.ObjectNotExecutableError`. 2847 Previously, 1.4 was attempting to execute the CTE as a statement however it 2848 was working only erratically. 2849 2850 .. change:: 2851 :tags: bug, regression, orm 2852 :tickets: 6206 2853 2854 Fixed critical regression where the :meth:`_orm.Query.yield_per` method in 2855 the ORM would set up the internal :class:`_engine.Result` to yield chunks 2856 at a time, however made use of the new :meth:`_engine.Result.unique` method 2857 which uniques across the entire result. This would lead to lost rows since 2858 the ORM is using ``id(obj)`` as the uniquing function, which leads to 2859 repeated identifiers for new objects as already-seen objects are garbage 2860 collected. 1.3's behavior here was to "unique" across each chunk, which 2861 does not actually produce "uniqued" results when results are yielded in 2862 chunks. As the :meth:`_orm.Query.yield_per` method is already explicitly 2863 disallowed when joined eager loading is in place, which is the primary 2864 rationale for the "uniquing" feature, the "uniquing" feature is now turned 2865 off entirely when :meth:`_orm.Query.yield_per` is used. 2866 2867 This regression only applies to the legacy :class:`_orm.Query` object; when 2868 using :term:`2.0 style` execution, "uniquing" is not automatically applied. 2869 To prevent the issue from arising from explicit use of 2870 :meth:`_engine.Result.unique`, an error is now raised if rows are fetched 2871 from a "uniqued" ORM-level :class:`_engine.Result` if any 2872 :ref:`yield per <orm_queryguide_yield_per>` API is also in use, as the 2873 purpose of ``yield_per`` is to allow for arbitrarily large numbers of rows, 2874 which cannot be uniqued in memory without growing the number of entries to 2875 fit the complete result size. 2876 2877 2878 .. change:: 2879 :tags: usecase, asyncio, postgresql 2880 :tickets: 6199 2881 2882 Added accessors ``.sqlstate`` and synonym ``.pgcode`` to the ``.orig`` 2883 attribute of the SQLAlchemy exception class raised by the asyncpg DBAPI 2884 adapter, that is, the intermediary exception object that wraps on top of 2885 that raised by the asyncpg library itself, but below the level of the 2886 SQLAlchemy dialect. 2887 2888.. changelog:: 2889 :version: 1.4.5 2890 :released: April 2, 2021 2891 2892 .. change:: 2893 :tags: bug, sql, postgresql 2894 :tickets: 6183 2895 2896 Fixed bug in new :meth:`_functions.FunctionElement.render_derived` feature 2897 where column names rendered out explicitly in the alias SQL would not have 2898 proper quoting applied for case sensitive names and other non-alphanumeric 2899 names. 2900 2901 .. change:: 2902 :tags: bug, regression, orm 2903 :tickets: 6172 2904 2905 Fixed regression where the :func:`_orm.joinedload` loader strategy would 2906 not successfully joinedload to a mapper that is mapper against a 2907 :class:`.CTE` construct. 2908 2909 .. change:: 2910 :tags: bug, regression, sql 2911 :tickets: 6181 2912 2913 Fixed regression where use of the :meth:`.Operators.in_` method with a 2914 :class:`_sql.Select` object against a non-table-bound column would produce 2915 an ``AttributeError``, or more generally using a :class:`_sql.ScalarSelect` 2916 that has no datatype in a binary expression would produce invalid state. 2917 2918 2919 .. change:: 2920 :tags: bug, mypy 2921 :tickets: sqlalchemy/sqlalchemy2-stubs/#14 2922 2923 Fixed issue in mypy plugin where newly added support for 2924 :func:`_orm.as_declarative` needed to more fully add the 2925 ``DeclarativeMeta`` class to the mypy interpreter's state so that it does 2926 not result in a name not found error; additionally improves how global 2927 names are setup for the plugin including the ``Mapped`` name. 2928 2929 2930 .. change:: 2931 :tags: bug, mysql, regression 2932 :tickets: 6163 2933 2934 Fixed regression in the MySQL dialect where the reflection query used to 2935 detect if a table exists would fail on very old MySQL 5.0 and 5.1 versions. 2936 2937 .. change:: 2938 :tags: bug, sql 2939 :tickets: 6184 2940 2941 Added a new flag to the :class:`_engine.Dialect` class called 2942 :attr:`_engine.Dialect.supports_statement_cache`. This flag now needs to be present 2943 directly on a dialect class in order for SQLAlchemy's 2944 :ref:`query cache <sql_caching>` to take effect for that dialect. The 2945 rationale is based on discovered issues such as :ticket:`6173` revealing 2946 that dialects which hardcode literal values from the compiled statement, 2947 often the numerical parameters used for LIMIT / OFFSET, will not be 2948 compatible with caching until these dialects are revised to use the 2949 parameters present in the statement only. For third party dialects where 2950 this flag is not applied, the SQL logging will show the message "dialect 2951 does not support caching", indicating the dialect should seek to apply this 2952 flag once they have verified that no per-statement literal values are being 2953 rendered within the compilation phase. 2954 2955 .. seealso:: 2956 2957 :ref:`engine_thirdparty_caching` 2958 2959 .. change:: 2960 :tags: bug, postgresql 2961 :tickets: 6099 2962 2963 Fixed typo in the fix for :ticket:`6099` released in 1.4.4 that completely 2964 prevented this change from working correctly, i.e. the error message did not match 2965 what was actually emitted by pg8000. 2966 2967 .. change:: 2968 :tags: bug, orm, regression 2969 :tickets: 6171 2970 2971 Scaled back the warning message added in :ticket:`5171` to not warn for 2972 overlapping columns in an inheritance scenario where a particular 2973 relationship is local to a subclass and therefore does not represent an 2974 overlap. 2975 2976 .. change:: 2977 :tags: bug, regression, oracle 2978 :tickets: 6173 2979 2980 Fixed critical regression where the Oracle compiler would not maintain the 2981 correct parameter values in the LIMIT/OFFSET for a select due to a caching 2982 issue. 2983 2984 2985 .. change:: 2986 :tags: bug, postgresql 2987 :tickets: 6170 2988 2989 Fixed issue where the PostgreSQL :class:`.PGInspector`, when generated 2990 against an :class:`_engine.Engine`, would fail for ``.get_enums()``, 2991 ``.get_view_names()``, ``.get_foreign_table_names()`` and 2992 ``.get_table_oid()`` when used against a "future" style engine and not the 2993 connection directly. 2994 2995 .. change:: 2996 :tags: bug, schema 2997 :tickets: 6146 2998 2999 Introduce a new parameter :paramref:`_types.Enum.omit_aliases` in 3000 :class:`_types.Enum` type allow filtering aliases when using a pep435 Enum. 3001 Previous versions of SQLAlchemy kept aliases in all cases, creating 3002 database enum type with additional states, meaning that they were treated 3003 as different values in the db. For backward compatibility this flag 3004 defaults to ``False`` in the 1.4 series, but will be switched to ``True`` 3005 in a future version. A deprecation warning is raise if this flag is not 3006 specified and the passed enum contains aliases. 3007 3008 .. change:: 3009 :tags: bug, mssql 3010 :tickets: 6163 3011 3012 Fixed a regression in MSSQL 2012+ that prevented the order by clause 3013 to be rendered when ``offset=0`` is used in a subquery. 3014 3015 .. change:: 3016 :tags: bug, asyncio 3017 :tickets: 6166 3018 3019 3020 Fixed issue where the asyncio extension could not be loaded 3021 if running Python 3.6 with the backport library of 3022 ``contextvars`` installed. 3023 3024.. changelog:: 3025 :version: 1.4.4 3026 :released: March 30, 2021 3027 3028 .. change:: 3029 :tags: bug, misc 3030 3031 Adjusted the usage of the ``importlib_metadata`` library for loading 3032 setuptools entrypoints in order to accommodate for some deprecation 3033 changes. 3034 3035 3036 .. change:: 3037 :tags: bug, postgresql 3038 :tickets: 6099 3039 3040 Modified the ``is_disconnect()`` handler for the pg8000 dialect, which now 3041 accommodates for a new ``InterfaceError`` emitted by pg8000 1.19.0. Pull 3042 request courtesy Hamdi Burak Usul. 3043 3044 3045 .. change:: 3046 :tags: bug, orm 3047 :tickets: 6139 3048 3049 Fixed critical issue in the new :meth:`_orm.PropComparator.and_` feature 3050 where loader strategies that emit secondary SELECT statements such as 3051 :func:`_orm.selectinload` and :func:`_orm.lazyload` would fail to 3052 accommodate for bound parameters in the user-defined criteria in terms of 3053 the current statement being executed, as opposed to the cached statement, 3054 causing stale bound values to be used. 3055 3056 This also adds a warning for the case where an object that uses 3057 :func:`_orm.lazyload` in conjunction with :meth:`_orm.PropComparator.and_` 3058 is attempted to be serialized; the loader criteria cannot reliably 3059 be serialized and deserialized and eager loading should be used for this 3060 case. 3061 3062 3063 .. change:: 3064 :tags: bug, engine 3065 :tickets: 6138 3066 3067 Repair wrong arguments to exception handling method 3068 in CursorResult. 3069 3070 .. change:: 3071 :tags: bug, regression, orm 3072 :tickets: 6144 3073 3074 Fixed missing method :meth:`_orm.Session.get` from the 3075 :class:`_orm.ScopedSession` interface. 3076 3077 3078 .. change:: 3079 :tags: usecase, engine 3080 :tickets: 6155 3081 3082 Modified the context manager used by :class:`_engine.Transaction` so that 3083 an "already detached" warning is not emitted by the ending of the context 3084 manager itself, if the transaction were already manually rolled back inside 3085 the block. This applies to regular transactions, savepoint transactions, 3086 and legacy "marker" transactions. A warning is still emitted if the 3087 ``.rollback()`` method is called explicitly more than once. 3088 3089.. changelog:: 3090 :version: 1.4.3 3091 :released: March 25, 2021 3092 3093 .. change:: 3094 :tags: bug, orm 3095 :tickets: 6069 3096 3097 Fixed a bug where python 2.7.5 (default on CentOS 7) wasn't able to import 3098 sqlalchemy, because on this version of Python ``exec "statement"`` and 3099 ``exec("statement")`` do not behave the same way. The compatibility 3100 ``exec_()`` function was used instead. 3101 3102 .. change:: 3103 :tags: sqlite, feature, asyncio 3104 :tickets: 5920 3105 3106 Added support for the aiosqlite database driver for use with the 3107 SQLAlchemy asyncio extension. 3108 3109 .. seealso:: 3110 3111 :ref:`aiosqlite` 3112 3113 .. change:: 3114 :tags: bug, regression, orm, declarative 3115 :tickets: 6128 3116 3117 Fixed regression where the ``.metadata`` attribute on a per class level 3118 would not be honored, breaking the use case of per-class-hierarchy 3119 :class:`.schema.MetaData` for abstract declarative classes and mixins. 3120 3121 3122 .. seealso:: 3123 3124 :ref:`declarative_metadata` 3125 3126 .. change:: 3127 :tags: bug, mypy 3128 3129 Added support for the Mypy extension to correctly interpret a declarative 3130 base class that's generated using the :func:`_orm.as_declarative` function 3131 as well as the :meth:`_orm.registry.as_declarative_base` method. 3132 3133 .. change:: 3134 :tags: bug, mypy 3135 :tickets: 6109 3136 3137 Fixed bug in Mypy plugin where the Python type detection 3138 for the :class:`_types.Boolean` column type would produce 3139 an exception; additionally implemented support for :class:`_types.Enum`, 3140 including detection of a string-based enum vs. use of Python ``enum.Enum``. 3141 3142 .. change:: 3143 :tags: bug, reflection, postgresql 3144 :tickets: 6129 3145 3146 Fixed reflection of identity columns in tables with mixed case names 3147 in PostgreSQL. 3148 3149 .. change:: 3150 :tags: bug, sqlite, regression 3151 :tickets: 5848 3152 3153 Repaired the ``pysqlcipher`` dialect to connect correctly which had 3154 regressed in 1.4, and added test + CI support to maintain the driver 3155 in working condition. The dialect now imports the ``sqlcipher3`` module 3156 for Python 3 by default before falling back to ``pysqlcipher3`` which 3157 is documented as now being unmaintained. 3158 3159 .. seealso:: 3160 3161 :ref:`pysqlcipher` 3162 3163 3164 .. change:: 3165 :tags: bug, orm 3166 :tickets: 6060 3167 3168 Fixed bug where ORM queries using a correlated subquery in conjunction with 3169 :func:`_orm.column_property` would fail to correlate correctly to an 3170 enclosing subquery or to a CTE when :meth:`_sql.Select.correlate_except` 3171 were used in the property to control correlation, in cases where the 3172 subquery contained the same selectables as ones within the correlated 3173 subquery that were intended to not be correlated. 3174 3175 .. change:: 3176 :tags: bug, orm 3177 :tickets: 6131 3178 3179 Fixed bug where combinations of the new "relationship with criteria" 3180 feature could fail in conjunction with features that make use of the new 3181 "lambda SQL" feature, including loader strategies such as selectinload and 3182 lazyload, for more complicated scenarios such as polymorphic loading. 3183 3184 .. change:: 3185 :tags: bug, orm 3186 :tickets: 6124 3187 3188 Repaired support so that the :meth:`_sql.ClauseElement.params` method can 3189 work correctly with a :class:`_sql.Select` object that includes joins 3190 across ORM relationship structures, which is a new feature in 1.4. 3191 3192 3193 .. change:: 3194 :tags: bug, engine, regression 3195 :tickets: 6119 3196 3197 Restored the :class:`_engine.ResultProxy` name back to the 3198 ``sqlalchemy.engine`` namespace. This name refers to the 3199 :class:`_engine.LegacyCursorResult` object. 3200 3201 .. change:: 3202 :tags: bug, orm 3203 :tickets: 6115 3204 3205 Fixed issue where a "removed in 2.0" warning were generated internally by 3206 the relationship loader mechanics. 3207 3208 3209.. changelog:: 3210 :version: 1.4.2 3211 :released: March 19, 2021 3212 3213 .. change:: 3214 :tags: bug, orm, dataclasses 3215 :tickets: 6093 3216 3217 Fixed issue in new ORM dataclasses functionality where dataclass fields on 3218 an abstract base or mixin that contained column or other mapping constructs 3219 would not be mapped if they also included a "default" key within the 3220 dataclasses.field() object. 3221 3222 3223 .. change:: 3224 :tags: bug, regression, orm 3225 :tickets: 6088 3226 3227 Fixed regression where the :attr:`_orm.Query.selectable` accessor, which is 3228 a synonym for :meth:`_orm.Query.__clause_element__`, got removed, it's now 3229 restored. 3230 3231 .. change:: 3232 :tags: bug, engine, regression 3233 3234 Restored top level import for ``sqlalchemy.engine.reflection``. This 3235 ensures that the base :class:`_reflection.Inspector` class is properly 3236 registered so that :func:`_sa.inspect` works for third party dialects that 3237 don't otherwise import this package. 3238 3239 3240 .. change:: 3241 :tags: bug, regression, orm 3242 :tickets: 6086 3243 3244 Fixed regression where use of an unnamed SQL expression such as a SQL 3245 function would raise a column targeting error if the query itself were 3246 using joinedload for an entity and was also being wrapped in a subquery by 3247 the joinedload eager loading process. 3248 3249 3250 .. change:: 3251 :tags: bug, orm, regression 3252 :tickets: 6092 3253 3254 Fixed regression where the :meth:`_orm.Query.filter_by` method would fail 3255 to locate the correct source entity if the :meth:`_orm.Query.join` method 3256 had been used targeting an entity without any kind of ON clause. 3257 3258 3259 .. change:: 3260 :tags: postgresql, usecase 3261 :tickets: 6982 3262 3263 Rename the column name used by a reflection query that used 3264 a reserved word in some postgresql compatible databases. 3265 3266 .. change:: 3267 :tags: usecase, orm, dataclasses 3268 :tickets: 6100 3269 3270 Added support for the :class:`_orm.declared_attr` object to work in the 3271 context of dataclass fields. 3272 3273 .. seealso:: 3274 3275 :ref:`orm_declarative_dataclasses_mixin` 3276 3277 .. change:: 3278 :tags: bug, sql, regression 3279 :tickets: 6101 3280 3281 Fixed issue where using a ``func`` that includes dotted packagenames would 3282 fail to be cacheable by the SQL caching system due to a Python list of 3283 names that needed to be a tuple. 3284 3285 3286 .. change:: 3287 :tags: bug, regression, orm 3288 :tickets: 6095 3289 3290 Fixed regression where the SQL compilation of a :class:`.Function` would 3291 not work correctly if the object had been "annotated", which is an internal 3292 memoization process used mostly by the ORM. In particular it could affect 3293 ORM lazy loads which make greater use of this feature in 1.4. 3294 3295 .. change:: 3296 :tags: bug, sql, regression 3297 :tickets: 6097 3298 3299 Fixed regression in the :func:`_sql.case` construct, where the "dictionary" 3300 form of argument specification failed to work correctly if it were passed 3301 positionally, rather than as a "whens" keyword argument. 3302 3303 .. change:: 3304 :tags: bug, orm 3305 :tickets: 6090 3306 3307 Fixed regression where the :class:`.ConcreteBase` would fail to map at all 3308 when a mapped column name overlapped with the discriminator column name, 3309 producing an assertion error. The use case here did not function correctly 3310 in 1.3 as the polymorphic union would produce a query that ignored the 3311 discriminator column entirely, while emitting duplicate column warnings. As 3312 1.4's architecture cannot easily reproduce this essentially broken behavior 3313 of 1.3 at the ``select()`` level right now, the use case now raises an 3314 informative error message instructing the user to use the 3315 ``.ConcreteBase._concrete_discriminator_name`` attribute to resolve the 3316 conflict. To assist with this configuration, 3317 ``.ConcreteBase._concrete_discriminator_name`` may be placed on the base 3318 class only where it will be automatically used by subclasses; previously 3319 this was not the case. 3320 3321 3322 .. change:: 3323 :tags: bug, mypy 3324 :tickets: sqlalchemy/sqlalchemy2-stubs/2 3325 3326 Fixed issue in MyPy extension which crashed on detecting the type of a 3327 :class:`.Column` if the type were given with a module prefix like 3328 ``sa.Integer()``. 3329 3330 3331.. changelog:: 3332 :version: 1.4.1 3333 :released: March 17, 2021 3334 3335 .. change:: 3336 :tags: bug, orm, regression 3337 :tickets: 6066 3338 3339 Fixed regression where producing a Core expression construct such as 3340 :func:`_sql.select` using ORM entities would eagerly configure the mappers, 3341 in an effort to maintain compatibility with the :class:`_orm.Query` object 3342 which necessarily does this to support many backref-related legacy cases. 3343 However, core :func:`_sql.select` constructs are also used in mapper 3344 configurations and such, and to that degree this eager configuration is 3345 more of an inconvenience, so eager configure has been disabled for the 3346 :func:`_sql.select` and other Core constructs in the absence of ORM loading 3347 types of functions such as :class:`_orm.Load`. 3348 3349 The change maintains the behavior of :class:`_orm.Query` so that backwards 3350 compatibility is maintained. However, when using a :func:`_sql.select` in 3351 conjunction with ORM entities, a "backref" that isn't explicitly placed on 3352 one of the classes until mapper configure time won't be available unless 3353 :func:`_orm.configure_mappers` or the newer :func:`_orm.registry.configure` 3354 has been called elsewhere. Prefer using 3355 :paramref:`_orm.relationship.back_populates` for more explicit relationship 3356 configuration which does not have the eager configure requirement. 3357 3358 3359 .. change:: 3360 :tags: bug, mssql, regression 3361 :tickets: 6058 3362 3363 Fixed regression where a new setinputsizes() API that's available for 3364 pyodbc was enabled, which is apparently incompatible with pyodbc's 3365 fast_executemany() mode in the absence of more accurate typing information, 3366 which as of yet is not fully implemented or tested. The pyodbc dialect and 3367 connector has been modified so that setinputsizes() is not used at all 3368 unless the parameter ``use_setinputsizes`` is passed to the dialect, e.g. 3369 via :func:`_sa.create_engine`, at which point its behavior can be 3370 customized using the :meth:`.DialectEvents.do_setinputsizes` hook. 3371 3372 .. seealso:: 3373 3374 :ref:`mssql_pyodbc_setinputsizes` 3375 3376 .. change:: 3377 :tags: bug, orm, regression 3378 :tickets: 6055 3379 3380 Fixed a critical regression in the relationship lazy loader where the SQL 3381 criteria used to fetch a related many-to-one object could go stale in 3382 relation to other memoized structures within the loader if the mapper had 3383 configuration changes, such as can occur when mappers are late configured 3384 or configured on demand, producing a comparison to None and returning no 3385 object. Huge thanks to Alan Hamlett for their help tracking this down late 3386 into the night. 3387 3388 3389 3390 .. change:: 3391 :tags: bug, regression 3392 :tickets: 6068 3393 3394 Added back ``items`` and ``values`` to ``ColumnCollection`` class. 3395 The regression was introduced while adding support for duplicate 3396 columns in from clauses and selectable in ticket #4753. 3397 3398 3399 .. change:: 3400 :tags: bug, engine, regression 3401 :tickets: 6074 3402 3403 The Python ``namedtuple()`` has the behavior such that the names ``count`` 3404 and ``index`` will be served as tuple values if the named tuple includes 3405 those names; if they are absent, then their behavior as methods of 3406 ``collections.abc.Sequence`` is maintained. Therefore the 3407 :class:`_result.Row` and :class:`_result.LegacyRow` classes have been fixed 3408 so that they work in this same way, maintaining the expected behavior for 3409 database rows that have columns named "index" or "count". 3410 3411 .. change:: 3412 :tags: bug, orm, regression 3413 :tickets: 6076 3414 3415 Fixed regression where the :meth:`_orm.Query.exists` method would fail to 3416 create an expression if the entity list of the :class:`_orm.Query` were 3417 an arbitrary SQL column expression. 3418 3419 3420 .. change:: 3421 :tags: bug, orm, regression 3422 :tickets: 6052 3423 3424 Fixed regression where calling upon :meth:`_orm.Query.count` in conjunction 3425 with a loader option such as :func:`_orm.joinedload` would fail to ignore 3426 the loader option. This is a behavior that has always been very specific to 3427 the :meth:`_orm.Query.count` method; an error is normally raised if a given 3428 :class:`_orm.Query` has options that don't apply to what it is returning. 3429 3430 .. change:: 3431 :tags: bug, orm, declarative, regression 3432 :tickets: 6054 3433 3434 Fixed bug where user-mapped classes that contained an attribute named 3435 "registry" would cause conflicts with the new registry-based mapping system 3436 when using :class:`.DeclarativeMeta`. While the attribute remains 3437 something that can be set explicitly on a declarative base to be 3438 consumed by the metaclass, once located it is placed under a private 3439 class variable so it does not conflict with future subclasses that use 3440 the same name for other purposes. 3441 3442 3443 3444 .. change:: 3445 :tags: bug, orm, regression 3446 :tickets: 6067 3447 3448 Fixed regression in :meth:`_orm.Session.identity_key`, including that the 3449 method and related methods were not covered by any unit test as well as 3450 that the method contained a typo preventing it from functioning correctly. 3451 3452 3453.. changelog:: 3454 :version: 1.4.0 3455 :released: March 15, 2021 3456 3457 .. change:: 3458 :tags: bug, mssql 3459 :tickets: 5919 3460 3461 Fix a reflection error for MSSQL 2005 introduced by the reflection of 3462 filtered indexes. 3463 3464 .. change:: 3465 :tags: feature, mypy 3466 :tickets: 4609 3467 3468 Rudimentary and experimental support for Mypy has been added in the form of 3469 a new plugin, which itself depends on new typing stubs for SQLAlchemy. The 3470 plugin allows declarative mappings in their standard form to both be 3471 compatible with Mypy as well as to provide typing support for mapped 3472 classes and instances. 3473 3474 .. seealso:: 3475 3476 :ref:`mypy_toplevel` 3477 3478 .. change:: 3479 :tags: bug, sql 3480 :tickets: 6016 3481 3482 Fixed bug where the "percent escaping" feature that occurs with dialects 3483 that use the "format" or "pyformat" bound parameter styles was not enabled 3484 for the :meth:`_sql.Operators.op` and :class:`_sql.custom_op` constructs, 3485 for custom operators that use percent signs. The percent sign will now be 3486 automatically doubled based on the paramstyle as necessary. 3487 3488 3489 3490 .. change:: 3491 :tags: bug, regression, sql 3492 :tickets: 5979 3493 3494 Fixed regression where the "unsupported compilation error" for unknown 3495 datatypes would fail to raise correctly. 3496 3497 .. change:: 3498 :tags: ext, usecase 3499 :tickets: 5942 3500 3501 Add new parameter 3502 :paramref:`_automap.AutomapBase.prepare.reflection_options` 3503 to allow passing of :meth:`_schema.MetaData.reflect` options like ``only`` 3504 or dialect-specific reflection options like ``oracle_resolve_synonyms``. 3505 3506 .. change:: 3507 :tags: change, sql 3508 3509 Altered the compilation for the :class:`.CTE` construct so that a string is 3510 returned representing the inner SELECT statement if the :class:`.CTE` is 3511 stringified directly, outside of the context of an enclosing SELECT; This 3512 is the same behavior of :meth:`_sql.FromClause.alias` and 3513 :meth:`_sql.Select.subquery`. Previously, a blank string would be 3514 returned as the CTE is normally placed above a SELECT after that SELECT has 3515 been generated, which is generally misleading when debugging. 3516 3517 3518 .. change:: 3519 :tags: bug, orm 3520 :tickets: 5981 3521 3522 Fixed regression where the :paramref:`_orm.relationship.query_class` 3523 parameter stopped being functional for "dynamic" relationships. The 3524 ``AppenderQuery`` remains dependent on the legacy :class:`_orm.Query` 3525 class; users are encouraged to migrate from the use of "dynamic" 3526 relationships to using :func:`_orm.with_parent` instead. 3527 3528 3529 .. change:: 3530 :tags: bug, orm, regression 3531 :tickets: 6003 3532 3533 Fixed regression where :meth:`_orm.Query.join` would produce no effect if 3534 the query itself as well as the join target were against a 3535 :class:`_schema.Table` object, rather than a mapped class. This was part of 3536 a more systemic issue where the legacy ORM query compiler would not be 3537 correctly used from a :class:`_orm.Query` if the statement produced had not 3538 ORM entities present within it. 3539 3540 3541 .. change:: 3542 :tags: bug, regression, sql 3543 :tickets: 6008 3544 3545 Fixed regression where usage of the standalone :func:`_sql.distinct()` used 3546 in the form of being directly SELECTed would fail to be locatable in the 3547 result set by column identity, which is how the ORM locates columns. While 3548 standalone :func:`_sql.distinct()` is not oriented towards being directly 3549 SELECTed (use :meth:`_sql.select.distinct` for a regular 3550 ``SELECT DISTINCT..``) , it was usable to a limited extent in this way 3551 previously (but wouldn't work in subqueries, for example). The column 3552 targeting for unary expressions such as "DISTINCT <col>" has been improved 3553 so that this case works again, and an additional improvement has been made 3554 so that usage of this form in a subquery at least generates valid SQL which 3555 was not the case previously. 3556 3557 The change additionally enhances the ability to target elements in 3558 ``row._mapping`` based on SQL expression objects in ORM-enabled 3559 SELECT statements, including whether the statement was invoked by 3560 ``connection.execute()`` or ``session.execute()``. 3561 3562 .. change:: 3563 :tags: bug, orm, asyncio 3564 :tickets: 5998 3565 3566 The API for :meth:`_asyncio.AsyncSession.delete` is now an awaitable; 3567 this method cascades along relationships which must be loaded in a 3568 similar manner as the :meth:`_asyncio.AsyncSession.merge` method. 3569 3570 3571 .. change:: 3572 :tags: usecase, postgresql, mysql, asyncio 3573 :tickets: 5967 3574 3575 Added an ``asyncio.Lock()`` within SQLAlchemy's emulated DBAPI cursor, 3576 local to the connection, for the asyncpg and aiomysql dialects for the 3577 scope of the ``cursor.execute()`` and ``cursor.executemany()`` methods. The 3578 rationale is to prevent failures and corruption for the case where the 3579 connection is used in multiple awaitables at once. 3580 3581 While this use case can also occur with threaded code and non-asyncio 3582 dialects, we anticipate this kind of use will be more common under asyncio, 3583 as the asyncio API is encouraging of such use. It's definitely better to 3584 use a distinct connection per concurrent awaitable however as concurrency 3585 will not be achieved otherwise. 3586 3587 For the asyncpg dialect, this is so that the space between 3588 the call to ``prepare()`` and ``fetch()`` is prevented from allowing 3589 concurrent executions on the connection from causing interface error 3590 exceptions, as well as preventing race conditions when starting a new 3591 transaction. Other PostgreSQL DBAPIs are threadsafe at the connection level 3592 so this intends to provide a similar behavior, outside the realm of server 3593 side cursors. 3594 3595 For the aiomysql dialect, the mutex will provide safety such that 3596 the statement execution and the result set fetch, which are two distinct 3597 steps at the connection level, won't get corrupted by concurrent 3598 executions on the same connection. 3599 3600 3601 .. change:: 3602 :tags: bug, engine 3603 :tickets: 6002 3604 3605 Improved engine logging to note ROLLBACK and COMMIT which is logged while 3606 the DBAPI driver is in AUTOCOMMIT mode. These ROLLBACK/COMMIT are library 3607 level and do not have any effect when AUTOCOMMIT is in effect, however it's 3608 still worthwhile to log as these indicate where SQLAlchemy sees the 3609 "transaction" demarcation. 3610 3611 .. change:: 3612 :tags: bug, regression, engine 3613 :tickets: 6004 3614 3615 Fixed a regression where the "reset agent" of the connection pool wasn't 3616 really being utilized by the :class:`_engine.Connection` when it were 3617 closed, and also leading to a double-rollback scenario that was somewhat 3618 wasteful. The newer architecture of the engine has been updated so that 3619 the connection pool "reset-on-return" logic will be skipped when the 3620 :class:`_engine.Connection` explicitly closes out the transaction before 3621 returning the pool to the connection. 3622 3623 .. change:: 3624 :tags: bug, schema 3625 :tickets: 5953 3626 3627 Deprecated all schema-level ``.copy()`` methods and renamed to 3628 ``_copy()``. These are not standard Python "copy()" methods as they 3629 typically rely upon being instantiated within particular contexts 3630 which are passed to the method as optional keyword arguments. The 3631 :meth:`_schema.Table.tometadata` method is the public API that provides 3632 copying for :class:`_schema.Table` objects. 3633 3634 .. change:: 3635 :tags: bug, ext 3636 :tickets: 6020 3637 3638 The ``sqlalchemy.ext.mutable`` extension now tracks the "parents" 3639 collection using the :class:`.InstanceState` associated with objects, 3640 rather than the object itself. The latter approach required that the object 3641 be hashable so that it can be inside of a ``WeakKeyDictionary``, which goes 3642 against the behavioral contract of the ORM overall which is that ORM mapped 3643 objects do not need to provide any particular kind of ``__hash__()`` method 3644 and that unhashable objects are supported. 3645 3646 .. change:: 3647 :tags: bug, orm 3648 :tickets: 5984 3649 3650 The unit of work process now turns off all "lazy='raise'" behavior 3651 altogether when a flush is proceeding. While there are areas where the UOW 3652 is sometimes loading things that aren't ultimately needed, the lazy="raise" 3653 strategy is not helpful here as the user often does not have much control 3654 or visibility into the flush process. 3655 3656 3657.. changelog:: 3658 :version: 1.4.0b3 3659 :released: March 15, 2021 3660 :released: February 15, 2021 3661 3662 .. change:: 3663 :tags: bug, orm 3664 :tickets: 5933 3665 3666 Fixed issue in new 1.4/2.0 style ORM queries where a statement-level label 3667 style would not be preserved in the keys used by result rows; this has been 3668 applied to all combinations of Core/ORM columns / session vs. connection 3669 etc. so that the linkage from statement to result row is the same in all 3670 cases. As part of this change, the labeling of column expressions 3671 in rows has been improved to retain the original name of the ORM 3672 attribute even if used in a subquery. 3673 3674 3675 3676 3677 .. change:: 3678 :tags: bug, sql 3679 :tickets: 5924 3680 3681 Fixed bug where the "cartesian product" assertion was not correctly 3682 accommodating for joins between tables that relied upon the use of LATERAL 3683 to connect from a subquery to another subquery in the enclosing context. 3684 3685 .. change:: 3686 :tags: bug, sql 3687 :tickets: 5934 3688 3689 Fixed 1.4 regression where the :meth:`_functions.Function.in_` method was 3690 not covered by tests and failed to function properly in all cases. 3691 3692 .. change:: 3693 :tags: bug, engine, postgresql 3694 :tickets: 5941 3695 3696 Continued with the improvement made as part of :ticket:`5653` to further 3697 support bound parameter names, including those generated against column 3698 names, for names that include colons, parenthesis, and question marks, as 3699 well as improved test support, so that bound parameter names even if they 3700 are auto-derived from column names should have no problem including for 3701 parenthesis in psycopg2's "pyformat" style. 3702 3703 As part of this change, the format used by the asyncpg DBAPI adapter (which 3704 is local to SQLAlchemy's asyncpg dialect) has been changed from using 3705 "qmark" paramstyle to "format", as there is a standard and internally 3706 supported SQL string escaping style for names that use percent signs with 3707 "format" style (i.e. to double percent signs), as opposed to names that use 3708 question marks with "qmark" style (where an escaping system is not defined 3709 by pep-249 or Python). 3710 3711 .. seealso:: 3712 3713 :ref:`change_5941` 3714 3715 .. change:: 3716 :tags: sql, usecase, postgresql, sqlite 3717 :tickets: 5939 3718 3719 Enhance ``set_`` keyword of :class:`.OnConflictDoUpdate` to accept a 3720 :class:`.ColumnCollection`, such as the ``.c.`` collection from a 3721 :class:`Selectable`, or the ``.excluded`` contextual object. 3722 3723 .. change:: 3724 :tags: feature, orm 3725 3726 The ORM used in :term:`2.0 style` can now return ORM objects from the rows 3727 returned by an UPDATE..RETURNING or INSERT..RETURNING statement, by 3728 supplying the construct to :meth:`_sql.Select.from_statement` in an ORM 3729 context. 3730 3731 .. seealso:: 3732 3733 :ref:`orm_dml_returning_objects` 3734 3735 3736 3737 .. change:: 3738 :tags: bug, sql 3739 :tickets: 5935 3740 3741 Fixed regression where use of an arbitrary iterable with the 3742 :func:`_sql.select` function was not working, outside of plain lists. The 3743 forwards/backwards compatibility logic here now checks for a wider range of 3744 incoming "iterable" types including that a ``.c`` collection from a 3745 selectable can be passed directly. Pull request compliments of Oliver Rice. 3746 3747.. changelog:: 3748 :version: 1.4.0b2 3749 :released: March 15, 2021 3750 :released: February 3, 2021 3751 3752 .. change:: 3753 :tags: usecase, sql 3754 :tickets: 5695 3755 3756 Multiple calls to "returning", e.g. :meth:`_sql.Insert.returning`, 3757 may now be chained to add new columns to the RETURNING clause. 3758 3759 3760 .. change:: 3761 :tags: bug, asyncio 3762 :tickets: 5615 3763 3764 Adjusted the greenlet integration, which provides support for Python asyncio 3765 in SQLAlchemy, to accommodate for the handling of Python ``contextvars`` 3766 (introduced in Python 3.7) for ``greenlet`` versions greater than 0.4.17. 3767 Greenlet version 0.4.17 added automatic handling of contextvars in a 3768 backwards-incompatible way; we've coordinated with the greenlet authors to 3769 add a preferred API for this in versions subsequent to 0.4.17 which is now 3770 supported by SQLAlchemy's greenlet integration. For greenlet versions prior 3771 to 0.4.17 no behavioral change is needed, version 0.4.17 itself is blocked 3772 from the dependencies. 3773 3774 .. change:: 3775 :tags: bug, engine, sqlite 3776 :tickets: 5845 3777 3778 Fixed bug in the 2.0 "future" version of :class:`.Engine` where emitting 3779 SQL during the :meth:`.EngineEvents.begin` event hook would cause a 3780 re-entrant (recursive) condition due to autobegin, affecting among other 3781 things the recipe documented for SQLite to allow for savepoints and 3782 serializable isolation support. 3783 3784 3785 .. change:: 3786 :tags: bug, orm, regression 3787 :tickets: 5845 3788 3789 Fixed issue in new :class:`_orm.Session` similar to that of the 3790 :class:`_engine.Connection` where the new "autobegin" logic could be 3791 tripped into a re-entrant (recursive) state if SQL were executed within the 3792 :meth:`.SessionEvents.after_transaction_create` event hook. 3793 3794 .. change:: 3795 :tags: sql 3796 :tickets: 4757 3797 3798 Replace :meth:`_orm.Query.with_labels` and 3799 :meth:`_sql.GenerativeSelect.apply_labels` with explicit getters and 3800 setters :meth:`_sql.GenerativeSelect.get_label_style` and 3801 :meth:`_sql.GenerativeSelect.set_label_style` to accommodate the three 3802 supported label styles: :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`, 3803 :data:`_sql.LABEL_STYLE_TABLENAME_PLUS_COL`, and 3804 :data:`_sql.LABEL_STYLE_NONE`. 3805 3806 In addition, for Core and "future style" ORM queries, 3807 ``LABEL_STYLE_DISAMBIGUATE_ONLY`` is now the default label style. This 3808 style differs from the existing "no labels" style in that labeling is 3809 applied in the case of column name conflicts; with ``LABEL_STYLE_NONE``, a 3810 duplicate column name is not accessible via name in any case. 3811 3812 For cases where labeling is significant, namely that the ``.c`` collection 3813 of a subquery is able to refer to all columns unambiguously, the behavior 3814 of ``LABEL_STYLE_DISAMBIGUATE_ONLY`` is now sufficient for all 3815 SQLAlchemy features across Core and ORM which involve this behavior. 3816 Result set rows since SQLAlchemy 1.0 are usually aligned with column 3817 constructs positionally. 3818 3819 For legacy ORM queries using :class:`_query.Query`, the table-plus-column 3820 names labeling style applied by ``LABEL_STYLE_TABLENAME_PLUS_COL`` 3821 continues to be used so that existing test suites and logging facilities 3822 see no change in behavior by default. 3823 3824 .. change:: 3825 :tags: bug, orm, unitofwork 3826 :tickets: 5735 3827 3828 Improved the unit of work topological sorting system such that the 3829 toplogical sort is now deterministic based on the sorting of the input set, 3830 which itself is now sorted at the level of mappers, so that the same inputs 3831 of affected mappers should produce the same output every time, among 3832 mappers / tables that don't have any dependency on each other. This further 3833 reduces the chance of deadlocks as can be observed in a flush that UPDATEs 3834 among multiple, unrelated tables such that row locks are generated. 3835 3836 3837 .. change:: 3838 :tags: changed, orm 3839 :tickets: 5897 3840 3841 Mapper "configuration", which occurs within the 3842 :func:`_orm.configure_mappers` function, is now organized to be on a 3843 per-registry basis. This allows for example the mappers within a certain 3844 declarative base to be configured, but not those of another base that is 3845 also present in memory. The goal is to provide a means of reducing 3846 application startup time by only running the "configure" process for sets 3847 of mappers that are needed. This also adds the 3848 :meth:`_orm.registry.configure` method that will run configure for the 3849 mappers local in a particular registry only. 3850 3851 .. change:: 3852 :tags: bug, orm 3853 :tickets: 5702 3854 3855 Fixed regression where the :paramref:`.Bundle.single_entity` flag would 3856 take effect for a :class:`.Bundle` even though it were not set. 3857 Additionally, this flag is legacy as it only makes sense for the 3858 :class:`_orm.Query` object and not 2.0 style execution. a deprecation 3859 warning is emitted when used with new-style execution. 3860 3861 .. change:: 3862 :tags: bug, sql 3863 :tickets: 5858 3864 3865 Fixed issue in new :meth:`_sql.Select.join` method where chaining from the 3866 current JOIN wasn't looking at the right state, causing an expression like 3867 "FROM a JOIN b <onclause>, b JOIN c <onclause>" rather than 3868 "FROM a JOIN b <onclause> JOIN c <onclause>". 3869 3870 .. change:: 3871 :tags: usecase, sql 3872 3873 Added :meth:`_sql.Select.outerjoin_from` method to complement 3874 :meth:`_sql.Select.join_from`. 3875 3876 .. change:: 3877 :tags: usecase, sql 3878 :tickets: 5888 3879 3880 Adjusted the "literal_binds" feature of :class:`_sql.Compiler` to render 3881 NULL for a bound parameter that has ``None`` as the value, either 3882 explicitly passed or omitted. The previous error message "bind parameter 3883 without a renderable value" is removed, and a missing or ``None`` value 3884 will now render NULL in all cases. Previously, rendering of NULL was 3885 starting to happen for DML statements due to internal refactorings, but was 3886 not explicitly part of test coverage, which it now is. 3887 3888 While no error is raised, when the context is within that of a column 3889 comparison, and the operator is not "IS"/"IS NOT", a warning is emitted 3890 that this is not generally useful from a SQL perspective. 3891 3892 3893 .. change:: 3894 :tags: bug, orm 3895 :tickets: 5750 3896 3897 Fixed regression where creating an :class:`_orm.aliased` construct against 3898 a plain selectable and including a name would raise an assertionerror. 3899 3900 3901 .. change:: 3902 :tags: bug, mssql, mysql, datatypes 3903 :tickets: 5788 3904 :versions: 1.4.0b2 3905 3906 Decimal accuracy and behavior has been improved when extracting floating 3907 point and/or decimal values from JSON strings using the 3908 :meth:`_sql.sqltypes.JSON.Comparator.as_float` method, when the numeric 3909 value inside of the JSON string has many significant digits; previously, 3910 MySQL backends would truncate values with many significant digits and SQL 3911 Server backends would raise an exception due to a DECIMAL cast with 3912 insufficient significant digits. Both backends now use a FLOAT-compatible 3913 approach that does not hardcode significant digits for floating point 3914 values. For precision numerics, a new method 3915 :meth:`_sql.sqltypes.JSON.Comparator.as_numeric` has been added which 3916 accepts arguments for precision and scale, and will return values as Python 3917 ``Decimal`` objects with no floating point conversion assuming the DBAPI 3918 supports it (all but pysqlite). 3919 3920 .. change:: 3921 :tags: feature, orm, declarative 3922 :tickets: 5745 3923 3924 Added an alternate resolution scheme to Declarative that will extract the 3925 SQLAlchemy column or mapped property from the "metadata" dictionary of a 3926 dataclasses.Field object. This allows full declarative mappings to be 3927 combined with dataclass fields. 3928 3929 .. seealso:: 3930 3931 :ref:`orm_declarative_dataclasses_declarative_table` 3932 3933 .. change:: 3934 :tags: bug, sql 3935 :tickets: 5754 3936 3937 Deprecation warnings are emitted under "SQLALCHEMY_WARN_20" mode when 3938 passing a plain string to :meth:`_orm.Session.execute`. 3939 3940 3941 .. change:: 3942 :tags: bug, sql, orm 3943 :tickets: 5760, 5763, 5765, 5768, 5770 3944 3945 A wide variety of fixes to the "lambda SQL" feature introduced at 3946 :ref:`engine_lambda_caching` have been implemented based on user feedback, 3947 with an emphasis on its use within the :func:`_orm.with_loader_criteria` 3948 feature where it is most prominently used [ticket:5760]: 3949 3950 * fixed issue where boolean True/False values referred towards in the 3951 closure variables of the lambda would cause failures [ticket:5763] 3952 3953 * Repaired a non-working detection for Python functions embedded in the 3954 lambda that produce bound values; this case is likely not supportable 3955 so raises an informative error, where the function should be invoked 3956 outside the lambda itself. New documentation has been added to 3957 further detail this behavior. [ticket:5770] 3958 3959 * The lambda system by default now rejects the use of non-SQL elements 3960 within the closure variables of the lambda entirely, where the error 3961 suggests the two options of either explicitly ignoring closure variables 3962 that are not SQL parameters, or specifying a specific set of values to be 3963 considered as part of the cache key based on hash value. This critically 3964 prevents the lambda system from assuming that arbitrary objects within 3965 the lambda's closure are appropriate for caching while also refusing to 3966 ignore them by default, preventing the case where their state might 3967 not be constant and have an impact on the SQL construct produced. 3968 The error message is comprehensive and new documentation has been 3969 added to further detail this behavior. [ticket:5765] 3970 3971 * Fixed support for the edge case where an ``in_()`` expression 3972 against a list of SQL elements, such as :func:`_sql.literal` objects, 3973 would fail to be accommodated correctly. [ticket:5768] 3974 3975 3976 .. change:: 3977 :tags: bug, orm 3978 :tickets: 5760, 5766, 5762, 5761, 5764 3979 3980 Related to the fixes for the lambda criteria system within Core, within the 3981 ORM implemented a variety of fixes for the 3982 :func:`_orm.with_loader_criteria` feature as well as the 3983 :meth:`_orm.SessionEvents.do_orm_execute` event handler that is often 3984 used in conjunction [ticket:5760]: 3985 3986 3987 * fixed issue where :func:`_orm.with_loader_criteria` function would fail 3988 if the given entity or base included non-mapped mixins in its descending 3989 class hierarchy [ticket:5766] 3990 3991 * The :func:`_orm.with_loader_criteria` feature is now unconditionally 3992 disabled for the case of ORM "refresh" operations, including loads 3993 of deferred or expired column attributes as well as for explicit 3994 operations like :meth:`_orm.Session.refresh`. These loads are necessarily 3995 based on primary key identity where additional WHERE criteria is 3996 never appropriate. [ticket:5762] 3997 3998 * Added new attribute :attr:`_orm.ORMExecuteState.is_column_load` to indicate 3999 that a :meth:`_orm.SessionEvents.do_orm_execute` handler that a particular 4000 operation is a primary-key-directed column attribute load, where additional 4001 criteria should not be added. The :func:`_orm.with_loader_criteria` 4002 function as above ignores these in any case now. [ticket:5761] 4003 4004 * Fixed issue where the :attr:`_orm.ORMExecuteState.is_relationship_load` 4005 attribute would not be set correctly for many lazy loads as well as all 4006 selectinloads. The flag is essential in order to test if options should 4007 be added to statements or if they would already have been propagated via 4008 relationship loads. [ticket:5764] 4009 4010 4011 .. change:: 4012 :tags: usecase, orm 4013 4014 Added :attr:`_orm.ORMExecuteState.bind_mapper` and 4015 :attr:`_orm.ORMExecuteState.all_mappers` accessors to 4016 :class:`_orm.ORMExecuteState` event object, so that handlers can respond to 4017 the target mapper and/or mapped class or classes involved in an ORM 4018 statement execution. 4019 4020 .. change:: 4021 :tags: bug, engine, postgresql, oracle 4022 4023 Adjusted the "setinputsizes" logic relied upon by the cx_Oracle, asyncpg 4024 and pg8000 dialects to support a :class:`.TypeDecorator` that includes 4025 an override the :meth:`.TypeDecorator.get_dbapi_type()` method. 4026 4027 4028 .. change:: 4029 :tags: postgresql, performance 4030 4031 Enhanced the performance of the asyncpg dialect by caching the asyncpg 4032 PreparedStatement objects on a per-connection basis. For a test case that 4033 makes use of the same statement on a set of pooled connections this appears 4034 to grant a 10-20% speed improvement. The cache size is adjustable and may 4035 also be disabled. 4036 4037 .. seealso:: 4038 4039 :ref:`asyncpg_prepared_statement_cache` 4040 4041 .. change:: 4042 :tags: feature, mysql 4043 :tickets: 5747 4044 4045 Added support for the aiomysql driver when using the asyncio SQLAlchemy 4046 extension. 4047 4048 .. seealso:: 4049 4050 :ref:`aiomysql` 4051 4052 .. change:: 4053 :tags: bug, reflection 4054 :tickets: 5684 4055 4056 Fixed bug where the now-deprecated ``autoload`` parameter was being called 4057 internally within the reflection routines when a related table were 4058 reflected. 4059 4060 4061 .. change:: 4062 :tags: platform, performance 4063 :tickets: 5681 4064 4065 Adjusted some elements related to internal class production at import time 4066 which added significant latency to the time spent to import the library vs. 4067 that of 1.3. The time is now about 20-30% slower than 1.3 instead of 4068 200%. 4069 4070 4071 .. change:: 4072 :tags: changed, schema 4073 :tickets: 5775 4074 4075 Altered the behavior of the :class:`_schema.Identity` construct such that 4076 when applied to a :class:`_schema.Column`, it will automatically imply that 4077 the value of :paramref:`_sql.Column.nullable` should default to ``False``, 4078 in a similar manner as when the :paramref:`_sql.Column.primary_key` 4079 parameter is set to ``True``. This matches the default behavior of all 4080 supporting databases where ``IDENTITY`` implies ``NOT NULL``. The 4081 PostgreSQL backend is the only one that supports adding ``NULL`` to an 4082 ``IDENTITY`` column, which is here supported by passing a ``True`` value 4083 for the :paramref:`_sql.Column.nullable` parameter at the same time. 4084 4085 4086 .. change:: 4087 :tags: bug, postgresql 4088 :tickets: 5698 4089 4090 Fixed a small regression where the query for "show 4091 standard_conforming_strings" upon initialization would be emitted even if 4092 the server version info were detected as less than version 8.2, previously 4093 it would only occur for server version 8.2 or greater. The query fails on 4094 Amazon Redshift which reports a PG server version older than this value. 4095 4096 4097 .. change:: 4098 :tags: bug, sql, postgresql, mysql, sqlite 4099 :tickets: 5169 4100 4101 An informative error message is now raised for a selected set of DML 4102 methods (currently all part of :class:`_dml.Insert` constructs) if they are 4103 called a second time, which would implicitly cancel out the previous 4104 setting. The methods altered include: 4105 :class:`_sqlite.Insert.on_conflict_do_update`, 4106 :class:`_sqlite.Insert.on_conflict_do_nothing` (SQLite), 4107 :class:`_postgresql.Insert.on_conflict_do_update`, 4108 :class:`_postgresql.Insert.on_conflict_do_nothing` (PostgreSQL), 4109 :class:`_mysql.Insert.on_duplicate_key_update` (MySQL) 4110 4111 .. change:: 4112 :tags: pool, tests, usecase 4113 :tickets: 5582 4114 4115 Improve documentation and add test for sub-second pool timeouts. 4116 Pull request courtesy Jordan Pittier. 4117 4118 .. change:: 4119 :tags: bug, general 4120 4121 Fixed a SQLite source file that had non-ascii characters inside of its 4122 docstring without a source encoding, introduced within the "INSERT..ON 4123 CONFLICT" feature, which would cause failures under Python 2. 4124 4125 .. change:: 4126 :tags: sqlite, usecase 4127 :tickets: 4010 4128 4129 Implemented INSERT... ON CONFLICT clause for SQLite. Pull request courtesy 4130 Ramon Williams. 4131 4132 .. seealso:: 4133 4134 :ref:`sqlite_on_conflict_insert` 4135 4136 .. change:: 4137 :tags: bug, asyncio 4138 :tickets: 5811 4139 4140 Implemented "connection-binding" for :class:`.AsyncSession`, the ability to 4141 pass an :class:`.AsyncConnection` to create an :class:`.AsyncSession`. 4142 Previously, this use case was not implemented and would use the associated 4143 engine when the connection were passed. This fixes the issue where the 4144 "join a session to an external transaction" use case would not work 4145 correctly for the :class:`.AsyncSession`. Additionally, added methods 4146 :meth:`.AsyncConnection.in_transaction`, 4147 :meth:`.AsyncConnection.in_nested_transaction`, 4148 :meth:`.AsyncConnection.get_transaction`, 4149 :meth:`.AsyncConnection.get_nested_transaction` and 4150 :attr:`.AsyncConnection.info` attribute. 4151 4152 .. change:: 4153 :tags: usecase, asyncio 4154 4155 The :class:`.AsyncEngine`, :class:`.AsyncConnection` and 4156 :class:`.AsyncTransaction` objects may be compared using Python ``==`` or 4157 ``!=``, which will compare the two given objects based on the "sync" object 4158 they are proxying towards. This is useful as there are cases particularly 4159 for :class:`.AsyncTransaction` where multiple instances of 4160 :class:`.AsyncTransaction` can be proxying towards the same sync 4161 :class:`_engine.Transaction`, and are actually equivalent. The 4162 :meth:`.AsyncConnection.get_transaction` method will currently return a new 4163 proxying :class:`.AsyncTransaction` each time as the 4164 :class:`.AsyncTransaction` is not otherwise statefully associated with its 4165 originating :class:`.AsyncConnection`. 4166 4167 .. change:: 4168 :tags: bug, oracle 4169 :tickets: 5884 4170 4171 Oracle two-phase transactions at a rudimentary level are now no longer 4172 deprecated. After receiving support from cx_Oracle devs we can provide for 4173 basic xid + begin/prepare support with some limitations, which will work 4174 more fully in an upcoming release of cx_Oracle. Two phase "recovery" is not 4175 currently supported. 4176 4177 .. change:: 4178 :tags: asyncio 4179 4180 The SQLAlchemy async mode now detects and raises an informative 4181 error when an non asyncio compatible :term:`DBAPI` is used. 4182 Using a standard ``DBAPI`` with async SQLAlchemy will cause 4183 it to block like any sync call, interrupting the executing asyncio 4184 loop. 4185 4186 .. change:: 4187 :tags: usecase, orm, asyncio 4188 :tickets: 5796, 5797, 5802 4189 4190 Added :meth:`_asyncio.AsyncSession.scalar`, 4191 :meth:`_asyncio.AsyncSession.get` as well as support for 4192 :meth:`_orm.sessionmaker.begin` to work as an async context manager with 4193 :class:`_asyncio.AsyncSession`. Also added 4194 :meth:`_asyncio.AsyncSession.in_transaction` accessor. 4195 4196 .. change:: 4197 :tags: bug, sql 4198 :tickets: 5785 4199 4200 Fixed issue in new :class:`_sql.Values` construct where passing tuples of 4201 objects would fall back to per-value type detection rather than making use 4202 of the :class:`_schema.Column` objects passed directly to 4203 :class:`_sql.Values` that tells SQLAlchemy what the expected type is. This 4204 would lead to issues for objects such as enumerations and numpy strings 4205 that are not actually necessary since the expected type is given. 4206 4207 .. change:: 4208 :tags: bug, engine 4209 4210 Added the "future" keyword to the list of words that are known by the 4211 :func:`_sa.engine_from_config` function, so that the values "true" and 4212 "false" may be configured as "boolean" values when using a key such 4213 as ``sqlalchemy.future = true`` or ``sqlalchemy.future = false``. 4214 4215 4216 .. change:: 4217 :tags: usecase, schema 4218 :tickets: 5712 4219 4220 The :meth:`_events.DDLEvents.column_reflect` event may now be applied to a 4221 :class:`_schema.MetaData` object where it will take effect for the 4222 :class:`_schema.Table` objects local to that collection. 4223 4224 .. seealso:: 4225 4226 :meth:`_events.DDLEvents.column_reflect` 4227 4228 :ref:`mapper_automated_reflection_schemes` - in the ORM mapping documentation 4229 4230 :ref:`automap_intercepting_columns` - in the :ref:`automap_toplevel` documentation 4231 4232 4233 4234 4235 .. change:: 4236 :tags: feature, engine 4237 4238 Dialect-specific constructs such as 4239 :meth:`_postgresql.Insert.on_conflict_do_update` can now stringify in-place 4240 without the need to specify an explicit dialect object. The constructs, 4241 when called upon for ``str()``, ``print()``, etc. now have internal 4242 direction to call upon their appropriate dialect rather than the 4243 "default"dialect which doesn't know how to stringify these. The approach 4244 is also adapted to generic schema-level create/drop such as 4245 :class:`_schema.AddConstraint`, which will adapt its stringify dialect to 4246 one indicated by the element within it, such as the 4247 :class:`_postgresql.ExcludeConstraint` object. 4248 4249 4250 .. change:: 4251 :tags: feature, engine 4252 :tickets: 5911 4253 4254 Added new execution option 4255 :paramref:`_engine.Connection.execution_options.logging_token`. This option 4256 will add an additional per-message token to log messages generated by the 4257 :class:`_engine.Connection` as it executes statements. This token is not 4258 part of the logger name itself (that part can be affected using the 4259 existing :paramref:`_sa.create_engine.logging_name` parameter), so is 4260 appropriate for ad-hoc connection use without the side effect of creating 4261 many new loggers. The option can be set at the level of 4262 :class:`_engine.Connection` or :class:`_engine.Engine`. 4263 4264 .. seealso:: 4265 4266 :ref:`dbengine_logging_tokens` 4267 4268 .. change:: 4269 :tags: bug, pool 4270 :tickets: 5708 4271 4272 Fixed regression where a connection pool event specified with a keyword, 4273 most notably ``insert=True``, would be lost when the event were set up. 4274 This would prevent startup events that need to fire before dialect-level 4275 events from working correctly. 4276 4277 4278 .. change:: 4279 :tags: usecase, pool 4280 :tickets: 5708, 5497 4281 4282 The internal mechanics of the engine connection routine has been altered 4283 such that it's now guaranteed that a user-defined event handler for the 4284 :meth:`_pool.PoolEvents.connect` handler, when established using 4285 ``insert=True``, will allow an event handler to run that is definitely 4286 invoked **before** any dialect-specific initialization starts up, most 4287 notably when it does things like detect default schema name. 4288 Previously, this would occur in most cases but not unconditionally. 4289 A new example is added to the schema documentation illustrating how to 4290 establish the "default schema name" within an on-connect event. 4291 4292 .. change:: 4293 :tags: usecase, postgresql 4294 4295 Added a read/write ``.autocommit`` attribute to the DBAPI-adaptation layer 4296 for the asyncpg dialect. This so that when working with DBAPI-specific 4297 schemes that need to use "autocommit" directly with the DBAPI connection, 4298 the same ``.autocommit`` attribute which works with both psycopg2 as well 4299 as pg8000 is available. 4300 4301 .. change:: 4302 :tags: bug, oracle 4303 :tickets: 5716 4304 4305 The Oracle dialect now uses 4306 ``select sys_context( 'userenv', 'current_schema' ) from dual`` to get 4307 the default schema name, rather than ``SELECT USER FROM DUAL``, to 4308 accommodate for changes to the session-local schema name under Oracle. 4309 4310 .. change:: 4311 :tags: schema, feature 4312 :tickets: 5659 4313 4314 Added :meth:`_types.TypeEngine.as_generic` to map dialect-specific types, 4315 such as :class:`sqlalchemy.dialects.mysql.INTEGER`, with the "best match" 4316 generic SQLAlchemy type, in this case :class:`_types.Integer`. Pull 4317 request courtesy Andrew Hannigan. 4318 4319 .. seealso:: 4320 4321 :ref:`metadata_reflection_dbagnostic_types` - example usage 4322 4323 .. change:: 4324 :tags: bug, sql 4325 :tickets: 5717 4326 4327 Fixed issue where a :class:`.RemovedIn20Warning` would erroneously emit 4328 when the ``.bind`` attribute were accessed internally on objects, 4329 particularly when stringifying a SQL construct. 4330 4331 .. change:: 4332 :tags: bug, orm 4333 :tickets: 5781 4334 4335 Fixed 1.4 regression where the use of :meth:`_orm.Query.having` in 4336 conjunction with queries with internally adapted SQL elements (common in 4337 inheritance scenarios) would fail due to an incorrect function call. Pull 4338 request courtesy esoh. 4339 4340 4341 .. change:: 4342 :tags: bug, pool, pypy 4343 :tickets: 5842 4344 4345 Fixed issue where connection pool would not return connections to the pool 4346 or otherwise be finalized upon garbage collection under pypy if the checked 4347 out connection fell out of scope without being closed. This is a long 4348 standing issue due to pypy's difference in GC behavior that does not call 4349 weakref finalizers if they are relative to another object that is also 4350 being garbage collected. A strong reference to the related record is now 4351 maintained so that the weakref has a strong-referenced "base" to trigger 4352 off of. 4353 4354 .. change:: 4355 :tags: bug, sqlite 4356 :tickets: 5699 4357 4358 Use python ``re.search()`` instead of ``re.match()`` as the operation 4359 used by the :meth:`Column.regexp_match` method when using sqlite. 4360 This matches the behavior of regular expressions on other databases 4361 as well as that of well-known SQLite plugins. 4362 4363 .. change:: 4364 :tags: changed, postgresql 4365 4366 Fixed issue where the psycopg2 dialect would silently pass the 4367 ``use_native_unicode=False`` flag without actually having any effect under 4368 Python 3, as the psycopg2 DBAPI uses Unicode unconditionally under Python 4369 3. This usage now raises an :class:`_exc.ArgumentError` when used under 4370 Python 3. Added test support for Python 2. 4371 4372 .. change:: 4373 :tags: bug, postgresql 4374 :tickets: 5722 4375 :versions: 1.4.0b2 4376 4377 Established support for :class:`_schema.Column` objects as well as ORM 4378 instrumented attributes as keys in the ``set_`` dictionary passed to the 4379 :meth:`_postgresql.Insert.on_conflict_do_update` and 4380 :meth:`_sqlite.Insert.on_conflict_do_update` methods, which match to the 4381 :class:`_schema.Column` objects in the ``.c`` collection of the target 4382 :class:`_schema.Table`. Previously, only string column names were 4383 expected; a column expression would be assumed to be an out-of-table 4384 expression that would render fully along with a warning. 4385 4386 .. change:: 4387 :tags: feature, sql 4388 :tickets: 3566 4389 4390 Implemented support for "table valued functions" along with additional 4391 syntaxes supported by PostgreSQL, one of the most commonly requested 4392 features. Table valued functions are SQL functions that return lists of 4393 values or rows, and are prevalent in PostgreSQL in the area of JSON 4394 functions, where the "table value" is commonly referred towards as the 4395 "record" datatype. Table valued functions are also supported by Oracle and 4396 SQL Server. 4397 4398 Features added include: 4399 4400 * the :meth:`_functions.FunctionElement.table_valued` modifier that creates a table-like 4401 selectable object from a SQL function 4402 * A :class:`_sql.TableValuedAlias` construct that renders a SQL function 4403 as a named table 4404 * Support for PostgreSQL's special "derived column" syntax that includes 4405 column names and sometimes datatypes, such as for the 4406 ``json_to_recordset`` function, using the 4407 :meth:`_sql.TableValuedAlias.render_derived` method. 4408 * Support for PostgreSQL's "WITH ORDINALITY" construct using the 4409 :paramref:`_functions.FunctionElement.table_valued.with_ordinality` parameter 4410 * Support for selection FROM a SQL function as column-valued scalar, a 4411 syntax supported by PostgreSQL and Oracle, via the 4412 :meth:`_functions.FunctionElement.column_valued` method 4413 * A way to SELECT a single column from a table-valued expression without 4414 using a FROM clause via the :meth:`_functions.FunctionElement.scalar_table_valued` 4415 method. 4416 4417 .. seealso:: 4418 4419 :ref:`tutorial_functions_table_valued` - in the :ref:`unified_tutorial` 4420 4421 .. change:: 4422 :tags: bug, asyncio 4423 :tickets: 5827 4424 4425 Fixed bug in asyncio connection pool where ``asyncio.TimeoutError`` would 4426 be raised rather than :class:`.exc.TimeoutError`. Also repaired the 4427 :paramref:`_sa.create_engine.pool_timeout` parameter set to zero when using 4428 the async engine, which previously would ignore the timeout and block 4429 rather than timing out immediately as is the behavior with regular 4430 :class:`.QueuePool`. 4431 4432 .. change:: 4433 :tags: bug, postgresql, asyncio 4434 :tickets: 5824 4435 4436 Fixed bug in asyncpg dialect where a failure during a "commit" or less 4437 likely a "rollback" should cancel the entire transaction; it's no longer 4438 possible to emit rollback. Previously the connection would continue to 4439 await a rollback that could not succeed as asyncpg would reject it. 4440 4441 .. change:: 4442 :tags: bug, orm 4443 4444 Fixed an issue where the API to create a custom executable SQL construct 4445 using the ``sqlalchemy.ext.compiles`` extension according to the 4446 documentation that's been up for many years would no longer function if 4447 only ``Executable, ClauseElement`` were used as the base classes, 4448 additional classes were needed if wanting to use 4449 :meth:`_orm.Session.execute`. This has been resolved so that those extra 4450 classes aren't needed. 4451 4452 .. change:: 4453 :tags: bug, regression, orm 4454 :tickets: 5867 4455 4456 Fixed ORM unit of work regression where an errant "assert primary_key" 4457 statement interferes with primary key generation sequences that don't 4458 actually consider the columns in the table to use a real primary key 4459 constraint, instead using :paramref:`_orm.mapper.primary_key` to establish 4460 certain columns as "primary". 4461 4462 .. change:: 4463 :tags: bug, sql 4464 :tickets: 5722 4465 :versions: 1.4.0b2 4466 4467 Properly render ``cycle=False`` and ``order=False`` as ``NO CYCLE`` and 4468 ``NO ORDER`` in :class:`_sql.Sequence` and :class:`_sql.Identity` 4469 objects. 4470 4471 .. change:: 4472 :tags: schema, usecase 4473 :tickets: 2843 4474 4475 Added parameters :paramref:`_ddl.CreateTable.if_not_exists`, 4476 :paramref:`_ddl.CreateIndex.if_not_exists`, 4477 :paramref:`_ddl.DropTable.if_exists` and 4478 :paramref:`_ddl.DropIndex.if_exists` to the :class:`_ddl.CreateTable`, 4479 :class:`_ddl.DropTable`, :class:`_ddl.CreateIndex` and 4480 :class:`_ddl.DropIndex` constructs which result in "IF NOT EXISTS" / "IF 4481 EXISTS" DDL being added to the CREATE/DROP. These phrases are not accepted 4482 by all databases and the operation will fail on a database that does not 4483 support it as there is no similarly compatible fallback within the scope of 4484 a single DDL statement. Pull request courtesy Ramon Williams. 4485 4486 .. change:: 4487 :tags: bug, pool, asyncio 4488 :tickets: 5823 4489 4490 When using an asyncio engine, the connection pool will now detach and 4491 discard a pooled connection that is was not explicitly closed/returned to 4492 the pool when its tracking object is garbage collected, emitting a warning 4493 that the connection was not properly closed. As this operation occurs 4494 during Python gc finalizers, it's not safe to run any IO operations upon 4495 the connection including transaction rollback or connection close as this 4496 will often be outside of the event loop. 4497 4498 The ``AsyncAdaptedQueue`` used by default on async dpapis 4499 should instantiate a queue only when it's first used 4500 to avoid binding it to a possibly wrong event loop. 4501 4502.. changelog:: 4503 :version: 1.4.0b1 4504 :released: March 15, 2021 4505 :released: November 2, 2020 4506 4507 .. change:: 4508 :tags: feature, orm 4509 :tickets: 5159 4510 4511 The ORM can now generate queries previously only available when using 4512 :class:`_orm.Query` using the :func:`_sql.select` construct directly. 4513 A new system by which ORM "plugins" may establish themselves within a 4514 Core :class:`_sql.Select` allow the majority of query building logic 4515 previously inside of :class:`_orm.Query` to now take place within 4516 a compilation-level extension for :class:`_sql.Select`. Similar changes 4517 have been made for the :class:`_sql.Update` and :class:`_sql.Delete` 4518 constructs as well. The constructs when invoked using :meth:`_orm.Session.execute` 4519 now do ORM-related work within the method. For :class:`_sql.Select`, 4520 the :class:`_engine.Result` object returned now contains ORM-level 4521 entities and results. 4522 4523 .. seealso:: 4524 4525 :ref:`change_5159` 4526 4527 .. change:: 4528 :tags: feature,sql 4529 :tickets: 4737 4530 4531 Added "from linting" as a built-in feature to the SQL compiler. This 4532 allows the compiler to maintain graph of all the FROM clauses in a 4533 particular SELECT statement, linked by criteria in either the WHERE 4534 or in JOIN clauses that link these FROM clauses together. If any two 4535 FROM clauses have no path between them, a warning is emitted that the 4536 query may be producing a cartesian product. As the Core expression 4537 language as well as the ORM are built on an "implicit FROMs" model where 4538 a particular FROM clause is automatically added if any part of the query 4539 refers to it, it is easy for this to happen inadvertently and it is 4540 hoped that the new feature helps with this issue. 4541 4542 .. seealso:: 4543 4544 :ref:`change_4737` 4545 4546 .. change:: 4547 :tags: deprecated, orm 4548 :tickets: 5606 4549 4550 The "slice index" feature used by :class:`_orm.Query` as well as by the 4551 dynamic relationship loader will no longer accept negative indexes in 4552 SQLAlchemy 2.0. These operations do not work efficiently and load the 4553 entire collection in, which is both surprising and undesirable. These 4554 will warn in 1.4 unless the :paramref:`_orm.Session.future` flag is set in 4555 which case they will raise IndexError. 4556 4557 4558 .. change:: 4559 :tags: sql, change 4560 :tickets: 4617 4561 4562 The "clause coercion" system, which is SQLAlchemy Core's system of receiving 4563 arguments and resolving them into :class:`_expression.ClauseElement` structures in order 4564 to build up SQL expression objects, has been rewritten from a series of 4565 ad-hoc functions to a fully consistent class-based system. This change 4566 is internal and should have no impact on end users other than more specific 4567 error messages when the wrong kind of argument is passed to an expression 4568 object, however the change is part of a larger set of changes involving 4569 the role and behavior of :func:`_expression.select` objects. 4570 4571 4572 .. change:: 4573 :tags: bug, mysql 4574 4575 The MySQL and MariaDB dialects now query from the information_schema.tables 4576 system view in order to determine if a particular table exists or not. 4577 Previously, the "DESCRIBE" command was used with an exception catch to 4578 detect non-existent, which would have the undesirable effect of emitting a 4579 ROLLBACK on the connection. There appeared to be legacy encoding issues 4580 which prevented the use of "SHOW TABLES", for this, but as MySQL support is 4581 now at 5.0.2 or above due to :ticket:`4189`, the information_schema tables 4582 are now available in all cases. 4583 4584 4585 .. change:: 4586 :tags: bug, orm 4587 :tickets: 5122 4588 4589 A query that is against a mapped inheritance subclass which also uses 4590 :meth:`_query.Query.select_entity_from` or a similar technique in order to 4591 provide an existing subquery to SELECT from, will now raise an error if the 4592 given subquery returns entities that do not correspond to the given 4593 subclass, that is, they are sibling or superclasses in the same hierarchy. 4594 Previously, these would be returned without error. Additionally, if the 4595 inheritance mapping is a single-inheritance mapping, the given subquery 4596 must apply the appropriate filtering against the polymorphic discriminator 4597 column in order to avoid this error; previously, the :class:`_query.Query` would 4598 add this criteria to the outside query however this interferes with some 4599 kinds of query that return other kinds of entities as well. 4600 4601 .. seealso:: 4602 4603 :ref:`change_5122` 4604 4605 .. change:: 4606 :tags: bug, engine 4607 :tickets: 5004 4608 4609 Revised the :paramref:`.Connection.execution_options.schema_translate_map` 4610 feature such that the processing of the SQL statement to receive a specific 4611 schema name occurs within the execution phase of the statement, rather than 4612 at the compile phase. This is to support the statement being efficiently 4613 cached. Previously, the current schema being rendered into the statement 4614 for a particular run would be considered as part of the cache key itself, 4615 meaning that for a run against hundreds of schemas, there would be hundreds 4616 of cache keys, rendering the cache much less performant. The new behavior 4617 is that the rendering is done in a similar manner as the "post compile" 4618 rendering added in 1.4 as part of :ticket:`4645`, :ticket:`4808`. 4619 4620 .. change:: 4621 :tags: usecase, sql 4622 :tickets: 527 4623 4624 The :meth:`.Index.create` and :meth:`.Index.drop` methods now have a 4625 parameter :paramref:`.Index.create.checkfirst`, in the same way as that of 4626 :class:`_schema.Table` and :class:`.Sequence`, which when enabled will cause the 4627 operation to detect if the index exists (or not) before performing a create 4628 or drop operation. 4629 4630 4631 .. change:: 4632 :tags: sql, postgresql 4633 :tickets: 5498 4634 4635 Allow specifying the data type when creating a :class:`.Sequence` in 4636 PostgreSQL by using the parameter :paramref:`.Sequence.data_type`. 4637 4638 .. change:: 4639 :tags: change, mssql 4640 :tickets: 5084 4641 4642 SQL Server OFFSET and FETCH keywords are now used for limit/offset, rather 4643 than using a window function, for SQL Server versions 11 and higher. TOP is 4644 still used for a query that features only LIMIT. Pull request courtesy 4645 Elkin. 4646 4647 .. change:: 4648 :tags: deprecated, engine 4649 :tickets: 5526 4650 4651 The :class:`_engine.URL` object is now an immutable named tuple. To modify 4652 a URL object, use the :meth:`_engine.URL.set` method to produce a new URL 4653 object. 4654 4655 .. seealso:: 4656 4657 :ref:`change_5526` - notes on migration 4658 4659 4660 .. change:: 4661 :tags: change, postgresql 4662 4663 When using the psycopg2 dialect for PostgreSQL, psycopg2 minimum version is 4664 set at 2.7. The psycopg2 dialect relies upon many features of psycopg2 4665 released in the past few years, so to simplify the dialect, version 2.7, 4666 released in March, 2017 is now the minimum version required. 4667 4668 4669 .. change:: 4670 :tags: usecase, sql 4671 4672 The :func:`.true` and :func:`.false` operators may now be applied as the 4673 "onclause" of a :func:`_expression.join` on a backend that does not support 4674 "native boolean" expressions, e.g. Oracle or SQL Server, and the expression 4675 will render as "1=1" for true and "1=0" false. This is the behavior that 4676 was introduced many years ago in :ticket:`2804` for and/or expressions. 4677 4678 .. change:: 4679 :tags: feature, engine 4680 :tickets: 5087, 4395, 4959 4681 4682 Implemented an all-new :class:`_result.Result` object that replaces the previous 4683 ``ResultProxy`` object. As implemented in Core, the subclass 4684 :class:`_result.CursorResult` features a compatible calling interface with the 4685 previous ``ResultProxy``, and additionally adds a great amount of new 4686 functionality that can be applied to Core result sets as well as ORM result 4687 sets, which are now integrated into the same model. :class:`_result.Result` 4688 includes features such as column selection and rearrangement, improved 4689 fetchmany patterns, uniquing, as well as a variety of implementations that 4690 can be used to create database results from in-memory structures as well. 4691 4692 4693 .. seealso:: 4694 4695 :ref:`change_result_14_core` 4696 4697 4698 .. change:: 4699 :tags: renamed, engine 4700 :tickets: 5244 4701 4702 The :meth:`_reflection.Inspector.reflecttable` was renamed to 4703 :meth:`_reflection.Inspector.reflect_table`. 4704 4705 .. change:: 4706 :tags: change, orm 4707 :tickets: 4662 4708 4709 The condition where a pending object being flushed with an identity that 4710 already exists in the identity map has been adjusted to emit a warning, 4711 rather than throw a :class:`.FlushError`. The rationale is so that the 4712 flush will proceed and raise a :class:`.IntegrityError` instead, in the 4713 same way as if the existing object were not present in the identity map 4714 already. This helps with schemes that are using the 4715 :class:`.IntegrityError` as a means of catching whether or not a row 4716 already exists in the table. 4717 4718 .. seealso:: 4719 4720 :ref:`change_4662` 4721 4722 4723 .. change:: 4724 :tags: bug, sql 4725 :tickets: 5001 4726 4727 Fixed issue where when constructing constraints from ORM-bound columns, 4728 primarily :class:`_schema.ForeignKey` objects but also :class:`.UniqueConstraint`, 4729 :class:`.CheckConstraint` and others, the ORM-level 4730 :class:`.InstrumentedAttribute` is discarded entirely, and all ORM-level 4731 annotations from the columns are removed; this is so that the constraints 4732 are still fully pickleable without the ORM-level entities being pulled in. 4733 These annotations are not necessary to be present at the schema/metadata 4734 level. 4735 4736 .. change:: 4737 :tags: bug, mysql 4738 :tickets: 5568 4739 4740 The "skip_locked" keyword used with ``with_for_update()`` will render "SKIP 4741 LOCKED" on all MySQL backends, meaning it will fail for MySQL less than 4742 version 8 and on current MariaDB backends. This is because those backends 4743 do not support "SKIP LOCKED" or any equivalent, so this error should not be 4744 silently ignored. This is upgraded from a warning in the 1.3 series. 4745 4746 4747 .. change:: 4748 :tags: performance, postgresql 4749 :tickets: 5401 4750 4751 The psycopg2 dialect now defaults to using the very performant 4752 ``execute_values()`` psycopg2 extension for compiled INSERT statements, 4753 and also implements RETURNING support when this extension is used. This 4754 allows INSERT statements that even include an autoincremented SERIAL 4755 or IDENTITY value to run very fast while still being able to return the 4756 newly generated primary key values. The ORM will then integrate this 4757 new feature in a separate change. 4758 4759 .. seealso:: 4760 4761 :ref:`change_5401` - full list of changes regarding the 4762 ``executemany_mode`` parameter. 4763 4764 4765 .. change:: 4766 :tags: feature, orm 4767 :tickets: 4472 4768 4769 Added the ability to add arbitrary criteria to the ON clause generated 4770 by a relationship attribute in a query, which applies to methods such 4771 as :meth:`_query.Query.join` as well as loader options like 4772 :func:`_orm.joinedload`. Additionally, a "global" version of the option 4773 allows limiting criteria to be applied to particular entities in 4774 a query globally. 4775 4776 .. seealso:: 4777 4778 :ref:`loader_option_criteria` 4779 4780 :ref:`do_orm_execute_global_criteria` 4781 4782 :func:`_orm.with_loader_criteria` 4783 4784 .. change:: 4785 :tags: renamed, sql 4786 4787 :class:`_schema.Table` parameter ``mustexist`` has been renamed 4788 to :paramref:`_schema.Table.must_exist` and will now warn when used. 4789 4790 .. change:: 4791 :tags: removed, sql 4792 :tickets: 4632 4793 4794 The "threadlocal" execution strategy, deprecated in 1.3, has been 4795 removed for 1.4, as well as the concept of "engine strategies" and the 4796 ``Engine.contextual_connect`` method. The "strategy='mock'" keyword 4797 argument is still accepted for now with a deprecation warning; use 4798 :func:`.create_mock_engine` instead for this use case. 4799 4800 .. seealso:: 4801 4802 :ref:`change_4393_threadlocal` - from the 1.3 migration notes which 4803 discusses the rationale for deprecation. 4804 4805 .. change:: 4806 :tags: mssql, postgresql, reflection, schema, usecase 4807 :tickets: 4458 4808 4809 Improved support for covering indexes (with INCLUDE columns). Added the 4810 ability for postgresql to render CREATE INDEX statements with an INCLUDE 4811 clause from Core. Index reflection also report INCLUDE columns separately 4812 for both mssql and postgresql (11+). 4813 4814 .. change:: 4815 :tags: change, platform 4816 :tickets: 5400 4817 4818 The ``importlib_metadata`` library is used to scan for setuptools 4819 entrypoints rather than pkg_resources. as importlib_metadata is a small 4820 library that is included as of Python 3.8, the compatibility library is 4821 installed as a dependency for Python versions older than 3.8. 4822 4823 4824 .. change:: 4825 :tags: feature, sql, mssql, oracle 4826 :tickets: 4808 4827 4828 Added new "post compile parameters" feature. This feature allows a 4829 :func:`.bindparam` construct to have its value rendered into the SQL string 4830 before being passed to the DBAPI driver, but after the compilation step, 4831 using the "literal render" feature of the compiler. The immediate 4832 rationale for this feature is to support LIMIT/OFFSET schemes that don't 4833 work or perform well as bound parameters handled by the database driver, 4834 while still allowing for SQLAlchemy SQL constructs to be cacheable in their 4835 compiled form. The immediate targets for the new feature are the "TOP 4836 N" clause used by SQL Server (and Sybase) which does not support a bound 4837 parameter, as well as the "ROWNUM" and optional "FIRST_ROWS()" schemes used 4838 by the Oracle dialect, the former of which has been known to perform better 4839 without bound parameters and the latter of which does not support a bound 4840 parameter. The feature builds upon the mechanisms first developed to 4841 support "expanding" parameters for IN expressions. As part of this 4842 feature, the Oracle ``use_binds_for_limits`` feature is turned on 4843 unconditionally and this flag is now deprecated. 4844 4845 .. seealso:: 4846 4847 :ref:`change_4808` 4848 4849 .. change:: 4850 :tags: feature, sql 4851 :tickets: 1390 4852 4853 Add support for regular expression on supported backends. 4854 Two operations have been defined: 4855 4856 * :meth:`_sql.ColumnOperators.regexp_match` implementing a regular 4857 expression match like function. 4858 * :meth:`_sql.ColumnOperators.regexp_replace` implementing a regular 4859 expression string replace function. 4860 4861 Supported backends include SQLite, PostgreSQL, MySQL / MariaDB, and Oracle. 4862 4863 .. seealso:: 4864 4865 :ref:`change_1390` 4866 4867 .. change:: 4868 :tags: bug, orm 4869 :tickets: 4696 4870 4871 The internal attribute symbols NO_VALUE and NEVER_SET have been unified, as 4872 there was no meaningful difference between these two symbols, other than a 4873 few codepaths where they were differentiated in subtle and undocumented 4874 ways, these have been fixed. 4875 4876 4877 .. change:: 4878 :tags: oracle, bug 4879 4880 Correctly render :class:`_schema.Sequence` and :class:`_schema.Identity` 4881 column options ``nominvalue`` and ``nomaxvalue`` as ``NOMAXVALUE` and 4882 ``NOMINVALUE`` on oracle database. 4883 4884 .. change:: 4885 :tags: bug, schema 4886 :tickets: 4262 4887 4888 Cleaned up the internal ``str()`` for datatypes so that all types produce a 4889 string representation without any dialect present, including that it works 4890 for third-party dialect types without that dialect being present. The 4891 string representation defaults to being the UPPERCASE name of that type 4892 with nothing else. 4893 4894 4895 .. change:: 4896 :tags: deprecated, sql 4897 :tickets: 5010 4898 4899 The :meth:`_sql.Join.alias` method is deprecated and will be removed in 4900 SQLAlchemy 2.0. An explicit select + subquery, or aliasing of the inner 4901 tables, should be used instead. 4902 4903 4904 .. change:: 4905 :tags: bug, orm 4906 :tickets: 4194 4907 4908 Fixed bug where a versioning column specified on a mapper against a 4909 :func:`_expression.select` construct where the version_id_col itself were against the 4910 underlying table would incur additional loads when accessed, even if the 4911 value were locally persisted by the flush. The actual fix is a result of 4912 the changes in :ticket:`4617`, by fact that a :func:`_expression.select` object no 4913 longer has a ``.c`` attribute and therefore does not confuse the mapper 4914 into thinking there's an unknown column value present. 4915 4916 .. change:: 4917 :tags: bug, orm 4918 :tickets: 3858 4919 4920 An ``UnmappedInstanceError`` is now raised for :class:`.InstrumentedAttribute` 4921 if an instance is an unmapped object. Prior to this an ``AttributeError`` 4922 was raised. Pull request courtesy Ramon Williams. 4923 4924 .. change:: 4925 :tags: removed, platform 4926 :tickets: 5634 4927 4928 Dropped support for python 3.4 and 3.5 that has reached EOL. SQLAlchemy 1.4 4929 series requires python 2.7 or 3.6+. 4930 4931 .. seealso:: 4932 4933 :ref:`change_5634` 4934 4935 .. change:: 4936 :tags: performance, sql 4937 :tickets: 4639 4938 4939 An all-encompassing reorganization and refactoring of Core and ORM 4940 internals now allows all Core and ORM statements within the areas of 4941 DQL (e.g. SELECTs) and DML (e.g. INSERT, UPDATE, DELETE) to allow their 4942 SQL compilation as well as the construction of result-fetching metadata 4943 to be fully cached in most cases. This effectively provides a transparent 4944 and generalized version of what the "Baked Query" extension has offered 4945 for the ORM in past versions. The new feature can calculate the 4946 cache key for any given SQL construction based on the string that 4947 it would ultimately produce for a given dialect, allowing functions that 4948 compose the equivalent select(), Query(), insert(), update() or delete() 4949 object each time to have that statement cached after it's generated 4950 the first time. 4951 4952 The feature is enabled transparently but includes some new programming 4953 paradigms that may be employed to make the caching even more efficient. 4954 4955 .. seealso:: 4956 4957 :ref:`change_4639` 4958 4959 :ref:`sql_caching` 4960 4961 .. change:: 4962 :tags: orm, removed 4963 :tickets: 4638 4964 4965 All long-deprecated "extension" classes have been removed, including 4966 MapperExtension, SessionExtension, PoolListener, ConnectionProxy, 4967 AttributeExtension. These classes have been deprecated since version 0.7 4968 long superseded by the event listener system. 4969 4970 4971 .. change:: 4972 :tags: feature, mssql, sql 4973 :tickets: 4384 4974 4975 Added support for the :class:`_types.JSON` datatype on the SQL Server 4976 dialect using the :class:`_mssql.JSON` implementation, which implements SQL 4977 Server's JSON functionality against the ``NVARCHAR(max)`` datatype as per 4978 SQL Server documentation. Implementation courtesy Gord Thompson. 4979 4980 .. change:: 4981 :tags: change, sql 4982 :tickets: 4868 4983 4984 Added a core :class:`Values` object that enables a VALUES construct 4985 to be used in the FROM clause of an SQL statement for databases that 4986 support it (mainly PostgreSQL and SQL Server). 4987 4988 .. change:: 4989 :tags: usecase, mysql 4990 :tickets: 5496 4991 4992 Added a new dialect token "mariadb" that may be used in place of "mysql" in 4993 the :func:`_sa.create_engine` URL. This will deliver a MariaDB dialect 4994 subclass of the MySQLDialect in use that forces the "is_mariadb" flag to 4995 True. The dialect will raise an error if a server version string that does 4996 not indicate MariaDB in use is received. This is useful for 4997 MariaDB-specific testing scenarios as well as to support applications that 4998 are hardcoding to MariaDB-only concepts. As MariaDB and MySQL featuresets 4999 and usage patterns continue to diverge, this pattern may become more 5000 prominent. 5001 5002 5003 .. change:: 5004 :tags: bug, postgresql 5005 5006 The pg8000 dialect has been revised and modernized for the most recent 5007 version of the pg8000 driver for PostgreSQL. Pull request courtesy Tony 5008 Locke. Note that this necessarily pins pg8000 at 1.16.6 or greater, 5009 which no longer has Python 2 support. Python 2 users who require pg8000 5010 should ensure their requirements are pinned at ``SQLAlchemy<1.4``. 5011 5012 .. change:: 5013 :tags: bug, orm 5014 :tickets: 5074 5015 5016 The :class:`.Session` object no longer initiates a 5017 :class:`.SessionTransaction` object immediately upon construction or after 5018 the previous transaction is closed; instead, "autobegin" logic now 5019 initiates the new :class:`.SessionTransaction` on demand when it is next 5020 needed. Rationale includes to remove reference cycles from a 5021 :class:`.Session` that has been closed out, as well as to remove the 5022 overhead incurred by the creation of :class:`.SessionTransaction` objects 5023 that are often discarded immediately. This change affects the behavior of 5024 the :meth:`.SessionEvents.after_transaction_create` hook in that the event 5025 will be emitted when the :class:`.Session` first requires a 5026 :class:`.SessionTransaction` be present, rather than whenever the 5027 :class:`.Session` were created or the previous :class:`.SessionTransaction` 5028 were closed. Interactions with the :class:`_engine.Engine` and the database 5029 itself remain unaffected. 5030 5031 .. seealso:: 5032 5033 :ref:`change_5074` 5034 5035 5036 .. change:: 5037 :tags: oracle, change 5038 5039 The LIMIT / OFFSET scheme used in Oracle now makes use of named subqueries 5040 rather than unnamed subqueries when it transparently rewrites a SELECT 5041 statement to one that uses a subquery that includes ROWNUM. The change is 5042 part of a larger change where unnamed subqueries are no longer directly 5043 supported by Core, as well as to modernize the internal use of the select() 5044 construct within the Oracle dialect. 5045 5046 5047 .. change:: 5048 :tags: feature, engine, orm 5049 :tickets: 3414 5050 5051 SQLAlchemy now includes support for Python asyncio within both Core and 5052 ORM, using the included :ref:`asyncio extension <asyncio_toplevel>`. The 5053 extension makes use of the `greenlet 5054 <https://greenlet.readthedocs.io/en/latest/>`_ library in order to adapt 5055 SQLAlchemy's sync-oriented internals such that an asyncio interface that 5056 ultimately interacts with an asyncio database adapter is now feasible. The 5057 single driver supported at the moment is the 5058 :ref:`dialect-postgresql-asyncpg` driver for PostgreSQL. 5059 5060 .. seealso:: 5061 5062 :ref:`change_3414` 5063 5064 5065 .. change:: 5066 :tags: removed, sql 5067 5068 Removed the ``sqlalchemy.sql.visitors.iterate_depthfirst`` and 5069 ``sqlalchemy.sql.visitors.traverse_depthfirst`` functions. These functions 5070 were unused by any part of SQLAlchemy. The 5071 :func:`_sa.sql.visitors.iterate` and :func:`_sa.sql.visitors.traverse` 5072 functions are commonly used for these functions. Also removed unused 5073 options from the remaining functions including "column_collections", 5074 "schema_visitor". 5075 5076 5077 .. change:: 5078 :tags: orm, performance 5079 5080 The bulk update and delete methods :meth:`.Query.update` and 5081 :meth:`.Query.delete`, as well as their 2.0-style counterparts, now make 5082 use of RETURNING when the "fetch" strategy is used in order to fetch the 5083 list of affected primary key identites, rather than emitting a separate 5084 SELECT, when the backend in use supports RETURNING. Additionally, the 5085 "fetch" strategy will in ordinary cases not expire the attributes that have 5086 been updated, and will instead apply the updated values directly in the 5087 same way that the "evaluate" strategy does, to avoid having to refresh the 5088 object. The "evaluate" strategy will also fall back to expiring 5089 attributes that were updated to a SQL expression that was unevaluable in 5090 Python. 5091 5092 .. seealso:: 5093 5094 :ref:`change_orm_update_returning_14` 5095 5096 .. change:: 5097 :tags: bug, orm 5098 :tickets: 4829 5099 5100 Added new entity-targeting capabilities to the ORM query context 5101 help with the case where the :class:`.Session` is using a bind dictionary 5102 against mapped classes, rather than a single bind, and the :class:`_query.Query` 5103 is against a Core statement that was ultimately generated from a method 5104 such as :meth:`_query.Query.subquery`. First implemented using a deep 5105 search, the current approach leverages the unified :func:`_sql.select` 5106 construct to keep track of the first mapper that is part of 5107 the construct. 5108 5109 5110 .. change:: 5111 :tags: mssql 5112 5113 The mssql dialect will assume that at least MSSQL 2005 is used. 5114 There is no hard exception raised if a previous version is detected, 5115 but operations may fail for older versions. 5116 5117 .. change:: 5118 :tags: bug, inheritance, orm 5119 :tickets: 4212 5120 5121 An :class:`.ArgumentError` is now raised if both the ``selectable`` and 5122 ``flat`` parameters are set to True in :func:`.orm.with_polymorphic`. The 5123 selectable name is already aliased and applying flat=True overrides the 5124 selectable name with an anonymous name that would've previously caused the 5125 code to break. Pull request courtesy Ramon Williams. 5126 5127 .. change:: 5128 :tags: mysql, usecase 5129 :tickets: 4976 5130 5131 Added support for use of the :class:`.Sequence` construct with MariaDB 10.3 5132 and greater, as this is now supported by this database. The construct 5133 integrates with the :class:`_schema.Table` object in the same way that it does for 5134 other databases like PostgreSQL and Oracle; if is present on the integer 5135 primary key "autoincrement" column, it is used to generate defaults. For 5136 backwards compatibility, to support a :class:`_schema.Table` that has a 5137 :class:`.Sequence` on it to support sequence only databases like Oracle, 5138 while still not having the sequence fire off for MariaDB, the optional=True 5139 flag should be set, which indicates the sequence should only be used to 5140 generate the primary key if the target database offers no other option. 5141 5142 .. seealso:: 5143 5144 :ref:`change_4976` 5145 5146 5147 .. change:: 5148 :tags: deprecated, engine 5149 :tickets: 4634 5150 5151 The :paramref:`_schema.MetaData.bind` argument as well as the overall 5152 concept of "bound metadata" is deprecated in SQLAlchemy 1.4 and will be 5153 removed in SQLAlchemy 2.0. The parameter as well as related functions now 5154 emit a :class:`_exc.RemovedIn20Warning` when :ref:`deprecation_20_mode` is 5155 in use. 5156 5157 .. seealso:: 5158 5159 :ref:`migration_20_implicit_execution` 5160 5161 5162 5163 .. change:: 5164 :tags: change, extensions 5165 :tickets: 5142 5166 5167 Added new parameter :paramref:`_automap.AutomapBase.prepare.autoload_with` 5168 which supersedes :paramref:`_automap.AutomapBase.prepare.reflect` 5169 and :paramref:`_automap.AutomapBase.prepare.engine`. 5170 5171 5172 5173 .. change:: 5174 :tags: usecase, mssql, postgresql 5175 :tickets: 4966 5176 5177 Added support for inspection / reflection of partial indexes / filtered 5178 indexes, i.e. those which use the ``mssql_where`` or ``postgresql_where`` 5179 parameters, with :class:`_schema.Index`. The entry is both part of the 5180 dictionary returned by :meth:`.Inspector.get_indexes` as well as part of a 5181 reflected :class:`_schema.Index` construct that was reflected. Pull 5182 request courtesy Ramon Williams. 5183 5184 .. change:: 5185 :tags: mssql, feature 5186 :tickets: 4235, 4633 5187 5188 Added support for "CREATE SEQUENCE" and full :class:`.Sequence` support for 5189 Microsoft SQL Server. This removes the deprecated feature of using 5190 :class:`.Sequence` objects to manipulate IDENTITY characteristics which 5191 should now be performed using ``mssql_identity_start`` and 5192 ``mssql_identity_increment`` as documented at :ref:`mssql_identity`. The 5193 change includes a new parameter :paramref:`.Sequence.data_type` to 5194 accommodate SQL Server's choice of datatype, which for that backend 5195 includes INTEGER, BIGINT, and DECIMAL(n, 0). The default starting value 5196 for SQL Server's version of :class:`.Sequence` has been set at 1; this 5197 default is now emitted within the CREATE SEQUENCE DDL for all backends. 5198 5199 .. seealso:: 5200 5201 :ref:`change_4235` 5202 5203 .. change:: 5204 :tags: bug, orm 5205 :tickets: 4718 5206 5207 Fixed issue in polymorphic loading internals which would fall back to a 5208 more expensive, soon-to-be-deprecated form of result column lookup within 5209 certain unexpiration scenarios in conjunction with the use of 5210 "with_polymorphic". 5211 5212 .. change:: 5213 :tags: mssql, reflection 5214 :tickets: 5527 5215 5216 As part of the support for reflecting :class:`_schema.Identity` objects, 5217 the method :meth:`_reflection.Inspector.get_columns` no longer returns 5218 ``mssql_identity_start`` and ``mssql_identity_increment`` as part of the 5219 ``dialect_options``. Use the information in the ``identity`` key instead. 5220 5221 .. change:: 5222 :tags: schema, sql 5223 :tickets: 5362, 5324, 5360 5224 5225 Added the :class:`_schema.Identity` construct that can be used to 5226 configure identity columns rendered with GENERATED { ALWAYS | 5227 BY DEFAULT } AS IDENTITY. Currently the supported backends are 5228 PostgreSQL >= 10, Oracle >= 12 and MSSQL (with different syntax 5229 and a subset of functionalities). 5230 5231 .. change:: 5232 :tags: change, orm, sql 5233 5234 A selection of Core and ORM query objects now perform much more of their 5235 Python computational tasks within the compile step, rather than at 5236 construction time. This is to support an upcoming caching model that will 5237 provide for caching of the compiled statement structure based on a cache 5238 key that is derived from the statement construct, which itself is expected 5239 to be newly constructed in Python code each time it is used. This means 5240 that the internal state of these objects may not be the same as it used to 5241 be, as well as that some but not all error raise scenarios for various 5242 kinds of argument validation will occur within the compilation / execution 5243 phase, rather than at statement construction time. See the migration 5244 notes linked below for complete details. 5245 5246 .. seealso:: 5247 5248 :ref:`change_deferred_construction` 5249 5250 5251 .. change:: 5252 :tags: usecase, mssql, reflection 5253 :tickets: 5506 5254 5255 Added support for reflection of temporary tables with the SQL Server dialect. 5256 Table names that are prefixed by a pound sign "#" are now introspected from 5257 the MSSQL "tempdb" system catalog. 5258 5259 .. change:: 5260 :tags: firebird, deprecated 5261 :tickets: 5189 5262 5263 The Firebird dialect is deprecated, as there is now a 3rd party 5264 dialect that supports this database. 5265 5266 .. change:: 5267 :tags: misc, deprecated 5268 :tickets: 5189 5269 5270 The Sybase dialect is deprecated. 5271 5272 5273 .. change:: 5274 :tags: mssql, deprecated 5275 :tickets: 5189 5276 5277 The adodbapi and mxODBC dialects are deprecated. 5278 5279 5280 .. change:: 5281 :tags: mysql, deprecated 5282 :tickets: 5189 5283 5284 The OurSQL dialect is deprecated. 5285 5286 .. change:: 5287 :tags: postgresql, deprecated 5288 :tickets: 5189 5289 5290 The pygresql and py-postgresql dialects are deprecated. 5291 5292 .. change:: 5293 :tags: bug, sql 5294 :tickets: 4649, 4569 5295 5296 Registered function names based on :class:`.GenericFunction` are now 5297 retrieved in a case-insensitive fashion in all cases, removing the 5298 deprecation logic from 1.3 which temporarily allowed multiple 5299 :class:`.GenericFunction` objects to exist with differing cases. A 5300 :class:`.GenericFunction` that replaces another on the same name whether or 5301 not it's case sensitive emits a warning before replacing the object. 5302 5303 .. change:: 5304 :tags: orm, performance, postgresql 5305 :tickets: 5263 5306 5307 Implemented support for the psycopg2 ``execute_values()`` extension 5308 within the ORM flush process via the enhancements to Core made 5309 in :ticket:`5401`, so that this extension is used 5310 both as a strategy to batch INSERT statements together as well as 5311 that RETURNING may now be used among multiple parameter sets to 5312 retrieve primary key values back in batch. This allows nearly 5313 all INSERT statements emitted by the ORM on behalf of PostgreSQL 5314 to be submitted in batch and also via the ``execute_values()`` 5315 extension which benches at five times faster than plain 5316 executemany() for this particular backend. 5317 5318 .. seealso:: 5319 5320 :ref:`change_5263` 5321 5322 .. change:: 5323 :tags: change, general 5324 :tickets: 4789 5325 5326 "python setup.py test" is no longer a test runner, as this is deprecated by 5327 Pypa. Please use "tox" with no arguments for a basic test run. 5328 5329 5330 .. change:: 5331 :tags: usecase, oracle 5332 :tickets: 4857 5333 5334 The max_identifier_length for the Oracle dialect is now 128 characters by 5335 default, unless compatibility version less than 12.2 upon first connect, in 5336 which case the legacy length of 30 characters is used. This is a 5337 continuation of the issue as committed to the 1.3 series which adds max 5338 identifier length detection upon first connect as well as warns for the 5339 change in Oracle server. 5340 5341 .. seealso:: 5342 5343 :ref:`oracle_max_identifier_lengths` - in the Oracle dialect documentation 5344 5345 5346 .. change:: 5347 :tags: bug, oracle 5348 :tickets: 4971 5349 5350 The :class:`_oracle.INTERVAL` class of the Oracle dialect is now correctly 5351 a subclass of the abstract version of :class:`.Interval` as well as the 5352 correct "emulated" base class, which allows for correct behavior under both 5353 native and non-native modes; previously it was only based on 5354 :class:`.TypeEngine`. 5355 5356 5357 .. change:: 5358 :tags: bug, orm 5359 :tickets: 4994 5360 5361 An error is raised if any persistence-related "cascade" settings are made 5362 on a :func:`_orm.relationship` that also sets up viewonly=True. The "cascade" 5363 settings now default to non-persistence related settings only when viewonly 5364 is also set. This is the continuation from :ticket:`4993` where this 5365 setting was changed to emit a warning in 1.3. 5366 5367 .. seealso:: 5368 5369 :ref:`change_4994` 5370 5371 5372 5373 .. change:: 5374 :tags: bug, sql 5375 :tickets: 5054 5376 5377 Creating an :func:`.and_` or :func:`.or_` construct with no arguments or 5378 empty ``*args`` will now emit a deprecation warning, as the SQL produced is 5379 a no-op (i.e. it renders as a blank string). This behavior is considered to 5380 be non-intuitive, so for empty or possibly empty :func:`.and_` or 5381 :func:`.or_` constructs, an appropriate default boolean should be included, 5382 such as ``and_(True, *args)`` or ``or_(False, *args)``. As has been the 5383 case for many major versions of SQLAlchemy, these particular boolean 5384 values will not render if the ``*args`` portion is non-empty. 5385 5386 .. change:: 5387 :tags: removed, sql 5388 5389 Removed the concept of a bound engine from the :class:`.Compiler` object, 5390 and removed the ``.execute()`` and ``.scalar()`` methods from 5391 :class:`.Compiler`. These were essentially forgotten methods from over a 5392 decade ago and had no practical use, and it's not appropriate for the 5393 :class:`.Compiler` object itself to be maintaining a reference to an 5394 :class:`_engine.Engine`. 5395 5396 .. change:: 5397 :tags: performance, engine 5398 :tickets: 4524 5399 5400 The pool "pre-ping" feature has been refined to not invoke for a DBAPI 5401 connection that was just opened in the same checkout operation. pre ping 5402 only applies to a DBAPI connection that's been checked into the pool 5403 and is being checked out again. 5404 5405 .. change:: 5406 :tags: deprecated, engine 5407 5408 The ``server_side_cursors`` engine-wide parameter is deprecated and will be 5409 removed in a future release. For unbuffered cursors, the 5410 :paramref:`_engine.Connection.execution_options.stream_results` execution 5411 option should be used on a per-execution basis. 5412 5413 .. change:: 5414 :tags: bug, orm 5415 :tickets: 4699 5416 5417 Improved declarative inheritance scanning to not get tripped up when the 5418 same base class appears multiple times in the base inheritance list. 5419 5420 5421 .. change:: 5422 :tags: orm, change 5423 :tickets: 4395 5424 5425 The automatic uniquing of rows on the client side is turned off for the new 5426 :term:`2.0 style` of ORM querying. This improves both clarity and 5427 performance. However, uniquing of rows on the client side is generally 5428 necessary when using joined eager loading for collections, as there 5429 will be duplicates of the primary entity for each element in the 5430 collection because a join was used. This uniquing must now be manually 5431 enabled and can be achieved using the new 5432 :meth:`_engine.Result.unique` modifier. To avoid silent failure, the ORM 5433 explicitly requires the method be called when the result of an ORM 5434 query in 2.0 style makes use of joined load collections. The newer 5435 :func:`_orm.selectinload` strategy is likely preferable for eager loading 5436 of collections in any case. 5437 5438 .. seealso:: 5439 5440 :ref:`joinedload_not_uniqued` 5441 5442 .. change:: 5443 :tags: bug, orm 5444 :tickets: 4195 5445 5446 Fixed bug in ORM versioning feature where assignment of an explicit 5447 version_id for a counter configured against a mapped selectable where 5448 version_id_col is against the underlying table would fail if the previous 5449 value were expired; this was due to the fact that the mapped attribute 5450 would not be configured with active_history=True. 5451 5452 5453 .. change:: 5454 :tags: mssql, bug, schema 5455 :tickets: 5597 5456 5457 Fixed an issue where :meth:`_reflection.has_table` always returned 5458 ``False`` for temporary tables. 5459 5460 .. change:: 5461 :tags: mssql, engine 5462 :tickets: 4809 5463 5464 Deprecated the ``legacy_schema_aliasing`` parameter to 5465 :meth:`_sa.create_engine`. This is a long-outdated parameter that has 5466 defaulted to False since version 1.1. 5467 5468 .. change:: 5469 :tags: usecase, orm 5470 :tickets: 1653 5471 5472 The evaluator that takes place within the ORM bulk update and delete for 5473 synchronize_session="evaluate" now supports the IN and NOT IN operators. 5474 Tuple IN is also supported. 5475 5476 5477 .. change:: 5478 :tags: change, sql 5479 :tickets: 5284 5480 5481 The :func:`_expression.select` construct is moving towards a new calling 5482 form that is ``select(col1, col2, col3, ..)``, with all other keyword 5483 arguments removed, as these are all suited using generative methods. The 5484 single list of column or table arguments passed to ``select()`` is still 5485 accepted, however is no longer necessary if expressions are passed in a 5486 simple positional style. Other keyword arguments are disallowed when this 5487 form is used. 5488 5489 5490 .. seealso:: 5491 5492 :ref:`change_5284` 5493 5494 .. change:: 5495 :tags: change, sqlite 5496 :tickets: 4895 5497 5498 Dropped support for right-nested join rewriting to support old SQLite 5499 versions prior to 3.7.16, released in 2013. It is expected that 5500 all modern Python versions among those now supported should all include 5501 much newer versions of SQLite. 5502 5503 .. seealso:: 5504 5505 :ref:`change_4895` 5506 5507 5508 .. change:: 5509 :tags: deprecated, engine 5510 :tickets: 5131 5511 5512 The :meth:`_engine.Connection.connect` method is deprecated as is the concept of 5513 "connection branching", which copies a :class:`_engine.Connection` into a new one 5514 that has a no-op ".close()" method. This pattern is oriented around the 5515 "connectionless execution" concept which is also being removed in 2.0. 5516 5517 .. change:: 5518 :tags: bug, general 5519 :tickets: 4656, 4689 5520 5521 Refactored the internal conventions used to cross-import modules that have 5522 mutual dependencies between them, such that the inspected arguments of 5523 functions and methods are no longer modified. This allows tools like 5524 pylint, Pycharm, other code linters, as well as hypothetical pep-484 5525 implementations added in the future to function correctly as they no longer 5526 see missing arguments to function calls. The new approach is also 5527 simpler and more performant. 5528 5529 .. seealso:: 5530 5531 :ref:`change_4656` 5532 5533 .. change:: 5534 :tags: sql, usecase 5535 :tickets: 5191 5536 5537 Change the method ``__str`` of :class:`ColumnCollection` to avoid 5538 confusing it with a python list of string. 5539 5540 .. change:: 5541 :tags: sql, reflection 5542 :tickets: 4741 5543 5544 The "NO ACTION" keyword for foreign key "ON UPDATE" is now considered to be 5545 the default cascade for a foreign key on all supporting backends (SQlite, 5546 MySQL, PostgreSQL) and when detected is not included in the reflection 5547 dictionary; this is already the behavior for PostgreSQL and MySQL for all 5548 previous SQLAlchemy versions in any case. The "RESTRICT" keyword is 5549 positively stored when detected; PostgreSQL does report on this keyword, 5550 and MySQL as of version 8.0 does as well. On earlier MySQL versions, it is 5551 not reported by the database. 5552 5553 .. change:: 5554 :tags: sql, reflection 5555 :tickets: 5527, 5324 5556 5557 Added support for reflecting "identity" columns, which are now returned 5558 as part of the structure returned by :meth:`_reflection.Inspector.get_columns`. 5559 When reflecting full :class:`_schema.Table` objects, identity columns will 5560 be represented using the :class:`_schema.Identity` construct. 5561 Currently the supported backends are 5562 PostgreSQL >= 10, Oracle >= 12 and MSSQL (with different syntax 5563 and a subset of functionalities). 5564 5565 .. change:: 5566 :tags: feature, sql 5567 :tickets: 4753 5568 5569 The :func:`_expression.select` construct and related constructs now allow for 5570 duplication of column labels and columns themselves in the columns clause, 5571 mirroring exactly how column expressions were passed in. This allows 5572 the tuples returned by an executed result to match what was SELECTed 5573 for in the first place, which is how the ORM :class:`_query.Query` works, so 5574 this establishes better cross-compatibility between the two constructs. 5575 Additionally, it allows column-positioning-sensitive structures such as 5576 UNIONs (i.e. :class:`_selectable.CompoundSelect`) to be more intuitively constructed 5577 in those cases where a particular column might appear in more than one 5578 place. To support this change, the :class:`_expression.ColumnCollection` has been 5579 revised to support duplicate columns as well as to allow integer index 5580 access. 5581 5582 .. seealso:: 5583 5584 :ref:`change_4753` 5585 5586 5587 .. change:: 5588 :tags: renamed, sql 5589 :tickets: 4617 5590 5591 The :meth:`_expression.SelectBase.as_scalar` and :meth:`_query.Query.as_scalar` methods have 5592 been renamed to :meth:`_expression.SelectBase.scalar_subquery` and 5593 :meth:`_query.Query.scalar_subquery`, respectively. The old names continue to 5594 exist within 1.4 series with a deprecation warning. In addition, the 5595 implicit coercion of :class:`_expression.SelectBase`, :class:`_expression.Alias`, and other 5596 SELECT oriented objects into scalar subqueries when evaluated in a column 5597 context is also deprecated, and emits a warning that the 5598 :meth:`_expression.SelectBase.scalar_subquery` method should be called explicitly. 5599 This warning will in a later major release become an error, however the 5600 message will always be clear when :meth:`_expression.SelectBase.scalar_subquery` needs 5601 to be invoked. The latter part of the change is for clarity and to reduce 5602 the implicit decisionmaking by the query coercion system. The 5603 :meth:`.Subquery.as_scalar` method, which was previously 5604 ``Alias.as_scalar``, is also deprecated; ``.scalar_subquery()`` should be 5605 invoked directly from ` :func:`_expression.select` object or :class:`_query.Query` object. 5606 5607 This change is part of the larger change to convert :func:`_expression.select` objects 5608 to no longer be directly part of the "from clause" class hierarchy, which 5609 also includes an overhaul of the clause coercion system. 5610 5611 5612 .. change:: 5613 :tags: bug, mssql 5614 :tickets: 4980 5615 5616 Fixed the base class of the :class:`_mssql.DATETIMEOFFSET` datatype to 5617 be based on the :class:`.DateTime` class hierarchy, as this is a 5618 datetime-holding datatype. 5619 5620 5621 .. change:: 5622 :tags: bug, engine 5623 :tickets: 4712 5624 5625 The :class:`_engine.Connection` object will now not clear a rolled-back 5626 transaction until the outermost transaction is explicitly rolled back. 5627 This is essentially the same behavior that the ORM :class:`.Session` has 5628 had for a long time, where an explicit call to ``.rollback()`` on all 5629 enclosing transactions is required for the transaction to logically clear, 5630 even though the DBAPI-level transaction has already been rolled back. 5631 The new behavior helps with situations such as the "ORM rollback test suite" 5632 pattern where the test suite rolls the transaction back within the ORM 5633 scope, but the test harness which seeks to control the scope of the 5634 transaction externally does not expect a new transaction to start 5635 implicitly. 5636 5637 .. seealso:: 5638 5639 :ref:`change_4712` 5640 5641 5642 .. change:: 5643 :tags: deprecated, orm 5644 :tickets: 4719 5645 5646 Calling the :meth:`_query.Query.instances` method without passing a 5647 :class:`.QueryContext` is deprecated. The original use case for this was 5648 that a :class:`_query.Query` could yield ORM objects when given only the entities 5649 to be selected as well as a DBAPI cursor object. However, for this to work 5650 correctly there is essential metadata that is passed from a SQLAlchemy 5651 :class:`_engine.ResultProxy` that is derived from the mapped column expressions, 5652 which comes originally from the :class:`.QueryContext`. To retrieve ORM 5653 results from arbitrary SELECT statements, the :meth:`_query.Query.from_statement` 5654 method should be used. 5655 5656 5657 .. change:: 5658 :tags: deprecated, sql 5659 5660 The :class:`_schema.Table` class now raises a deprecation warning 5661 when columns with the same name are defined. To replace a column a new 5662 parameter :paramref:`_schema.Table.append_column.replace_existing` was 5663 added to the :meth:`_schema.Table.append_column` method. 5664 5665 The :meth:`_expression.ColumnCollection.contains_column` will now 5666 raises an error when called with a string, suggesting the caller 5667 to use ``in`` instead. 5668 5669 .. change:: 5670 :tags: deprecated, engine 5671 :tickets: 4878 5672 5673 The :paramref:`.case_sensitive` flag on :func:`_sa.create_engine` is 5674 deprecated; this flag was part of the transition of the result row object 5675 to allow case sensitive column matching as the default, while providing 5676 backwards compatibility for the former matching method. All string access 5677 for a row should be assumed to be case sensitive just like any other Python 5678 mapping. 5679 5680 5681 .. change:: 5682 :tags: bug, sql 5683 :tickets: 5127 5684 5685 Improved the :func:`_sql.tuple_` construct such that it behaves predictably 5686 when used in a columns-clause context. The SQL tuple is not supported as a 5687 "SELECT" columns clause element on most backends; on those that do 5688 (PostgreSQL, not surprisingly), the Python DBAPI does not have a "nested 5689 type" concept so there are still challenges in fetching rows for such an 5690 object. Use of :func:`_sql.tuple_` in a :func:`_sql.select` or 5691 :class:`_orm.Query` will now raise a :class:`_exc.CompileError` at the 5692 point at which the :func:`_sql.tuple_` object is seen as presenting itself 5693 for fetching rows (i.e., if the tuple is in the columns clause of a 5694 subquery, no error is raised). For ORM use,the :class:`_orm.Bundle` object 5695 is an explicit directive that a series of columns should be returned as a 5696 sub-tuple per row and is suggested by the error message. Additionally ,the 5697 tuple will now render with parenthesis in all contexts. Previously, the 5698 parenthesization would not render in a columns context leading to 5699 non-defined behavior. 5700 5701 .. change:: 5702 :tags: usecase, sql 5703 :tickets: 5576 5704 5705 Add support to ``FETCH {FIRST | NEXT} [ count ] 5706 {ROW | ROWS} {ONLY | WITH TIES}`` in the select for the supported 5707 backends, currently PostgreSQL, Oracle and MSSQL. 5708 5709 .. change:: 5710 :tags: feature, engine, alchemy2 5711 :tickets: 4644 5712 5713 Implemented the :paramref:`_sa.create_engine.future` parameter which 5714 enables forwards compatibility with SQLAlchemy 2. is used for forwards 5715 compatibility with SQLAlchemy 2. This engine features 5716 always-transactional behavior with autobegin. 5717 5718 .. seealso:: 5719 5720 :ref:`migration_20_toplevel` 5721 5722 .. change:: 5723 :tags: usecase, sql 5724 :tickets: 4449 5725 5726 Additional logic has been added such that certain SQL expressions which 5727 typically wrap a single database column will use the name of that column as 5728 their "anonymous label" name within a SELECT statement, potentially making 5729 key-based lookups in result tuples more intuitive. The primary example of 5730 this is that of a CAST expression, e.g. ``CAST(table.colname AS INTEGER)``, 5731 which will export its default name as "colname", rather than the usual 5732 "anon_1" label, that is, ``CAST(table.colname AS INTEGER) AS colname``. 5733 If the inner expression doesn't have a name, then the previous "anonymous 5734 label" logic is used. When using SELECT statements that make use of 5735 :meth:`_expression.Select.apply_labels`, such as those emitted by the ORM, the 5736 labeling logic will produce ``<tablename>_<inner column name>`` in the same 5737 was as if the column were named alone. The logic applies right now to the 5738 :func:`.cast` and :func:`.type_coerce` constructs as well as some 5739 single-element boolean expressions. 5740 5741 .. seealso:: 5742 5743 :ref:`change_4449` 5744 5745 .. change:: 5746 :tags: feature, orm 5747 :tickets: 5508 5748 5749 The ORM Declarative system is now unified into the ORM itself, with new 5750 import spaces under ``sqlalchemy.orm`` and new kinds of mappings. Support 5751 for decorator-based mappings without using a base class, support for 5752 classical style-mapper() calls that have access to the declarative class 5753 registry for relationships, and full integration of Declarative with 3rd 5754 party class attribute systems like ``dataclasses`` and ``attrs`` is now 5755 supported. 5756 5757 .. seealso:: 5758 5759 :ref:`change_5508` 5760 5761 :ref:`change_5027` 5762 5763 .. change:: 5764 :tags: removed, platform 5765 :tickets: 5094 5766 5767 Removed all dialect code related to support for Jython and zxJDBC. Jython 5768 has not been supported by SQLAlchemy for many years and it is not expected 5769 that the current zxJDBC code is at all functional; for the moment it just 5770 takes up space and adds confusion by showing up in documentation. At the 5771 moment, it appears that Jython has achieved Python 2.7 support in its 5772 releases but not Python 3. If Jython were to be supported again, the form 5773 it should take is against the Python 3 version of Jython, and the various 5774 zxJDBC stubs for various backends should be implemented as a third party 5775 dialect. 5776 5777 5778 .. change:: 5779 :tags: feature, sql 5780 :tickets: 5221 5781 5782 Enhanced the disambiguating labels feature of the 5783 :func:`_expression.select` construct such that when a select statement 5784 is used in a subquery, repeated column names from different tables are now 5785 automatically labeled with a unique label name, without the need to use the 5786 full "apply_labels()" feature that combines tablename plus column name. 5787 The disambiguated labels are available as plain string keys in the .c 5788 collection of the subquery, and most importantly the feature allows an ORM 5789 :func:`_orm.aliased` construct against the combination of an entity and an 5790 arbitrary subquery to work correctly, targeting the correct columns despite 5791 same-named columns in the source tables, without the need for an "apply 5792 labels" warning. 5793 5794 5795 .. seealso:: 5796 5797 :ref:`migration_20_query_from_self` - Illustrates the new 5798 disambiguation feature as part of a strategy to migrate away from the 5799 :meth:`_query.Query.from_self` method. 5800 5801 .. change:: 5802 :tags: usecase, postgresql 5803 :tickets: 5549 5804 5805 Added support for PostgreSQL "readonly" and "deferrable" flags for all of 5806 psycopg2, asyncpg and pg8000 dialects. This takes advantage of a newly 5807 generalized version of the "isolation level" API to support other kinds of 5808 session attributes set via execution options that are reliably reset 5809 when connections are returned to the connection pool. 5810 5811 .. seealso:: 5812 5813 :ref:`postgresql_readonly_deferrable` 5814 5815 .. change:: 5816 :tags: mysql, feature 5817 :tickets: 5459 5818 5819 Added support for MariaDB Connector/Python to the mysql dialect. Original 5820 pull request courtesy Georg Richter. 5821 5822 .. change:: 5823 :tags: usecase, orm 5824 :tickets: 5171 5825 5826 Enhanced logic that tracks if relationships will be conflicting with each 5827 other when they write to the same column to include simple cases of two 5828 relationships that should have a "backref" between them. This means that 5829 if two relationships are not viewonly, are not linked with back_populates 5830 and are not otherwise in an inheriting sibling/overriding arrangement, and 5831 will populate the same foreign key column, a warning is emitted at mapper 5832 configuration time warning that a conflict may arise. A new parameter 5833 :paramref:`_orm.relationship.overlaps` is added to suit those very rare cases 5834 where such an overlapping persistence arrangement may be unavoidable. 5835 5836 5837 .. change:: 5838 :tags: deprecated, orm 5839 :tickets: 4705, 5202 5840 5841 Using strings to represent relationship names in ORM operations such as 5842 :meth:`_orm.Query.join`, as well as strings for all ORM attribute names 5843 in loader options like :func:`_orm.selectinload` 5844 is deprecated and will be removed in SQLAlchemy 2.0. The class-bound 5845 attribute should be passed instead. This provides much better specificity 5846 to the given method, allows for modifiers such as ``of_type()``, and 5847 reduces internal complexity. 5848 5849 Additionally, the ``aliased`` and ``from_joinpoint`` parameters to 5850 :meth:`_orm.Query.join` are also deprecated. The :func:`_orm.aliased` 5851 construct now provides for a great deal of flexibility and capability 5852 and should be used directly. 5853 5854 .. seealso:: 5855 5856 :ref:`migration_20_orm_query_join_strings` 5857 5858 :ref:`migration_20_query_join_options` 5859 5860 .. change:: 5861 :tags: change, platform 5862 :tickets: 5404 5863 5864 Installation has been modernized to use setup.cfg for most package 5865 metadata. 5866 5867 .. change:: 5868 :tags: bug, sql, postgresql 5869 :tickets: 5653 5870 5871 Improved support for column names that contain percent signs in the string, 5872 including repaired issues involving anonymous labels that also embedded a 5873 column name with a percent sign in it, as well as re-established support 5874 for bound parameter names with percent signs embedded on the psycopg2 5875 dialect, using a late-escaping process similar to that used by the 5876 cx_Oracle dialect. 5877 5878 5879 .. change:: 5880 :tags: orm, deprecated 5881 :tickets: 5134 5882 5883 Deprecated logic in :meth:`_query.Query.distinct` that automatically adds 5884 columns in the ORDER BY clause to the columns clause; this will be removed 5885 in 2.0. 5886 5887 .. seealso:: 5888 5889 :ref:`migration_20_query_distinct` 5890 5891 .. change:: 5892 :tags: orm, removed 5893 :tickets: 4642 5894 5895 Remove the deprecated loader options ``joinedload_all``, ``subqueryload_all``, 5896 ``lazyload_all``, ``selectinload_all``. The normal version with method chaining 5897 should be used in their place. 5898 5899 .. change:: 5900 :tags: bug, sql 5901 :tickets: 4887 5902 5903 Custom functions that are created as subclasses of 5904 :class:`.FunctionElement` will now generate an "anonymous label" based on 5905 the "name" of the function just like any other :class:`.Function` object, 5906 e.g. ``"SELECT myfunc() AS myfunc_1"``. While SELECT statements no longer 5907 require labels in order for the result proxy object to function, the ORM 5908 still targets columns in rows by using objects as mapping keys, which works 5909 more reliably when the column expressions have distinct names. In any 5910 case, the behavior is now made consistent between functions generated by 5911 :attr:`.func` and those generated as custom :class:`.FunctionElement` 5912 objects. 5913 5914 5915 .. change:: 5916 :tags: usecase, extensions 5917 :tickets: 4887 5918 5919 Custom compiler constructs created using the :mod:`sqlalchemy.ext.compiled` 5920 extension will automatically add contextual information to the compiler 5921 when a custom construct is interpreted as an element in the columns 5922 clause of a SELECT statement, such that the custom element will be 5923 targetable as a key in result row mappings, which is the kind of targeting 5924 that the ORM uses in order to match column elements into result tuples. 5925 5926 .. change:: 5927 :tags: engine, bug 5928 :tickets: 5497 5929 5930 Adjusted the dialect initialization process such that the 5931 :meth:`_engine.Dialect.on_connect` is not called a second time 5932 on the first connection. The hook is called first, then the 5933 :meth:`_engine.Dialect.initialize` is called if that connection is the 5934 first for that dialect, then no more events are called. This eliminates 5935 the two calls to the "on_connect" function which can produce very 5936 difficult debugging situations. 5937 5938 .. change:: 5939 :tags: feature, engine, pyodbc 5940 :tickets: 5649 5941 5942 Reworked the "setinputsizes()" set of dialect hooks to be correctly 5943 extensible for any arbitrary DBAPI, by allowing dialects individual hooks 5944 that may invoke cursor.setinputsizes() in the appropriate style for that 5945 DBAPI. In particular this is intended to support pyodbc's style of usage 5946 which is fundamentally different from that of cx_Oracle. Added support 5947 for pyodbc. 5948 5949 5950 .. change:: 5951 :tags: deprecated, engine 5952 :tickets: 4846 5953 5954 "Implicit autocommit", which is the COMMIT that occurs when a DML or DDL 5955 statement is emitted on a connection, is deprecated and won't be part of 5956 SQLAlchemy 2.0. A 2.0-style warning is emitted when autocommit takes 5957 effect, so that the calling code may be adjusted to use an explicit 5958 transaction. 5959 5960 As part of this change, DDL methods such as 5961 :meth:`_schema.MetaData.create_all` when used against an 5962 :class:`_engine.Engine` will run the operation in a BEGIN block if one is 5963 not started already. 5964 5965 .. seealso:: 5966 5967 :ref:`deprecation_20_mode` 5968 5969 5970 .. change:: 5971 :tags: deprecated, orm 5972 :tickets: 5573 5973 5974 Passing keyword arguments to methods such as :meth:`_orm.Session.execute` 5975 to be passed into the :meth:`_orm.Session.get_bind` method is deprecated; 5976 the new :paramref:`_orm.Session.execute.bind_arguments` dictionary should 5977 be passed instead. 5978 5979 5980 .. change:: 5981 :tags: renamed, schema 5982 :tickets: 5413 5983 5984 Renamed the :meth:`_schema.Table.tometadata` method to 5985 :meth:`_schema.Table.to_metadata`. The previous name remains with a 5986 deprecation warning. 5987 5988 .. change:: 5989 :tags: bug, sql 5990 :tickets: 4336 5991 5992 Reworked the :meth:`_expression.ClauseElement.compare` methods in terms of a new 5993 visitor-based approach, and additionally added test coverage ensuring that 5994 all :class:`_expression.ClauseElement` subclasses can be accurately compared 5995 against each other in terms of structure. Structural comparison 5996 capability is used to a small degree within the ORM currently, however 5997 it also may form the basis for new caching features. 5998 5999 .. change:: 6000 :tags: feature, orm 6001 :tickets: 1763 6002 6003 Eager loaders, such as joined loading, SELECT IN loading, etc., when 6004 configured on a mapper or via query options will now be invoked during 6005 the refresh on an expired object; in the case of selectinload and 6006 subqueryload, since the additional load is for a single object only, 6007 the "immediateload" scheme is used in these cases which resembles the 6008 single-parent query emitted by lazy loading. 6009 6010 .. seealso:: 6011 6012 :ref:`change_1763` 6013 6014 .. change:: 6015 :tags: usecase, orm 6016 :tickets: 5018, 3903 6017 6018 The ORM bulk update and delete operations, historically available via the 6019 :meth:`_orm.Query.update` and :meth:`_orm.Query.delete` methods as well as 6020 via the :class:`_dml.Update` and :class:`_dml.Delete` constructs for 6021 :term:`2.0 style` execution, will now automatically accommodate for the 6022 additional WHERE criteria needed for a single-table inheritance 6023 discriminator in order to limit the statement to rows referring to the 6024 specific subtype requested. The new :func:`_orm.with_loader_criteria` 6025 construct is also supported for with bulk update/delete operations. 6026 6027 .. change:: 6028 :tags: engine, removed 6029 :tickets: 4643 6030 6031 Remove deprecated method ``get_primary_keys`` in the :class:`.Dialect` and 6032 :class:`_reflection.Inspector` classes. Please refer to the 6033 :meth:`.Dialect.get_pk_constraint` and :meth:`_reflection.Inspector.get_primary_keys` 6034 methods. 6035 6036 Remove deprecated event ``dbapi_error`` and the method 6037 ``ConnectionEvents.dbapi_error``. Please refer to the 6038 :meth:`_events.ConnectionEvents.handle_error` event. 6039 This change also removes the attributes ``ExecutionContext.is_disconnect`` 6040 and ``ExecutionContext.exception``. 6041 6042 .. change:: 6043 :tags: removed, postgresql 6044 :tickets: 4643 6045 6046 Remove support for deprecated engine URLs of the form ``postgres://``; 6047 this has emitted a warning for many years and projects should be 6048 using ``postgresql://``. 6049 6050 .. change:: 6051 :tags: removed, mysql 6052 :tickets: 4643 6053 6054 Remove deprecated dialect ``mysql+gaerdbms`` that has been deprecated 6055 since version 1.0. Use the MySQLdb dialect directly. 6056 6057 Remove deprecated parameter ``quoting`` from :class:`.mysql.ENUM` 6058 and :class:`.mysql.SET` in the ``mysql`` dialect. The values passed to the 6059 enum or the set are quoted by SQLAlchemy when needed automatically. 6060 6061 .. change:: 6062 :tags: removed, orm 6063 :tickets: 4643 6064 6065 Remove deprecated function ``comparable_property``. Please refer to the 6066 :mod:`~sqlalchemy.ext.hybrid` extension. This also removes the function 6067 ``comparable_using`` in the declarative extension. 6068 6069 Remove deprecated function ``compile_mappers``. Please use 6070 :func:`.configure_mappers` 6071 6072 Remove deprecated method ``collection.linker``. Please refer to the 6073 :meth:`.AttributeEvents.init_collection` and 6074 :meth:`.AttributeEvents.dispose_collection` event handlers. 6075 6076 Remove deprecated method ``Session.prune`` and parameter 6077 ``Session.weak_identity_map``. See the recipe at 6078 :ref:`session_referencing_behavior` for an event-based approach to 6079 maintaining strong identity references. 6080 This change also removes the class ``StrongInstanceDict``. 6081 6082 Remove deprecated parameter ``mapper.order_by``. Use :meth:`_query.Query.order_by` 6083 to determine the ordering of a result set. 6084 6085 Remove deprecated parameter ``Session._enable_transaction_accounting``. 6086 6087 Remove deprecated parameter ``Session.is_modified.passive``. 6088 6089 .. change:: 6090 :tags: removed, schema 6091 :tickets: 4643 6092 6093 Remove deprecated class ``Binary``. Please use :class:`.LargeBinary`. 6094 6095 .. change:: 6096 :tags: removed, sql 6097 :tickets: 4643 6098 6099 Remove deprecated methods ``Compiled.compile``, ``ClauseElement.__and__`` and 6100 ``ClauseElement.__or__`` and attribute ``Over.func``. 6101 6102 Remove deprecated ``FromClause.count`` method. Please use the 6103 :class:`_functions.count` function available from the 6104 :attr:`.func` namespace. 6105 6106 .. change:: 6107 :tags: removed, sql 6108 :tickets: 4643 6109 6110 Remove deprecated parameters ``text.bindparams`` and ``text.typemap``. 6111 Please refer to the :meth:`_expression.TextClause.bindparams` and 6112 :meth:`_expression.TextClause.columns` methods. 6113 6114 Remove deprecated parameter ``Table.useexisting``. Please use 6115 :paramref:`_schema.Table.extend_existing`. 6116 6117 .. change:: 6118 :tags: bug, orm 6119 :tickets: 4836 6120 6121 An exception is now raised if the ORM loads a row for a polymorphic 6122 instance that has a primary key but the discriminator column is NULL, as 6123 discriminator columns should not be null. 6124 6125 6126 6127 .. change:: 6128 :tags: bug, sql 6129 :tickets: 4002 6130 6131 Deprecate usage of ``DISTINCT ON`` in dialect other than PostgreSQL. 6132 Deprecate old usage of string distinct in MySQL dialect 6133 6134 .. change:: 6135 :tags: orm, usecase 6136 :tickets: 5237 6137 6138 Update :paramref:`_orm.relationship.sync_backref` flag in a relationship 6139 to make it implicitly ``False`` in ``viewonly=True`` relationships, 6140 preventing synchronization events. 6141 6142 6143 .. seealso:: 6144 6145 :ref:`change_5237_14` 6146 6147 .. change:: 6148 :tags: deprecated, engine 6149 :tickets: 4877 6150 6151 Deprecated the behavior by which a :class:`_schema.Column` can be used as the key 6152 in a result set row lookup, when that :class:`_schema.Column` is not part of the 6153 SQL selectable that is being selected; that is, it is only matched on name. 6154 A deprecation warning is now emitted for this case. Various ORM use 6155 cases, such as those involving :func:`_expression.text` constructs, have been improved 6156 so that this fallback logic is avoided in most cases. 6157 6158 6159 .. change:: 6160 :tags: change, schema 6161 :tickets: 5367 6162 6163 The :paramref:`.Enum.create_constraint` and 6164 :paramref:`.Boolean.create_constraint` parameters now default to False, 6165 indicating when a so-called "non-native" version of these two datatypes is 6166 created, a CHECK constraint will not be generated by default. These CHECK 6167 constraints present schema-management maintenance complexities that should 6168 be opted in to, rather than being turned on by default. 6169 6170 .. seealso:: 6171 6172 :ref:`change_5367` 6173 6174 .. change:: 6175 :tags: feature, sql 6176 :tickets: 4645 6177 6178 The "expanding IN" feature, which generates IN expressions at query 6179 execution time which are based on the particular parameters associated with 6180 the statement execution, is now used for all IN expressions made against 6181 lists of literal values. This allows IN expressions to be fully cacheable 6182 independently of the list of values being passed, and also includes support 6183 for empty lists. For any scenario where the IN expression contains 6184 non-literal SQL expressions, the old behavior of pre-rendering for each 6185 position in the IN is maintained. The change also completes support for 6186 expanding IN with tuples, where previously type-specific bind processors 6187 weren't taking effect. 6188 6189 .. seealso:: 6190 6191 :ref:`change_4645` 6192 6193 .. change:: 6194 :tags: bug, mysql 6195 :tickets: 4189 6196 6197 MySQL dialect's server_version_info tuple is now all numeric. String 6198 tokens like "MariaDB" are no longer present so that numeric comparison 6199 works in all cases. The .is_mariadb flag on the dialect should be 6200 consulted for whether or not mariadb was detected. Additionally removed 6201 structures meant to support extremely old MySQL versions 3.x and 4.x; 6202 the minimum MySQL version supported is now version 5.0.2. 6203 6204 6205 .. change:: 6206 :tags: engine, feature 6207 :tickets: 2056 6208 6209 Added new reflection method :meth:`.Inspector.get_sequence_names` which 6210 returns all the sequences defined and :meth:`.Inspector.has_sequence` to 6211 check if a particular sequence exits. 6212 Support for this method has been added to the backend that support 6213 :class:`.Sequence`: PostgreSQL, Oracle and MariaDB >= 10.3. 6214 6215 .. change:: 6216 :tags: usecase, postgresql 6217 :tickets: 4914 6218 6219 The maximum buffer size for the :class:`.BufferedRowResultProxy`, which 6220 is used by dialects such as PostgreSQL when ``stream_results=True``, can 6221 now be set to a number greater than 1000 and the buffer will grow to 6222 that size. Previously, the buffer would not go beyond 1000 even if the 6223 value were set larger. The growth of the buffer is also now based 6224 on a simple multiplying factor currently set to 5. Pull request courtesy 6225 Soumaya Mauthoor. 6226 6227 6228 .. change:: 6229 :tags: bug, orm 6230 :tickets: 4519 6231 6232 Accessing a collection-oriented attribute on a newly created object no 6233 longer mutates ``__dict__``, but still returns an empty collection as has 6234 always been the case. This allows collection-oriented attributes to work 6235 consistently in comparison to scalar attributes which return ``None``, but 6236 also don't mutate ``__dict__``. In order to accommodate for the collection 6237 being mutated, the same empty collection is returned each time once 6238 initially created, and when it is mutated (e.g. an item appended, added, 6239 etc.) it is then moved into ``__dict__``. This removes the last of 6240 mutating side-effects on read-only attribute access within the ORM. 6241 6242 .. seealso:: 6243 6244 :ref:`change_4519` 6245 6246 .. change:: 6247 :tags: change, sql 6248 :tickets: 4617 6249 6250 As part of the SQLAlchemy 2.0 migration project, a conceptual change has 6251 been made to the role of the :class:`_expression.SelectBase` class hierarchy, 6252 which is the root of all "SELECT" statement constructs, in that they no 6253 longer serve directly as FROM clauses, that is, they no longer subclass 6254 :class:`_expression.FromClause`. For end users, the change mostly means that any 6255 placement of a :func:`_expression.select` construct in the FROM clause of another 6256 :func:`_expression.select` requires first that it be wrapped in a subquery first, 6257 which historically is through the use of the :meth:`_expression.SelectBase.alias` 6258 method, and is now also available through the use of 6259 :meth:`_expression.SelectBase.subquery`. This was usually a requirement in any 6260 case since several databases don't accept unnamed SELECT subqueries 6261 in their FROM clause in any case. 6262 6263 .. seealso:: 6264 6265 :ref:`change_4617` 6266 6267 .. change:: 6268 :tags: change, sql 6269 :tickets: 4617 6270 6271 Added a new Core class :class:`.Subquery`, which takes the place of 6272 :class:`_expression.Alias` when creating named subqueries against a :class:`_expression.SelectBase` 6273 object. :class:`.Subquery` acts in the same way as :class:`_expression.Alias` 6274 and is produced from the :meth:`_expression.SelectBase.subquery` method; for 6275 ease of use and backwards compatibility, the :meth:`_expression.SelectBase.alias` 6276 method is synonymous with this new method. 6277 6278 .. seealso:: 6279 6280 :ref:`change_4617` 6281 6282 .. change:: 6283 :tags: change, orm 6284 :tickets: 4617 6285 6286 The ORM will now warn when asked to coerce a :func:`_expression.select` construct into 6287 a subquery implicitly. This occurs within places such as the 6288 :meth:`_query.Query.select_entity_from` and :meth:`_query.Query.select_from` methods 6289 as well as within the :func:`.with_polymorphic` function. When a 6290 :class:`_expression.SelectBase` (which is what's produced by :func:`_expression.select`) or 6291 :class:`_query.Query` object is passed directly to these functions and others, 6292 the ORM is typically coercing them to be a subquery by calling the 6293 :meth:`_expression.SelectBase.alias` method automatically (which is now superseded by 6294 the :meth:`_expression.SelectBase.subquery` method). See the migration notes linked 6295 below for further details. 6296 6297 .. seealso:: 6298 6299 :ref:`change_4617` 6300 6301 .. change:: 6302 :tags: bug, sql 6303 :tickets: 4617 6304 6305 The ORDER BY clause of a :class:`_selectable.CompoundSelect`, e.g. UNION, EXCEPT, etc. 6306 will not render the table name associated with a given column when applying 6307 :meth:`_selectable.CompoundSelect.order_by` in terms of a :class:`_schema.Table` - bound 6308 column. Most databases require that the names in the ORDER BY clause be 6309 expressed as label names only which are matched to names in the first 6310 SELECT statement. The change is related to :ticket:`4617` in that a 6311 previous workaround was to refer to the ``.c`` attribute of the 6312 :class:`_selectable.CompoundSelect` in order to get at a column that has no table 6313 name. As the subquery is now named, this change allows both the workaround 6314 to continue to work, as well as allows table-bound columns as well as the 6315 :attr:`_selectable.CompoundSelect.selected_columns` collections to be usable in the 6316 :meth:`_selectable.CompoundSelect.order_by` method. 6317 6318 .. change:: 6319 :tags: bug, orm 6320 :tickets: 5226 6321 6322 The refresh of an expired object will now trigger an autoflush if the list 6323 of expired attributes include one or more attributes that were explicitly 6324 expired or refreshed using the :meth:`.Session.expire` or 6325 :meth:`.Session.refresh` methods. This is an attempt to find a middle 6326 ground between the normal unexpiry of attributes that can happen in many 6327 cases where autoflush is not desirable, vs. the case where attributes are 6328 being explicitly expired or refreshed and it is possible that these 6329 attributes depend upon other pending state within the session that needs to 6330 be flushed. The two methods now also gain a new flag 6331 :paramref:`.Session.expire.autoflush` and 6332 :paramref:`.Session.refresh.autoflush`, defaulting to True; when set to 6333 False, this will disable the autoflush that occurs on unexpire for these 6334 attributes. 6335 6336 .. change:: 6337 :tags: feature, sql 6338 :tickets: 5380 6339 6340 Along with the new transparent statement caching feature introduced as part 6341 of :ticket:`4369`, a new feature intended to decrease the Python overhead 6342 of creating statements is added, allowing lambdas to be used when 6343 indicating arguments being passed to a statement object such as select(), 6344 Query(), update(), etc., as well as allowing the construction of full 6345 statements within lambdas in a similar manner as that of the "baked query" 6346 system. The rationale of using lambdas is adapted from that of the "baked 6347 query" approach which uses lambdas to encapsulate any amount of Python code 6348 into a callable that only needs to be called when the statement is first 6349 constructed into a string. The new feature however is more sophisticated 6350 in that Python literal values that would be passed as parameters are 6351 automatically extracted, so that there is no longer a need to use 6352 bindparam() objects with such queries. Use of the feature is optional and 6353 can be used to as small or as great a degree as is desired, while still 6354 allowing statements to be fully cacheable. 6355 6356 .. seealso:: 6357 6358 :ref:`engine_lambda_caching` 6359 6360 6361 .. change:: 6362 :tags: feature, orm 6363 :tickets: 5027 6364 6365 Added support for direct mapping of Python classes that are defined using 6366 the Python ``dataclasses`` decorator. Pull request courtesy Václav 6367 Klusák. The new feature integrates into new support at the Declarative 6368 level for systems such as ``dataclasses`` and ``attrs``. 6369 6370 .. seealso:: 6371 6372 :ref:`change_5027` 6373 6374 :ref:`change_5508` 6375 6376 6377 .. change:: 6378 :tags: change, engine 6379 :tickets: 4710 6380 6381 The ``RowProxy`` class is no longer a "proxy" object, and is instead 6382 directly populated with the post-processed contents of the DBAPI row tuple 6383 upon construction. Now named :class:`.Row`, the mechanics of how the 6384 Python-level value processors have been simplified, particularly as it impacts the 6385 format of the C code, so that a DBAPI row is processed into a result tuple 6386 up front. The object returned by the :class:`_engine.ResultProxy` is now the 6387 :class:`.LegacyRow` subclass, which maintains mapping/tuple hybrid behavior, 6388 however the base :class:`.Row` class now behaves more fully like a named 6389 tuple. 6390 6391 .. seealso:: 6392 6393 :ref:`change_4710_core` 6394 6395 6396 .. change:: 6397 :tags: change, orm 6398 :tickets: 4710 6399 6400 The "KeyedTuple" class returned by :class:`_query.Query` is now replaced with the 6401 Core :class:`.Row` class, which behaves in the same way as KeyedTuple. 6402 In SQLAlchemy 2.0, both Core and ORM will return result rows using the same 6403 :class:`.Row` object. In the interim, Core uses a backwards-compatibility 6404 class :class:`.LegacyRow` that maintains the former mapping/tuple hybrid 6405 behavior used by "RowProxy". 6406 6407 .. seealso:: 6408 6409 :ref:`change_4710_orm` 6410 6411 .. change:: 6412 :tags: feature, orm 6413 :tickets: 4826 6414 6415 Added "raiseload" feature for ORM mapped columns via :paramref:`.orm.defer.raiseload` 6416 parameter on :func:`.defer` and :func:`.deferred`. This provides 6417 similar behavior for column-expression mapped attributes as the 6418 :func:`.raiseload` option does for relationship mapped attributes. The 6419 change also includes some behavioral changes to deferred columns regarding 6420 expiration; see the migration notes for details. 6421 6422 .. seealso:: 6423 6424 :ref:`change_4826` 6425 6426 6427 .. change:: 6428 :tags: bug, orm 6429 :tickets: 5150 6430 6431 The behavior of the :paramref:`_orm.relationship.cascade_backrefs` flag 6432 will be reversed in 2.0 and set to ``False`` unconditionally, such that 6433 backrefs don't cascade save-update operations from a forwards-assignment to 6434 a backwards assignment. A 2.0 deprecation warning is emitted when the 6435 parameter is left at its default of ``True`` at the point at which such a 6436 cascade operation actually takes place. The new behavior can be 6437 established as always by setting the flag to ``False`` on a specific 6438 :func:`_orm.relationship`, or more generally can be set up across the board 6439 by setting the the :paramref:`_orm.Session.future` flag to True. 6440 6441 .. seealso:: 6442 6443 :ref:`change_5150` 6444 6445 .. change:: 6446 :tags: deprecated, engine 6447 :tickets: 4755 6448 6449 Deprecated remaining engine-level introspection and utility methods 6450 including :meth:`_engine.Engine.run_callable`, :meth:`_engine.Engine.transaction`, 6451 :meth:`_engine.Engine.table_names`, :meth:`_engine.Engine.has_table`. The utility 6452 methods are superseded by modern context-manager patterns, and the table 6453 introspection tasks are suited by the :class:`_reflection.Inspector` object. 6454 6455 .. change:: 6456 :tags: removed, engine 6457 :tickets: 4755 6458 6459 The internal dialect method ``Dialect.reflecttable`` has been removed. A 6460 review of third party dialects has not found any making use of this method, 6461 as it was already documented as one that should not be used by external 6462 dialects. Additionally, the private ``Engine._run_visitor`` method 6463 is also removed. 6464 6465 6466 .. change:: 6467 :tags: removed, engine 6468 :tickets: 4755 6469 6470 The long-deprecated ``Inspector.get_table_names.order_by`` parameter has 6471 been removed. 6472 6473 .. change:: 6474 :tags: feature, engine 6475 :tickets: 4755 6476 6477 The :paramref:`_schema.Table.autoload_with` parameter now accepts an :class:`_reflection.Inspector` object 6478 directly, as well as any :class:`_engine.Engine` or :class:`_engine.Connection` as was the case before. 6479 6480 6481 .. change:: 6482 :tags: change, performance, engine, py3k 6483 :tickets: 5315 6484 6485 Disabled the "unicode returns" check that runs on dialect startup when 6486 running under Python 3, which for many years has occurred in order to test 6487 the current DBAPI's behavior for whether or not it returns Python Unicode 6488 or Py2K strings for the VARCHAR and NVARCHAR datatypes. The check still 6489 occurs by default under Python 2, however the mechanism to test the 6490 behavior will be removed in SQLAlchemy 2.0 when Python 2 support is also 6491 removed. 6492 6493 This logic was very effective when it was needed, however now that Python 3 6494 is standard, all DBAPIs are expected to return Python 3 strings for 6495 character datatypes. In the unlikely case that a third party DBAPI does 6496 not support this, the conversion logic within :class:`.String` is still 6497 available and the third party dialect may specify this in its upfront 6498 dialect flags by setting the dialect level flag ``returns_unicode_strings`` 6499 to one of :attr:`.String.RETURNS_CONDITIONAL` or 6500 :attr:`.String.RETURNS_BYTES`, both of which will enable Unicode conversion 6501 even under Python 3. 6502 6503 .. change:: 6504 :tags: renamed, sql 6505 :tickets: 5435, 5429 6506 6507 Several operators are renamed to achieve more consistent naming across 6508 SQLAlchemy. 6509 6510 The operator changes are: 6511 6512 * ``isfalse`` is now ``is_false`` 6513 * ``isnot_distinct_from`` is now ``is_not_distinct_from`` 6514 * ``istrue`` is now ``is_true`` 6515 * ``notbetween`` is now ``not_between`` 6516 * ``notcontains`` is now ``not_contains`` 6517 * ``notendswith`` is now ``not_endswith`` 6518 * ``notilike`` is now ``not_ilike`` 6519 * ``notlike`` is now ``not_like`` 6520 * ``notmatch`` is now ``not_match`` 6521 * ``notstartswith`` is now ``not_startswith`` 6522 * ``nullsfirst`` is now ``nulls_first`` 6523 * ``nullslast`` is now ``nulls_last`` 6524 * ``isnot`` is now ``is_not`` 6525 * ``not_in_`` is now ``not_in`` 6526 6527 Because these are core operators, the internal migration strategy for this 6528 change is to support legacy terms for an extended period of time -- if not 6529 indefinitely -- but update all documentation, tutorials, and internal usage 6530 to the new terms. The new terms are used to define the functions, and 6531 the legacy terms have been deprecated into aliases of the new terms. 6532 6533 6534 6535 .. change:: 6536 :tags: orm, deprecated 6537 :tickets: 5192 6538 6539 The :func:`.eagerload` and :func:`.relation` were old aliases and are 6540 now deprecated. Use :func:`_orm.joinedload` and :func:`_orm.relationship` 6541 respectively. 6542 6543 6544 .. change:: 6545 :tags: bug, sql 6546 :tickets: 4621 6547 6548 The :class:`_expression.Join` construct no longer considers the "onclause" as a source 6549 of additional FROM objects to be omitted from the FROM list of an enclosing 6550 :class:`_expression.Select` object as standalone FROM objects. This applies to an ON 6551 clause that includes a reference to another FROM object outside the JOIN; 6552 while this is usually not correct from a SQL perspective, it's also 6553 incorrect for it to be omitted, and the behavioral change makes the 6554 :class:`_expression.Select` / :class:`_expression.Join` behave a bit more intuitively. 6555 6556