diff -Nru sqlalchemy-1.3.20+ds1/debian/changelog sqlalchemy-1.3.22+ds1/debian/changelog --- sqlalchemy-1.3.20+ds1/debian/changelog 2020-12-07 17:46:11.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/debian/changelog 2020-12-30 16:25:19.000000000 +0000 @@ -1,8 +1,14 @@ -sqlalchemy (1.3.20+ds1-1build1) hirsute; urgency=medium +sqlalchemy (1.3.22+ds1-1) unstable; urgency=medium - * No-change rebuild to drop python3.8 extensions. + [ Debian Janitor ] + * Apply multi-arch hints. + + python-sqlalchemy-doc: Add Multi-Arch: foreign. + + python3-sqlalchemy-ext: Add Multi-Arch: same. - -- Matthias Klose Mon, 07 Dec 2020 18:46:11 +0100 + [ Piotr Ożarowski ] + * New upstream release + + -- Piotr Ożarowski Wed, 30 Dec 2020 17:25:19 +0100 sqlalchemy (1.3.20+ds1-1) unstable; urgency=medium diff -Nru sqlalchemy-1.3.20+ds1/debian/control sqlalchemy-1.3.22+ds1/debian/control --- sqlalchemy-1.3.20+ds1/debian/control 2020-10-24 15:51:53.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/debian/control 2020-12-30 16:21:14.000000000 +0000 @@ -24,6 +24,7 @@ libjs-underscore, ${misc:Depends}, Recommends: python3-sqlalchemy, +Multi-Arch: foreign Description: documentation for the SQLAlchemy Python library SQLAlchemy is an SQL database abstraction library for Python. . @@ -73,6 +74,7 @@ ${misc:Depends}, ${python3:Depends}, ${shlibs:Depends}, +Multi-Arch: same Description: SQL toolkit and Object Relational Mapper for Python3 - C extension SQLAlchemy is an SQL database abstraction library for Python. . diff -Nru sqlalchemy-1.3.20+ds1/debian/patches/0001-drop_notfound_page_from_docs.patch sqlalchemy-1.3.22+ds1/debian/patches/0001-drop_notfound_page_from_docs.patch --- sqlalchemy-1.3.20+ds1/debian/patches/0001-drop_notfound_page_from_docs.patch 2020-10-30 22:10:16.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/debian/patches/0001-drop_notfound_page_from_docs.patch 2020-12-30 16:25:19.000000000 +0000 @@ -8,10 +8,10 @@ 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/doc/build/conf.py b/doc/build/conf.py -index de35350..80ad6e7 100644 +index 532d7cd..7f75680 100644 --- a/doc/build/conf.py +++ b/doc/build/conf.py -@@ -267,7 +267,7 @@ html_last_updated_fmt = "%m/%d/%Y %H:%M:%S" +@@ -271,7 +271,7 @@ html_last_updated_fmt = "%m/%d/%Y %H:%M:%S" # Additional templates that should be rendered to pages, maps page names to # template names. diff -Nru sqlalchemy-1.3.20+ds1/doc/build/changelog/changelog_13.rst sqlalchemy-1.3.22+ds1/doc/build/changelog/changelog_13.rst --- sqlalchemy-1.3.20+ds1/doc/build/changelog/changelog_13.rst 2020-10-12 22:30:08.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/doc/build/changelog/changelog_13.rst 2020-12-18 21:05:31.000000000 +0000 @@ -11,6 +11,174 @@ :start-line: 5 .. changelog:: + :version: 1.3.22 + :released: December 18, 2020 + + .. change:: + :tags: bug, oracle + :tickets: 5784 + :versions: 1.4.0b2 + + Fixed regression which occured due to :ticket:`5755` which implemented + isolation level support for Oracle. It has been reported that many Oracle + accounts don't actually have permission to query the ``v$transaction`` + view so this feature has been altered to gracefully fallback when it fails + upon database connect, where the dialect will assume "READ COMMITTED" is + the default isolation level as was the case prior to SQLAlchemy 1.3.21. + However, explicit use of the :meth:`_engine.Connection.get_isolation_level` + method must now necessarily raise an exception, as Oracle databases with + this restriction explicitly disallow the user from reading the current + isolation level. + +.. changelog:: + :version: 1.3.21 + :released: December 17, 2020 + + .. change:: + :tags: bug, orm + :tickets: 5774 + :versions: 1.4.0b2 + + Added a comprehensive check and an informative error message for the case + where a mapped class, or a string mapped class name, is passed to + :paramref:`_orm.relationship.secondary`. This is an extremely common error + which warrants a clear message. + + Additionally, added a new rule to the class registry resolution such that + with regards to the :paramref:`_orm.relationship.secondary` parameter, if a + mapped class and its table are of the identical string name, the + :class:`.Table` will be favored when resolving this parameter. In all + other cases, the class continues to be favored if a class and table + share the identical name. + + .. change:: + :tags: sqlite, usecase + :tickets: 5685 + + Added ``sqlite_with_rowid=False`` dialect keyword to enable creating + tables as ``CREATE TABLE … WITHOUT ROWID``. Patch courtesy Sean Anderson. + + .. change:: + :tags: bug, sql + :tickets: 5691 + + A warning is emmitted if a returning() method such as + :meth:`_sql.Insert.returning` is called multiple times, as this does not + yet support additive operation. Version 1.4 will support additive + operation for this. Additionally, any combination of the + :meth:`_sql.Insert.returning` and :meth:`_sql.ValuesBase.return_defaults` + methods now raises an error as these methods are mutually exclusive; + previously the operation would fail silently. + + + .. change:: + :tags: bug, mssql + :tickets: 5751 + + Fixed bug where a CREATE INDEX statement was rendered incorrectly when + both ``mssql-include`` and ``mssql_where`` were specified. Pull request + courtesy @Adiorz. + + .. change:: + :tags: bug, postgresql, mysql + :tickets: 5729 + :versions: 1.4.0b2 + + Fixed regression introduced in 1.3.2 for the PostgreSQL dialect, also + copied out to the MySQL dialect's feature in 1.3.18, where usage of a non + :class:`_schema.Table` construct such as :func:`_sql.text` as the argument + to :paramref:`_sql.Select.with_for_update.of` would fail to be accommodated + correctly within the PostgreSQL or MySQL compilers. + + + .. change:: + :tags: bug, mssql + :tickets: 5646 + + Added SQL Server code "01000" to the list of disconnect codes. + + + .. change:: + :tags: usecase, postgresql + :tickets: 5604 + :versions: 1.4.0b2 + + Added new parameter :paramref:`_postgresql.ExcludeConstraint.ops` to the + :class:`_postgresql.ExcludeConstraint` object, to support operator class + specification with this constraint. Pull request courtesy Alon Menczer. + + .. change:: + :tags: bug, mysql, reflection + :tickets: 5744 + :versions: 1.4.0b2 + + Fixed issue where reflecting a server default on MariaDB only that + contained a decimal point in the value would fail to be reflected + correctly, leading towards a reflected table that lacked any server + default. + + + .. change:: + :tags: bug, orm + :tickets: 5664 + + Fixed bug in :meth:`_query.Query.update` where objects in the + :class:`_ormsession.Session` that were already expired would be + unnecessarily SELECTed individually when they were refreshed by the + "evaluate"synchronize strategy. + + .. change:: + :tags: usecase, oracle + :tickets: 5755 + + Implemented support for the SERIALIZABLE isolation level for Oracle + databases, as well as a real implementation for + :meth:`_engine.Connection.get_isolation_level`. + + .. seealso:: + + :ref:`oracle_isolation_level` + + .. change:: + :tags: mysql, sql + :tickets: 5696 + + Added missing keywords to the ``RESERVED_WORDS`` list for the MySQL + dialect: ``action``, ``level``, ``mode``, ``status``, ``text``, ``time``. + Pull request courtesy Oscar Batori. + + .. change:: + :tags: bug, orm + :tickets: 5737 + :versions: 1.4.0b2 + + Fixed bug involving the ``restore_load_context`` option of ORM events such + as :meth:`_ormevent.InstanceEvents.load` such that the flag would not be + carried along to subclasses which were mapped after the event handler were + first established. + + + + .. change:: + :tags: bug, sql + :tickets: 5656 + + Fixed structural compiler issue where some constructs such as MySQL / + PostgreSQL "on conflict / on duplicate key" would rely upon the state of + the :class:`_sql.Compiler` object being fixed against their statement as + the top level statement, which would fail in cases where those statements + are branched from a different context, such as a DDL construct linked to a + SQL statement. + + + .. change:: + :tags: mssql, sqlite, reflection + :tickets: 5661 + + Fixed issue with composite primary key columns not being reported + in the correct order. Patch courtesy @fulpm. + +.. changelog:: :version: 1.3.20 :released: October 12, 2020 diff -Nru sqlalchemy-1.3.20+ds1/doc/build/changelog/migration_13.rst sqlalchemy-1.3.22+ds1/doc/build/changelog/migration_13.rst --- sqlalchemy-1.3.20+ds1/doc/build/changelog/migration_13.rst 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/doc/build/changelog/migration_13.rst 2020-12-18 21:05:18.000000000 +0000 @@ -512,7 +512,7 @@ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Given an :class:`.AssociationProxy` where the target is a database column, -as opposed to an object reference:: +and is **not** an object reference or another association proxy:: class User(Base): # ... diff -Nru sqlalchemy-1.3.20+ds1/doc/build/conf.py sqlalchemy-1.3.22+ds1/doc/build/conf.py --- sqlalchemy-1.3.20+ds1/doc/build/conf.py 2020-10-12 22:30:08.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/doc/build/conf.py 2020-12-18 21:05:31.000000000 +0000 @@ -123,6 +123,9 @@ "_schema": "sqlalchemy.schema", "_types": "sqlalchemy.types", "_expression": "sqlalchemy.sql.expression", + "_sql": "sqlalchemy.sql.expression", + "_dml": "sqlalchemy.sql.expression", + "_ddl": "sqlalchemy.schema", "_functions": "sqlalchemy.sql.functions", "_pool": "sqlalchemy.pool", "_event": "sqlalchemy.event", @@ -131,7 +134,8 @@ "_reflection": "sqlalchemy.engine.reflection", "_orm": "sqlalchemy.orm", "_query": "sqlalchemy.orm.query", - "_ormevent": "sqlalchemy.orm.event", + "_ormevent": "sqlalchemy.orm.events", + "_ormsession": "sqlalchemy.orm.session", "_ormexc": "sqlalchemy.orm.exc", "_baked": "sqlalchemy.ext.baked", "_associationproxy": "sqlalchemy.ext.associationproxy", @@ -167,9 +171,9 @@ # The short X.Y version. version = "1.3" # The full version, including alpha/beta/rc tags. -release = "1.3.20" +release = "1.3.22" -release_date = "October 12, 2020" +release_date = "December 18, 2020" site_base = os.environ.get("RTD_SITE_BASE", "http://www.sqlalchemy.org") site_adapter_template = "docs_adapter.mako" diff -Nru sqlalchemy-1.3.20+ds1/doc/build/core/engines.rst sqlalchemy-1.3.22+ds1/doc/build/core/engines.rst --- sqlalchemy-1.3.20+ds1/doc/build/core/engines.rst 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/doc/build/core/engines.rst 2020-12-18 21:05:18.000000000 +0000 @@ -366,34 +366,85 @@ ``sqlalchemy`` namespace so that no log operations occur, even within an application that has logging enabled otherwise. -The ``echo`` flags present as keyword arguments to -:func:`~sqlalchemy.create_engine` and others as well as the ``echo`` property -on :class:`~sqlalchemy.engine.Engine`, when set to ``True``, will first -attempt to ensure that logging is enabled. Unfortunately, the ``logging`` -module provides no way of determining if output has already been configured -(note we are referring to if a logging configuration has been set up, not just -that the logging level is set). For this reason, any ``echo=True`` flags will -result in a call to ``logging.basicConfig()`` using sys.stdout as the -destination. It also sets up a default format using the level name, timestamp, -and logger name. Note that this configuration has the affect of being -configured **in addition** to any existing logger configurations. Therefore, -**when using Python logging, ensure all echo flags are set to False at all -times**, to avoid getting duplicate log lines. - -The logger name of instance such as an :class:`~sqlalchemy.engine.Engine` -or :class:`~sqlalchemy.pool.Pool` defaults to using a truncated hex identifier -string. To set this to a specific name, use the "logging_name" and -"pool_logging_name" keyword arguments with :func:`sqlalchemy.create_engine`. - .. note:: - The SQLAlchemy :class:`_engine.Engine` conserves Python function call overhead - by only emitting log statements when the current logging level is detected - as ``logging.INFO`` or ``logging.DEBUG``. It only checks this level when - a new connection is procured from the connection pool. Therefore when - changing the logging configuration for an already-running application, any - :class:`_engine.Connection` that's currently active, or more commonly a - :class:`~.orm.session.Session` object that's active in a transaction, won't log any - SQL according to the new configuration until a new :class:`_engine.Connection` - is procured (in the case of :class:`~.orm.session.Session`, this is - after the current transaction ends and a new one begins). + The SQLAlchemy :class:`_engine.Engine` conserves Python function call + overhead by only emitting log statements when the current logging level is + detected as ``logging.INFO`` or ``logging.DEBUG``. It only checks this + level when a new connection is procured from the connection pool. Therefore + when changing the logging configuration for an already-running application, + any :class:`_engine.Connection` that's currently active, or more commonly a + :class:`~.orm.session.Session` object that's active in a transaction, won't + log any SQL according to the new configuration until a new + :class:`_engine.Connection` is procured (in the case of + :class:`~.orm.session.Session`, this is after the current transaction ends + and a new one begins). + +More on the Echo Flag +--------------------- + +As mentioned previously, the :paramref:`_sa.create_engine.echo` and :paramref:`_sa.create_engine.echo_pool` +parameters are a shortcut to immediate logging to ``sys.stdout``:: + + + >>> from sqlalchemy import create_engine, text + >>> e = create_engine("sqlite://", echo=True, echo_pool='debug') + >>> with e.connect() as conn: + ... print(conn.scalar(text("select 'hi'"))) + ... + 2020-10-24 12:54:57,701 DEBUG sqlalchemy.pool.impl.SingletonThreadPool Created new connection + 2020-10-24 12:54:57,701 DEBUG sqlalchemy.pool.impl.SingletonThreadPool Connection checked out from pool + 2020-10-24 12:54:57,702 INFO sqlalchemy.engine.Engine select 'hi' + 2020-10-24 12:54:57,702 INFO sqlalchemy.engine.Engine () + hi + 2020-10-24 12:54:57,703 DEBUG sqlalchemy.pool.impl.SingletonThreadPool Connection being returned to pool + 2020-10-24 12:54:57,704 DEBUG sqlalchemy.pool.impl.SingletonThreadPool Connection rollback-on-return + +Use of these flags is roughly equivalent to:: + + import logging + logging.basicConfig() + logging.getLogger("sqlalchemy.engine").setLevel(logging.INFO) + logging.getLogger("sqlalchemy.pool").setLevel(logging.DEBUG) + +It's important to note that these two flags work **independently** of any +existing logging configuration, and will make use of ``logging.basicConfig()`` +unconditionally. This has the effect of being configured **in addition** to +any existing logger configurations. Therefore, **when configuring logging +explicitly, ensure all echo flags are set to False at all times**, to avoid +getting duplicate log lines. + +Setting the Logging Name +------------------------- + +The logger name of instance such as an :class:`~sqlalchemy.engine.Engine` or +:class:`~sqlalchemy.pool.Pool` defaults to using a truncated hex identifier +string. To set this to a specific name, use the +:paramref:`_sa.create_engine.logging_name` and +:paramref:`_sa.create_engine.pool_logging_name` with +:func:`sqlalchemy.create_engine`:: + + >>> from sqlalchemy import create_engine + >>> from sqlalchemy import text + >>> e = create_engine("sqlite://", echo=True, logging_name='myengine') + >>> with e.connect() as conn: + ... conn.execute(text("select 'hi'")) + ... + 2020-10-24 12:47:04,291 INFO sqlalchemy.engine.Engine.myengine select 'hi' + 2020-10-24 12:47:04,292 INFO sqlalchemy.engine.Engine.myengine () + +Hiding Parameters +------------------ + +The logging emitted by :class:`_engine.Engine` also indicates an excerpt +of the SQL parameters that are present for a particular statement. To prevent +these parameters from being logged for privacy purposes, enable the +:paramref:`_sa.create_engine.hide_parameters` flag:: + + >>> e = create_engine("sqlite://", echo=True, hide_parameters=True) + >>> with e.connect() as conn: + ... conn.execute(text("select :some_private_name"), {"some_private_name": "pii"}) + ... + 2020-10-24 12:48:32,808 INFO sqlalchemy.engine.Engine select ? + 2020-10-24 12:48:32,808 INFO sqlalchemy.engine.Engine [SQL parameters hidden due to hide_parameters=True] + diff -Nru sqlalchemy-1.3.20+ds1/doc/build/core/pooling.rst sqlalchemy-1.3.22+ds1/doc/build/core/pooling.rst --- sqlalchemy-1.3.20+ds1/doc/build/core/pooling.rst 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/doc/build/core/pooling.rst 2020-12-18 21:05:18.000000000 +0000 @@ -212,6 +212,8 @@ .. versionadded:: 1.2 Added "pre-ping" capability to the :class:`_pool.Pool` class. +.. _pool_disconnects_pessimistic_custom: + Custom / Legacy Pessimistic Ping ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ diff -Nru sqlalchemy-1.3.20+ds1/doc/build/core/tutorial.rst sqlalchemy-1.3.22+ds1/doc/build/core/tutorial.rst --- sqlalchemy-1.3.20+ds1/doc/build/core/tutorial.rst 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/doc/build/core/tutorial.rst 2020-12-18 21:05:18.000000000 +0000 @@ -2149,6 +2149,7 @@ COMMIT {stop} +.. _tutorial_1x_correlated_updates: Correlated Updates ------------------ diff -Nru sqlalchemy-1.3.20+ds1/doc/build/errors.rst sqlalchemy-1.3.22+ds1/doc/build/errors.rst --- sqlalchemy-1.3.20+ds1/doc/build/errors.rst 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/doc/build/errors.rst 2020-12-18 21:05:18.000000000 +0000 @@ -596,15 +596,25 @@ to pass objects off to other systems that can't run in the same context even though they're in the same process. In this case, the application should try to make appropriate use of :term:`eager loading` to ensure - that objects have what they need up front. As an additional measure, - special directives like the :func:`.raiseload` option can ensure that - systems don't call upon lazy loading when its not expected. + that objects have what they need up front. + + When using this approach, it is usually necessary that the + :paramref:`_orm.Session.expire_on_commit` parameter be set to ``False``, so + that after a :meth:`_orm.Session.commit` operation, the objects within the + session aren't :term:`expired`, which would incur a lazy load if their + attributes were subsequently accessed. Additionally, the + :meth:`_orm.Session.rollback` method unconditionally expires all contents in + the :class:`_orm.Session` and should also be avoided in non-error scenarios. .. seealso:: :ref:`loading_toplevel` - detailed documentation on eager loading and other relationship-oriented loading techniques + :ref:`session_committing` - background on session commit + + :ref:`session_expire` - background on attribute expiry + .. _error_7s2a: diff -Nru sqlalchemy-1.3.20+ds1/doc/build/glossary.rst sqlalchemy-1.3.22+ds1/doc/build/glossary.rst --- sqlalchemy-1.3.20+ds1/doc/build/glossary.rst 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/doc/build/glossary.rst 2020-12-18 21:05:18.000000000 +0000 @@ -460,6 +460,7 @@ :doc:`orm/session` expire + expired expires expiring Expiring diff -Nru sqlalchemy-1.3.20+ds1/doc/build/orm/cascades.rst sqlalchemy-1.3.22+ds1/doc/build/orm/cascades.rst --- sqlalchemy-1.3.20+ds1/doc/build/orm/cascades.rst 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/doc/build/orm/cascades.rst 2020-12-18 21:05:18.000000000 +0000 @@ -93,12 +93,11 @@ >>> address3 in sess >>> True -``save-update`` has the possibly surprising behavior which is that -persistent objects which were *removed* from a collection -or in some cases a scalar attribute -may also be pulled into the :class:`.Session` of a parent object; this is +A ``save-update`` cascade can exhibit surprising behavior when removing an item from +a collection or de-associating an object from a scalar attribute. In some cases, the +orphaned objects may still be pulled into the ex-parent's :class:`.Session`; this is so that the flush process may handle that related object appropriately. -This case can usually only arise if an object is removed from one :class:`.Session` +This case usually only arises if an object is removed from one :class:`.Session` and added to another:: >>> user1 = sess1.query(User).filter_by(id=1).first() diff -Nru sqlalchemy-1.3.20+ds1/doc/build/orm/extensions/associationproxy.rst sqlalchemy-1.3.22+ds1/doc/build/orm/extensions/associationproxy.rst --- sqlalchemy-1.3.20+ds1/doc/build/orm/extensions/associationproxy.rst 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/doc/build/orm/extensions/associationproxy.rst 2020-12-18 21:05:18.000000000 +0000 @@ -367,29 +367,32 @@ name = Column(String(64)) # the same 'user_keywords'->'keyword' proxy as in - # the basic dictionary example + # the basic dictionary example. keywords = association_proxy( - 'user_keywords', - 'keyword', - creator=lambda k, v: - UserKeyword(special_key=k, keyword=v) - ) + 'user_keywords', + 'keyword', + creator=lambda k, v: UserKeyword(special_key=k, keyword=v) + ) + + # another proxy that is directly column-targeted + special_keys = association_proxy("user_keywords", "special_key") def __init__(self, name): self.name = name class UserKeyword(Base): __tablename__ = 'user_keyword' - user_id = Column(Integer, ForeignKey('user.id'), primary_key=True) - keyword_id = Column(Integer, ForeignKey('keyword.id'), - primary_key=True) + user_id = Column(ForeignKey('user.id'), primary_key=True) + keyword_id = Column(ForeignKey('keyword.id'), primary_key=True) special_key = Column(String) - user = relationship(User, backref=backref( + user = relationship( + User, + backref=backref( "user_keywords", collection_class=attribute_mapped_collection("special_key"), cascade="all, delete-orphan" - ) ) + ) # the relationship to Keyword is now called # 'kw' @@ -445,54 +448,67 @@ --------------------------------- The :class:`.AssociationProxy` features simple SQL construction capabilities -which relate down to the underlying :func:`_orm.relationship` in use as well -as the target attribute. For example, the :meth:`.RelationshipProperty.Comparator.any` -and :meth:`.RelationshipProperty.Comparator.has` operations are available, and will produce -a "nested" EXISTS clause, such as in our basic association object example:: - - >>> print(session.query(User).filter(User.keywords.any(keyword='jek'))) - SELECT user.id AS user_id, user.name AS user_name - FROM user +which work at the class level in a similar way as other ORM-mapped attributes. +Class-bound attributes such as ``User.keywords`` and ``User.special_keys`` +in the preceding example will provide for a SQL generating construct +when accessed at the class level. + +.. note:: The primary purpose of the association proxy extension is to allow + for improved persistence and object-access patterns with mapped object + instances that are already loaded. The class-bound querying feature + is of limited use and will not replace the need to refer to the underlying + attributes when constructing SQL queries with JOINs, eager loading + options, etc. + +The SQL generated takes the form of a correlated subquery against +the EXISTS SQL operator so that it can be used in a WHERE clause without +the need for additional modifications to the enclosing query. If the +immediate target of an association proxy is a **mapped column expression**, +standard column operators can be used which will be embedded in the subquery. +For example a straight equality operator:: + + >>> print(session.query(User).filter(User.special_keys == "jek")) + SELECT "user".id AS user_id, "user".name AS user_name + FROM "user" WHERE EXISTS (SELECT 1 FROM user_keyword - WHERE user.id = user_keyword.user_id AND (EXISTS (SELECT 1 - FROM keyword - WHERE keyword.id = user_keyword.keyword_id AND keyword.keyword = :keyword_1))) + WHERE "user".id = user_keyword.user_id AND user_keyword.special_key = :special_key_1) -For a proxy to a scalar attribute, ``__eq__()`` is supported:: +a LIKE operator:: - >>> print(session.query(UserKeyword).filter(UserKeyword.keyword == 'jek')) - SELECT user_keyword.* + >>> print(session.query(User).filter(User.special_keys.like("%jek"))) + SELECT "user".id AS user_id, "user".name AS user_name + FROM "user" + WHERE EXISTS (SELECT 1 FROM user_keyword + WHERE "user".id = user_keyword.user_id AND user_keyword.special_key LIKE :special_key_1) + +For association proxies where the immediate target is a **related object or collection, +or another association proxy or attribute on the related object**, relationship-oriented +operators can be used instead, such as :meth:`_orm.PropComparator.has` and +:meth:`_orm.PropComparator.any`. The ``User.keywords`` attribute is in fact +two association proxies linked together, so when using this proxy for generating +SQL phrases, we get two levels of EXISTS subqueries:: + + >>> print(session.query(User).filter(User.keywords.any(Keyword.keyword == "jek"))) + SELECT "user".id AS user_id, "user".name AS user_name + FROM "user" WHERE EXISTS (SELECT 1 - FROM keyword - WHERE keyword.id = user_keyword.keyword_id AND keyword.keyword = :keyword_1) + FROM user_keyword + WHERE "user".id = user_keyword.user_id AND (EXISTS (SELECT 1 + FROM keyword + WHERE keyword.id = user_keyword.keyword_id AND keyword.keyword = :keyword_1))) + +This is not the most efficient form of SQL, so while association proxies can be +convenient for generating WHERE criteria quickly, SQL results should be +inspected and "unrolled" into explicit JOIN criteria for best use, especially +when chaining association proxies together. + + +.. versionchanged:: 1.3 Association proxy features distinct querying modes + based on the type of target. See :ref:`change_4351`. -and ``.contains()`` is available for a proxy to a scalar collection:: - >>> print(session.query(User).filter(User.keywords.contains('jek'))) - SELECT user.* - FROM user - WHERE EXISTS (SELECT 1 - FROM userkeywords, keyword - WHERE user.id = userkeywords.user_id - AND keyword.id = userkeywords.keyword_id - AND keyword.keyword = :keyword_1) - -:class:`.AssociationProxy` can be used with :meth:`_query.Query.join` somewhat manually -using the :attr:`~.AssociationProxy.attr` attribute in a star-args context:: - - q = session.query(User).join(*User.keywords.attr) - -:attr:`~.AssociationProxy.attr` is composed of :attr:`.AssociationProxy.local_attr` and :attr:`.AssociationProxy.remote_attr`, -which are just synonyms for the actual proxied attributes, and can also -be used for querying:: - - uka = aliased(UserKeyword) - ka = aliased(Keyword) - q = session.query(User).\ - join(uka, User.keywords.local_attr).\ - join(ka, User.keywords.remote_attr) .. _cascade_scalar_deletes: diff -Nru sqlalchemy-1.3.20+ds1/doc/build/orm/loading_relationships.rst sqlalchemy-1.3.22+ds1/doc/build/orm/loading_relationships.rst --- sqlalchemy-1.3.20+ds1/doc/build/orm/loading_relationships.rst 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/doc/build/orm/loading_relationships.rst 2020-12-18 21:05:18.000000000 +0000 @@ -704,26 +704,19 @@ ----------------- Select IN loading is similar in operation to subquery eager loading, however -the SELECT statement which is emitted has a much simpler structure than -that of subquery eager loading. Additionally, select IN loading applies -itself to subsets of the load result at a time, so unlike joined and subquery -eager loading, is compatible with batching of results using -:meth:`_query.Query.yield_per`, provided the database driver supports simultaneous -cursors. - -Overall, especially as of the 1.3 series of SQLAlchemy, selectin loading -is the most simple and efficient way to eagerly load collections of objects -in most cases. The only scenario in which selectin eager loading is not feasible -is when the model is using composite primary keys, and the backend database -does not support tuples with IN, which includes SQLite, Oracle and -SQL Server. +the SELECT statement which is emitted has a much simpler structure than that of +subquery eager loading. In most cases, selectin loading is the most simple and +efficient way to eagerly load collections of objects. The only scenario in +which selectin eager loading is not feasible is when the model is using +composite primary keys, and the backend database does not support tuples with +IN, which currently includes SQL Server. .. versionadded:: 1.2 "Select IN" eager loading is provided using the ``"selectin"`` argument to :paramref:`_orm.relationship.lazy` or by using the :func:`.selectinload` loader option. This style of loading emits a SELECT that refers to the primary key -values of the parent object, or in the case of a simple many-to-one +values of the parent object, or in the case of a many-to-one relationship to the those of the child objects, inside of an IN clause, in order to load related associations: @@ -746,28 +739,22 @@ addresses.user_id AS addresses_user_id FROM addresses WHERE addresses.user_id IN (?, ?) - ORDER BY addresses.user_id, addresses.id (5, 7) Above, the second SELECT refers to ``addresses.user_id IN (5, 7)``, where the "5" and "7" are the primary key values for the previous two ``User`` objects loaded; after a batch of objects are completely loaded, their primary key values are injected into the ``IN`` clause for the second SELECT. -Because the relationship between ``User`` and ``Address`` provides that the +Because the relationship between ``User`` and ``Address`` has a simple [1]_ +primary join condition and provides that the primary key values for ``User`` can be derived from ``Address.user_id``, the statement has no joins or subqueries at all. .. versionchanged:: 1.3 selectin loading can omit the JOIN for a simple one-to-many collection. -.. versionchanged:: 1.3.6 selectin loading can also omit the JOIN for a simple - many-to-one relationship. - -For collections, in the case where the primary key of the parent object isn't -present in the related row, "selectin" loading will also JOIN to the parent -table so that the parent primary key values are present. This also takes place -for a non-collection, many-to-one load where the related column values are not -loaded on the parent objects and would otherwise need to be loaded: +For simple [1]_ many-to-one loads, a JOIN is also not needed as the foreign key +value from the parent object is used: .. sourcecode:: python+sql @@ -779,19 +766,26 @@ addresses.user_id AS addresses_user_id FROM addresses SELECT - addresses_1.id AS addresses_1_id, users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.nickname AS users_nickname - FROM addresses AS addresses_1 - JOIN users ON users.id = addresses_1.user_id - WHERE addresses_1.id IN (?, ?) - ORDER BY addresses_1.id + FROM users + WHERE users.id IN (?, ?) (1, 2) -"Select IN" loading is the newest form of eager loading added to SQLAlchemy -as of the 1.2 series. Things to know about this kind of loading include: +.. versionchanged:: 1.3.6 selectin loading can also omit the JOIN for a simple + many-to-one relationship. + +.. [1] by "simple" we mean that the :paramref:`_orm.relationship.primaryjoin` + condition expresses an equality comparison between the primary key of the + "one" side and a straight foreign key of the "many" side, without any + additional criteria. + +Select IN loading also supports many-to-many relationships, where it currently +will JOIN across all three tables to match rows from one side to the other. + +Things to know about this kind of loading include: * The SELECT statement emitted by the "selectin" loader strategy, unlike that of "subquery", does not @@ -804,53 +798,30 @@ is always linking directly to a parent primary key and can't really return the wrong result. -* "selectin" loading, unlike joined or subquery eager loading, always emits - its SELECT in terms of the immediate parent objects just loaded, and not the +* "selectin" loading, unlike joined or subquery eager loading, always emits its + SELECT in terms of the immediate parent objects just loaded, and not the original type of object at the top of the chain. So if eager loading many - levels deep, "selectin" loading still uses no more than one JOIN, and usually - no JOINs, in the statement. In comparison, joined and subquery eager - loading always refer to multiple JOINs up to the original parent. - -* "selectin" loading produces a SELECT statement of a predictable structure, - independent of that of the original query. As such, taking advantage of - a new feature with :meth:`.ColumnOperators.in_` that allows it to work - with cached queries, the selectin loader makes full use of the - :mod:`sqlalchemy.ext.baked` extension to cache generated SQL and greatly - cut down on internal function call overhead. + levels deep, "selectin" loading still will not require any JOINs for simple + one-to-many or many-to-one relationships. In comparison, joined and + subquery eager loading always refer to multiple JOINs up to the original + parent. -* The strategy will only query for at most 500 parent primary key values at a +* The strategy emits a SELECT for up to 500 parent primary key values at a time, as the primary keys are rendered into a large IN expression in the SQL statement. Some databases like Oracle have a hard limit on how large an IN expression can be, and overall the size of the SQL string shouldn't - be arbitrarily large. So for large result sets, "selectin" loading - will emit a SELECT per 500 parent rows returned. These SELECT statements - emit with minimal Python overhead due to the "baked" queries and also minimal - SQL overhead as they query against primary key directly. - -* "selectin" loading is the only eager loading that can work in conjunction with - the "batching" feature provided by :meth:`_query.Query.yield_per`, provided - the database driver supports simultaneous cursors. As it only - queries for related items against specific result objects, "selectin" loading - allows for eagerly loaded collections against arbitrarily large result sets - with a top limit on memory use when used with :meth:`_query.Query.yield_per`. - - Current database drivers that support simultaneous cursors include - SQLite, PostgreSQL. The MySQL drivers mysqlclient and pymysql currently - **do not** support simultaneous cursors, nor do the ODBC drivers for - SQL Server. + be arbitrarily large. * As "selectin" loading relies upon IN, for a mapping with composite primary keys, it must use the "tuple" form of IN, which looks like ``WHERE (table.column_a, table.column_b) IN ((?, ?), (?, ?), (?, ?))``. This syntax - is not supported on every database; within the dialects that are included - with SQLAlchemy, it is known to be supported by modern PostgreSQL, MySQL and - SQLite versions. Therefore **selectin loading is not platform-agnostic for - composite primary keys**. There is no special logic in SQLAlchemy to check + is not currently supported on SQL Server and for SQLite requires at least + version 3.15. There is no special logic in SQLAlchemy to check ahead of time which platforms support this syntax or not; if run against a non-supporting platform, the database will return an error immediately. An advantage to SQLAlchemy just running the SQL out for it to fail is that if a particular database does start supporting this syntax, it will work without - any changes to SQLAlchemy. + any changes to SQLAlchemy (as was the case with SQLite). In general, "selectin" loading is probably superior to "subquery" eager loading in most ways, save for the syntax requirement with composite primary keys @@ -1044,10 +1015,9 @@ options(contains_eager(User.addresses)) -If the "eager" portion of the statement is "aliased", the ``alias`` keyword -argument to :func:`~sqlalchemy.orm.contains_eager` may be used to indicate it. -This is sent as a reference to an :func:`.aliased` or :class:`_expression.Alias` -construct: +If the "eager" portion of the statement is "aliased", the path +should be specified using :meth:`.PropComparator.of_type`, which allows +the specific :func:`_orm.aliased` construct to be passed: .. sourcecode:: python+sql @@ -1056,8 +1026,8 @@ # construct a Query object which expects the "addresses" results query = session.query(User).\ - outerjoin(adalias, User.addresses).\ - options(contains_eager(User.addresses, alias=adalias)) + outerjoin(User.addresses.of_type(adalias)).\ + options(contains_eager(User.addresses.of_type(adalias))) # get results normally r = query.all() @@ -1074,13 +1044,7 @@ The path given as the argument to :func:`.contains_eager` needs to be a full path from the starting entity. For example if we were loading -``Users->orders->Order->items->Item``, the string version would look like:: - - query(User).options( - contains_eager('orders'). - contains_eager('items')) - -Or using the class-bound descriptor:: +``Users->orders->Order->items->Item``, the option would be used as:: query(User).options( contains_eager(User.orders). @@ -1096,64 +1060,43 @@ scalar attributes. As an example, we can load a ``User`` object and eagerly load only particular -addresses into its ``.addresses`` collection just by filtering:: - - q = session.query(User).join(User.addresses).\ - filter(Address.email.like('%ed%')).\ - options(contains_eager(User.addresses)) +addresses into its ``.addresses`` collection by filtering the joined data, +routing it using :func:`_orm.contains_eager`, also using +:meth:`_query.Query.populate_existing` to ensure any already-loaded collections +are overwritten:: + + q = session.query(User).\ + join(User.addresses).\ + filter(Address.email_address.like('%@aol.com')).\ + options(contains_eager(User.addresses)).\ + populate_existing() The above query will load only ``User`` objects which contain at -least ``Address`` object that contains the substring ``'ed'`` in its +least ``Address`` object that contains the substring ``'aol.com'`` in its ``email`` field; the ``User.addresses`` collection will contain **only** these ``Address`` entries, and *not* any other ``Address`` entries that are in fact associated with the collection. -.. warning:: - - Keep in mind that when we load only a subset of objects into a collection, - that collection no longer represents what's actually in the database. If - we attempted to add entries to this collection, we might find ourselves - conflicting with entries that are already in the database but not locally - loaded. - - In addition, the **collection will fully reload normally** once the - object or attribute is expired. This expiration occurs whenever the - :meth:`.Session.commit`, :meth:`.Session.rollback` methods are used - assuming default session settings, or the :meth:`.Session.expire_all` - or :meth:`.Session.expire` methods are used. - - For these reasons, prefer returning separate fields in a tuple rather - than artificially altering a collection, when an object plus a custom - set of related objects is desired:: - - q = session.query(User, Address).join(User.addresses).\ - filter(Address.email.like('%ed%')) - - -Advanced Usage with Arbitrary Statements -^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ - -The ``alias`` argument can be more creatively used, in that it can be made -to represent any set of arbitrary names to match up into a statement. -Below it is linked to a :func:`_expression.select` which links a set of column objects -to a string SQL statement:: - - # label the columns of the addresses table - eager_columns = select([ - addresses.c.address_id.label('a1'), - addresses.c.email_address.label('a2'), - addresses.c.user_id.label('a3') - ]) - - # select from a raw SQL statement which uses those label names for the - # addresses table. contains_eager() matches them up. - query = session.query(User).\ - from_statement("select users.*, addresses.address_id as a1, " - "addresses.email_address as a2, " - "addresses.user_id as a3 " - "from users left outer join " - "addresses on users.user_id=addresses.user_id").\ - options(contains_eager(User.addresses, alias=eager_columns)) +.. tip:: In all cases, the SQLAlchemy ORM does **not overwrite already loaded + attributes and collections** unless told to do so. As there is an + :term:`identity map` in use, it is often the case that an ORM query is + returning objects that were in fact already present and loaded in memory. + Therefore, when using :func:`_orm.contains_eager` to populate a collection + in an alternate way, it is usually a good idea to use + :meth:`_query.Query.populate_existing` as illustrated above so that an + already-loaded collection is refreshed with the new data. + :meth:`_query.Query.populate_existing` will reset **all** attributes that were + already present, including pending changes, so make sure all data is flushed + before using it. Using the :class:`_orm.Session` with its default behavior + of :ref:`autoflush ` is sufficient. + +.. note:: The customized collection we load using :func:`_orm.contains_eager` + is not "sticky"; that is, the next time this collection is loaded, it will + be loaded with its usual default contents. The collection is subject + to being reloaded if the object is expired, which occurs whenever the + :meth:`.Session.commit`, :meth:`.Session.rollback` methods are used + assuming default session settings, or the :meth:`.Session.expire_all` + or :meth:`.Session.expire` methods are used. Creating Custom Load Rules -------------------------- diff -Nru sqlalchemy-1.3.20+ds1/doc/build/orm/mapping_columns.rst sqlalchemy-1.3.22+ds1/doc/build/orm/mapping_columns.rst --- sqlalchemy-1.3.20+ds1/doc/build/orm/mapping_columns.rst 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/doc/build/orm/mapping_columns.rst 2020-12-18 21:05:18.000000000 +0000 @@ -73,7 +73,7 @@ class MyClass(Base): __table__ = Table("some_table", Base.metadata, - autoload=True, autoload_with=some_engine) + autoload_with=some_engine) If we want to qualify our event to only react for the specific :class:`_schema.MetaData` object above, we can check for it in our event:: diff -Nru sqlalchemy-1.3.20+ds1/doc/build/orm/persistence_techniques.rst sqlalchemy-1.3.22+ds1/doc/build/orm/persistence_techniques.rst --- sqlalchemy-1.3.20+ds1/doc/build/orm/persistence_techniques.rst 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/doc/build/orm/persistence_techniques.rst 2020-12-18 21:05:18.000000000 +0000 @@ -652,22 +652,29 @@ Bulk Operations =============== -.. note:: Bulk Operations mode is a new series of operations made available - on the :class:`.Session` object for the purpose of invoking INSERT and - UPDATE statements with greatly reduced Python overhead, at the expense - of much less functionality, automation, and error checking. - As of SQLAlchemy 1.0, these features should be considered as "beta", and - additionally are intended for advanced users. +.. deepalchemy:: Bulk operations are essentially lower-functionality versions + of the Unit of Work's facilities for emitting INSERT and UPDATE statements + on primary key targeted rows. These routines were added to suit some + cases where many rows being inserted or updated could be run into the + database without as much of the usual unit of work overhead, in that + most unit of work features are **disabled**. + + There is **usually no need to use these routines, and they are not easy + to use as there are many missing behaviors that are usually expected when + using ORM objects**; for efficient + bulk inserts, it's better to use the Core :class:`_sql.Insert` construct + directly. Please read all caveats at :ref:`bulk_operations_caveats`. .. versionadded:: 1.0.0 -Bulk operations on the :class:`.Session` include :meth:`.Session.bulk_save_objects`, -:meth:`.Session.bulk_insert_mappings`, and :meth:`.Session.bulk_update_mappings`. -The purpose of these methods is to directly expose internal elements of the unit of work system, -such that facilities for emitting INSERT and UPDATE statements given dictionaries -or object states can be utilized alone, bypassing the normal unit of work -mechanics of state, relationship and attribute management. The advantages -to this approach is strictly one of reduced Python overhead: +Bulk INSERT/per-row UPDATE operations on the :class:`.Session` include +:meth:`.Session.bulk_save_objects`, :meth:`.Session.bulk_insert_mappings`, and +:meth:`.Session.bulk_update_mappings`. The purpose of these methods is to +directly expose internal elements of the unit of work system, such that +facilities for emitting INSERT and UPDATE statements given dictionaries or +object states can be utilized alone, bypassing the normal unit of work +mechanics of state, relationship and attribute management. The advantages to +this approach is strictly one of reduced Python overhead: * The flush() process, including the survey of all objects, their state, their cascade status, the status of all objects associated with them @@ -757,8 +764,13 @@ suite in :ref:`examples_performance` should be carefully studied in order to gain familiarity with how fast bulk performance can be achieved. -ORM Compatibility ------------------ +.. _bulk_operations_caveats: + +ORM Compatibility / Caveats +---------------------------- + +.. warning:: Be sure to familiarize with these limitations before using the + bulk routines. The bulk insert / update methods lose a significant amount of functionality versus traditional ORM use. The following is a listing of features that @@ -772,7 +784,10 @@ * Session-management on the given objects, including attachment to the session, identity map management. -* Functionality related to primary key mutation, ON UPDATE cascade +* Functionality related to primary key mutation, ON UPDATE cascade - + **mutation of primary key columns will not work** - as the original PK + value of each row is not available, so the WHERE criteria cannot be + generated. * SQL expression inserts / updates (e.g. :ref:`flush_embedded_sql_expressions`) - having to evaluate these would prevent INSERT and UPDATE statements from diff -Nru sqlalchemy-1.3.20+ds1/doc/build/orm/versioning.rst sqlalchemy-1.3.22+ds1/doc/build/orm/versioning.rst --- sqlalchemy-1.3.20+ds1/doc/build/orm/versioning.rst 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/doc/build/orm/versioning.rst 2020-12-18 21:05:18.000000000 +0000 @@ -153,7 +153,7 @@ id = Column(Integer, primary_key=True) name = Column(String(50), nullable=False) - xmin = Column("xmin", Integer, system=True, server_default=FetchedValue()) + xmin = Column("xmin", String, system=True, server_default=FetchedValue()) __mapper_args__ = { 'version_id_col': xmin, @@ -167,7 +167,9 @@ In the above scenario, as ``xmin`` is a system column provided by PostgreSQL, we use the ``system=True`` argument to mark it as a system-provided - column, omitted from the ``CREATE TABLE`` statement. + column, omitted from the ``CREATE TABLE`` statement. The datatype of this + column is an internal PostgreSQL type called ``xid`` which acts mostly + like a string, so we use the :class:`_types.String` datatype. The ORM typically does not actively fetch the values of database-generated diff -Nru sqlalchemy-1.3.20+ds1/lib/sqlalchemy/dialects/mssql/base.py sqlalchemy-1.3.22+ds1/lib/sqlalchemy/dialects/mssql/base.py --- sqlalchemy-1.3.20+ds1/lib/sqlalchemy/dialects/mssql/base.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/lib/sqlalchemy/dialects/mssql/base.py 2020-12-18 21:05:18.000000000 +0000 @@ -2043,14 +2043,6 @@ ), ) - whereclause = index.dialect_options["mssql"]["where"] - - if whereclause is not None: - where_compiled = self.sql_compiler.process( - whereclause, include_table=False, literal_binds=True - ) - text += " WHERE " + where_compiled - # handle other included columns if index.dialect_options["mssql"]["include"]: inclusions = [ @@ -2064,6 +2056,14 @@ [preparer.quote(c.name) for c in inclusions] ) + whereclause = index.dialect_options["mssql"]["where"] + + if whereclause is not None: + where_compiled = self.sql_compiler.process( + whereclause, include_table=False, literal_binds=True + ) + text += " WHERE " + where_compiled + return text def visit_drop_index(self, drop): @@ -2796,7 +2796,7 @@ C.c.table_name == tablename, C.c.table_schema == owner, ), - ) + ).order_by(TC.c.constraint_name, C.c.ordinal_position) c = connection.execute(s) constraint_name = None for row in c: diff -Nru sqlalchemy-1.3.20+ds1/lib/sqlalchemy/dialects/mssql/pyodbc.py sqlalchemy-1.3.22+ds1/lib/sqlalchemy/dialects/mssql/pyodbc.py --- sqlalchemy-1.3.20+ds1/lib/sqlalchemy/dialects/mssql/pyodbc.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/lib/sqlalchemy/dialects/mssql/pyodbc.py 2020-12-18 21:05:18.000000000 +0000 @@ -52,7 +52,7 @@ Other keywords interpreted by the Pyodbc dialect to be passed to ``pyodbc.connect()`` in both the DSN and hostname cases include: -``odbc_autotranslate``, ``ansi``, ``unicode_results``, ``autocommit``, +``odbc_autotranslate``, ``ansi``, ``unicode_results``, ``autocommit``, ``authentication`` (e.g., ``authentication=ActiveDirectoryIntegrated``). Note that in order for the dialect to recognize these keywords (including the ``driver`` keyword above) they must be all lowercase. @@ -451,8 +451,9 @@ def is_disconnect(self, e, connection, cursor): if isinstance(e, self.dbapi.Error): code = e.args[0] - if code in ( + if code in { "08S01", + "01000", "01002", "08003", "08007", @@ -461,7 +462,7 @@ "HYT00", "HY010", "10054", - ): + }: return True return super(MSDialect_pyodbc, self).is_disconnect( e, connection, cursor diff -Nru sqlalchemy-1.3.20+ds1/lib/sqlalchemy/dialects/mysql/base.py sqlalchemy-1.3.22+ds1/lib/sqlalchemy/dialects/mysql/base.py --- sqlalchemy-1.3.20+ds1/lib/sqlalchemy/dialects/mysql/base.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/lib/sqlalchemy/dialects/mysql/base.py 2020-12-18 21:05:18.000000000 +0000 @@ -906,7 +906,7 @@ RESERVED_WORDS = set( [ "accessible", - "accessible", + "action", "add", "admin", "all", @@ -1036,6 +1036,7 @@ "leading", "leave", "left", + "level", "like", "limit", "linear", @@ -1064,6 +1065,7 @@ "minute_microsecond", "minute_second", "mod", + "mode", "modifies", "natural", "no_write_to_binlog", @@ -1147,13 +1149,16 @@ "sqlwarning", "ssl", "starting", + "status", "stored", "straight_join", "system", "table", "tables", # 4.1 "terminated", + "text", "then", + "time", "tinyblob", "tinyint", "tinytext", @@ -1383,6 +1388,8 @@ return self._render_json_extract_from_binary(binary, operator, **kw) def visit_on_duplicate_key_update(self, on_duplicate, **kw): + statement = self.current_executable + if on_duplicate._parameter_ordering: parameter_ordering = [ elements._column_as_key(key) @@ -1390,14 +1397,12 @@ ] ordered_keys = set(parameter_ordering) cols = [ - self.statement.table.c[key] + statement.table.c[key] for key in parameter_ordering - if key in self.statement.table.c - ] + [ - c for c in self.statement.table.c if c.key not in ordered_keys - ] + if key in statement.table.c + ] + [c for c in statement.table.c if c.key not in ordered_keys] else: - cols = self.statement.table.c + cols = statement.table.c clauses = [] # traverses through all table columns to preserve table column order diff -Nru sqlalchemy-1.3.20+ds1/lib/sqlalchemy/dialects/mysql/reflection.py sqlalchemy-1.3.22+ds1/lib/sqlalchemy/dialects/mysql/reflection.py --- sqlalchemy-1.3.20+ds1/lib/sqlalchemy/dialects/mysql/reflection.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/lib/sqlalchemy/dialects/mysql/reflection.py 2020-12-18 21:05:18.000000000 +0000 @@ -381,8 +381,8 @@ r"(?: +COLLATE +(?P[\w_]+))?" r"(?: +(?P(?:NOT )?NULL))?" r"(?: +DEFAULT +(?P" - r"(?:NULL|'(?:''|[^'])*'|[\w\(\)]+" - r"(?: +ON UPDATE [\w\(\)]+)?)" + r"(?:NULL|'(?:''|[^'])*'|[\w\.\(\)]+" + r"(?: +ON UPDATE [\w\.\(\)]+)?)" r"))?" r"(?: +(?:GENERATED ALWAYS)? ?AS +(?P\(" r".*\))? ?(?PVIRTUAL|STORED)?)?" diff -Nru sqlalchemy-1.3.20+ds1/lib/sqlalchemy/dialects/oracle/base.py sqlalchemy-1.3.22+ds1/lib/sqlalchemy/dialects/oracle/base.py --- sqlalchemy-1.3.20+ds1/lib/sqlalchemy/dialects/oracle/base.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/lib/sqlalchemy/dialects/oracle/base.py 2020-12-18 21:05:18.000000000 +0000 @@ -38,18 +38,15 @@ autoload=True ) + +.. _oracle_isolation_level: + Transaction Isolation Level / Autocommit ---------------------------------------- -The Oracle database supports "READ COMMITTED" and "SERIALIZABLE" modes -of isolation, however the SQLAlchemy Oracle dialect currently only has -explicit support for "READ COMMITTED". It is possible to emit a -"SET TRANSACTION" statement on a connection in order to use SERIALIZABLE -isolation, however the SQLAlchemy dialect will remain unaware of this setting, -such as if the :meth:`_engine.Connection.get_isolation_level` method is used; -this method is hardcoded to return "READ COMMITTED" right now. - -The AUTOCOMMIT isolation level is also supported by the cx_Oracle dialect. +The Oracle database supports "READ COMMITTED" and "SERIALIZABLE" modes of +isolation. The AUTOCOMMIT isolation level is also supported by the cx_Oracle +dialect. To set using per-connection execution options:: @@ -58,15 +55,50 @@ isolation_level="AUTOCOMMIT" ) +For ``READ COMMITTED`` and ``SERIALIZABLE``, the Oracle dialect sets the +level at the session level using ``ALTER SESSION``, which is reverted back +to its default setting when the connection is returned to the connection +pool. + Valid values for ``isolation_level`` include: * ``READ COMMITTED`` * ``AUTOCOMMIT`` +* ``SERIALIZABLE`` +.. note:: The implementation for the + :meth:`_engine.Connection.get_isolation_level` method as implemented by the + Oracle dialect necessarily forces the start of a transaction using the + Oracle LOCAL_TRANSACTION_ID function; otherwise no level is normally + readable. + + Additionally, the :meth:`_engine.Connection.get_isolation_level` method will + raise an exception if the ``v$transaction`` view is not available due to + permissions or other reasons, which is a common occurrence in Oracle + installations. + + The cx_Oracle dialect attempts to call the + :meth:`_engine.Connection.get_isolation_level` method when the dialect makes + its first connection to the database in order to acquire the + "default"isolation level. This default level is necessary so that the level + can be reset on a connection after it has been temporarily modified using + :meth:`_engine.Connection.execution_options` method. In the common event + that the :meth:`_engine.Connection.get_isolation_level` method raises an + exception due to ``v$transaction`` not being readable as well as any other + database-related failure, the level is assumed to be "READ COMMITTED". No + warning is emitted for this initial first-connect condition as it is + expected to be a common restriction on Oracle databases. .. versionadded:: 1.3.16 added support for AUTOCOMMIT to the cx_oracle dialect - as well as the notion of a default isolation level, currently hardcoded - to "READ COMMITTED". + as well as the notion of a default isolation level + +.. versionadded:: 1.3.21 Added support for SERIALIZABLE as well as live + reading of the isolation level. + +.. versionchanged:: 1.3.22 In the event that the default isolation + level cannot be read due to permissions on the v$transaction view as + is common in Oracle installations, the default isolation level is hardcoded + to "READ COMMITTED" which was the behavior prior to 1.3.21. .. seealso:: @@ -1394,19 +1426,21 @@ connection, additional_tests ) - _isolation_lookup = ["READ COMMITTED"] + _isolation_lookup = ["READ COMMITTED", "SERIALIZABLE"] def get_isolation_level(self, connection): - return "READ COMMITTED" + raise NotImplementedError("implemented by cx_Oracle dialect") + + def get_default_isolation_level(self, dbapi_conn): + try: + return self.get_isolation_level(dbapi_conn) + except NotImplementedError: + raise + except: + return "READ COMMITTED" def set_isolation_level(self, connection, level): - # prior to adding AUTOCOMMIT support for cx_Oracle, the Oracle dialect - # had no notion of setting the isolation level. As Oracle - # does not have a straightforward way of getting the isolation level - # if a server-side transaction is not yet in progress, we currently - # hardcode to only support "READ COMMITTED" and "AUTOCOMMIT" at the - # cx_oracle level. See #5200. - pass + raise NotImplementedError("implemented by cx_Oracle dialect") def has_table(self, connection, table_name, schema=None): if not schema: diff -Nru sqlalchemy-1.3.20+ds1/lib/sqlalchemy/dialects/oracle/cx_oracle.py sqlalchemy-1.3.22+ds1/lib/sqlalchemy/dialects/oracle/cx_oracle.py --- sqlalchemy-1.3.20+ds1/lib/sqlalchemy/dialects/oracle/cx_oracle.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/lib/sqlalchemy/dialects/oracle/cx_oracle.py 2020-12-18 21:05:18.000000000 +0000 @@ -924,6 +924,66 @@ self._detect_decimal_char(connection) + def get_isolation_level(self, connection): + # sources: + + # general idea of transaction id, have to start one, etc. + # https://stackoverflow.com/questions/10711204/how-to-check-isoloation-level + + # how to decode xid cols from v$transaction to match + # https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9532779900346079444 + + # Oracle tuple comparison without using IN: + # https://www.sql-workbench.eu/comparison/tuple_comparison.html + + with connection.cursor() as cursor: + # this is the only way to ensure a transaction is started without + # actually running DML. There's no way to see the configured + # isolation level without getting it from v$transaction which + # means transaction has to be started. + outval = cursor.var(str) + cursor.execute( + """ + begin + :trans_id := dbms_transaction.local_transaction_id( TRUE ); + end; + """, + {"trans_id": outval}, + ) + trans_id = outval.getvalue() + xidusn, xidslot, xidsqn = trans_id.split(".", 2) + + cursor.execute( + "SELECT CASE BITAND(t.flag, POWER(2, 28)) " + "WHEN 0 THEN 'READ COMMITTED' " + "ELSE 'SERIALIZABLE' END AS isolation_level " + "FROM v$transaction t WHERE " + "(t.xidusn, t.xidslot, t.xidsqn) = " + "((:xidusn, :xidslot, :xidsqn))", + {"xidusn": xidusn, "xidslot": xidslot, "xidsqn": xidsqn}, + ) + row = cursor.fetchone() + if row is None: + raise exc.InvalidRequestError( + "could not retrieve isolation level" + ) + result = row[0] + + return result + + def set_isolation_level(self, connection, level): + if hasattr(connection, "connection"): + dbapi_connection = connection.connection + else: + dbapi_connection = connection + if level == "AUTOCOMMIT": + dbapi_connection.autocommit = True + else: + dbapi_connection.autocommit = False + connection.rollback() + with connection.cursor() as cursor: + cursor.execute("ALTER SESSION SET ISOLATION_LEVEL=%s" % level) + def _detect_decimal_char(self, connection): # we have the option to change this setting upon connect, # or just look at what it is upon connect and convert. @@ -1206,18 +1266,5 @@ def do_recover_twophase(self, connection): connection.info.pop("cx_oracle_prepared", None) - def set_isolation_level(self, connection, level): - if hasattr(connection, "connection"): - dbapi_connection = connection.connection - else: - dbapi_connection = connection - if level == "AUTOCOMMIT": - dbapi_connection.autocommit = True - else: - dbapi_connection.autocommit = False - super(OracleDialect_cx_oracle, self).set_isolation_level( - dbapi_connection, level - ) - dialect = OracleDialect_cx_oracle diff -Nru sqlalchemy-1.3.20+ds1/lib/sqlalchemy/dialects/postgresql/base.py sqlalchemy-1.3.22+ds1/lib/sqlalchemy/dialects/postgresql/base.py --- sqlalchemy-1.3.20+ds1/lib/sqlalchemy/dialects/postgresql/base.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/lib/sqlalchemy/dialects/postgresql/base.py 2020-12-18 21:05:18.000000000 +0000 @@ -614,6 +614,8 @@ Index('my_index', my_table.c.id, postgresql_where=my_table.c.value > 10) +.. _postgresql_operator_classes: + Operator Classes ^^^^^^^^^^^^^^^^ @@ -630,11 +632,10 @@ 'id': 'int4_ops' }) -Note that the keys in the ``postgresql_ops`` dictionary are the "key" name of -the :class:`_schema.Column`, i.e. the name used to access it from the ``.c`` -collection of :class:`_schema.Table`, -which can be configured to be different than -the actual name of the column as expressed in the database. +Note that the keys in the ``postgresql_ops`` dictionaries are the +"key" name of the :class:`_schema.Column`, i.e. the name used to access it from +the ``.c`` collection of :class:`_schema.Table`, which can be configured to be +different than the actual name of the column as expressed in the database. If ``postgresql_ops`` is to be used against a complex SQL expression such as a function call, then to apply to the column it must be given a label @@ -648,6 +649,14 @@ 'id': 'int4_ops' }) +Operator classes are also supported by the +:class:`_postgresql.ExcludeConstraint` construct using the +:paramref:`_postgresql.ExcludeConstraint.ops` parameter. See that parameter for +details. + +.. versionadded:: 1.3.21 added support for operator classes with + :class:`_postgresql.ExcludeConstraint`. + Index Types ^^^^^^^^^^^ @@ -1951,7 +1960,7 @@ "Additional column names not matching " "any column keys in table '%s': %s" % ( - self.statement.table.name, + self.current_executable.table.name, (", ".join("'%s'" % c for c in set_parameters)), ) ) @@ -2170,9 +2179,13 @@ elements = [] for expr, name, op in constraint._render_exprs: kw["include_table"] = False - elements.append( - "%s WITH %s" % (self.sql_compiler.process(expr, **kw), op) + exclude_element = self.sql_compiler.process(expr, **kw) + ( + (" " + constraint.ops[expr.key]) + if hasattr(expr, "key") and expr.key in constraint.ops + else "" ) + + elements.append("%s WITH %s" % (exclude_element, op)) text += "EXCLUDE USING %s (%s)" % ( self.preparer.validate_sql_phrase( constraint.using, IDX_USING diff -Nru sqlalchemy-1.3.20+ds1/lib/sqlalchemy/dialects/postgresql/ext.py sqlalchemy-1.3.22+ds1/lib/sqlalchemy/dialects/postgresql/ext.py --- sqlalchemy-1.3.20+ds1/lib/sqlalchemy/dialects/postgresql/ext.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/lib/sqlalchemy/dialects/postgresql/ext.py 2020-12-18 21:05:18.000000000 +0000 @@ -105,7 +105,8 @@ const = ExcludeConstraint( (Column('period'), '&&'), (Column('group'), '='), - where=(Column('group') != 'some group') + where=(Column('group') != 'some group'), + ops={'group': 'my_operator_class'} ) The constraint is normally embedded into the :class:`_schema.Table` @@ -124,7 +125,8 @@ (some_table.c.period, '&&'), (some_table.c.group, '='), where=some_table.c.group != 'some group', - name='some_table_excl_const' + name='some_table_excl_const', + ops={'group': 'my_operator_class'} ) ) @@ -162,6 +164,19 @@ If set, emit WHERE when issuing DDL for this constraint. + :param ops: + Optional dictionary. Used to define operator classes for the + elements; works the same way as that of the + :ref:`postgresql_ops ` + parameter specified to the :class:`_schema.Index` construct. + + .. versionadded:: 1.3.21 + + .. seealso:: + + :ref:`postgresql_operator_classes` - general description of how + PostgreSQL operator classes are specified. + """ columns = [] render_exprs = [] @@ -201,6 +216,8 @@ where, allow_coercion_to_text=True ) + self.ops = kw.get("ops", {}) + def copy(self, **kw): elements = [(col, self.operators[col]) for col in self.columns.keys()] c = self.__class__( diff -Nru sqlalchemy-1.3.20+ds1/lib/sqlalchemy/dialects/sqlite/base.py sqlalchemy-1.3.22+ds1/lib/sqlalchemy/dialects/sqlite/base.py --- sqlalchemy-1.3.20+ds1/lib/sqlalchemy/dialects/sqlite/base.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/lib/sqlalchemy/dialects/sqlite/base.py 2020-12-18 21:05:18.000000000 +0000 @@ -584,6 +584,21 @@ When using the per-:class:`_engine.Engine` execution option, note that **Core and ORM queries that use UNION may not function properly**. +SQLite-specific table options +----------------------------- + +One option for CREATE TABLE is supported directly by the SQLite +dialect in conjunction with the :class:`_schema.Table` construct: + +* ``WITHOUT ROWID``:: + + Table("some_table", metadata, ..., sqlite_with_rowid=False) + +.. seealso:: + + `SQLite CREATE TABLE options + `_ + """ # noqa import datetime @@ -1254,6 +1269,11 @@ return text + def post_create_table(self, table): + if table.dialect_options["sqlite"]["with_rowid"] is False: + return "\n WITHOUT ROWID" + return "" + class SQLiteTypeCompiler(compiler.GenericTypeCompiler): def visit_large_binary(self, type_, **kw): @@ -1461,7 +1481,13 @@ isolation_level = None construct_arguments = [ - (sa_schema.Table, {"autoincrement": False}), + ( + sa_schema.Table, + { + "autoincrement": False, + "with_rowid": True, + }, + ), (sa_schema.Index, {"where": None}), ( sa_schema.Column, @@ -1839,6 +1865,7 @@ constraint_name = result.group(1) if result else None cols = self.get_columns(connection, table_name, schema, **kw) + cols.sort(key=lambda col: col.get("primary_key")) pkeys = [] for col in cols: if col["primary_key"]: diff -Nru sqlalchemy-1.3.20+ds1/lib/sqlalchemy/engine/default.py sqlalchemy-1.3.22+ds1/lib/sqlalchemy/engine/default.py --- sqlalchemy-1.3.20+ds1/lib/sqlalchemy/engine/default.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/lib/sqlalchemy/engine/default.py 2020-12-18 21:05:18.000000000 +0000 @@ -322,7 +322,7 @@ self.default_schema_name = None try: - self.default_isolation_level = self.get_isolation_level( + self.default_isolation_level = self.get_default_isolation_level( connection.connection ) except NotImplementedError: @@ -367,6 +367,22 @@ """ return None + def get_default_isolation_level(self, dbapi_conn): + """Given a DBAPI connection, return its isolation level, or + a default isolation level if one cannot be retrieved. + + May be overridden by subclasses in order to provide a + "fallback" isolation level for databases that cannot reliably + retrieve the actual isolation level. + + By default, calls the :meth:`_engine.Interfaces.get_isolation_level` + method, propagating any exceptions raised. + + .. versionadded:: 1.3.22 + + """ + return self.get_isolation_level(dbapi_conn) + def _check_unicode_returns(self, connection, additional_tests=None): if util.py2k and not self.supports_unicode_statements: cast_to = util.binary_type diff -Nru sqlalchemy-1.3.20+ds1/lib/sqlalchemy/engine/__init__.py sqlalchemy-1.3.22+ds1/lib/sqlalchemy/engine/__init__.py --- sqlalchemy-1.3.20+ds1/lib/sqlalchemy/engine/__init__.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/lib/sqlalchemy/engine/__init__.py 2020-12-18 21:05:18.000000000 +0000 @@ -187,6 +187,7 @@ :ref:`dbengine_logging` - further detail on how to configure logging. + :param echo_pool=False: if True, the connection pool will log informational output such as when connections are invalidated as well as when connections are recycled to the default log handler, @@ -278,6 +279,11 @@ .. versionadded:: 1.3.8 + .. seealso:: + + :ref:`dbengine_logging` - further detail on how to configure + logging. + :param implicit_returning=True: When ``True``, a RETURNING- compatible construct, if available, will be used to fetch newly generated primary key values when a single row @@ -356,6 +362,13 @@ "sqlalchemy.engine" logger. Defaults to a hexstring of the object's id. + .. seealso:: + + :ref:`dbengine_logging` - further detail on how to configure + logging. + + + :param max_identifier_length: integer; override the max_identifier_length determined by the dialect. if ``None`` or zero, has no effect. This is the database's configured maximum number of characters that may be @@ -415,6 +428,13 @@ "sqlalchemy.pool" logger. Defaults to a hexstring of the object's id. + + .. seealso:: + + :ref:`dbengine_logging` - further detail on how to configure + logging. + + :param pool_pre_ping: boolean, if True will enable the connection pool "pre-ping" feature that tests connections for liveness upon each checkout. diff -Nru sqlalchemy-1.3.20+ds1/lib/sqlalchemy/engine/interfaces.py sqlalchemy-1.3.22+ds1/lib/sqlalchemy/engine/interfaces.py --- sqlalchemy-1.3.20+ds1/lib/sqlalchemy/engine/interfaces.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/lib/sqlalchemy/engine/interfaces.py 2020-12-18 21:05:18.000000000 +0000 @@ -872,6 +872,26 @@ raise NotImplementedError() + def get_default_isolation_level(self, dbapi_conn): + """Given a DBAPI connection, return its isolation level, or + a default isolation level if one cannot be retrieved. + + This method may only raise NotImplementedError and + **must not raise any other exception**, as it is used implicitly upon + first connect. + + The method **must return a value** for a dialect that supports + isolation level settings, as this level is what will be reverted + towards when a per-connection isolation level change is made. + + The method defaults to using the :meth:`.Dialect.get_isolation_level` + method unless overridden by a dialect. + + .. versionadded:: 1.3.22 + + """ + raise NotImplementedError() + @classmethod def get_dialect_cls(cls, url): """Given a URL, return the :class:`.Dialect` that will be used. @@ -1447,6 +1467,16 @@ a connection and pool invalidation can be invoked or prevented by changing this flag. + + .. note:: The pool "pre_ping" handler enabled using the + :paramref:`_sa.create_engine.pool_pre_ping` parameter does **not** + consult this event before deciding if the "ping" returned false, + as opposed to receiving an unhandled error. For this use case, the + :ref:`legacy recipe based on engine_connect() may be used + `. A future API allow more + comprehensive customization of the "disconnect" detection mechanism + across all functions. + """ invalidate_pool_on_disconnect = True diff -Nru sqlalchemy-1.3.20+ds1/lib/sqlalchemy/events.py sqlalchemy-1.3.22+ds1/lib/sqlalchemy/events.py --- sqlalchemy-1.3.20+ds1/lib/sqlalchemy/events.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/lib/sqlalchemy/events.py 2020-12-18 21:05:18.000000000 +0000 @@ -220,22 +220,49 @@ """Called for each unit of 'column info' retrieved when a :class:`_schema.Table` is being reflected. + Currently, this event may only be applied to the :class:`_schema.Table` + class directly:: + + from sqlalchemy import Table + + @event.listens_for(Table, 'column_reflect') + def receive_column_reflect(inspector, table, column_info): + # receives for all Table objects that are reflected + + Or applied using the + :paramref:`_schema.Table.listeners` parameter:: + + t1 = Table( + "my_table", + autoload_with=some_engine, + listeners=[ + ('column_reflect', receive_column_reflect) + ] + ) + + A future release will allow it to be associated with a specific + :class:`_schema.MetaData` object as well. + The dictionary of column information as returned by the dialect is passed, and can be modified. The dictionary is that returned in each element of the list returned by :meth:`.reflection.Inspector.get_columns`: - * ``name`` - the column's name + * ``name`` - the column's name, is applied to the + :paramref:`_schema.Column.name` parameter * ``type`` - the type of this column, which should be an instance - of :class:`~sqlalchemy.types.TypeEngine` + of :class:`~sqlalchemy.types.TypeEngine`, is applied to the + :paramref:`_schema.Column.type` parameter - * ``nullable`` - boolean flag if the column is NULL or NOT NULL + * ``nullable`` - boolean flag if the column is NULL or NOT NULL, + is applied to the :paramref:`_schema.Column.nullable` parameter * ``default`` - the column's server default value. This is normally specified as a plain string SQL expression, however the event can pass a :class:`.FetchedValue`, :class:`.DefaultClause`, - or :func:`_expression.text` object as well. + or :func:`_expression.text` object as well. Is applied to the + :paramref:`_schema.Column.server_default` parameter .. versionchanged:: 1.1.6 @@ -245,53 +272,30 @@ specified as the value of ``default`` in the column dictionary. - * ``attrs`` - dict containing optional column attributes - The event is called before any action is taken against - this dictionary, and the contents can be modified. - The :class:`_schema.Column` specific arguments ``info``, ``key``, - and ``quote`` can also be added to the dictionary and - will be passed to the constructor of :class:`_schema.Column`. - - Note that this event is only meaningful if either - associated with the :class:`_schema.Table` class across the - board, e.g.:: - - from sqlalchemy.schema import Table - from sqlalchemy import event - - def listen_for_reflect(inspector, table, column_info): - "receive a column_reflect event" - # ... - - event.listen( - Table, - 'column_reflect', - listen_for_reflect) - - ...or with a specific :class:`_schema.Table` instance using - the ``listeners`` argument:: - - def listen_for_reflect(inspector, table, column_info): - "receive a column_reflect event" - # ... - - t = Table( - 'sometable', - autoload=True, - listeners=[ - ('column_reflect', listen_for_reflect) - ]) - - This because the reflection process initiated by ``autoload=True`` - completes within the scope of the constructor for - :class:`_schema.Table`. + this dictionary, and the contents can be modified; the following + additional keys may be added to the dictionary to further modify + how the :class:`_schema.Column` is constructed: + + + * ``key`` - the string key that will be used to access this + :class:`_schema.Column` in the ``.c`` collection; will be applied + to the :paramref:`_schema.Column.key` parameter. Is also used + for ORM mapping. See the section + :ref:`mapper_automated_reflection_schemes` for an example. + + * ``quote`` - force or un-force quoting on the column name; + is applied to the :paramref:`_schema.Column.quote` parameter. + + * ``info`` - a dictionary of arbitrary data to follow along with + the :class:`_schema.Column`, is applied to the + :paramref:`_schema.Column.info` parameter. :func:`.event.listen` also accepts the ``propagate=True`` modifier for this event; when True, the listener function will be established for any copies made of the target object, i.e. those copies that are generated when - :meth:`_schema.Table.tometadata` is used. + :meth:`_schema.Table.to_metadata` is used. """ @@ -844,7 +848,7 @@ * exception re-writing * Establishing or disabling whether a connection or the owning connection pool is invalidated or expired in response to a - specific exception. + specific exception. [1]_. The hook is called while the cursor from the failed operation (if any) is still open and accessible. Special cleanup operations @@ -854,21 +858,17 @@ the scope of this hook; the rollback of the per-statement transaction also occurs after the hook is called. - For the common case of detecting a "disconnect" situation which - is not currently handled by the SQLAlchemy dialect, the - :attr:`.ExceptionContext.is_disconnect` flag can be set to True which - will cause the exception to be considered as a disconnect situation, - which typically results in the connection pool being invalidated:: - - @event.listens_for(Engine, "handle_error") - def handle_exception(context): - if isinstance(context.original_exception, pyodbc.Error): - for code in ( - '08S01', '01002', '08003', - '08007', '08S02', '08001', 'HYT00', 'HY010'): + .. note:: - if code in str(context.original_exception): - context.is_disconnect = True + .. [1] The pool "pre_ping" handler enabled using the + :paramref:`_sa.create_engine.pool_pre_ping` parameter does + **not** consult this event before deciding if the "ping" + returned false, as opposed to receiving an unhandled error. + For this use case, the :ref:`legacy recipe based on + engine_connect() may be used + `. A future API allow + more comprehensive customization of the "disconnect" + detection mechanism across all functions. A handler function has two options for replacing the SQLAlchemy-constructed exception into one that is user diff -Nru sqlalchemy-1.3.20+ds1/lib/sqlalchemy/ext/declarative/clsregistry.py sqlalchemy-1.3.22+ds1/lib/sqlalchemy/ext/declarative/clsregistry.py --- sqlalchemy-1.3.20+ds1/lib/sqlalchemy/ext/declarative/clsregistry.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/lib/sqlalchemy/ext/declarative/clsregistry.py 2020-12-18 21:05:18.000000000 +0000 @@ -259,23 +259,34 @@ class _class_resolver(object): - def __init__(self, cls, prop, fallback, arg): + def __init__(self, cls, prop, fallback, arg, favor_tables=False): self.cls = cls self.prop = prop self.arg = self._declarative_arg = arg self.fallback = fallback self._dict = util.PopulateDict(self._access_cls) self._resolvers = () + self.favor_tables = favor_tables def _access_cls(self, key): cls = self.cls + + if self.favor_tables: + if key in cls.metadata.tables: + return cls.metadata.tables[key] + elif key in cls.metadata._schemas: + return _GetTable(key, cls.metadata) + if key in cls._decl_class_registry: return _determine_container(key, cls._decl_class_registry[key]) - elif key in cls.metadata.tables: - return cls.metadata.tables[key] - elif key in cls.metadata._schemas: - return _GetTable(key, cls.metadata) - elif ( + + if not self.favor_tables: + if key in cls.metadata.tables: + return cls.metadata.tables[key] + elif key in cls.metadata._schemas: + return _GetTable(key, cls.metadata) + + if ( "_sa_module_registry" in cls._decl_class_registry and key in cls._decl_class_registry["_sa_module_registry"] ): @@ -340,8 +351,10 @@ fallback = sqlalchemy.__dict__.copy() fallback.update({"foreign": foreign, "remote": remote}) - def resolve_arg(arg): - return _class_resolver(cls, prop, fallback, arg) + def resolve_arg(arg, favor_tables=False): + return _class_resolver( + cls, prop, fallback, arg, favor_tables=favor_tables + ) def resolve_name(arg): return _class_resolver(cls, prop, fallback, arg)._resolve_name @@ -364,7 +377,11 @@ ): v = getattr(prop, attr) if isinstance(v, util.string_types): - setattr(prop, attr, resolve_arg(v)) + setattr( + prop, + attr, + resolve_arg(v, favor_tables=attr == "secondary"), + ) for attr in ("argument",): v = getattr(prop, attr) diff -Nru sqlalchemy-1.3.20+ds1/lib/sqlalchemy/ext/hybrid.py sqlalchemy-1.3.22+ds1/lib/sqlalchemy/ext/hybrid.py --- sqlalchemy-1.3.20+ds1/lib/sqlalchemy/ext/hybrid.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/lib/sqlalchemy/ext/hybrid.py 2020-12-18 21:05:18.000000000 +0000 @@ -64,9 +64,10 @@ When dealing with the ``Interval`` class itself, the :class:`.hybrid_property` descriptor evaluates the function body given the ``Interval`` class as the argument, which when evaluated with SQLAlchemy expression mechanics +(here using the :attr:`.QueryableAttribute.expression` accessor) returns a new SQL expression:: - >>> print(Interval.length) + >>> print(Interval.length.expression) interval."end" - interval.start >>> print(Session().query(Interval).filter(Interval.length > 10)) diff -Nru sqlalchemy-1.3.20+ds1/lib/sqlalchemy/__init__.py sqlalchemy-1.3.22+ds1/lib/sqlalchemy/__init__.py --- sqlalchemy-1.3.20+ds1/lib/sqlalchemy/__init__.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/lib/sqlalchemy/__init__.py 2020-12-18 21:05:18.000000000 +0000 @@ -124,7 +124,7 @@ from .engine import engine_from_config # noqa nosort -__version__ = "1.3.20" +__version__ = "1.3.22" def __go(lcls): diff -Nru sqlalchemy-1.3.20+ds1/lib/sqlalchemy/orm/attributes.py sqlalchemy-1.3.22+ds1/lib/sqlalchemy/orm/attributes.py --- sqlalchemy-1.3.20+ds1/lib/sqlalchemy/orm/attributes.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/lib/sqlalchemy/orm/attributes.py 2020-12-18 21:05:18.000000000 +0000 @@ -176,6 +176,13 @@ @property def expression(self): + """The SQL expression object represented by this + :class:`.QueryableAttribute`. + + This will typically be an instance of a :class:`.ColumnElement` + subclass representing a column expression. + + """ return self.comparator.__clause_element__() def __clause_element__(self): diff -Nru sqlalchemy-1.3.20+ds1/lib/sqlalchemy/orm/events.py sqlalchemy-1.3.22+ds1/lib/sqlalchemy/orm/events.py --- sqlalchemy-1.3.20+ds1/lib/sqlalchemy/orm/events.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/lib/sqlalchemy/orm/events.py 2020-12-18 21:05:18.000000000 +0000 @@ -540,7 +540,13 @@ collection = target.all_holds[target.class_] = {} event.registry._stored_in_collection(event_key, target) - collection[event_key._key] = (event_key, raw, propagate, retval) + collection[event_key._key] = ( + event_key, + raw, + propagate, + retval, + kw, + ) if propagate: stack = list(target.class_.__subclasses__()) @@ -567,7 +573,13 @@ for subclass in class_.__mro__: if subclass in cls.all_holds: collection = cls.all_holds[subclass] - for event_key, raw, propagate, retval in collection.values(): + for ( + event_key, + raw, + propagate, + retval, + kw, + ) in collection.values(): if propagate or subclass is class_: # since we can't be sure in what order different # classes in a hierarchy are triggered with @@ -575,7 +587,7 @@ # assignment, instead of using the generic propagate # flag. event_key.with_dispatch_target(subject).listen( - raw=raw, propagate=False, retval=retval + raw=raw, propagate=False, retval=retval, **kw ) diff -Nru sqlalchemy-1.3.20+ds1/lib/sqlalchemy/orm/persistence.py sqlalchemy-1.3.22+ds1/lib/sqlalchemy/orm/persistence.py --- sqlalchemy-1.3.20+ds1/lib/sqlalchemy/orm/persistence.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/lib/sqlalchemy/orm/persistence.py 2020-12-18 21:05:18.000000000 +0000 @@ -1786,7 +1786,9 @@ pk, identity_token, ), obj in query.session.identity_map.items() - if issubclass(cls, target_cls) and eval_condition(obj) + if issubclass(cls, target_cls) + and not attributes.instance_state(obj).expired + and eval_condition(obj) ] @@ -1957,7 +1959,8 @@ # only evaluate unmodified attributes to_evaluate = state.unmodified.intersection(evaluated_keys) for key in to_evaluate: - dict_[key] = self.value_evaluators[key](obj) + if key in dict_: + dict_[key] = self.value_evaluators[key](obj) state.manager.dispatch.refresh(state, None, to_evaluate) diff -Nru sqlalchemy-1.3.20+ds1/lib/sqlalchemy/orm/query.py sqlalchemy-1.3.22+ds1/lib/sqlalchemy/orm/query.py --- sqlalchemy-1.3.20+ds1/lib/sqlalchemy/orm/query.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/lib/sqlalchemy/orm/query.py 2020-12-18 21:05:18.000000000 +0000 @@ -1782,7 +1782,7 @@ ``FOR UPDATE`` clause. The behavior of this method is identical to that of - :meth:`_expression.SelectBase.with_for_update`. + :meth:`_expression.GenerativeSelect.with_for_update`. When called with no arguments, the resulting ``SELECT`` statement will have a ``FOR UPDATE`` clause appended. When additional arguments are specified, backend-specific diff -Nru sqlalchemy-1.3.20+ds1/lib/sqlalchemy/orm/relationships.py sqlalchemy-1.3.22+ds1/lib/sqlalchemy/orm/relationships.py --- sqlalchemy-1.3.20+ds1/lib/sqlalchemy/orm/relationships.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/lib/sqlalchemy/orm/relationships.py 2020-12-18 21:05:18.000000000 +0000 @@ -21,6 +21,7 @@ from . import attributes from . import dependency from . import mapper as mapperlib +from .base import _is_mapped_class from .base import state_str from .interfaces import MANYTOMANY from .interfaces import MANYTOONE @@ -2101,7 +2102,7 @@ "remote_side", ): attr_value = getattr(self, attr) - if util.callable(attr_value): + if util.callable(attr_value) and not _is_mapped_class(attr_value): setattr(self, attr, attr_value()) # remove "annotations" which are present if mapped class @@ -2117,6 +2118,15 @@ ), ) + if self.secondary is not None and _is_mapped_class(self.secondary): + raise sa_exc.ArgumentError( + "secondary argument %s passed to to relationship() %s must " + "be a Table object or other FROM clause; can't send a mapped " + "class directly as rows in 'secondary' are persisted " + "independently of a class that is mapped " + "to that same table." % (self.secondary, self) + ) + # ensure expressions in self.order_by, foreign_keys, # remote_side are all columns, not strings. if self.order_by is not False and self.order_by is not None: diff -Nru sqlalchemy-1.3.20+ds1/lib/sqlalchemy/orm/session.py sqlalchemy-1.3.22+ds1/lib/sqlalchemy/orm/session.py --- sqlalchemy-1.3.20+ds1/lib/sqlalchemy/orm/session.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/lib/sqlalchemy/orm/session.py 2020-12-18 21:05:18.000000000 +0000 @@ -803,6 +803,10 @@ so that all attribute/object access subsequent to a completed transaction will load from the most recent database state. + .. seealso:: + + :ref:`session_committing` + :param extension: An optional :class:`~.SessionExtension` instance, or a list of such instances, which will receive pre- and post- commit and @@ -2709,9 +2713,10 @@ and SQL clause support are **silently omitted** in favor of raw INSERT/UPDATES of records. - **Please read the list of caveats at** :ref:`bulk_operations` - **before using this method, and fully test and confirm the - functionality of all code developed using these systems.** + **Please read the list of caveats at** + :ref:`bulk_operations_caveats` **before using this method, and + fully test and confirm the functionality of all code developed + using these systems.** :param objects: a sequence of mapped object instances. The mapped objects are persisted as is, and are **not** associated with the @@ -2809,9 +2814,10 @@ and SQL clause support are **silently omitted** in favor of raw INSERT of records. - **Please read the list of caveats at** :ref:`bulk_operations` - **before using this method, and fully test and confirm the - functionality of all code developed using these systems.** + **Please read the list of caveats at** + :ref:`bulk_operations_caveats` **before using this method, and + fully test and confirm the functionality of all code developed + using these systems.** :param mapper: a mapped class, or the actual :class:`_orm.Mapper` object, @@ -2901,9 +2907,10 @@ and SQL clause support are **silently omitted** in favor of raw UPDATES of records. - **Please read the list of caveats at** :ref:`bulk_operations` - **before using this method, and fully test and confirm the - functionality of all code developed using these systems.** + **Please read the list of caveats at** + :ref:`bulk_operations_caveats` **before using this method, and + fully test and confirm the functionality of all code developed + using these systems.** :param mapper: a mapped class, or the actual :class:`_orm.Mapper` object, @@ -3255,8 +3262,10 @@ :class:`.Session` objects. :param autocommit: The autocommit setting to use with newly created :class:`.Session` objects. - :param expire_on_commit=True: the expire_on_commit setting to use + :param expire_on_commit=True: the + :paramref:`_orm.Session.expire_on_commit` setting to use with newly created :class:`.Session` objects. + :param info: optional dictionary of information that will be available via :attr:`.Session.info`. Note this dictionary is *updated*, not replaced, when the ``info`` parameter is specified to the specific diff -Nru sqlalchemy-1.3.20+ds1/lib/sqlalchemy/orm/strategy_options.py sqlalchemy-1.3.22+ds1/lib/sqlalchemy/orm/strategy_options.py --- sqlalchemy-1.3.20+ds1/lib/sqlalchemy/orm/strategy_options.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/lib/sqlalchemy/orm/strategy_options.py 2020-12-18 21:05:18.000000000 +0000 @@ -1030,40 +1030,18 @@ ``User`` entity, and the returned ``Order`` objects would have the ``Order.user`` attribute pre-populated. - When making use of aliases with :func:`.contains_eager`, the path - should be specified using :meth:`.PropComparator.of_type`:: + It may also be used for customizing the entries in an eagerly loaded + collection; queries will normally want to use the + :meth:`_query.Query.populate_existing` method assuming the primary + collection of parent objects may already have been loaded:: + + sess.query(User).\ + join(User.addresses).\ + filter(Address.email_address.like('%@aol.com')).\ + options(contains_eager(User.addresses)).\ + populate_existing() - user_alias = aliased(User) - sess.query(Order).\ - join((user_alias, Order.user)).\ - options(contains_eager(Order.user.of_type(user_alias))) - - :meth:`.PropComparator.of_type` is also used to indicate a join - against specific subclasses of an inherting mapper, or - of a :func:`.with_polymorphic` construct:: - - # employees of a particular subtype - sess.query(Company).\ - outerjoin(Company.employees.of_type(Manager)).\ - options( - contains_eager( - Company.employees.of_type(Manager), - ) - ) - - # employees of a multiple subtypes - wp = with_polymorphic(Employee, [Manager, Engineer]) - sess.query(Company).\ - outerjoin(Company.employees.of_type(wp)).\ - options( - contains_eager( - Company.employees.of_type(wp), - ) - ) - - The :paramref:`.contains_eager.alias` parameter is used for a similar - purpose, however the :meth:`.PropComparator.of_type` approach should work - in all cases and is more effective and explicit. + See the section :ref:`contains_eager` for complete usage details. .. seealso:: @@ -1706,7 +1684,7 @@ .. note:: the target attribute is populated only if the target object is **not currently loaded** in the current :class:`_orm.Session` - unless the :meth:`_orm.Query.populate_existing` method is used. + unless the :meth:`_query.Query.populate_existing` method is used. Please refer to :ref:`mapper_querytime_expression` for complete usage details. diff -Nru sqlalchemy-1.3.20+ds1/lib/sqlalchemy/sql/compiler.py sqlalchemy-1.3.22+ds1/lib/sqlalchemy/sql/compiler.py --- sqlalchemy-1.3.20+ds1/lib/sqlalchemy/sql/compiler.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/lib/sqlalchemy/sql/compiler.py 2020-12-18 21:05:18.000000000 +0000 @@ -600,6 +600,44 @@ self._apply_numbered_params() @property + def current_executable(self): + """Return the current 'executable' that is being compiled. + + This is currently the :class:`_sql.Select`, :class:`_sql.Insert`, + :class:`_sql.Update`, :class:`_sql.Delete`, + :class:`_sql.CompoundSelect` object that is being compiled. + Specifically it's assigned to the ``self.stack`` list of elements. + + When a statement like the above is being compiled, it normally + is also assigned to the ``.statement`` attribute of the + :class:`_sql.Compiler` object. However, all SQL constructs are + ultimately nestable, and this attribute should never be consulted + by a ``visit_`` method, as it is not guaranteed to be assigned + nor guaranteed to correspond to the current statement being compiled. + + .. versionadded:: 1.3.21 + + For compatibility with previous versions, use the following + recipe:: + + statement = getattr(self, "current_executable", False) + if statement is False: + statement = self.stack[-1]["selectable"] + + For versions 1.4 and above, ensure only .current_executable + is used; the format of "self.stack" may change. + + + """ + try: + return self.stack[-1]["selectable"] + except IndexError as ie: + util.raise_( + IndexError("Compiler does not have a stack entry"), + replace_context=ie, + ) + + @property def prefetch(self): return list(self.insert_prefetch + self.update_prefetch) diff -Nru sqlalchemy-1.3.20+ds1/lib/sqlalchemy/sql/dml.py sqlalchemy-1.3.22+ds1/lib/sqlalchemy/sql/dml.py --- sqlalchemy-1.3.20+ds1/lib/sqlalchemy/sql/dml.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/lib/sqlalchemy/sql/dml.py 2020-12-18 21:05:18.000000000 +0000 @@ -42,6 +42,7 @@ _parameter_ordering = None _prefixes = () named_with_column = False + _return_defaults = None def _process_colparams(self, parameters): def process_single(p): @@ -119,11 +120,10 @@ for server_flag, updated_timestamp in connection.execute(stmt): print(server_flag, updated_timestamp) - The given collection of column expressions should be derived from - the table that is - the target of the INSERT, UPDATE, or DELETE. While - :class:`_schema.Column` - objects are typical, the elements can also be expressions:: + The given collection of column expressions should be derived from the + table that is the target of the INSERT, UPDATE, or DELETE. While + :class:`_schema.Column` objects are typical, the elements can also be + expressions:: stmt = table.insert().returning( (table.c.first_name + " " + table.c.last_name). @@ -159,6 +159,16 @@ """ + if self._return_defaults: + raise exc.InvalidRequestError( + "return_defaults() is already configured on this statement" + ) + if self._returning: + util.warn( + "The returning() method does not currently support multiple " + "additive calls. The existing RETURNING clause being " + "replaced by new columns." + ) self._returning = cols @_generative @@ -476,6 +486,10 @@ :attr:`_engine.ResultProxy.returned_defaults` """ + if self._returning: + raise exc.InvalidRequestError( + "RETURNING is already configured on this statement" + ) self._return_defaults = cols or True @@ -686,23 +700,9 @@ table to be updated. :param whereclause: Optional SQL expression describing the ``WHERE`` - condition of the ``UPDATE`` statement. Modern applications - may prefer to use the generative :meth:`~Update.where()` - method to specify the ``WHERE`` clause. - - The WHERE clause can refer to multiple tables. - For databases which support this, an ``UPDATE FROM`` clause will - be generated, or on MySQL, a multi-table update. The statement - will fail on databases that don't have support for multi-table - update statements. A SQL-standard method of referring to - additional tables in the WHERE clause is to use a correlated - subquery:: - - users.update().values(name='ed').where( - users.c.name==select([addresses.c.email_address]).\ - where(addresses.c.user_id==users.c.id).\ - as_scalar() - ) + condition of the ``UPDATE`` statement; is equivalent to using the + more modern :meth:`~Update.where()` method to specify the ``WHERE`` + clause. :param values: Optional dictionary which specifies the ``SET`` conditions of the @@ -806,6 +806,22 @@ """Return a new update() construct with the given expression added to its WHERE clause, joined to the existing clause via AND, if any. + Both :meth:`_dml.Update.where` and :meth:`_dml.Delete.where` + support multiple-table forms, including database-specific + ``UPDATE...FROM`` as well as ``DELETE..USING``. For backends that + don't have multiple-table support, a backend agnostic approach + to using multiple tables is to make use of correlated subqueries. + See the linked tutorial sections below for examples. + + .. seealso:: + + :ref:`tutorial_1x_correlated_updates` + + :ref:`multi_table_updates` + + :ref:`multi_table_deletes` + + """ if self._whereclause is not None: self._whereclause = and_( @@ -859,27 +875,10 @@ :param table: The table to delete rows from. - :param whereclause: A :class:`_expression.ClauseElement` - describing the ``WHERE`` - condition of the ``DELETE`` statement. Note that the - :meth:`~Delete.where()` generative method may be used instead. - - The WHERE clause can refer to multiple tables. - For databases which support this, a ``DELETE..USING`` or similar - clause will be generated. The statement - will fail on databases that don't have support for multi-table - delete statements. A SQL-standard method of referring to - additional tables in the WHERE clause is to use a correlated - subquery:: - - users.delete().where( - users.c.name==select([addresses.c.email_address]).\ - where(addresses.c.user_id==users.c.id).\ - as_scalar() - ) - - .. versionchanged:: 1.2.0 - The WHERE clause of DELETE can refer to multiple tables. + :param whereclause: Optional SQL expression describing the ``WHERE`` + condition of the ``DELETE`` statement; is equivalent to using the + more modern :meth:`~Delete.where()` method to specify the ``WHERE`` + clause. .. seealso:: @@ -908,7 +907,25 @@ @_generative def where(self, whereclause): - """Add the given WHERE clause to a newly returned delete construct.""" + """Add the given WHERE clause to a newly returned delete construct. + + Both :meth:`_dml.Update.where` and :meth:`_dml.Delete.where` + support multiple-table forms, including database-specific + ``UPDATE...FROM`` as well as ``DELETE..USING``. For backends that + don't have multiple-table support, a backend agnostic approach + to using multiple tables is to make use of correlated subqueries. + See the linked tutorial sections below for examples. + + .. seealso:: + + :ref:`tutorial_1x_correlated_updates` + + :ref:`multi_table_updates` + + :ref:`multi_table_deletes` + + + """ if self._whereclause is not None: self._whereclause = and_( diff -Nru sqlalchemy-1.3.20+ds1/lib/sqlalchemy/sql/util.py sqlalchemy-1.3.22+ds1/lib/sqlalchemy/sql/util.py --- sqlalchemy-1.3.20+ds1/lib/sqlalchemy/sql/util.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/lib/sqlalchemy/sql/util.py 2020-12-18 21:05:18.000000000 +0000 @@ -386,7 +386,12 @@ elif isinstance(elem, FromGrouping): stack.append(elem.element) elif isinstance(elem, ColumnClause): - stack.append(elem.table) + if elem.table is not None: + stack.append(elem.table) + else: + yield elem + elif elem is not None: + yield elem def surface_column_elements(clause, include_scalar_selects=True): diff -Nru sqlalchemy-1.3.20+ds1/lib/sqlalchemy/testing/assertions.py sqlalchemy-1.3.22+ds1/lib/sqlalchemy/testing/assertions.py --- sqlalchemy-1.3.20+ds1/lib/sqlalchemy/testing/assertions.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/lib/sqlalchemy/testing/assertions.py 2020-12-18 21:05:18.000000000 +0000 @@ -21,6 +21,7 @@ from .. import exc as sa_exc from .. import pool from .. import schema +from .. import sql from .. import types as sqltypes from .. import util from ..engine import default @@ -444,7 +445,60 @@ if compile_kwargs: kw["compile_kwargs"] = compile_kwargs - c = clause.compile(dialect=dialect, **kw) + class DontAccess(object): + def __getattribute__(self, key): + raise NotImplementedError( + "compiler accessed .statement; use " + "compiler.current_executable" + ) + + class CheckCompilerAccess(object): + def __init__(self, test_statement): + self.test_statement = test_statement + self.supports_execution = getattr( + test_statement, "supports_execution", False + ) + if self.supports_execution: + self._execution_options = test_statement._execution_options + + if isinstance( + test_statement, (sql.Insert, sql.Update, sql.Delete) + ): + self._returning = test_statement._returning + if isinstance(test_statement, (sql.Insert, sql.Update)): + self.inline = test_statement.inline + self._return_defaults = test_statement._return_defaults + + def _default_dialect(self): + return self.test_statement._default_dialect() + + def compile(self, dialect, **kw): + return self.test_statement.compile.__func__( + self, dialect=dialect, **kw + ) + + def _compiler(self, dialect, **kw): + return self.test_statement._compiler.__func__( + self, dialect, **kw + ) + + def _compiler_dispatch(self, compiler, **kwargs): + if hasattr(compiler, "statement"): + with mock.patch.object( + compiler, "statement", DontAccess() + ): + return self.test_statement._compiler_dispatch( + compiler, **kwargs + ) + else: + return self.test_statement._compiler_dispatch( + compiler, **kwargs + ) + + # no construct can assume it's the "top level" construct in all cases + # as anything can be nested. ensure constructs don't assume they + # are the "self.statement" element + c = CheckCompilerAccess(clause).compile(dialect=dialect, **kw) param_str = repr(getattr(c, "params", {})) diff -Nru sqlalchemy-1.3.20+ds1/lib/sqlalchemy/testing/engines.py sqlalchemy-1.3.22+ds1/lib/sqlalchemy/testing/engines.py --- sqlalchemy-1.3.20+ds1/lib/sqlalchemy/testing/engines.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/lib/sqlalchemy/testing/engines.py 2020-12-18 21:05:18.000000000 +0000 @@ -338,6 +338,12 @@ def __getattr__(self, key): return getattr(self.cursor, key) + def __enter__(self): + return self + + def __exit__(self, *args): + pass + class DBAPIProxyConnection(object): """Proxy a DBAPI connection. diff -Nru sqlalchemy-1.3.20+ds1/lib/sqlalchemy/testing/requirements.py sqlalchemy-1.3.22+ds1/lib/sqlalchemy/testing/requirements.py --- sqlalchemy-1.3.20+ds1/lib/sqlalchemy/testing/requirements.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/lib/sqlalchemy/testing/requirements.py 2020-12-18 21:05:18.000000000 +0000 @@ -307,6 +307,11 @@ return exclusions.closed() @property + def tuple_in_w_empty(self): + """Target platform tuple IN w/ empty set""" + return self.tuple_in + + @property def duplicate_names_in_cursor_description(self): """target platform supports a SELECT statement that has the same name repeated more than once in the columns list.""" diff -Nru sqlalchemy-1.3.20+ds1/lib/sqlalchemy/testing/suite/test_dialect.py sqlalchemy-1.3.22+ds1/lib/sqlalchemy/testing/suite/test_dialect.py --- sqlalchemy-1.3.20+ds1/lib/sqlalchemy/testing/suite/test_dialect.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/lib/sqlalchemy/testing/suite/test_dialect.py 2020-12-18 21:05:18.000000000 +0000 @@ -119,6 +119,28 @@ eq_(conn.get_isolation_level(), existing) + def test_all_levels(self): + levels = requirements.get_isolation_levels(config) + + all_levels = levels["supported"] + + for level in set(all_levels).difference(["AUTOCOMMIT"]): + with config.db.connect() as conn: + conn.execution_options(isolation_level=level) + + eq_(conn.get_isolation_level(), level) + + trans = conn.begin() + trans.rollback() + + eq_(conn.get_isolation_level(), level) + + with config.db.connect() as conn: + eq_( + conn.get_isolation_level(), + levels["default"], + ) + class AutocommitTest(fixtures.TablesTest): diff -Nru sqlalchemy-1.3.20+ds1/lib/sqlalchemy/testing/suite/test_reflection.py sqlalchemy-1.3.22+ds1/lib/sqlalchemy/testing/suite/test_reflection.py --- sqlalchemy-1.3.20+ds1/lib/sqlalchemy/testing/suite/test_reflection.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/lib/sqlalchemy/testing/suite/test_reflection.py 2020-12-18 21:05:18.000000000 +0000 @@ -1348,10 +1348,64 @@ ) +class CompositeKeyReflectionTest(fixtures.TablesTest): + __backend__ = True + + @classmethod + def define_tables(cls, metadata): + tb1 = Table( + "tb1", + metadata, + Column("id", Integer), + Column("attr", Integer), + Column("name", sql_types.VARCHAR(20)), + sa.PrimaryKeyConstraint("name", "id", "attr", name="pk_tb1"), + schema=None, + test_needs_fk=True, + ) + Table( + "tb2", + metadata, + Column("id", Integer, primary_key=True), + Column("pid", Integer), + Column("pattr", Integer), + Column("pname", sql_types.VARCHAR(20)), + sa.ForeignKeyConstraint( + ["pname", "pid", "pattr"], + [tb1.c.name, tb1.c.id, tb1.c.attr], + name="fk_tb1_name_id_attr", + ), + schema=None, + test_needs_fk=True, + ) + + @testing.requires.primary_key_constraint_reflection + @testing.provide_metadata + def test_pk_column_order(self): + # test for issue #5661 + meta = self.metadata + insp = inspect(meta.bind) + primary_key = insp.get_pk_constraint(self.tables.tb1.name) + eq_(primary_key.get("constrained_columns"), ["name", "id", "attr"]) + + @testing.requires.foreign_key_constraint_reflection + @testing.provide_metadata + def test_fk_column_order(self): + # test for issue #5661 + meta = self.metadata + insp = inspect(meta.bind) + foreign_keys = insp.get_foreign_keys(self.tables.tb2.name) + eq_(len(foreign_keys), 1) + fkey1 = foreign_keys[0] + eq_(fkey1.get("referred_columns"), ["name", "id", "attr"]) + eq_(fkey1.get("constrained_columns"), ["pname", "pid", "pattr"]) + + __all__ = ( "ComponentReflectionTest", "QuotedNameArgumentTest", "HasTableTest", "NormalizedNameTest", "ComputedReflectionTest", + "CompositeKeyReflectionTest", ) diff -Nru sqlalchemy-1.3.20+ds1/lib/sqlalchemy/testing/suite/test_select.py sqlalchemy-1.3.22+ds1/lib/sqlalchemy/testing/suite/test_select.py --- sqlalchemy-1.3.20+ds1/lib/sqlalchemy/testing/suite/test_select.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/lib/sqlalchemy/testing/suite/test_select.py 2020-12-18 21:05:18.000000000 +0000 @@ -407,7 +407,7 @@ self._assert_result(stmt, [], params={"q": [], "p": []}) - @testing.requires.tuple_in + @testing.requires.tuple_in_w_empty def test_empty_heterogeneous_tuples(self): table = self.tables.some_table @@ -423,7 +423,7 @@ self._assert_result(stmt, [], params={"q": []}) - @testing.requires.tuple_in + @testing.requires.tuple_in_w_empty def test_empty_homogeneous_tuples(self): table = self.tables.some_table diff -Nru sqlalchemy-1.3.20+ds1/lib/sqlalchemy/util/langhelpers.py sqlalchemy-1.3.22+ds1/lib/sqlalchemy/util/langhelpers.py --- sqlalchemy-1.3.20+ds1/lib/sqlalchemy/util/langhelpers.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/lib/sqlalchemy/util/langhelpers.py 2020-12-18 21:05:18.000000000 +0000 @@ -170,15 +170,19 @@ class can serve as documentation for the function. """ + if isinstance(target, type): fn = target.__init__ callable_ = target doc = ( - "Construct a new :class:`.%s` object. \n\n" + "Construct a new :class:`%s` object. \n\n" "This constructor is mirrored as a public API function; " "see :func:`sqlalchemy%s` " "for a full usage and argument description." - % (target.__name__, location) + % ( + class_location if class_location else ".%s" % target.__name__, + location, + ) ) else: fn = callable_ = target @@ -202,6 +206,7 @@ env = {"cls": callable_, "symbol": symbol} exec(code, env) decorated = env[location_name] + if hasattr(fn, "_linked_to"): linked_to, linked_to_location = fn._linked_to linked_to_doc = linked_to.__doc__ @@ -211,9 +216,11 @@ linked_to_doc = inject_docstring_text( linked_to_doc, ".. container:: inherited_member\n\n " - "Inherited from :func:`sqlalchemy%s`; this constructor " - "creates a :class:`%s` object" - % (linked_to_location, class_location), + "This documentation is inherited from :func:`sqlalchemy%s`; " + "this constructor, :func:`sqlalchemy%s`, " + "creates a :class:`sqlalchemy%s` object. See that class for " + "additional details describing this subclass." + % (linked_to_location, location, class_location), 1, ) decorated.__doc__ = linked_to_doc @@ -226,6 +233,7 @@ "public_factory location %s is not in sys.modules" % (decorated.__module__,) ) + if compat.py2k or hasattr(fn, "__func__"): fn.__func__.__doc__ = doc if not hasattr(fn.__func__, "_linked_to"): @@ -234,6 +242,7 @@ fn.__doc__ = doc if not hasattr(fn, "_linked_to"): fn._linked_to = (decorated, location) + return decorated diff -Nru sqlalchemy-1.3.20+ds1/PKG-INFO sqlalchemy-1.3.22+ds1/PKG-INFO --- sqlalchemy-1.3.20+ds1/PKG-INFO 2020-10-12 22:31:11.688836000 +0000 +++ sqlalchemy-1.3.22+ds1/PKG-INFO 2020-12-18 21:06:41.924536000 +0000 @@ -1,6 +1,6 @@ Metadata-Version: 2.1 Name: SQLAlchemy -Version: 1.3.20 +Version: 1.3.22 Summary: Database Abstraction Library Home-page: http://www.sqlalchemy.org Author: Mike Bayer diff -Nru sqlalchemy-1.3.20+ds1/test/dialect/mssql/test_compiler.py sqlalchemy-1.3.22+ds1/test/dialect/mssql/test_compiler.py --- sqlalchemy-1.3.20+ds1/test/dialect/mssql/test_compiler.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/test/dialect/mssql/test_compiler.py 2020-12-18 21:05:18.000000000 +0000 @@ -18,6 +18,7 @@ from sqlalchemy import String from sqlalchemy import Table from sqlalchemy import testing +from sqlalchemy import text from sqlalchemy import union from sqlalchemy import UniqueConstraint from sqlalchemy import update @@ -1255,6 +1256,31 @@ schema.CreateIndex(idx), "CREATE INDEX foo ON test (x) INCLUDE (y)" ) + def test_index_include_where(self): + metadata = MetaData() + tbl = Table( + "test", + metadata, + Column("x", Integer), + Column("y", Integer), + Column("z", Integer), + ) + idx = Index( + "foo", tbl.c.x, mssql_include=[tbl.c.y], mssql_where=tbl.c.y > 1 + ) + self.assert_compile( + schema.CreateIndex(idx), + "CREATE INDEX foo ON test (x) INCLUDE (y) WHERE y > 1", + ) + + idx = Index( + "foo", tbl.c.x, mssql_include=[tbl.c.y], mssql_where=text("y > 1") + ) + self.assert_compile( + schema.CreateIndex(idx), + "CREATE INDEX foo ON test (x) INCLUDE (y) WHERE y > 1", + ) + def test_try_cast(self): metadata = MetaData() t1 = Table("t1", metadata, Column("id", Integer, primary_key=True)) diff -Nru sqlalchemy-1.3.20+ds1/test/dialect/mysql/test_for_update.py sqlalchemy-1.3.22+ds1/test/dialect/mysql/test_for_update.py --- sqlalchemy-1.3.20+ds1/test/dialect/mysql/test_for_update.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/test/dialect/mysql/test_for_update.py 2020-12-18 21:05:18.000000000 +0000 @@ -9,8 +9,10 @@ from sqlalchemy import exc from sqlalchemy import ForeignKey from sqlalchemy import Integer +from sqlalchemy import literal_column from sqlalchemy import Table from sqlalchemy import testing +from sqlalchemy import text from sqlalchemy import update from sqlalchemy.dialects.mysql import base as mysql from sqlalchemy.exc import ProgrammingError @@ -362,6 +364,27 @@ dialect=self.for_update_of_dialect, ) + def test_for_update_textual_of(self): + self.assert_compile( + self.table1.select(self.table1.c.myid == 7).with_for_update( + of=text("mytable") + ), + "SELECT mytable.myid, mytable.name, mytable.description " + "FROM mytable WHERE mytable.myid = %s " + "FOR UPDATE OF mytable", + dialect=self.for_update_of_dialect, + ) + + self.assert_compile( + self.table1.select(self.table1.c.myid == 7).with_for_update( + of=literal_column("mytable") + ), + "SELECT mytable.myid, mytable.name, mytable.description " + "FROM mytable WHERE mytable.myid = %s " + "FOR UPDATE OF mytable", + dialect=self.for_update_of_dialect, + ) + class SkipLockedTest(fixtures.TablesTest): __only_on__ = ("mysql",) diff -Nru sqlalchemy-1.3.20+ds1/test/dialect/mysql/test_reflection.py sqlalchemy-1.3.22+ds1/test/dialect/mysql/test_reflection.py --- sqlalchemy-1.3.20+ds1/test/dialect/mysql/test_reflection.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/test/dialect/mysql/test_reflection.py 2020-12-18 21:05:18.000000000 +0000 @@ -235,6 +235,9 @@ def test_default_reflection(self): """Test reflection of column defaults.""" + # TODO: this test is a mess. should be broken into individual + # combinations + from sqlalchemy.dialects.mysql import VARCHAR def_table = Table( @@ -259,6 +262,8 @@ ) ), ), + Column("c7", mysql.DOUBLE(), DefaultClause("0.0000")), + Column("c8", mysql.DOUBLE(22, 6), DefaultClause("0.0000")), ) def_table.create() try: @@ -278,6 +283,15 @@ assert reflected.c.c5.default is None assert reflected.c.c5.server_default is None assert reflected.c.c6.default is None + assert str(reflected.c.c7.server_default.arg) in ("0", "'0'") + + # this is because the numeric is 6 decimal places, MySQL + # formats it to that many places. + assert str(reflected.c.c8.server_default.arg) in ( + "0.000000", + "'0.000000'", + ) + assert re.match( r"CURRENT_TIMESTAMP(\(\))? ON UPDATE CURRENT_TIMESTAMP(\(\))?", str(reflected.c.c6.server_default.arg).upper(), @@ -298,6 +312,11 @@ assert reflected.c.c5.default is None assert reflected.c.c5.server_default is None assert reflected.c.c6.default is None + assert str(reflected.c.c7.server_default.arg) in ("0", "'0'") + assert str(reflected.c.c8.server_default.arg) in ( + "0.000000", + "'0.000000'", + ) assert re.match( r"CURRENT_TIMESTAMP(\(\))? ON UPDATE CURRENT_TIMESTAMP(\(\))?", str(reflected.c.c6.server_default.arg).upper(), diff -Nru sqlalchemy-1.3.20+ds1/test/dialect/oracle/test_compiler.py sqlalchemy-1.3.22+ds1/test/dialect/oracle/test_compiler.py --- sqlalchemy-1.3.20+ds1/test/dialect/oracle/test_compiler.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/test/dialect/oracle/test_compiler.py 2020-12-18 21:05:18.000000000 +0000 @@ -379,6 +379,24 @@ "mytable_1.myid, mytable_1.name", ) + # ensure of=text() for of works + self.assert_compile( + table1.select(table1.c.myid == 7).with_for_update( + read=True, of=text("table1") + ), + "SELECT mytable.myid, mytable.name, mytable.description " + "FROM mytable WHERE mytable.myid = :myid_1 FOR UPDATE OF table1", + ) + + # ensure of=literal_column() for of works + self.assert_compile( + table1.select(table1.c.myid == 7).with_for_update( + read=True, of=literal_column("table1") + ), + "SELECT mytable.myid, mytable.name, mytable.description " + "FROM mytable WHERE mytable.myid = :myid_1 FOR UPDATE OF table1", + ) + def test_for_update_of_w_limit_adaption_col_present(self): table1 = table("mytable", column("myid"), column("name")) diff -Nru sqlalchemy-1.3.20+ds1/test/dialect/oracle/test_dialect.py sqlalchemy-1.3.22+ds1/test/dialect/oracle/test_dialect.py --- sqlalchemy-1.3.20+ds1/test/dialect/oracle/test_dialect.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/test/dialect/oracle/test_dialect.py 2020-12-18 21:05:18.000000000 +0000 @@ -24,6 +24,7 @@ from sqlalchemy.testing import assert_raises_message from sqlalchemy.testing import AssertsCompiledSQL from sqlalchemy.testing import AssertsExecutionResults +from sqlalchemy.testing import engines from sqlalchemy.testing import eq_ from sqlalchemy.testing import fixtures from sqlalchemy.testing import mock @@ -65,6 +66,73 @@ cx_oracle.OracleDialect_cx_oracle(dbapi=Mock()) +class DialectWBackendTest(fixtures.TestBase): + __backend__ = True + __only_on__ = "oracle" + + def test_hypothetical_not_implemented_isolation_level(self): + engine = engines.testing_engine() + + def get_isolation_level(connection): + raise NotImplementedError + + with mock.patch.object( + engine.dialect, "get_isolation_level", get_isolation_level + ): + conn = engine.connect() + + # for NotImplementedError we get back None. But the + # cx_Oracle dialect does not raise this. + eq_(conn.dialect.default_isolation_level, None) + + dbapi_conn = conn.connection.connection + + eq_( + testing.db.dialect.get_isolation_level(dbapi_conn), + "READ COMMITTED", + ) + + def test_graceful_failure_isolation_level_not_available(self): + engine = engines.testing_engine() + + def get_isolation_level(connection): + raise exc.DBAPIError( + "get isolation level", + {}, + engine.dialect.dbapi.Error("isolation level failed"), + ) + + with mock.patch.object( + engine.dialect, "get_isolation_level", get_isolation_level + ): + conn = engine.connect() + eq_(conn.dialect.default_isolation_level, "READ COMMITTED") + + # test that we can use isolation level setting and that it + # reverts for "real" back to READ COMMITTED even though we + # can't read it + dbapi_conn = conn.connection.connection + + conn = conn.execution_options(isolation_level="SERIALIZABLE") + eq_( + testing.db.dialect.get_isolation_level(dbapi_conn), + "SERIALIZABLE", + ) + + conn.close() + eq_( + testing.db.dialect.get_isolation_level(dbapi_conn), + "READ COMMITTED", + ) + + with engine.connect() as conn: + assert_raises_message( + exc.DBAPIError, + r".*isolation level failed.*", + conn.get_isolation_level, + ) + + class EncodingErrorsTest(fixtures.TestBase): """mock test for encoding_errors. @@ -409,9 +477,15 @@ return server_version dialect = oracle.dialect( - dbapi=Mock(version="0.0.0", paramstyle="named"), **kw + dbapi=Mock( + version="0.0.0", + paramstyle="named", + ), + **kw ) + dialect._get_server_version_info = server_version_info + dialect.get_isolation_level = Mock() dialect._check_unicode_returns = Mock() dialect._check_unicode_description = Mock() dialect._get_default_schema_name = Mock() diff -Nru sqlalchemy-1.3.20+ds1/test/dialect/postgresql/test_compiler.py sqlalchemy-1.3.22+ds1/test/dialect/postgresql/test_compiler.py --- sqlalchemy-1.3.20+ds1/test/dialect/postgresql/test_compiler.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/test/dialect/postgresql/test_compiler.py 2020-12-18 21:05:18.000000000 +0000 @@ -780,13 +780,14 @@ where="room > 100", deferrable=True, initially="immediate", + ops={"room": "my_opclass"}, ) tbl.append_constraint(cons) self.assert_compile( schema.AddConstraint(cons), "ALTER TABLE testtbl ADD CONSTRAINT my_name " "EXCLUDE USING gist " - "(room WITH =, during WITH " + "(room my_opclass WITH =, during WITH " "&&) WHERE " "(room > 100) DEFERRABLE INITIALLY immediate", dialect=postgresql.dialect(), @@ -895,6 +896,24 @@ dialect=postgresql.dialect(), ) + def test_exclude_constraint_ops_many(self): + m = MetaData() + tbl = Table( + "testtbl", m, Column("room", String), Column("during", TSRANGE) + ) + cons = ExcludeConstraint( + ("room", "="), + ("during", "&&"), + ops={"room": "first_opsclass", "during": "second_opclass"}, + ) + tbl.append_constraint(cons) + self.assert_compile( + schema.AddConstraint(cons), + "ALTER TABLE testtbl ADD EXCLUDE USING gist " + "(room first_opsclass WITH =, during second_opclass WITH &&)", + dialect=postgresql.dialect(), + ) + def test_substring(self): self.assert_compile( func.substring("abc", 1, 2), @@ -1163,6 +1182,26 @@ "FOR UPDATE OF mytable_1, table2", ) + # ensure of=text() for of works + self.assert_compile( + table1.select(table1.c.myid == 7).with_for_update( + of=text("table1") + ), + "SELECT mytable.myid, mytable.name, mytable.description " + "FROM mytable WHERE mytable.myid = %(myid_1)s " + "FOR UPDATE OF table1", + ) + + # ensure literal_column of works + self.assert_compile( + table1.select(table1.c.myid == 7).with_for_update( + of=literal_column("table1") + ), + "SELECT mytable.myid, mytable.name, mytable.description " + "FROM mytable WHERE mytable.myid = %(myid_1)s " + "FOR UPDATE OF table1", + ) + def test_for_update_with_schema(self): m = MetaData() table1 = Table( diff -Nru sqlalchemy-1.3.20+ds1/test/dialect/test_sqlite.py sqlalchemy-1.3.22+ds1/test/dialect/test_sqlite.py --- sqlalchemy-1.3.20+ds1/test/dialect/test_sqlite.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/test/dialect/test_sqlite.py 2020-12-18 21:05:18.000000000 +0000 @@ -1113,6 +1113,16 @@ "(q, p) IN (VALUES (?, ?), (?, ?))", ) + def test_create_table_without_rowid(self): + m = MetaData() + tbl = Table( + "atable", m, Column("id", Integer), sqlite_with_rowid=False + ) + self.assert_compile( + schema.CreateTable(tbl), + "CREATE TABLE atable (id INTEGER) WITHOUT ROWID", + ) + class OnConflictDDLTest(fixtures.TestBase, AssertsCompiledSQL): diff -Nru sqlalchemy-1.3.20+ds1/test/ext/declarative/test_basic.py sqlalchemy-1.3.22+ds1/test/ext/declarative/test_basic.py --- sqlalchemy-1.3.20+ds1/test/ext/declarative/test_basic.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/test/ext/declarative/test_basic.py 2020-12-18 21:05:18.000000000 +0000 @@ -840,6 +840,59 @@ class_mapper(User).get_property("props").secondary is user_to_prop ) + def test_string_dependency_resolution_table_over_class(self): + # test for second half of #5774 + class User(Base, fixtures.ComparableEntity): + + __tablename__ = "users" + id = Column(Integer, primary_key=True) + name = Column(String(50)) + props = relationship( + "Prop", + secondary="Secondary", + backref="users", + ) + + class Prop(Base, fixtures.ComparableEntity): + + __tablename__ = "props" + id = Column(Integer, primary_key=True) + name = Column(String(50)) + + # class name and table name match + class Secondary(Base): + __tablename__ = "Secondary" + user_id = Column(Integer, ForeignKey("users.id"), primary_key=True) + prop_id = Column(Integer, ForeignKey("props.id"), primary_key=True) + + configure_mappers() + assert ( + class_mapper(User).get_property("props").secondary + is Secondary.__table__ + ) + + def test_string_dependency_resolution_class_over_table(self): + # test for second half of #5774 + class User(Base, fixtures.ComparableEntity): + + __tablename__ = "users" + id = Column(Integer, primary_key=True) + name = Column(String(50)) + secondary = relationship( + "Secondary", + ) + + # class name and table name match + class Secondary(Base): + __tablename__ = "Secondary" + user_id = Column(Integer, ForeignKey("users.id"), primary_key=True) + + configure_mappers() + assert ( + class_mapper(User).get_property("secondary").mapper + is Secondary.__mapper__ + ) + def test_string_dependency_resolution_schemas(self): Base = decl.declarative_base() diff -Nru sqlalchemy-1.3.20+ds1/test/orm/test_evaluator.py sqlalchemy-1.3.22+ds1/test/orm/test_evaluator.py --- sqlalchemy-1.3.20+ds1/test/orm/test_evaluator.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/test/orm/test_evaluator.py 2020-12-18 21:05:18.000000000 +0000 @@ -9,9 +9,11 @@ from sqlalchemy import or_ from sqlalchemy import String from sqlalchemy.orm import evaluator +from sqlalchemy.orm import exc as orm_exc from sqlalchemy.orm import mapper from sqlalchemy.orm import relationship from sqlalchemy.orm import Session +from sqlalchemy.testing import assert_raises from sqlalchemy.testing import assert_raises_message from sqlalchemy.testing import expect_warnings from sqlalchemy.testing import fixtures @@ -224,10 +226,10 @@ name = Column(String(50), primary_key=True) parent = relationship(Parent) - def test_delete(self): + def test_delete_not_expired(self): Parent, Child = self.classes("Parent", "Child") - session = Session() + session = Session(expire_on_commit=False) p = Parent(id=1) session.add(p) @@ -240,3 +242,24 @@ session.query(Child).filter(Child.parent == p).delete("evaluate") is_(inspect(c).deleted, True) + + def test_delete_expired(self): + Parent, Child = self.classes("Parent", "Child") + + session = Session() + + p = Parent(id=1) + session.add(p) + session.commit() + + c = Child(name="foo", parent=p) + session.add(c) + session.commit() + + session.query(Child).filter(Child.parent == p).delete("evaluate") + + # because it's expired + is_(inspect(c).deleted, False) + + # but it's gone + assert_raises(orm_exc.ObjectDeletedError, lambda: c.name) diff -Nru sqlalchemy-1.3.20+ds1/test/orm/test_events.py sqlalchemy-1.3.22+ds1/test/orm/test_events.py --- sqlalchemy-1.3.20+ds1/test/orm/test_events.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/test/orm/test_events.py 2020-12-18 21:05:18.000000000 +0000 @@ -716,6 +716,32 @@ event.listen(target, event_name, fn, restore_load_context=True) s.query(A).all() + @testing.combinations( + ("load", lambda instance, context: instance.unloaded), + ( + "refresh", + lambda instance, context, attrs: instance.unloaded, + ), + ) + def test_flag_resolves_existing_for_subclass(self, event_name, fn): + Base = declarative_base() + + event.listen( + Base, event_name, fn, propagate=True, restore_load_context=True + ) + + class A(Base): + __tablename__ = "a" + id = Column(Integer, primary_key=True) + unloaded = deferred(Column(String(50))) + + s = Session(testing.db) + + a1 = s.query(A).all()[0] + if event_name == "refresh": + s.refresh(a1) + s.close() + @_combinations def test_flag_resolves(self, target, event_name, fn): A = self.classes.A diff -Nru sqlalchemy-1.3.20+ds1/test/orm/test_inspect.py sqlalchemy-1.3.22+ds1/test/orm/test_inspect.py --- sqlalchemy-1.3.20+ds1/test/orm/test_inspect.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/test/orm/test_inspect.py 2020-12-18 21:05:18.000000000 +0000 @@ -434,14 +434,16 @@ def _random_names(self): import random + import keyword - return [ + names = { "".join( random.choice("abcdegfghijklmnopqrstuvwxyz") for i in range(random.randint(3, 15)) ) for j in range(random.randint(4, 12)) - ] + } + return list(names.difference(keyword.kwlist)) def _ordered_name_fixture(self, glbls, clsname, base, supercls): import random diff -Nru sqlalchemy-1.3.20+ds1/test/orm/test_relationships.py sqlalchemy-1.3.22+ds1/test/orm/test_relationships.py --- sqlalchemy-1.3.20+ds1/test/orm/test_relationships.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/test/orm/test_relationships.py 2020-12-18 21:05:18.000000000 +0000 @@ -4254,6 +4254,47 @@ sa.orm.configure_mappers() +class SecondaryArgTest(fixtures.TestBase): + def teardown(self): + clear_mappers() + + @testing.combinations((True,), (False,)) + def test_informative_message_on_cls_as_secondary(self, string): + Base = declarative_base() + + class C(Base): + __tablename__ = "c" + id = Column(Integer, primary_key=True) + a_id = Column(ForeignKey("a.id")) + b_id = Column(ForeignKey("b.id")) + + if string: + c_arg = "C" + else: + c_arg = C + + class A(Base): + __tablename__ = "a" + + id = Column(Integer, primary_key=True) + data = Column(String) + bs = relationship("B", secondary=c_arg) + + class B(Base): + __tablename__ = "b" + id = Column(Integer, primary_key=True) + + assert_raises_message( + exc.ArgumentError, + r"secondary argument passed to to " + r"relationship\(\) A.bs " + "must be a Table object or other FROM clause; can't send a " + "mapped class directly as rows in 'secondary' are persisted " + "independently of a class that is mapped to that same table.", + configure_mappers, + ) + + class SecondaryNestedJoinTest( fixtures.MappedTest, AssertsCompiledSQL, testing.AssertsExecutionResults ): diff -Nru sqlalchemy-1.3.20+ds1/test/orm/test_update_delete.py sqlalchemy-1.3.22+ds1/test/orm/test_update_delete.py --- sqlalchemy-1.3.20+ds1/test/orm/test_update_delete.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/test/orm/test_update_delete.py 2020-12-18 21:05:18.000000000 +0000 @@ -22,6 +22,7 @@ from sqlalchemy.testing import fixtures from sqlalchemy.testing import is_ from sqlalchemy.testing import mock +from sqlalchemy.testing.assertsql import CompiledSQL from sqlalchemy.testing.schema import Column from sqlalchemy.testing.schema import Table @@ -229,6 +230,184 @@ ) eq_(jill.ufoo, "moonbeam") + @testing.combinations( + (False, False), + (False, True), + (True, False), + (True, True), + ) + def test_evaluate_dont_refresh_expired_objects( + self, expire_jane_age, add_filter_criteria + ): + User = self.classes.User + + sess = Session() + + john, jack, jill, jane = sess.query(User).order_by(User.id).all() + + sess.expire(john) + sess.expire(jill) + + if expire_jane_age: + sess.expire(jane, ["name", "age"]) + else: + sess.expire(jane, ["name"]) + + with self.sql_execution_asserter() as asserter: + # using 1.x style for easier backport + if add_filter_criteria: + sess.query(User).filter(User.name != None).update( + {"age": User.age + 10}, synchronize_session="evaluate" + ) + else: + sess.query(User).update( + {"age": User.age + 10}, synchronize_session="evaluate" + ) + + if add_filter_criteria: + if expire_jane_age: + asserter.assert_( + # it has to unexpire jane.name, because jane is not fully + # expired and the critiera needs to look at this particular + # key + CompiledSQL( + "SELECT users.age_int AS users_age_int, " + "users.name AS users_name FROM users " + "WHERE users.id = :param_1", + [{"param_1": 4}], + ), + CompiledSQL( + "UPDATE users " + "SET age_int=(users.age_int + :age_int_1) " + "WHERE users.name IS NOT NULL", + [{"age_int_1": 10}], + ), + ) + else: + asserter.assert_( + # it has to unexpire jane.name, because jane is not fully + # expired and the critiera needs to look at this particular + # key + CompiledSQL( + "SELECT users.name AS users_name FROM users " + "WHERE users.id = :param_1", + [{"param_1": 4}], + ), + CompiledSQL( + "UPDATE users SET " + "age_int=(users.age_int + :age_int_1) " + "WHERE users.name IS NOT NULL", + [{"age_int_1": 10}], + ), + ) + else: + asserter.assert_( + CompiledSQL( + "UPDATE users SET age_int=(users.age_int + :age_int_1)", + [{"age_int_1": 10}], + ), + ) + + with self.sql_execution_asserter() as asserter: + eq_(john.age, 35) # needs refresh + eq_(jack.age, 57) # no SQL needed + eq_(jill.age, 39) # needs refresh + eq_(jane.age, 47) # needs refresh + + to_assert = [ + # refresh john + CompiledSQL( + "SELECT users.age_int AS users_age_int, " + "users.id AS users_id, users.name AS users_name FROM users " + "WHERE users.id = :param_1", + [{"param_1": 1}], + ), + # refresh jill + CompiledSQL( + "SELECT users.age_int AS users_age_int, " + "users.id AS users_id, users.name AS users_name FROM users " + "WHERE users.id = :param_1", + [{"param_1": 3}], + ), + ] + + if expire_jane_age and not add_filter_criteria: + to_assert.append( + # refresh jane + CompiledSQL( + "SELECT users.age_int AS users_age_int, " + "users.name AS users_name FROM users " + "WHERE users.id = :param_1", + [{"param_1": 4}], + ) + ) + asserter.assert_(*to_assert) + + def test_fetch_dont_refresh_expired_objects(self): + User = self.classes.User + + sess = Session() + + john, jack, jill, jane = sess.query(User).order_by(User.id).all() + + sess.expire(john) + sess.expire(jill) + sess.expire(jane, ["name"]) + + with self.sql_execution_asserter() as asserter: + # using 1.x style for easier backport + sess.query(User).filter(User.name != None).update( + {"age": User.age + 10}, synchronize_session="fetch" + ) + + asserter.assert_( + CompiledSQL( + "SELECT users.id AS users_id FROM users " + "WHERE users.name IS NOT NULL" + ), + CompiledSQL( + "UPDATE users SET age_int=(users.age_int + :age_int_1) " + "WHERE users.name IS NOT NULL", + [{"age_int_1": 10}], + ), + ) + + with self.sql_execution_asserter() as asserter: + eq_(john.age, 35) # needs refresh + eq_(jack.age, 57) # refreshes in 1.3 + eq_(jill.age, 39) # needs refresh + eq_(jane.age, 47) # refreshes in 1.3 + + asserter.assert_( + # refresh john + CompiledSQL( + "SELECT users.age_int AS users_age_int, " + "users.id AS users_id, users.name AS users_name FROM users " + "WHERE users.id = :param_1", + [{"param_1": 1}], + ), + # refresh jack.age in 1.3 only + CompiledSQL( + "SELECT users.age_int AS users_age_int FROM users " + "WHERE users.id = :param_1", + [{"param_1": 2}], + ), + # refresh jill + CompiledSQL( + "SELECT users.age_int AS users_age_int, " + "users.id AS users_id, users.name AS users_name FROM users " + "WHERE users.id = :param_1", + [{"param_1": 3}], + ), + # refresh jane, seems to be a full refresh in 1.3. + CompiledSQL( + "SELECT users.age_int AS users_age_int, " + "users.name AS users_name FROM users " + "WHERE users.id = :param_1", + [{"param_1": 4}], + ), + ) + def test_delete(self): User = self.classes.User diff -Nru sqlalchemy-1.3.20+ds1/test/requirements.py sqlalchemy-1.3.22+ds1/test/requirements.py --- sqlalchemy-1.3.20+ds1/test/requirements.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/test/requirements.py 2020-12-18 21:05:18.000000000 +0000 @@ -261,7 +261,7 @@ config, "sqlite" ) and config.db.dialect.dbapi.sqlite_version_info >= (3, 15, 0) - return only_on(["mysql", "postgresql", _sqlite_tuple_in]) + return only_on(["mysql", "postgresql", _sqlite_tuple_in, "oracle"]) @property def independent_cursors(self): @@ -350,6 +350,10 @@ return {"default": default, "supported": levels} @property + def tuple_in_w_empty(self): + return self.tuple_in + skip_if(["oracle"]) + + @property def autocommit(self): """target dialect supports 'AUTOCOMMIT' as an isolation_level""" diff -Nru sqlalchemy-1.3.20+ds1/test/sql/test_compiler.py sqlalchemy-1.3.22+ds1/test/sql/test_compiler.py --- sqlalchemy-1.3.20+ds1/test/sql/test_compiler.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/test/sql/test_compiler.py 2020-12-18 21:05:18.000000000 +0000 @@ -74,6 +74,7 @@ from sqlalchemy.sql import label from sqlalchemy.sql import table from sqlalchemy.sql.expression import _literal_as_text +from sqlalchemy.sql.expression import ClauseElement from sqlalchemy.sql.expression import ClauseList from sqlalchemy.sql.expression import HasPrefixes from sqlalchemy.testing import assert_raises @@ -83,6 +84,7 @@ from sqlalchemy.testing import eq_ignore_whitespace from sqlalchemy.testing import fixtures from sqlalchemy.testing import is_ +from sqlalchemy.testing import mock from sqlalchemy.util import u @@ -146,6 +148,53 @@ ) +class TestCompilerFixture(fixtures.TestBase, AssertsCompiledSQL): + def test_dont_access_statement(self): + def visit_foobar(self, element, **kw): + self.statement.table + + class Foobar(ClauseElement): + __visit_name__ = "foobar" + + with mock.patch.object( + testing.db.dialect.statement_compiler, + "visit_foobar", + visit_foobar, + create=True, + ): + assert_raises_message( + NotImplementedError, + "compiler accessed .statement; use " + "compiler.current_executable", + self.assert_compile, + Foobar(), + "", + ) + + def test_no_stack(self): + def visit_foobar(self, element, **kw): + self.current_executable.table + + class Foobar(ClauseElement): + __visit_name__ = "foobar" + + with mock.patch.object( + testing.db.dialect.statement_compiler, + "visit_foobar", + visit_foobar, + create=True, + ): + compiler = testing.db.dialect.statement_compiler( + testing.db.dialect, None + ) + assert_raises_message( + IndexError, + "Compiler does not have a stack entry", + compiler.process, + Foobar(), + ) + + class SelectTest(fixtures.TestBase, AssertsCompiledSQL): __dialect__ = "default" diff -Nru sqlalchemy-1.3.20+ds1/test/sql/test_returning.py sqlalchemy-1.3.22+ds1/test/sql/test_returning.py --- sqlalchemy-1.3.20+ds1/test/sql/test_returning.py 2020-10-12 22:29:58.000000000 +0000 +++ sqlalchemy-1.3.22+ds1/test/sql/test_returning.py 2020-12-18 21:05:18.000000000 +0000 @@ -1,15 +1,19 @@ import itertools from sqlalchemy import Boolean +from sqlalchemy import delete from sqlalchemy import exc as sa_exc from sqlalchemy import func +from sqlalchemy import insert from sqlalchemy import Integer from sqlalchemy import MetaData from sqlalchemy import select from sqlalchemy import Sequence from sqlalchemy import String from sqlalchemy import testing +from sqlalchemy import update from sqlalchemy.testing import assert_raises_message +from sqlalchemy.testing import AssertsCompiledSQL from sqlalchemy.testing import AssertsExecutionResults from sqlalchemy.testing import engines from sqlalchemy.testing import eq_ @@ -22,6 +26,76 @@ table = GoofyType = seq = None +class ReturnCombinationTests(fixtures.TestBase, AssertsCompiledSQL): + __dialect__ = "postgresql" + + @testing.fixture + def table_fixture(self): + return Table( + "foo", + MetaData(), + Column("id", Integer, primary_key=True), + Column("q", Integer, server_default="5"), + Column("x", Integer), + Column("y", Integer), + ) + + @testing.combinations( + ( + insert, + "INSERT INTO foo (id, q, x, y) " + "VALUES (%(id)s, %(q)s, %(x)s, %(y)s)", + ), + (update, "UPDATE foo SET id=%(id)s, q=%(q)s, x=%(x)s, y=%(y)s"), + (delete, "DELETE FROM foo"), + argnames="dml_fn, sql_frag", + id_="na", + ) + def test_return_combinations(self, table_fixture, dml_fn, sql_frag): + t = table_fixture + stmt = dml_fn(t) + + stmt = stmt.returning(t.c.x) + + with testing.expect_warnings( + r"The returning\(\) method does not currently " + "support multiple additive calls." + ): + stmt = stmt.returning(t.c.y) + + self.assert_compile( + stmt, + "%s RETURNING foo.y" % (sql_frag), + ) + + def test_return_no_return_defaults(self, table_fixture): + t = table_fixture + + stmt = t.insert() + + stmt = stmt.returning(t.c.x) + + assert_raises_message( + sa_exc.InvalidRequestError, + "RETURNING is already configured on this statement", + stmt.return_defaults, + ) + + def test_return_defaults_no_returning(self, table_fixture): + t = table_fixture + + stmt = t.insert() + + stmt = stmt.return_defaults() + + assert_raises_message( + sa_exc.InvalidRequestError, + r"return_defaults\(\) is already configured on this statement", + stmt.returning, + t.c.x, + ) + + class ReturningTest(fixtures.TestBase, AssertsExecutionResults): __requires__ = ("returning",) __backend__ = True