This package includes a relatively small number of transitional elements

to allow "2.0 mode" to take place within SQLAlchemy 1.4. The primary

objects provided here are :class:`_future.Engine` and :class:`_future.Connection`,

which are both subclasses of the existing :class:`_engine.Engine` and

:class:`_engine.Connection` objects with essentially a smaller set of

methods and the removal of "autocommit".

Within the 1.4 series, the "2.0" style of engines and connections is enabled

by passing the :paramref:`_sa.create_engine.future` flag to

:func:`_sa.create_engine`::

Similarly, with the ORM, to enable "future" behavior in the ORM :class:`.Session`,

pass the :paramref:`_orm.Session.future` parameter either to the

:class:`.Session` constructor directly, or via the :class:`_orm.sessionmaker`

class::

.. autoclass:: ReturnsRows

:members:

:inherited-members: ClauseElement

:exclude-members: memoized_attribute, memoized_instancemethod, append_correlation, append_column, append_prefix, append_whereclause, append_having, append_from, append_order_by, append_group_by

:inherited-members: ClauseElement

Commonly Used Operators

Here's a rundown of some of the most common operators used in both the

Core expression language as well as in the ORM. Here we see expressions

that are most commonly present when using the :meth:`_sql.Select.where` method,

but can be used in other scenarios as well.

A listing of all the column-level operations common to all column-like

objects is at :class:`.ColumnOperators`.

* :meth:`equals <.ColumnOperators.__eq__>`::

* :meth:`not equals <.ColumnOperators.__ne__>`::

* :meth:`LIKE <.ColumnOperators.like>`::

* :meth:`ILIKE <.ColumnOperators.ilike>` (case-insensitive LIKE)::

* :meth:`IN <.ColumnOperators.in_>`::

* :meth:`NOT IN <.ColumnOperators.notin_>`::

* :meth:`IS NULL <.ColumnOperators.is_>`::

* :meth:`IS NOT NULL <.ColumnOperators.isnot>`::

* :func:`AND <.sql.expression.and_>`::

* :func:`OR <.sql.expression.or_>`::

* :meth:`MATCH <.ColumnOperators.match>`::

1.x style

2.0 style

1.x-style

2.0-style

These terms are new in SQLAlchemy 1.4 and refer to the SQLAlchemy 1.4->

2.0 transition plan, described at :ref:`migration_20_toplevel`. The

term "1.x style" refers to an API used in the way it's been documented

throughout the 1.x series of SQLAlhcemy and earlier (e.g. 1.3, 1.2, etc)

and the term "2.0 style" refers to the way an API will look in version

2.0. Version 1.4 implements nearly all of 2.0's API in so-called

"transition mode".

.. seealso::

:ref:`migration_20_toplevel`

plugin

plugin-specific

"plugin-specific" generally indicates a function or method in

SQLAlchemy Core which will behave differently when used in an ORM

context.

SQLAlchemy allows Core consrtucts such as :class:`_sql.Select` objects

to participate in a "plugin" system, which can inject additional

behaviors and features into the object that are not present by default.

Specifically, the primary "plugin" is the "orm" plugin, which is

at the base of the system that the SQLAlchemy ORM makes use of

Core constructs in order to compose and execute SQL queries that

return ORM results.

.. seealso::

:ref:`migration_20_unify_select`

* **SQLAlchemy 2.0 Compatibility:** :ref:`migration_20_toplevel`

A key feature to enable management of a large collection is the so-called

"dynamic" relationship. This is an optional form of

:func:`_orm.relationship` which returns a

:class:`_orm.AppenderQuery` object in place of a collection

when accessed. Filtering criterion may be applied as well as limits and

offsets, either explicitly or via array slices::

:meth:`_orm.AppenderQuery.append` and :meth:`_orm.AppenderQuery.remove` methods::

.. autoclass:: sqlalchemy.orm.AppenderQuery

:members:

.. _session_deleting_from_collections:

Notes on Delete - Deleting Objects Referenced from Collections and Scalar Relationships

The ORM in general never modifies the contents of a collection or scalar

relationship during the flush process. This means, if your class has a

:func:`_orm.relationship` that refers to a collection of objects, or a reference

to a single object such as many-to-one, the contents of this attribute will

not be modified when the flush process occurs. Instead, it is expected

that the :class:`.Session` would eventually be expired, either through the expire-on-commit behavior of

:meth:`.Session.commit` or through explicit use of :meth:`.Session.expire`.

At that point, any referenced object or collection associated with that

:class:`.Session` will be cleared and will re-load itself upon next access.

A common confusion that arises regarding this behavior involves the use of the

:meth:`~.Session.delete` method. When :meth:`.Session.delete` is invoked upon

an object and the :class:`.Session` is flushed, the row is deleted from the

database. Rows that refer to the target row via foreign key, assuming they

are tracked using a :func:`_orm.relationship` between the two mapped object types,

will also see their foreign key attributes UPDATED to null, or if delete

cascade is set up, the related rows will be deleted as well. However, even

though rows related to the deleted object might be themselves modified as well,

**no changes occur to relationship-bound collections or object references on

the objects** involved in the operation within the scope of the flush

itself. This means if the object was a

member of a related collection, it will still be present on the Python side

until that collection is expired. Similarly, if the object were

referenced via many-to-one or one-to-one from another object, that reference

will remain present on that object until the object is expired as well.

Below, we illustrate that after an ``Address`` object is marked

for deletion, it's still present in the collection associated with the

parent ``User``, even after a flush::

When the above session is committed, all attributes are expired. The next

access of ``user.addresses`` will re-load the collection, revealing the

desired state::

There is a recipe for intercepting :meth:`.Session.delete` and invoking this

expiration automatically; see `ExpireRelationshipOnFKChange <http://www.sqlalchemy.org/trac/wiki/UsageRecipes/ExpireRelationshipOnFKChange>`_ for this. However, the usual practice of

deleting items within collections is to forego the usage of

:meth:`~.Session.delete` directly, and instead use cascade behavior to

automatically invoke the deletion as a result of removing the object from the

parent collection. The ``delete-orphan`` cascade accomplishes this, as

illustrated in the example below::

Where above, upon removing the ``Address`` object from the ``User.addresses``

collection, the ``delete-orphan`` cascade has the effect of marking the ``Address``

object for deletion in the same way as passing it to :meth:`~.Session.delete`.

The ``delete-orphan`` cascade can also be applied to a many-to-one

or one-to-one relationship, so that when an object is de-associated from its

parent, it is also automatically marked for deletion. Using ``delete-orphan``

cascade on a many-to-one or one-to-one requires an additional flag

:paramref:`_orm.relationship.single_parent` which invokes an assertion

that this related object is not to shared with any other parent simultaneously::

Above, if a hypothetical ``Preference`` object is removed from a ``User``,

it will be deleted on flush::

.. seealso::

:ref:`unitofwork_cascades` for detail on cascades.

The declarative base and ORM mapping functions described at

:ref:`mapper_config_toplevel` are the primary configurational interface for the

ORM. Once mappings are configured, the primary usage interface for

persistence operations is the