Sqlalchemy

:released: May 27, 2019

.. change::
:tags: feature, mssql
:tickets: 4657

Added support for SQL Server filtered indexes, via the ``mssql_where``
parameter which works similarly to that of the ``postgresql_where`` index
function in the PostgreSQL dialect.

.. seealso::

:ref:`mssql_index_where`

.. change::
:tags: bug, misc
:tickets: 4625

Removed errant "sqla_nose.py" symbol from MANIFEST.in which created an
undesirable warning message.

.. change::
:tags: bug, sql
:tickets: 4653

Fixed that the :class:`.GenericFunction` class was inadvertently
registering itself as one of the named functions. Pull request courtesy
Adrien Berchet.

.. change::
:tags: bug, engine, postgresql
:tickets: 4663

Moved the "rollback" which occurs during dialect initialization so that it
occurs after additional dialect-specific initialize steps, in particular
those of the psycopg2 dialect which would inadvertently leave transactional
state on the first new connection, which could interfere with some
psycopg2-specific APIs which require that no transaction is started. Pull
request courtesy Matthew Wilkes.


.. change::
:tags: bug, orm
:tickets: 4695

Fixed issue where the :paramref:`.AttributeEvents.active_history` flag
would not be set for an event listener that propagated to a subclass via the
:paramref:`.AttributeEvents.propagate` flag. This bug has been present
for the full span of the :class:`.AttributeEvents` system.


.. change::
:tags: bug, orm
:tickets: 4690

Fixed regression where new association proxy system was still not proxying
hybrid attributes when they made use of the ``hybrid_property.expression``
decorator to return an alternate SQL expression, or when the hybrid
returned an arbitrary :class:`.PropComparator`, at the expression level.
This involved further generalization of the heuristics used to detect the
type of object being proxied at the level of :class:`.QueryableAttribute`,
to better detect if the descriptor ultimately serves mapped classes or
column expressions.

.. change::
:tags: bug, orm
:tickets: 4686

Applied the mapper "configure mutex" against the declarative class mapping
process, to guard against the race which can occur if mappers are used
while dynamic module import schemes are still in the process of configuring
mappers for related classes. This does not guard against all possible race
conditions, such as if the concurrent import has not yet encountered the
dependent classes as of yet, however it guards against as much as possible
within the SQLAlchemy declarative process.

.. change::
:tags: bug, mssql
:tickets: 4680

Added error code 20047 to "is_disconnect" for pymssql. Pull request
courtesy Jon Schuff.


.. change::
:tags: bug, postgresql, orm
:tickets: 4661

Fixed an issue where the "number of rows matched" warning would emit even if
the dialect reported "supports_sane_multi_rowcount=False", as is the case
for psycogp2 with ``use_batch_mode=True`` and others.


.. change::
:tags: bug, sql
:tickets: 4618

Fixed issue where double negation of a boolean column wouldn't reset
the "NOT" operator.

.. change::
:tags: mysql, bug
:tickets: 4650

Added support for DROP CHECK constraint which is required by MySQL 8.0.16
to drop a CHECK constraint; MariaDB supports plain DROP CONSTRAINT. The
logic distinguishes between the two syntaxes by checking the server version
string for MariaDB presence. Alembic migrations has already worked
around this issue by implementing its own DROP for MySQL / MariaDB CHECK
constraints, however this change implements it straight in Core so that its
available for general use. Pull request courtesy Hannes Hansen.

.. change::
:tags: bug, orm
:tickets: 4647

A warning is now emitted for the case where a transient object is being
merged into the session with :meth:`.Session.merge` when that object is
already transient in the :class:`.Session`. This warns for the case where
the object would normally be double-inserted.


.. change::
:tags: bug, orm
:tickets: 4676

Fixed regression in new relationship m2o comparison logic first introduced
at :ref:`change_4359` when comparing to an attribute that is persisted as
NULL and is in an un-fetched state in the mapped instance. Since the
attribute has no explicit default, it needs to default to NULL when
accessed in a persistent setting.


.. change::
:tags: bug, sql
:tickets: 4569

The :class:`.GenericFunction` namespace is being migrated so that function
names are looked up in a case-insensitive manner, as SQL functions do not
collide on case sensitive differences nor is this something which would
occur with user-defined functions or stored procedures. Lookups for
functions declared with :class:`.GenericFunction` now use a case
insensitive scheme, however a deprecation case is supported which allows
two or more :class:`.GenericFunction` objects with the same name of
different cases to exist, which will cause case sensitive lookups to occur
for that particular name, while emitting a warning at function registration
time. Thanks to Adrien Berchet for a lot of work on this complicated
feature.


.. changelog::

:released: April 15, 2019

.. change::
:tags: bug, postgresql
:tickets: 4601

Fixed regression from release 1.3.2 caused by :ticket:`4562` where a URL
that contained only a query string and no hostname, such as for the
purposes of specifying a service file with connection information, would no
longer be propagated to psycopg2 properly. The change in :ticket:`4562`
has been adjusted to further suit psycopg2's exact requirements, which is
that if there are any connection parameters whatsoever, the "dsn" parameter
is no longer required, so in this case the query string parameters are
passed alone.

.. change::
:tags: bug, pool
:tickets: 4585

Fixed behavioral regression as a result of deprecating the "use_threadlocal"
flag for :class:`_pool.Pool`, where the :class:`.SingletonThreadPool` no longer
makes use of this option which causes the "rollback on return" logic to take
place when the same :class:`_engine.Engine` is used multiple times in the context
of a transaction to connect or implicitly execute, thereby cancelling the
transaction. While this is not the recommended way to work with engines
and connections, it is nonetheless a confusing behavioral change as when
using :class:`.SingletonThreadPool`, the transaction should stay open
regardless of what else is done with the same engine in the same thread.
The ``use_threadlocal`` flag remains deprecated however the
:class:`.SingletonThreadPool` now implements its own version of the same
logic.


.. change::
:tags: bug, orm
:tickets: 4584

Fixed 1.3 regression in new "ambiguous FROMs" query logic introduced in
:ref:`change_4365` where a :class:`_query.Query` that explicitly places an entity
in the FROM clause with :meth:`_query.Query.select_from` and also joins to it
using :meth:`_query.Query.join` would later cause an "ambiguous FROM" error if
that entity were used in additional joins, as the entity appears twice in
the "from" list of the :class:`_query.Query`. The fix resolves this ambiguity by
folding the standalone entity into the join that it's already a part of in
the same way that ultimately happens when the SELECT statement is rendered.

.. change::
:tags: bug, ext
:tickets: 4603

Fixed bug where using ``copy.copy()`` or ``copy.deepcopy()`` on
:class:`.MutableList` would cause the items within the list to be
duplicated, due to an inconsistency in how Python pickle and copy both make
use of ``__getstate__()`` and ``__setstate__()`` regarding lists. In order
to resolve, a ``__reduce_ex__`` method had to be added to
:class:`.MutableList`. In order to maintain backwards compatibility with
existing pickles based on ``__getstate__()``, the ``__setstate__()`` method
remains as well; the test suite asserts that pickles made against the old
version of the class can still be deserialized by the pickle module.

.. change::
:tags: bug, orm
:tickets: 4606

Adjusted the :meth:`_query.Query.filter_by` method to not call :func:`.and()`
internally against multiple criteria, instead passing it off to
:meth:`_query.Query.filter` as a series of criteria, instead of a single criteria.
This allows :meth:`_query.Query.filter_by` to defer to :meth:`_query.Query.filter`'s
treatment of variable numbers of clauses, including the case where the list
is empty. In this case, the :class:`_query.Query` object will not have a
``.whereclause``, which allows subsequent "no whereclause" methods like
:meth:`_query.Query.select_from` to behave consistently.

.. change::
:tags: bug, mssql
:tickets: 4587

Fixed issue in SQL Server dialect where if a bound parameter were present in
an ORDER BY expression that would ultimately not be rendered in the SQL
Server version of the statement, the parameters would still be part of the
execution parameters, leading to DBAPI-level errors. Pull request courtesy
Matt Lewellyn.

.. changelog::

:released: April 2, 2019

.. change::
:tags: bug, documentation, sql
:tickets: 4580

Thanks to :ref:`change_3981`, we no longer need to rely on recipes that
subclass dialect-specific types directly, :class:`.TypeDecorator` can now
handle all cases. Additionally, the above change made it slightly less
likely that a direct subclass of a base SQLAlchemy type would work as
expected, which could be misleading. Documentation has been updated to use
:class:`.TypeDecorator` for these examples including the PostgreSQL
"ArrayOfEnum" example datatype and direct support for the "subclass a type
directly" has been removed.

.. change::
:tags: bug, postgresql
:tickets: 4550

Modified the :paramref:`.Select.with_for_update.of` parameter so that if a
join or other composed selectable is passed, the individual :class:`_schema.Table`
objects will be filtered from it, allowing one to pass a join() object to
the parameter, as occurs normally when using joined table inheritance with
the ORM. Pull request courtesy Raymond Lu.


.. change::
:tags: feature, postgresql
:tickets: 4562

Added support for parameter-less connection URLs for the psycopg2 dialect,
meaning, the URL can be passed to :func:`_sa.create_engine` as
``"postgresql+psycopg2://"`` with no additional arguments to indicate an
empty DSN passed to libpq, which indicates to connect to "localhost" with
no username, password, or database given. Pull request courtesy Julian
Mehnle.

.. change::
:tags: bug, orm, ext
:tickets: 4574, 4573

Restored instance-level support for plain Python descriptors, e.g.
``property`` objects, in conjunction with association proxies, in that if
the proxied object is not within ORM scope at all, it gets classified as
"ambiguous" but is proxed directly. For class level access, a basic class
level``__get__()`` now returns the
:class:`.AmbiguousAssociationProxyInstance` directly, rather than raising
its exception, which is the closest approximation to the previous behavior
that returned the :class:`.AssociationProxy` itself that's possible. Also
improved the stringification of these objects to be more descriptive of
current state.

.. change::
:tags: bug, orm
:tickets: 4537

Fixed bug where use of :func:`.with_polymorphic` or other aliased construct
would not properly adapt when the aliased target were used as the
:meth:`_expression.Select.correlate_except` target of a subquery used inside of a
:func:`.column_property`. This required a fix to the clause adaption
mechanics to properly handle a selectable that shows up in the "correlate
except" list, in a similar manner as which occurs for selectables that show
up in the "correlate" list. This is ultimately a fairly fundamental bug
that has lasted for a long time but it is hard to come across it.


.. change::
:tags: bug, orm
:tickets: 4566

Fixed regression where a new error message that was supposed to raise when
attempting to link a relationship option to an AliasedClass without using
:meth:`.PropComparator.of_type` would instead raise an ``AttributeError``.
Note that in 1.3, it is no longer valid to create an option path from a
plain mapper relationship to an :class:`.AliasedClass` without using
:meth:`.PropComparator.of_type`.

.. changelog::

:released: March 9, 2019

.. change::
:tags: bug, mssql
:tickets: 4525

Fixed regression in SQL Server reflection due to :ticket:`4393` where the
removal of open-ended ``**kw`` from the :class:`.Float` datatype caused
reflection of this type to fail due to a "scale" argument being passed.

.. change::
:tags: bug, orm, ext
:tickets: 4522

Fixed regression where an association proxy linked to a synonym would no
longer work, both at instance level and at class level.

.. changelog::

:released: March 4, 2019

.. change::
:tags: feature, schema
:tickets: 4517

Added new parameters :paramref:`_schema.Table.resolve_fks` and
:paramref:`.MetaData.reflect.resolve_fks` which when set to False will
disable the automatic reflection of related tables encountered in
:class:`_schema.ForeignKey` objects, which can both reduce SQL overhead for omitted
tables as well as avoid tables that can't be reflected for database-specific
reasons. Two :class:`_schema.Table` objects present in the same :class:`_schema.MetaData`
collection can still refer to each other even if the reflection of the two
tables occurred separately.


.. change::
:tags: feature, orm
:tickets: 4316

The :meth:`_query.Query.get` method can now accept a dictionary of attribute keys
and values as a means of indicating the primary key value to load; is
particularly useful for composite primary keys. Pull request courtesy
Sanjana S.

.. change::
:tags: feature, orm
:tickets: 3133

A SQL expression can now be assigned to a primary key attribute for an ORM
flush in the same manner as ordinary attributes as described in
:ref:`flush_embedded_sql_expressions` where the expression will be evaluated
and then returned to the ORM using RETURNING, or in the case of pysqlite,
works using the cursor.lastrowid attribute.Requires either a database that
supports RETURNING (e.g. Postgresql, Oracle, SQL Server) or pysqlite.

.. change::
:tags: bug, sql
:tickets: 4509

The :class:`_expression.Alias` class and related subclasses :class:`_expression.CTE`,
:class:`_expression.Lateral` and :class:`_expression.TableSample` have been reworked so that it is
not possible for a user to construct the objects directly. These constructs
require that the standalone construction function or selectable-bound method
be used to instantiate new objects.


.. change::
:tags: feature, engine
:tickets: 4500

Revised the formatting for :class:`.StatementError` when stringified. Each
error detail is broken up over multiple newlines instead of spaced out on a
single line. Additionally, the SQL representation now stringifies the SQL
statement rather than using ``repr()``, so that newlines are rendered as is.
Pull request courtesy Nate Clark.

.. seealso::

:ref:`change_4500`

.. changelog::

:released: March 4, 2019
:released: February 8, 2019

.. change::
:tags: bug, ext
:tickets: 2642

Implemented a more comprehensive assignment operation (e.g. "bulk replace")
when using association proxy with sets or dictionaries. Fixes the problem
of redundant proxy objects being created to replace the old ones, which
leads to excessive events and SQL and in the case of unique constraints
will cause the flush to fail.

.. seealso::

:ref:`change_2642`

.. change::
:tags: bug, postgresql
:tickets: 4473

Fixed issue where using an uppercase name for an index type (e.g. GIST,
BTREE, etc. ) or an EXCLUDE constraint would treat it as an identifier to
be quoted, rather than rendering it as is. The new behavior converts these
types to lowercase and ensures they contain only valid SQL characters.

.. change::
:tags: bug, orm
:tickets: 4469

Improved the behavior of :func:`_orm.with_polymorphic` in conjunction with
loader options, in particular wildcard operations as well as
:func:`_orm.load_only`. The polymorphic object will be more accurately
targeted so that column-level options on the entity will correctly take
effect.The issue is a continuation of the same kinds of things fixed in
:ticket:`4468`.


.. change::
:tags: bug, sql
:tickets: 4481

Fully removed the behavior of strings passed directly as components of a
:func:`_expression.select` or :class:`_query.Query` object being coerced to :func:`_expression.text`
constructs automatically; the warning that has been emitted is now an
ArgumentError or in the case of order_by() / group_by() a CompileError.
This has emitted a warning since version 1.0 however its presence continues
to create concerns for the potential of mis-use of this behavior.

Note that public CVEs have been posted for order_by() / group_by() which
are resolved by this commit: CVE-2019-7164 CVE-2019-7548


.. seealso::

:ref:`change_4481`

.. change::
:tags: bug, sql
:tickets: 4467

Quoting is applied to :class:`.Function` names, those which are usually but
not necessarily generated from the :attr:`_expression.func` construct, at compile
time if they contain illegal characters, such as spaces or punctuation. The
names are as before treated as case insensitive however, meaning if the
names contain uppercase or mixed case characters, that alone does not
trigger quoting. The case insensitivity is currently maintained for
backwards compatibility.


.. change::
:tags: bug, sql
:tickets: 4481

Added "SQL phrase validation" to key DDL phrases that are accepted as plain
strings, including :paramref:`_schema.ForeignKeyConstraint.on_delete`,
:paramref:`_schema.ForeignKeyConstraint.on_update`,
:paramref:`.ExcludeConstraint.using`,
:paramref:`_schema.ForeignKeyConstraint.initially`, for areas where a series of SQL
keywords only are expected.Any non-space characters that suggest the phrase
would need to be quoted will raise a :class:`.CompileError`. This change
is related to the series of changes committed as part of :ticket:`4481`.

.. change::
:tags: bug, orm, declarative
:tickets: 4470

Added some helper exceptions that invoke when a mapping based on
:class:`.AbstractConcreteBase`, :class:`.DeferredReflection`, or
:class:`.AutoMap` is used before the mapping is ready to be used, which
contain descriptive information on the class, rather than falling through
into other failure modes that are less informative.


.. change::
:tags: change, tests
:tickets: 4460

The test system has removed support for Nose, which is unmaintained for
several years and is producing warnings under Python 3. The test suite is
currently standardized on Pytest. Pull request courtesy Parth Shandilya.

.. changelog::