Release: 1.4.0b1 beta release | Release Date: November 2, 2020

SQLAlchemy 1.4 Documentation

Asynchronous I/O (asyncio)

Support for Python asyncio. Support for Core and ORM usage is included, using asyncio-compatible dialects.

New in version 1.4.

The asyncio extension requires at least Python version 3.6.

Note

The asyncio should be regarded as alpha level for the 1.4 release of SQLAlchemy. API details are subject to change at any time.

See also

Asynchronous IO Support for Core and ORM - initial feature announcement

Asyncio Integration - example scripts illustrating working examples of Core and ORM use within the asyncio extension.

Synopsis - Core

For Core use, the create_async_engine() function creates an instance of AsyncEngine which then offers an async version of the traditional Engine API. The AsyncEngine delivers an AsyncConnection via its AsyncEngine.connect() and AsyncEngine.begin() methods which both deliver asynchronous context managers. The AsyncConnection can then invoke statements using either the AsyncConnection.execute() method to deliver a buffered Result, or the AsyncConnection.stream() method to deliver a streaming server-side AsyncResult:

import asyncio

from sqlalchemy.ext.asyncio import create_async_engine

async def async_main():
    engine = create_async_engine(
        "postgresql+asyncpg://scott:tiger@localhost/test", echo=True,
    )

    async with engine.begin() as conn:
        await conn.run_sync(meta.drop_all)
        await conn.run_sync(meta.create_all)

        await conn.execute(
            t1.insert(), [{"name": "some name 1"}, {"name": "some name 2"}]
        )

    async with engine.connect() as conn:

        # select a Result, which will be delivered with buffered
        # results
        result = await conn.execute(select(t1).where(t1.c.name == "some name 1"))

        print(result.fetchall())


asyncio.run(async_main())

Above, the AsyncConnection.run_sync() method may be used to invoke special DDL functions such as MetaData.create_all() that don’t include an awaitable hook.

The AsyncConnection also features a “streaming” API via the AsyncConnection.stream() method that returns an AsyncResult object. This result object uses a server-side cursor and provides an async/await API, such as an async iterator:

async with engine.connect() as conn:
    async_result = await conn.stream(select(t1))

    async for row in async_result:
        print("row: %s" % (row, ))

Synopsis - ORM

Using 2.0 style querying, the AsyncSession class provides full ORM functionality. Within the default mode of use, special care must be taken to avoid lazy loading of ORM relationships and column attributes, as below where the selectinload() eager loading strategy is used to ensure the A.bs on each A object is loaded:

import asyncio

from sqlalchemy.ext.asyncio import create_async_engine
from sqlalchemy.ext.asyncio import AsyncSession

async def async_main():
    engine = create_async_engine(
        "postgresql+asyncpg://scott:tiger@localhost/test", echo=True,
    )
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.drop_all)
        await conn.run_sync(Base.metadata.create_all)

    async with AsyncSession(engine) as session:
        async with session.begin():
            session.add_all(
                [
                    A(bs=[B(), B()], data="a1"),
                    A(bs=[B()], data="a2"),
                    A(bs=[B(), B()], data="a3"),
                ]
            )

        stmt = select(A).options(selectinload(A.bs))

        result = await session.execute(stmt)

        for a1 in result.scalars():
            print(a1)
            for b1 in a1.bs:
                print(b1)

        result = await session.execute(select(A).order_by(A.id))

        a1 = result.scalars().first()

        a1.data = "new data"

        await session.commit()

asyncio.run(async_main())

Above, the selectinload() eager loader is employed in order to eagerly load the A.bs collection within the scope of the await session.execute() call. If the default loader strategy of “lazyload” were left in place, the access of the A.bs attribute would raise an asyncio exception. Using traditional asyncio, the application needs to avoid any points at which IO-on-attribute access may occur. This also includes that methods such as Session.expire() should be avoided in favor of AsyncSession.refresh(), and that appropriate loader options should be employed for deferred() columns as well as for relationship() constructs.

Adapting ORM Lazy loads to asyncio

Deep Alchemy

This approach is essentially exposing publicly the mechanism by which SQLAlchemy is able to provide the asyncio interface in the first place. While there is no technical issue with doing so, overall the approach can probably be considered “controversial” as it works against some of the central philosophies of the asyncio programming model, which is essentially that any programming statement that can potentially result in IO being invoked must have an await call, lest the program does not make it explicitly clear every line at which IO may occur. This approach does not change that general idea, except that it allows a series of synchronous IO instructions to be exempted from this rule within the scope of a function call, essentially bundled up into a single awaitable.

As an alternative means of integrating traditional SQLAlchemy “lazy loading” within an asyncio event loop, an optional method known as AsyncSession.run_sync() is provided which will run any Python function inside of a greenlet, where traditional synchronous programming concepts will be translated to use await when they reach the database driver. A hypothetical approach here is an asyncio-oriented application can package up database-related methods into functions that are invoked using AsyncSession.run_sync().

Altering the above example, if we didn’t use selectinload() for the A.bs collection, we could accomplish our treatment of these attribute accesses within a separate function:

import asyncio

from sqlalchemy.ext.asyncio import create_async_engine
from sqlalchemy.ext.asyncio import AsyncSession

def fetch_and_update_objects(session):
    """run traditional sync-style ORM code in a function that will be
    invoked within an awaitable.

    """

    # the session object here is a traditional ORM Session.
    # all features are available here including legacy Query use.

    stmt = select(A)

    result = session.execute(stmt)
    for a1 in result.scalars():
        print(a1)

        # lazy loads
        for b1 in a1.bs:
            print(b1)

    # legacy Query use
    a1 = session.query(A).order_by(A.id).first()

    a1.data = "new data"


async def async_main():
    engine = create_async_engine(
        "postgresql+asyncpg://scott:tiger@localhost/test", echo=True,
    )
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.drop_all)
        await conn.run_sync(Base.metadata.create_all)

    async with AsyncSession(engine) as session:
        async with session.begin():
            session.add_all(
                [
                    A(bs=[B(), B()], data="a1"),
                    A(bs=[B()], data="a2"),
                    A(bs=[B(), B()], data="a3"),
                ]
            )

        await session.run_sync(fetch_and_update_objects)

        await session.commit()

asyncio.run(async_main())

The above approach of running certain functions within a “sync” runner has some parallels to an application that runs a SQLAlchemy application on top of an event-based programming library such as gevent. The differences are as follows:

  1. unlike when using gevent, we can continue to use the standard Python asyncio event loop, or any custom event loop, without the need to integrate into the gevent event loop.

  2. There is no “monkeypatching” whatsoever. The above example makes use of a real asyncio driver and the underlying SQLAlchemy connection pool is also using the Python built-in asyncio.Queue for pooling connections.

  3. The program can freely switch between async/await code and contained functions that use sync code with virtually no performance penalty. There is no “thread executor” or any additional waiters or synchronization in use.

  4. The underlying network drivers are also using pure Python asyncio concepts, no third party networking libraries as gevent and eventlet provides are in use.

Engine API Documentation

Object Name Description

AsyncConnection

An asyncio proxy for a Connection.

AsyncEngine

An asyncio proxy for a Engine.

AsyncTransaction

An asyncio proxy for a Transaction.

create_async_engine(*arg, **kw)

Create a new async engine instance.

function sqlalchemy.ext.asyncio.create_async_engine(*arg, **kw)

Create a new async engine instance.

Arguments passed to create_async_engine() are mostly identical to those passed to the create_engine() function. The specified dialect must be an asyncio-compatible dialect such as asyncpg.

New in version 1.4.

class sqlalchemy.ext.asyncio.AsyncEngine(sync_engine: sqlalchemy.future.engine.Engine)

An asyncio proxy for a Engine.

AsyncEngine is acquired using the create_async_engine() function:

from sqlalchemy.ext.asyncio import create_async_engine
engine = create_async_engine("postgresql+asyncpg://user:pass@host/dbname")

New in version 1.4.

Class signature

class sqlalchemy.ext.asyncio.AsyncEngine (sqlalchemy.ext.asyncio.AsyncConnectable)

method sqlalchemy.ext.asyncio.AsyncEngine.begin()

Return a context manager which when entered will deliver an AsyncConnection with an AsyncTransaction established.

E.g.:

async with async_engine.begin() as conn:
    await conn.execute(
        text("insert into table (x, y, z) values (1, 2, 3)")
    )
    await conn.execute(text("my_special_procedure(5)"))
method sqlalchemy.ext.asyncio.AsyncEngine.clear_compiled_cache()

Clear the compiled cache associated with the dialect.

Proxied for the Engine class on behalf of the AsyncEngine class.

This applies only to the built-in cache that is established via the create_engine.query_cache_size parameter. It will not impact any dictionary caches that were passed via the Connection.execution_options.query_cache parameter.

New in version 1.4.

method sqlalchemy.ext.asyncio.AsyncEngine.connect()sqlalchemy.ext.asyncio.AsyncConnection

Return an AsyncConnection object.

The AsyncConnection will procure a database connection from the underlying connection pool when it is entered as an async context manager:

async with async_engine.connect() as conn:
    result = await conn.execute(select(user_table))

The AsyncConnection may also be started outside of a context manager by invoking its AsyncConnection.start() method.

async method sqlalchemy.ext.asyncio.AsyncEngine.dispose()

Dispose of the connection pool used by this AsyncEngine.

This will close all connection pool connections that are currently checked in. See the documentation for the underlying Engine.dispose() method for further notes.

See also

Engine.dispose()

method sqlalchemy.ext.asyncio.AsyncEngine.execution_options(**opt)

Return a new AsyncEngine that will provide AsyncConnection objects with the given execution options.

Proxied from Engine.execution_options(). See that method for details.

method sqlalchemy.ext.asyncio.AsyncEngine.get_execution_options()

Get the non-SQL options which will take effect during execution.

Proxied for the Engine class on behalf of the AsyncEngine class.

async method sqlalchemy.ext.asyncio.AsyncEngine.raw_connection() → Any

Return a “raw” DBAPI connection from the connection pool.

method sqlalchemy.ext.asyncio.AsyncEngine.update_execution_options(**opt)

Update the default execution_options dictionary of this Engine.

Proxied for the Engine class on behalf of the AsyncEngine class.

The given keys/values in **opt are added to the default execution options that will be used for all connections. The initial contents of this dictionary can be sent via the execution_options parameter to create_engine().

class sqlalchemy.ext.asyncio.AsyncConnection(async_engine: sqlalchemy.ext.asyncio.engine.AsyncEngine, sync_connection: Optional[sqlalchemy.future.engine.Connection] = None)

An asyncio proxy for a Connection.

AsyncConnection is acquired using the AsyncEngine.connect() method of AsyncEngine:

from sqlalchemy.ext.asyncio import create_async_engine
engine = create_async_engine("postgresql+asyncpg://user:pass@host/dbname")

async with engine.connect() as conn:
    result = await conn.execute(select(table))

New in version 1.4.

Class signature

class sqlalchemy.ext.asyncio.AsyncConnection (sqlalchemy.ext.asyncio.base.StartableContext, sqlalchemy.ext.asyncio.AsyncConnectable)

method sqlalchemy.ext.asyncio.AsyncConnection.begin()sqlalchemy.ext.asyncio.AsyncTransaction

Begin a transaction prior to autobegin occurring.

method sqlalchemy.ext.asyncio.AsyncConnection.begin_nested()sqlalchemy.ext.asyncio.AsyncTransaction

Begin a nested transaction and return a transaction handle.

async method sqlalchemy.ext.asyncio.AsyncConnection.close()

Close this AsyncConnection.

This has the effect of also rolling back the transaction if one is in place.

async method sqlalchemy.ext.asyncio.AsyncConnection.commit()

Commit the transaction that is currently in progress.

This method commits the current transaction if one has been started. If no transaction was started, the method has no effect, assuming the connection is in a non-invalidated state.

A transaction is begun on a Connection automatically whenever a statement is first executed, or when the Connection.begin() method is called.

attribute sqlalchemy.ext.asyncio.AsyncConnection.connection

Not implemented for async; call AsyncConnection.get_raw_connection().

async method sqlalchemy.ext.asyncio.AsyncConnection.exec_driver_sql(statement: sqlalchemy.sql.base.Executable, parameters: Optional[Mapping] = None, execution_options: Mapping = {})sqlalchemy.engine.Result

Executes a driver-level SQL string and return buffered Result.

async method sqlalchemy.ext.asyncio.AsyncConnection.execute(statement: sqlalchemy.sql.base.Executable, parameters: Optional[Mapping] = None, execution_options: Mapping = {})sqlalchemy.engine.Result

Executes a SQL statement construct and return a buffered Result.

Parameters
  • object

    The statement to be executed. This is always an object that is in both the ClauseElement and Executable hierarchies, including:

  • parameters – parameters which will be bound into the statement. This may be either a dictionary of parameter names to values, or a mutable sequence (e.g. a list) of dictionaries. When a list of dictionaries is passed, the underlying statement execution will make use of the DBAPI cursor.executemany() method. When a single dictionary is passed, the DBAPI cursor.execute() method will be used.

  • execution_options – optional dictionary of execution options, which will be associated with the statement execution. This dictionary can provide a subset of the options that are accepted by Connection.execution_options().

Returns

a Result object.

async method sqlalchemy.ext.asyncio.AsyncConnection.execution_options(**opt)

Set non-SQL options for the connection which take effect during execution.

This returns this AsyncConnection object with the new options added.

See Connection.execution_options() for full details on this method.

async method sqlalchemy.ext.asyncio.AsyncConnection.get_raw_connection()

Return the pooled DBAPI-level connection in use by this AsyncConnection.

This is typically the SQLAlchemy connection-pool proxied connection which then has an attribute .connection that refers to the actual DBAPI-level connection.

async method sqlalchemy.ext.asyncio.AsyncConnection.invalidate(exception=None)

Invalidate the underlying DBAPI connection associated with this Connection.

See the method Connection.invalidate() for full detail on this method.

async method sqlalchemy.ext.asyncio.AsyncConnection.rollback()

Roll back the transaction that is currently in progress.

This method rolls back the current transaction if one has been started. If no transaction was started, the method has no effect. If a transaction was started and the connection is in an invalidated state, the transaction is cleared using this method.

A transaction is begun on a Connection automatically whenever a statement is first executed, or when the Connection.begin() method is called.

async method sqlalchemy.ext.asyncio.AsyncConnection.run_sync(fn: Callable, *arg, **kw) → Any

Invoke the given sync callable passing self as the first argument.

This method maintains the asyncio event loop all the way through to the database connection by running the given callable in a specially instrumented greenlet.

E.g.:

with async_engine.begin() as conn:
    await conn.run_sync(metadata.create_all)
async method sqlalchemy.ext.asyncio.AsyncConnection.scalar(statement: sqlalchemy.sql.base.Executable, parameters: Optional[Mapping] = None, execution_options: Mapping = {}) → Any

Executes a SQL statement construct and returns a scalar object.

This method is shorthand for invoking the Result.scalar() method after invoking the Connection.execute() method. Parameters are equivalent.

Returns

a scalar Python value representing the first column of the first row returned.

async method sqlalchemy.ext.asyncio.AsyncConnection.start()

Start this AsyncConnection object’s context outside of using a Python with: block.

async method sqlalchemy.ext.asyncio.AsyncConnection.stream(statement: sqlalchemy.sql.base.Executable, parameters: Optional[Mapping] = None, execution_options: Mapping = {})sqlalchemy.ext.asyncio.AsyncResult

Execute a statement and return a streaming AsyncResult object.

class sqlalchemy.ext.asyncio.AsyncTransaction(connection: sqlalchemy.ext.asyncio.engine.AsyncConnection, nested: bool = False)

An asyncio proxy for a Transaction.

Class signature

class sqlalchemy.ext.asyncio.AsyncTransaction (sqlalchemy.ext.asyncio.base.StartableContext)

async method sqlalchemy.ext.asyncio.AsyncTransaction.close()

Close this Transaction.

If this transaction is the base transaction in a begin/commit nesting, the transaction will rollback(). Otherwise, the method returns.

This is used to cancel a Transaction without affecting the scope of an enclosing transaction.

async method sqlalchemy.ext.asyncio.AsyncTransaction.commit()

Commit this Transaction.

async method sqlalchemy.ext.asyncio.AsyncTransaction.rollback()

Roll back this Transaction.

async method sqlalchemy.ext.asyncio.AsyncTransaction.start()

Start this AsyncTransaction object’s context outside of using a Python with: block.

Result Set API Documentation

The AsyncResult object is an async-adapted version of the Result object. It is only returned when using the AsyncConnection.stream() or AsyncSession.stream() methods, which return a result object that is on top of an active database cursor.

Object Name Description

AsyncMappingResult

A wrapper for a AsyncResult that returns dictionary values rather than Row values.

AsyncResult

An asyncio wrapper around a Result object.

AsyncScalarResult

A wrapper for a AsyncResult that returns scalar values rather than Row values.

class sqlalchemy.ext.asyncio.AsyncResult(real_result)

An asyncio wrapper around a Result object.

The AsyncResult only applies to statement executions that use a server-side cursor. It is returned only from the AsyncConnection.stream() and AsyncSession.stream() methods.

New in version 1.4.

Class signature

class sqlalchemy.ext.asyncio.AsyncResult (sqlalchemy.engine.FilterResult)

async method sqlalchemy.ext.asyncio.AsyncResult.all()

Return all rows in a list.

Closes the result set after invocation. Subsequent invocations will return an empty list.

Returns

a list of Row objects.

method sqlalchemy.ext.asyncio.AsyncResult.columns(*col_expressions)

Establish the columns that should be returned in each row.

Refer to Result.columns() in the synchronous SQLAlchemy API for a complete behavioral description.

async method sqlalchemy.ext.asyncio.AsyncResult.fetchmany(size=None)

Fetch many rows.

When all rows are exhausted, returns an empty list.

This method is provided for backwards compatibility with SQLAlchemy 1.x.x.

To fetch rows in groups, use the AsyncResult.partitions() method.

Returns

a list of Row objects.

async method sqlalchemy.ext.asyncio.AsyncResult.fetchone()

Fetch one row.

When all rows are exhausted, returns None.

This method is provided for backwards compatibility with SQLAlchemy 1.x.x.

To fetch the first row of a result only, use the Result.first() method. To iterate through all rows, iterate the Result object directly.

Returns

a Row object if no filters are applied, or None if no rows remain.

async method sqlalchemy.ext.asyncio.AsyncResult.first()

Fetch the first row or None if no row is present.

Closes the result set and discards remaining rows.

Note

This method returns one row, e.g. tuple, by default. To return exactly one single scalar value, that is, the first column of the first row, use the AsyncResult.scalar() method, or combine AsyncResult.scalars() and AsyncResult.first().

Returns

a Row object, or None if no rows remain.

async method sqlalchemy.ext.asyncio.AsyncResult.freeze()

Return a callable object that will produce copies of this AsyncResult when invoked.

The callable object returned is an instance of FrozenResult.

This is used for result set caching. The method must be called on the result when it has been unconsumed, and calling the method will consume the result fully. When the FrozenResult is retrieved from a cache, it can be called any number of times where it will produce a new Result object each time against its stored set of rows.

See also

Re-Executing Statements - example usage within the ORM to implement a result-set cache.

method sqlalchemy.ext.asyncio.AsyncResult.keys()

Return the Result.keys() collection from the underlying Result.

method sqlalchemy.ext.asyncio.AsyncResult.mappings()

Apply a mappings filter to returned rows, returning an instance of AsyncMappingResult.

When this filter is applied, fetching rows will return RowMapping objects instead of Row objects.

Refer to Result.mappings() in the synchronous SQLAlchemy API for a complete behavioral description.

Returns

a new AsyncMappingResult filtering object referring to the underlying Result object.

method sqlalchemy.ext.asyncio.AsyncResult.merge(*others)

Merge this AsyncResult with other compatible result objects.

The object returned is an instance of MergedResult, which will be composed of iterators from the given result objects.

The new result will use the metadata from this result object. The subsequent result objects must be against an identical set of result / cursor metadata, otherwise the behavior is undefined.

async method sqlalchemy.ext.asyncio.AsyncResult.one()

Return exactly one row or raise an exception.

Raises NoResultFound if the result returns no rows, or MultipleResultsFound if multiple rows would be returned.

Note

This method returns one row, e.g. tuple, by default. To return exactly one single scalar value, that is, the first column of the first row, use the AsyncResult.scalar_one() method, or combine AsyncResult.scalars() and AsyncResult.one().

New in version 1.4.

Returns

The first Row.

Raises

MultipleResultsFound, NoResultFound

async method sqlalchemy.ext.asyncio.AsyncResult.one_or_none()

Return at most one result or raise an exception.

Returns None if the result has no rows. Raises MultipleResultsFound if multiple rows are returned.

New in version 1.4.

Returns

The first Row or None if no row is available.

Raises

MultipleResultsFound

method sqlalchemy.ext.asyncio.AsyncResult.partitions(size=None)

Iterate through sub-lists of rows of the size given.

An async iterator is returned:

async def scroll_results(connection):
    result = await connection.stream(select(users_table))

    async for partition in result.partitions(100):
        print("list of rows: %s" % partition)
async method sqlalchemy.ext.asyncio.AsyncResult.scalar()

Fetch the first column of the first row, and close the result set.

Returns None if there are no rows to fetch.

No validation is performed to test if additional rows remain.

After calling this method, the object is fully closed, e.g. the CursorResult.close() method will have been called.

Returns

a Python scalar value , or None if no rows remain.

async method sqlalchemy.ext.asyncio.AsyncResult.scalar_one()

Return exactly one scalar result or raise an exception.

This is equvalent to calling AsyncResult.scalars() and then AsyncResult.one().

async method sqlalchemy.ext.asyncio.AsyncResult.scalar_one_or_none()

Return exactly one or no scalar result.

This is equvalent to calling AsyncResult.scalars() and then AsyncResult.one_or_none().

method sqlalchemy.ext.asyncio.AsyncResult.scalars(index=0)

Return an AsyncScalarResult filtering object which will return single elements rather than Row objects.

Refer to Result.scalars() in the synchronous SQLAlchemy API for a complete behavioral description.

Parameters

index – integer or row key indicating the column to be fetched from each row, defaults to 0 indicating the first column.

Returns

a new AsyncScalarResult filtering object referring to this AsyncResult object.

method sqlalchemy.ext.asyncio.AsyncResult.unique(strategy=None)

Apply unique filtering to the objects returned by this AsyncResult.

Refer to Result.unique() in the synchronous SQLAlchemy API for a complete behavioral description.

class sqlalchemy.ext.asyncio.AsyncScalarResult(real_result, index)

A wrapper for a AsyncResult that returns scalar values rather than Row values.

The AsyncScalarResult object is acquired by calling the AsyncResult.scalars() method.

Refer to the ScalarResult object in the synchronous SQLAlchemy API for a complete behavioral description.

New in version 1.4.

Class signature

class sqlalchemy.ext.asyncio.AsyncScalarResult (sqlalchemy.engine.FilterResult)

async method sqlalchemy.ext.asyncio.AsyncScalarResult.all()

Return all scalar values in a list.

Equivalent to AsyncResult.all() except that scalar values, rather than Row objects, are returned.

async method sqlalchemy.ext.asyncio.AsyncScalarResult.fetchall()

A synonym for the AsyncScalarResult.all() method.

async method sqlalchemy.ext.asyncio.AsyncScalarResult.fetchmany(size=None)

Fetch many objects.

Equivalent to AsyncResult.fetchmany() except that scalar values, rather than Row objects, are returned.

async method sqlalchemy.ext.asyncio.AsyncScalarResult.first()

Fetch the first object or None if no object is present.

Equivalent to AsyncResult.first() except that scalar values, rather than Row objects, are returned.

async method sqlalchemy.ext.asyncio.AsyncScalarResult.one()

Return exactly one object or raise an exception.

Equivalent to AsyncResult.one() except that scalar values, rather than Row objects, are returned.

async method sqlalchemy.ext.asyncio.AsyncScalarResult.one_or_none()

Return at most one object or raise an exception.

Equivalent to AsyncResult.one_or_none() except that scalar values, rather than Row objects, are returned.

method sqlalchemy.ext.asyncio.AsyncScalarResult.partitions(size=None)

Iterate through sub-lists of elements of the size given.

Equivalent to AsyncResult.partitions() except that scalar values, rather than Row objects, are returned.

method sqlalchemy.ext.asyncio.AsyncScalarResult.unique(strategy=None)

Apply unique filtering to the objects returned by this AsyncScalarResult.

See AsyncResult.unique() for usage details.

class sqlalchemy.ext.asyncio.AsyncMappingResult(result)

A wrapper for a AsyncResult that returns dictionary values rather than Row values.

The AsyncMappingResult object is acquired by calling the AsyncResult.mappings() method.

Refer to the MappingResult object in the synchronous SQLAlchemy API for a complete behavioral description.

New in version 1.4.

Class signature

class sqlalchemy.ext.asyncio.AsyncMappingResult (sqlalchemy.engine.FilterResult)

async method sqlalchemy.ext.asyncio.AsyncMappingResult.all()

Return all scalar values in a list.

Equivalent to AsyncResult.all() except that mapping values, rather than Row objects, are returned.

method sqlalchemy.ext.asyncio.AsyncMappingResult.columns(*col_expressions)

Establish the columns that should be returned in each row.

async method sqlalchemy.ext.asyncio.AsyncMappingResult.fetchall()

A synonym for the AsyncMappingResult.all() method.

async method sqlalchemy.ext.asyncio.AsyncMappingResult.fetchmany(size=None)

Fetch many objects.

Equivalent to AsyncResult.fetchmany() except that mapping values, rather than Row objects, are returned.

async method sqlalchemy.ext.asyncio.AsyncMappingResult.fetchone()

Fetch one object.

Equivalent to AsyncResult.fetchone() except that mapping values, rather than Row objects, are returned.

async method sqlalchemy.ext.asyncio.AsyncMappingResult.first()

Fetch the first object or None if no object is present.

Equivalent to AsyncResult.first() except that mapping values, rather than Row objects, are returned.

method sqlalchemy.ext.asyncio.AsyncMappingResult.keys()

Return an iterable view which yields the string keys that would be represented by each Row.

The view also can be tested for key containment using the Python in operator, which will test both for the string keys represented in the view, as well as for alternate keys such as column objects.

Changed in version 1.4: a key view object is returned rather than a plain list.

async method sqlalchemy.ext.asyncio.AsyncMappingResult.one()

Return exactly one object or raise an exception.

Equivalent to AsyncResult.one() except that mapping values, rather than Row objects, are returned.

async method sqlalchemy.ext.asyncio.AsyncMappingResult.one_or_none()

Return at most one object or raise an exception.

Equivalent to AsyncResult.one_or_none() except that mapping values, rather than Row objects, are returned.

method sqlalchemy.ext.asyncio.AsyncMappingResult.partitions(size=None)

Iterate through sub-lists of elements of the size given.

Equivalent to AsyncResult.partitions() except that mapping values, rather than Row objects, are returned.

method sqlalchemy.ext.asyncio.AsyncMappingResult.unique(strategy=None)

Apply unique filtering to the objects returned by this AsyncMappingResult.

See AsyncResult.unique() for usage details.

ORM Session API Documentation

Object Name Description

AsyncSession

Asyncio version of Session.

AsyncSessionTransaction

A wrapper for the ORM SessionTransaction object.

class sqlalchemy.ext.asyncio.AsyncSession(bind: sqlalchemy.ext.asyncio.engine.AsyncEngine = None, binds: Mapping[object, sqlalchemy.ext.asyncio.engine.AsyncEngine] = None, **kw)

Asyncio version of Session.

New in version 1.4.

method sqlalchemy.ext.asyncio.AsyncSession.add(instance, _warn=True)

Place an object in the Session.

Proxied for the Session class on behalf of the AsyncSession class.

Its state will be persisted to the database on the next flush operation.

Repeated calls to add() will be ignored. The opposite of add() is expunge().

method sqlalchemy.ext.asyncio.AsyncSession.add_all(instances)

Add the given collection of instances to this Session.

Proxied for the Session class on behalf of the AsyncSession class.

method sqlalchemy.ext.asyncio.AsyncSession.begin(**kw)

Return an AsyncSessionTransaction object.

The underlying Session will perform the “begin” action when the AsyncSessionTransaction object is entered:

async with async_session.begin():
    # .. ORM transaction is begun

Note that database IO will not normally occur when the session-level transaction is begun, as database transactions begin on an on-demand basis. However, the begin block is async to accommodate for a SessionEvents.after_transaction_create() event hook that may perform IO.

For a general description of ORM begin, see Session.begin().

method sqlalchemy.ext.asyncio.AsyncSession.begin_nested(**kw)

Return an AsyncSessionTransaction object which will begin a “nested” transaction, e.g. SAVEPOINT.

Behavior is the same as that of AsyncSession.begin().

For a general description of ORM begin nested, see Session.begin_nested().

async method sqlalchemy.ext.asyncio.AsyncSession.close()

Close this AsyncSession.

method sqlalchemy.ext.asyncio.AsyncSession.async classmethod close_all()

Close all AsyncSession sessions.

async method sqlalchemy.ext.asyncio.AsyncSession.commit()

Commit the current transaction in progress.

async method sqlalchemy.ext.asyncio.AsyncSession.connection()

Return a AsyncConnection object corresponding to this Session object’s transactional state.

method sqlalchemy.ext.asyncio.AsyncSession.delete(instance)

Mark an instance as deleted.

Proxied for the Session class on behalf of the AsyncSession class.

The database delete operation occurs upon flush().

attribute sqlalchemy.ext.asyncio.AsyncSession.deleted

The set of all instances marked as ‘deleted’ within this Session

Proxied for the Session class on behalf of the AsyncSession class.

attribute sqlalchemy.ext.asyncio.AsyncSession.dirty

The set of all persistent instances considered dirty.

Proxied for the Session class on behalf of the AsyncSession class.

E.g.:

some_mapped_object in session.dirty

Instances are considered dirty when they were modified but not deleted.

Note that this ‘dirty’ calculation is ‘optimistic’; most attribute-setting or collection modification operations will mark an instance as ‘dirty’ and place it in this set, even if there is no net change to the attribute’s value. At flush time, the value of each attribute is compared to its previously saved value, and if there’s no net change, no SQL operation will occur (this is a more expensive operation so it’s only done at flush time).

To check if an instance has actionable net changes to its attributes, use the Session.is_modified() method.

async method sqlalchemy.ext.asyncio.AsyncSession.execute(statement: sqlalchemy.sql.base.Executable, params: Optional[Mapping] = None, execution_options: Mapping = {}, bind_arguments: Optional[Mapping] = None, **kw)sqlalchemy.engine.Result

Execute a statement and return a buffered Result object.

method sqlalchemy.ext.asyncio.AsyncSession.expire(instance, attribute_names=None)

Expire the attributes on an instance.

Proxied for the Session class on behalf of the AsyncSession class.

Marks the attributes of an instance as out of date. When an expired attribute is next accessed, a query will be issued to the Session object’s current transactional context in order to load all expired attributes for the given instance. Note that a highly isolated transaction will return the same values as were previously read in that same transaction, regardless of changes in database state outside of that transaction.

To expire all objects in the Session simultaneously, use Session.expire_all().

The Session object’s default behavior is to expire all state whenever the Session.rollback() or Session.commit() methods are called, so that new state can be loaded for the new transaction. For this reason, calling Session.expire() only makes sense for the specific case that a non-ORM SQL statement was emitted in the current transaction.

Parameters
  • instance – The instance to be refreshed.

  • attribute_names – optional list of string attribute names indicating a subset of attributes to be expired.

method sqlalchemy.ext.asyncio.AsyncSession.expire_all()

Expires all persistent instances within this Session.

Proxied for the Session class on behalf of the AsyncSession class.

When any attributes on a persistent instance is next accessed, a query will be issued using the Session object’s current transactional context in order to load all expired attributes for the given instance. Note that a highly isolated transaction will return the same values as were previously read in that same transaction, regardless of changes in database state outside of that transaction.

To expire individual objects and individual attributes on those objects, use Session.expire().

The Session object’s default behavior is to expire all state whenever the Session.rollback() or Session.commit() methods are called, so that new state can be loaded for the new transaction. For this reason, calling Session.expire_all() should not be needed when autocommit is False, assuming the transaction is isolated.

method sqlalchemy.ext.asyncio.AsyncSession.expunge(instance)

Remove the instance from this Session.

Proxied for the Session class on behalf of the AsyncSession class.

This will free all internal references to the instance. Cascading will be applied according to the expunge cascade rule.

method sqlalchemy.ext.asyncio.AsyncSession.expunge_all()

Remove all object instances from this Session.

Proxied for the Session class on behalf of the AsyncSession class.

This is equivalent to calling expunge(obj) on all objects in this Session.

async method sqlalchemy.ext.asyncio.AsyncSession.flush(objects=None)

Flush all the object changes to the database.

See also

Session.flush()

method sqlalchemy.ext.asyncio.AsyncSession.get_bind(mapper=None, clause=None, bind=None, _sa_skip_events=None, _sa_skip_for_implicit_returning=False)

Return a “bind” to which this Session is bound.

Proxied for the Session class on behalf of the AsyncSession class.

The “bind” is usually an instance of Engine, except in the case where the Session has been explicitly bound directly to a Connection.

For a multiply-bound or unbound Session, the mapper or clause arguments are used to determine the appropriate bind to return.

Note that the “mapper” argument is usually present when Session.get_bind() is called via an ORM operation such as a Session.query(), each individual INSERT/UPDATE/DELETE operation within a Session.flush(), call, etc.

The order of resolution is:

  1. if mapper given and Session.binds is present, locate a bind based first on the mapper in use, then on the mapped class in use, then on any base classes that are present in the __mro__ of the mapped class, from more specific superclasses to more general.

  2. if clause given and Session.binds is present, locate a bind based on Table objects found in the given clause present in Session.binds.

  3. if Session.binds is present, return that.

  4. if clause given, attempt to return a bind linked to the MetaData ultimately associated with the clause.

  5. if mapper given, attempt to return a bind linked to the MetaData ultimately associated with the Table or other selectable to which the mapper is mapped.

  6. No bind can be found, UnboundExecutionError is raised.

Note that the Session.get_bind() method can be overridden on a user-defined subclass of Session to provide any kind of bind resolution scheme. See the example at Custom Vertical Partitioning.

Parameters
  • mapper – Optional mapper() mapped class or instance of Mapper. The bind can be derived from a Mapper first by consulting the “binds” map associated with this Session, and secondly by consulting the MetaData associated with the Table to which the Mapper is mapped for a bind.

  • clause – A ClauseElement (i.e. select(), text(), etc.). If the mapper argument is not present or could not produce a bind, the given expression construct will be searched for a bound element, typically a Table associated with bound MetaData.

method sqlalchemy.ext.asyncio.AsyncSession.classmethod identity_key(*args, **kwargs)

Return an identity key.

Proxied for the Session class on behalf of the AsyncSession class.

This is an alias of identity_key().

attribute sqlalchemy.ext.asyncio.AsyncSession.info

A user-modifiable dictionary.

Proxied for the Session class on behalf of the AsyncSession class.

The initial value of this dictionary can be populated using the info argument to the Session constructor or sessionmaker constructor or factory methods. The dictionary here is always local to this Session and can be modified independently of all other Session objects.

attribute sqlalchemy.ext.asyncio.AsyncSession.is_active

True if this Session not in “partial rollback” state.

Proxied for the Session class on behalf of the AsyncSession class.

Changed in version 1.4: The Session no longer begins a new transaction immediately, so this attribute will be False when the Session is first instantiated.

“partial rollback” state typically indicates that the flush process of the Session has failed, and that the Session.rollback() method must be emitted in order to fully roll back the transaction.

If this Session is not in a transaction at all, the Session will autobegin when it is first used, so in this case Session.is_active will return True.

Otherwise, if this Session is within a transaction, and that transaction has not been rolled back internally, the Session.is_active will also return True.

method sqlalchemy.ext.asyncio.AsyncSession.is_modified(instance, include_collections=True)

Return True if the given instance has locally modified attributes.

Proxied for the Session class on behalf of the AsyncSession class.

This method retrieves the history for each instrumented attribute on the instance and performs a comparison of the current value to its previously committed value, if any.

It is in effect a more expensive and accurate version of checking for the given instance in the Session.dirty collection; a full test for each attribute’s net “dirty” status is performed.

E.g.:

return session.is_modified(someobject)

A few caveats to this method apply:

  • Instances present in the Session.dirty collection may report False when tested with this method. This is because the object may have received change events via attribute mutation, thus placing it in Session.dirty, but ultimately the state is the same as that loaded from the database, resulting in no net change here.

  • Scalar attributes may not have recorded the previously set value when a new value was applied, if the attribute was not loaded, or was expired, at the time the new value was received - in these cases, the attribute is assumed to have a change, even if there is ultimately no net change against its database value. SQLAlchemy in most cases does not need the “old” value when a set event occurs, so it skips the expense of a SQL call if the old value isn’t present, based on the assumption that an UPDATE of the scalar value is usually needed, and in those few cases where it isn’t, is less expensive on average than issuing a defensive SELECT.

    The “old” value is fetched unconditionally upon set only if the attribute container has the active_history flag set to True. This flag is set typically for primary key attributes and scalar object references that are not a simple many-to-one. To set this flag for any arbitrary mapped column, use the active_history argument with column_property().

Parameters
  • instance – mapped instance to be tested for pending changes.

  • include_collections – Indicates if multivalued collections should be included in the operation. Setting this to False is a way to detect only local-column based properties (i.e. scalar columns or many-to-one foreign keys) that would result in an UPDATE for this instance upon flush.

async method sqlalchemy.ext.asyncio.AsyncSession.merge(instance, load=True)

Copy the state of a given instance into a corresponding instance within this AsyncSession.

attribute sqlalchemy.ext.asyncio.AsyncSession.new

The set of all instances marked as ‘new’ within this Session.

Proxied for the Session class on behalf of the AsyncSession class.

attribute sqlalchemy.ext.asyncio.AsyncSession.no_autoflush

Return a context manager that disables autoflush.

Proxied for the Session class on behalf of the AsyncSession class.

e.g.:

with session.no_autoflush:

    some_object = SomeClass()
    session.add(some_object)
    # won't autoflush
    some_object.related_thing = session.query(SomeRelated).first()

Operations that proceed within the with: block will not be subject to flushes occurring upon query access. This is useful when initializing a series of objects which involve existing database queries, where the uncompleted object should not yet be flushed.

method sqlalchemy.ext.asyncio.AsyncSession.classmethod object_session(instance)

Return the Session to which an object belongs.

Proxied for the Session class on behalf of the AsyncSession class.

This is an alias of object_session().

async method sqlalchemy.ext.asyncio.AsyncSession.refresh(instance, attribute_names=None, with_for_update=None)

Expire and refresh the attributes on the given instance.

A query will be issued to the database and all attributes will be refreshed with their current database value.

This is the async version of the Session.refresh() method. See that method for a complete description of all options.

async method sqlalchemy.ext.asyncio.AsyncSession.rollback()

Rollback the current transaction in progress.

async method sqlalchemy.ext.asyncio.AsyncSession.run_sync(fn: Callable, *arg, **kw) → Any

Invoke the given sync callable passing sync self as the first argument.

This method maintains the asyncio event loop all the way through to the database connection by running the given callable in a specially instrumented greenlet.

E.g.:

with AsyncSession(async_engine) as session:
    await session.run_sync(some_business_method)
async method sqlalchemy.ext.asyncio.AsyncSession.stream(statement, params=None, execution_options={}, bind_arguments=None, **kw)

Execute a statement and return a streaming AsyncResult object.

class sqlalchemy.ext.asyncio.AsyncSessionTransaction(session, nested=False)

A wrapper for the ORM SessionTransaction object.

This object is provided so that a transaction-holding object for the AsyncSession.begin() may be returned.

The object supports both explicit calls to AsyncSessionTransaction.commit() and AsyncSessionTransaction.rollback(), as well as use as an async context manager.

New in version 1.4.

Class signature

class sqlalchemy.ext.asyncio.AsyncSessionTransaction (sqlalchemy.ext.asyncio.base.StartableContext)

async method sqlalchemy.ext.asyncio.AsyncSessionTransaction.commit()

Commit this AsyncTransaction.

async method sqlalchemy.ext.asyncio.AsyncSessionTransaction.rollback()

Roll back this AsyncTransaction.

Previous: ORM Extensions Next: Association Proxy