Release: 1.4.0b1 pre release | Release Date: unreleased

SQLAlchemy 1.4 Documentation

Working with Engines and Connections

This section details direct usage of the Engine, Connection, and related objects. Its important to note that when using the SQLAlchemy ORM, these objects are not generally accessed; instead, the Session object is used as the interface to the database. However, for applications that are built around direct usage of textual SQL statements and/or SQL expression constructs without involvement by the ORM’s higher level management services, the Engine and Connection are king (and queen?) - read on.

Basic Usage

Recall from Engine Configuration that an Engine is created via the create_engine() call:

engine = create_engine('mysql://scott:tiger@localhost/test')

The typical usage of create_engine() is once per particular database URL, held globally for the lifetime of a single application process. A single Engine manages many individual DBAPI connections on behalf of the process and is intended to be called upon in a concurrent fashion. The Engine is not synonymous to the DBAPI connect function, which represents just one connection resource - the Engine is most efficient when created just once at the module level of an application, not per-object or per-function call.

The most basic function of the Engine is to provide access to a Connection, which can then invoke SQL statements. To emit a textual statement to the database looks like:

from sqlalchemy import text

with engine.connect() as connection:
    result = connection.execute(text("select username from users"))
    for row in result:
        print("username:", row['username'])

Above, the Engine.connect() method returns a Connection object, and by using it in a Python context manager (e.g. the with: statement) the Connection.close() method is automatically invoked at the end of the block. The Connection, is a proxy object for an actual DBAPI connection. The DBAPI connection is retrieved from the connection pool at the point at which Connection is created.

The object returned is known as CursorResult, which references a DBAPI cursor and provides methods for fetching rows similar to that of the DBAPI cursor. The DBAPI cursor will be closed by the CursorResult when all of its result rows (if any) are exhausted. A CursorResult that returns no rows, such as that of an UPDATE statement (without any returned rows), releases cursor resources immediately upon construction.

When the Connection is closed at the end of the with: block, the referenced DBAPI connection is released to the connection pool. From the perspective of the database itself, the connection pool will not actually “close” the connection assuming the pool has room to store this connection for the next use. When the connection is returned to the pool for re-use, the pooling mechanism issues a rollback() call on the DBAPI connection so that any transactional state or locks are removed, and the connection is ready for its next use.

Deprecated since version 2.0: The CursorResult object is replaced in SQLAlchemy 2.0 with a newly refined object known as Result.

Our example above illustrated the execution of a textual SQL string, which should be invoked by using the text() construct to indicate that we’d like to use textual SQL. The Connection.execute() method can of course accommodate more than that, including the variety of SQL expression constructs described in SQL Expression Language Tutorial.

Using Transactions

Note

This section describes how to use transactions when working directly with Engine and Connection objects. When using the SQLAlchemy ORM, the public API for transaction control is via the Session object, which makes usage of the Transaction object internally. See Managing Transactions for further information.

The Connection object provides a Connection.begin() method which returns a Transaction object. Like the Connection itself, this object is usually used within a Python with: block so that its scope is managed:

with engine.connect() as connection:
    with connection.begin():
        r1 = connection.execute(table1.select())
        connection.execute(table1.insert(), {"col1": 7, "col2": "this is some data"})

The above block can be stated more simply by using the Engine.begin() method of Engine:

# runs a transaction
with engine.begin() as connection:
    r1 = connection.execute(table1.select())
    connection.execute(table1.insert(), {"col1": 7, "col2": "this is some data"})

The block managed by each .begin() method has the behavior such that the transaction is committed when the block completes. If an exception is raised, the transaction is instead rolled back, and the exception propagated outwards.

The underlying object used to represent the transaction is the Transaction object. This object is returned by the Connection.begin() method and includes the methods Transaction.commit() and Transaction.rollback(). The context manager calling form, which invokes these methods automatically, is recommended as a best practice.

Nesting of Transaction Blocks

Note

The “transaction nesting” feature of SQLAlchemy is a legacy feature that will be deprecated in an upcoming release. New usage paradigms will eliminate the need for it to be present.

The Transaction object also handles “nested” behavior by keeping track of the outermost begin/commit pair. In this example, two functions both issue a transaction on a Connection, but only the outermost Transaction object actually takes effect when it is committed.

# method_a starts a transaction and calls method_b
def method_a(connection):
    with connection.begin():  # open a transaction
        method_b(connection)

# method_b also starts a transaction
def method_b(connection):
    with connection.begin(): # open a transaction - this runs in the
                             # context of method_a's transaction
        connection.execute(text("insert into mytable values ('bat', 'lala')"))
        connection.execute(mytable.insert(), {"col1": "bat", "col2": "lala"})

# open a Connection and call method_a
with engine.connect() as conn:
    method_a(conn)

Above, method_a is called first, which calls connection.begin(). Then it calls method_b. When method_b calls connection.begin(), it just increments a counter that is decremented when it calls commit(). If either method_a or method_b calls rollback(), the whole transaction is rolled back. The transaction is not committed until method_a calls the commit() method. This “nesting” behavior allows the creation of functions which “guarantee” that a transaction will be used if one was not already available, but will automatically participate in an enclosing transaction if one exists.

Understanding Autocommit

Deprecated since version 2.0: The “autocommit” feature of SQLAlchemy Core is deprecated and will not be present in version 2.0 of SQLAlchemy. See Library-level (but not driver level) “Autocommit” removed from both Core and ORM for background.

The previous transaction example illustrates how to use Transaction so that several executions can take part in the same transaction. What happens when we issue an INSERT, UPDATE or DELETE call without using Transaction? While some DBAPI implementations provide various special “non-transactional” modes, the core behavior of DBAPI per PEP-0249 is that a transaction is always in progress, providing only rollback() and commit() methods but no begin(). SQLAlchemy assumes this is the case for any given DBAPI.

Given this requirement, SQLAlchemy implements its own “autocommit” feature which works completely consistently across all backends. This is achieved by detecting statements which represent data-changing operations, i.e. INSERT, UPDATE, DELETE, as well as data definition language (DDL) statements such as CREATE TABLE, ALTER TABLE, and then issuing a COMMIT automatically if no transaction is in progress. The detection is based on the presence of the autocommit=True execution option on the statement. If the statement is a text-only statement and the flag is not set, a regular expression is used to detect INSERT, UPDATE, DELETE, as well as a variety of other commands for a particular backend:

conn = engine.connect()
conn.execute(text("INSERT INTO users VALUES (1, 'john')"))  # autocommits

The “autocommit” feature is only in effect when no Transaction has otherwise been declared. This means the feature is not generally used with the ORM, as the Session object by default always maintains an ongoing Transaction.

Full control of the “autocommit” behavior is available using the generative Connection.execution_options() method provided on Connection and Engine, using the “autocommit” flag which will turn on or off the autocommit for the selected scope. For example, a text() construct representing a stored procedure that commits might use it so that a SELECT statement will issue a COMMIT:

with engine.connect().execution_options(autocommit=True) as conn:
    conn.execute(text("SELECT my_mutating_procedure()"))

Connectionless Execution, Implicit Execution

Deprecated since version 2.0: The features of “connectionless” and “implicit” execution in SQLAlchemy are deprecated and will be removed in version 2.0. See “Implicit” and “Connectionless” execution, “bound metadata” removed for background.

Recall from the first section we mentioned executing with and without explicit usage of Connection. “Connectionless” execution refers to the usage of the execute() method on an object which is not a Connection. This was illustrated using the Engine.execute() method of Engine:

result = engine.execute(text("select username from users"))
for row in result:
    print("username:", row['username'])

In addition to “connectionless” execution, it is also possible to use the Executable.execute() method of any Executable construct, which is a marker for SQL expression objects that support execution. The SQL expression object itself references an Engine or Connection known as the bind, which it uses in order to provide so-called “implicit” execution services.

Given a table as below:

from sqlalchemy import MetaData, Table, Column, Integer

meta = MetaData()
users_table = Table('users', meta,
    Column('id', Integer, primary_key=True),
    Column('name', String(50))
)

Explicit execution delivers the SQL text or constructed SQL expression to the Connection.execute() method of Connection:

engine = create_engine('sqlite:///file.db')
with engine.connect() as connection:
    result = connection.execute(users_table.select())
    for row in result:
        # ....

Explicit, connectionless execution delivers the expression to the Engine.execute() method of Engine:

engine = create_engine('sqlite:///file.db')
result = engine.execute(users_table.select())
for row in result:
    # ....
result.close()

Implicit execution is also connectionless, and makes usage of the Executable.execute() method on the expression itself. This method is provided as part of the Executable class, which refers to a SQL statement that is sufficient for being invoked against the database. The method makes usage of the assumption that either an Engine or Connection has been bound to the expression object. By “bound” we mean that the special attribute MetaData.bind has been used to associate a series of Table objects and all SQL constructs derived from them with a specific engine:

engine = create_engine('sqlite:///file.db')
meta.bind = engine
result = users_table.select().execute()
for row in result:
    # ....
result.close()

Above, we associate an Engine with a MetaData object using the special attribute MetaData.bind. The select() construct produced from the Table object has a method Executable.execute(), which will search for an Engine that’s “bound” to the Table.

Overall, the usage of “bound metadata” has three general effects:

  • SQL statement objects gain an Executable.execute() method which automatically locates a “bind” with which to execute themselves.

  • The ORM Session object supports using “bound metadata” in order to establish which Engine should be used to invoke SQL statements on behalf of a particular mapped class, though the Session also features its own explicit system of establishing complex Engine/ mapped class configurations.

  • The MetaData.create_all(), MetaData.drop_all(), Table.create(), Table.drop(), and “autoload” features all make usage of the bound Engine automatically without the need to pass it explicitly.

Note

The concepts of “bound metadata” and “implicit execution” are not emphasized in modern SQLAlchemy. While they offer some convenience, they are no longer required by any API and are never necessary.

In applications where multiple Engine objects are present, each one logically associated with a certain set of tables (i.e. vertical sharding), the “bound metadata” technique can be used so that individual Table can refer to the appropriate Engine automatically; in particular this is supported within the ORM via the Session object as a means to associate Table objects with an appropriate Engine, as an alternative to using the bind arguments accepted directly by the Session.

However, the “implicit execution” technique is not at all appropriate for use with the ORM, as it bypasses the transactional context maintained by the Session.

Overall, in the vast majority of cases, “bound metadata” and “implicit execution” are not useful. While “bound metadata” has a marginal level of usefulness with regards to ORM configuration, “implicit execution” is a very old usage pattern that in most cases is more confusing than it is helpful, and its usage is discouraged. Both patterns seem to encourage the overuse of expedient “short cuts” in application design which lead to problems later on.

Modern SQLAlchemy usage, especially the ORM, places a heavy stress on working within the context of a transaction at all times; the “implicit execution” concept makes the job of associating statement execution with a particular transaction much more difficult. The Executable.execute() method on a particular SQL statement usually implies that the execution is not part of any particular transaction, which is usually not the desired effect.

In both “connectionless” examples, the Connection is created behind the scenes; the CursorResult returned by the execute() call references the Connection used to issue the SQL statement. When the CursorResult is closed, the underlying Connection is closed for us, resulting in the DBAPI connection being returned to the pool with transactional resources removed.

Translation of Schema Names

To support multi-tenancy applications that distribute common sets of tables into multiple schemas, the Connection.execution_options.schema_translate_map execution option may be used to repurpose a set of Table objects to render under different schema names without any changes.

Given a table:

user_table = Table(
    'user', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String(50))
)

The “schema” of this Table as defined by the Table.schema attribute is None. The Connection.execution_options.schema_translate_map can specify that all Table objects with a schema of None would instead render the schema as user_schema_one:

connection = engine.connect().execution_options(
    schema_translate_map={None: "user_schema_one"})

result = connection.execute(user_table.select())

The above code will invoke SQL on the database of the form:

SELECT user_schema_one.user.id, user_schema_one.user.name FROM
user_schema_one.user

That is, the schema name is substituted with our translated name. The map can specify any number of target->destination schemas:

connection = engine.connect().execution_options(
    schema_translate_map={
        None: "user_schema_one",     # no schema name -> "user_schema_one"
        "special": "special_schema", # schema="special" becomes "special_schema"
        "public": None               # Table objects with schema="public" will render with no schema
    })

The Connection.execution_options.schema_translate_map parameter affects all DDL and SQL constructs generated from the SQL expression language, as derived from the Table or Sequence objects. It does not impact literal string SQL used via the text() construct nor via plain strings passed to Connection.execute().

The feature takes effect only in those cases where the name of the schema is derived directly from that of a Table or Sequence; it does not impact methods where a string schema name is passed directly. By this pattern, it takes effect within the “can create” / “can drop” checks performed by methods such as MetaData.create_all() or MetaData.drop_all() are called, and it takes effect when using table reflection given a Table object. However it does not affect the operations present on the Inspector object, as the schema name is passed to these methods explicitly.

New in version 1.1.

SQL Compilation Caching

New in version 1.4: SQLAlchemy now has a transparent query caching system that substantially lowers the Python computational overhead involved in converting SQL statement constructs into SQL strings across both Core and ORM. See the introduction at Transparent SQL Compilation Caching added to All DQL, DML Statements in Core, ORM.

SQLAlchemy includes a comprehensive caching system for the SQL compiler as well as its ORM variants. This caching system is transparent within the Engine and provides that the SQL compilation process for a given Core or ORM SQL statement, as well as related computations which assemble result-fetching mechanics for that statement, will only occur once for that statement object and all others with the identical structure, for the duration that the particular structure remains within the engine’s “compiled cache”. By “statement objects that have the identical structure”, this generally corresponds to a SQL statement that is constructed within a function and is built each time that function runs:

def run_my_statement(connection, parameter):
    stmt = select(table)
    stmt = stmt.where(table.c.col == parameter)
    stmt = stmt.order_by(table.c.id)
    return connection.execute(stmt)

The above statement will generate SQL resembling SELECT id, col FROM table WHERE col = :col ORDER BY id, noting that while the value of parameter is a plain Python object such as a string or an integer, the string SQL form of the statement does not include this value as it uses bound parameters. Subsequent invocations of the above run_my_statement() function will use a cached compilation construct within the scope of the connection.execute() call for enhanced performance.

Note

it is important to note that the SQL compilation cache is caching the SQL string that is passed to the database only, and not the results returned by a query. It is in no way a data cache and does not impact the results returned for a particular SQL statement nor does it imply any memory use linked to fetching of result rows.

While SQLAlchemy has had a rudimentary statement cache since the early 1.x series, and additionally has featured the “Baked Query” extension for the ORM, both of these systems required a high degree of special API use in order for the cache to be effective. The new cache as of 1.4 is instead completely automatic and requires no change in programming style to be effective.

The cache is automatically used without any configurational changes and no special steps are needed in order to enable it. The following sections detail the configuration and advanced usage patterns for the cache.

Configuration

The cache itself is a dictionary-like object called an LRUCache, which is an internal SQLAlchemy dictionary subclass that tracks the usage of particular keys and features a periodic “pruning” step which removes the least recently used items when the size of the cache reaches a certain threshold. The size of this cache defaults to 500 and may be configured using the create_engine.query_cache_size parameter:

engine = create_engine("postgresql://scott:tiger@localhost/test", query_cache_size=1200)

The size of the cache can grow to be a factor of 150% of the size given, before it’s pruned back down to the target size. A cache of size 1200 above can therefore grow to be 1800 elements in size at which point it will be pruned to 1200.

The sizing of the cache is based on a single entry per unique SQL statement rendered, per engine. SQL statements generated from both the Core and the ORM are treated equally. DDL statements will usually not be cached. In order to determine what the cache is doing, engine logging will include details about the cache’s behavior, described in the next section.

Estimating Cache Performance Using Logging

The above cache size of 1200 is actually fairly large. For small applications, a size of 100 is likely sufficient. To estimate the optimal size of the cache, assuming enough memory is present on the target host, the size of the cache should be based on the number of unique SQL strings that may be rendered for the target engine in use. The most expedient way to see this is to use SQL echoing, which is most directly enabled by using the create_engine.echo flag, or by using Python logging; see the section Configuring Logging for background on logging configuration.

As an example, we will examine the logging produced by the following program:

from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import ForeignKey
from sqlalchemy import Integer
from sqlalchemy import String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm import Session

Base = declarative_base()


class A(Base):
    __tablename__ = "a"

    id = Column(Integer, primary_key=True)
    data = Column(String)
    bs = relationship("B")


class B(Base):
    __tablename__ = "b"
    id = Column(Integer, primary_key=True)
    a_id = Column(ForeignKey("a.id"))
    data = Column(String)


e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

s = Session(e)

s.add_all(
    [A(bs=[B(), B(), B()]), A(bs=[B(), B(), B()]), A(bs=[B(), B(), B()])]
)
s.commit()

for a_rec in s.query(A):
    print(a_rec.bs)

When run, each SQL statement that’s logged will include a bracketed cache statistics badge to the left of the parameters passed. The four types of message we may see are summarized as follows:

  • [raw sql] - the driver or the end-user emitted raw SQL using Connection.exec_driver_sql() - caching does not apply

  • [no key] - the statement object is a DDL statement that is not cached, or the statement object contains uncacheable elements such as user-defined constructs or arbitrarily large VALUES clauses.

  • [generated in Xs] - the statement was a cache miss and had to be compiled, then stored in the cache. it took X seconds to produce the compiled construct. The number X will be in the small fractional seconds.

  • [cached since Xs ago] - the statement was a cache hit and did not have to be recompiled. The statement has been stored in the cache since X seconds ago. The number X will be proportional to how long the application has been running and how long the statement has been cached, so for example would be 86400 for a 24 hour period.

Each badge is described in more detail below.

The first statements we see for the above program will be the SQLite dialect checking for the existence of the “a” and “b” tables:

INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("a")
INFO sqlalchemy.engine.Engine [raw sql] ()
INFO sqlalchemy.engine.Engine PRAGMA main.table_info("b")
INFO sqlalchemy.engine.Engine [raw sql] ()

For the above two SQLite PRAGMA statements, the badge reads [raw sql], which indicates the driver is sending a Python string directly to the database using Connection.exec_driver_sql(). Caching does not apply to such statements because they already exist in string form, and there is nothing known about what kinds of result rows will be returned since SQLAlchemy does not parse SQL strings ahead of time.

The next statements we see are the CREATE TABLE statements:

INFO sqlalchemy.engine.Engine
CREATE TABLE a (
  id INTEGER NOT NULL,
  data VARCHAR,
  PRIMARY KEY (id)
)

INFO sqlalchemy.engine.Engine [no key 0.00007s] ()
INFO sqlalchemy.engine.Engine
CREATE TABLE b (
  id INTEGER NOT NULL,
  a_id INTEGER,
  data VARCHAR,
  PRIMARY KEY (id),
  FOREIGN KEY(a_id) REFERENCES a (id)
)

INFO sqlalchemy.engine.Engine [no key 0.00006s] ()

For each of these statements, the badge reads [no key 0.00006s]. This indicates that these two particular statements, caching did not occur because the DDL-oriented CreateTable construct did not produce a cache key. DDL constructs generally do not participate in caching because they are not typically subject to being repeated a second time and DDL is also a database configurational step where performance is not as critical.

The [no key] badge is important for one other reason, as it can be produced for SQL statements that are cacheable except for some particular sub-construct that is not currently cacheable. Examples of this include custom user-defined SQL elements that don’t define caching parameters, as well as some constructs that generate arbitrarily long and non-reproducible SQL strings, the main examples being the Values construct as well as when using “multivalued inserts” with the Insert.values() method.

So far our cache is still empty. The next statements will be cached however, a segment looks like:

INFO sqlalchemy.engine.Engine INSERT INTO a (data) VALUES (?)
INFO sqlalchemy.engine.Engine [generated in 0.00011s] (None,)
INFO sqlalchemy.engine.Engine INSERT INTO a (data) VALUES (?)
INFO sqlalchemy.engine.Engine [cached since 0.0003533s ago] (None,)
INFO sqlalchemy.engine.Engine INSERT INTO a (data) VALUES (?)
INFO sqlalchemy.engine.Engine [cached since 0.0005326s ago] (None,)
INFO sqlalchemy.engine.Engine INSERT INTO b (a_id, data) VALUES (?, ?)
INFO sqlalchemy.engine.Engine [generated in 0.00010s] (1, None)
INFO sqlalchemy.engine.Engine INSERT INTO b (a_id, data) VALUES (?, ?)
INFO sqlalchemy.engine.Engine [cached since 0.0003232s ago] (1, None)
INFO sqlalchemy.engine.Engine INSERT INTO b (a_id, data) VALUES (?, ?)
INFO sqlalchemy.engine.Engine [cached since 0.0004887s ago] (1, None)

Above, we see essentially two unique SQL strings; "INSERT INTO a (data) VALUES (?)" and "INSERT INTO b (a_id, data) VALUES (?, ?)". Since SQLAlchemy uses bound parameters for all literal values, even though these statements are repeated many times for different objects, because the parameters are separate, the actual SQL string stays the same.

Note

the above two statements are generated by the ORM unit of work process, and in fact will be caching these in a separate cache that is local to each mapper. However the mechanics and terminology are the same. The section Disabling or using an alternate dictionary to cache some (or all) statements below will describe how user-facing code can also use an alternate caching container on a per-statement basis.

The caching badge we see for the first occurrence of each of these two statements is [generated in 0.00011s]. This indicates that the statement was not in the cache, was compiled into a String in .00011s and was then cached. When we see the [generated] badge, we know that this means there was a cache miss. This is to be expected for the first occurrence of a particular statement. However, if lots of new [generated] badges are observed for a long-running application that is generally using the same series of SQL statements over and over, this may be a sign that the create_engine.query_cache_size parameter is too small. When a statement that was cached is then evicted from the cache due to the LRU cache pruning lesser used items, it will display the [generated] badge when it is next used.

The caching badge that we then see for the subsequent occurrences of each of these two statements looks like [cached since 0.0003533s ago]. This indicates that the statement was found in the cache, and was originally placed into the cache .0003533 seconds ago. It is important to note that while the [generated] and [cached since] badges refer to a number of seconds, they mean different things; in the case of [generated], the number is a rough timing of how long it took to compile the statement, and will be an extremely small amount of time. In the case of [cached since], this is the total time that a statement has been present in the cache. For an application that’s been running for six hours, this number may read [cached since 21600 seconds ago], and that’s a good thing. Seeing high numbers for “cached since” is an indication that these statements have not been subject to cache misses for a long time. Statements that frequently have a low number of “cached since” even if the application has been running a long time may indicate these statements are too frequently subject to cache misses, and that the create_engine.query_cache_size may need to be increased.

Our example program then performs some SELECTs where we can see the same pattern of “generated” then “cached”, for the SELECT of the “a” table as well as for subsequent lazy loads of the “b” table:

INFO sqlalchemy.engine.Engine SELECT a.id AS a_id, a.data AS a_data
FROM a
INFO sqlalchemy.engine.Engine [generated in 0.00009s] ()
INFO sqlalchemy.engine.Engine SELECT b.id AS b_id, b.a_id AS b_a_id, b.data AS b_data
FROM b
WHERE ? = b.a_id
INFO sqlalchemy.engine.Engine [generated in 0.00010s] (1,)
INFO sqlalchemy.engine.Engine SELECT b.id AS b_id, b.a_id AS b_a_id, b.data AS b_data
FROM b
WHERE ? = b.a_id
INFO sqlalchemy.engine.Engine [cached since 0.0005922s ago] (2,)
INFO sqlalchemy.engine.Engine SELECT b.id AS b_id, b.a_id AS b_a_id, b.data AS b_data
FROM b
WHERE ? = b.a_id

From our above program, a full run shows a total of four distinct SQL strings being cached. Which indicates a cache size of four would be sufficient. This is obviously an extremely small size, and the default size of 500 is fine to be left at its default.

How much memory does the cache use?

The previous section detailed some techniques to check if the create_engine.query_cache_size needs to be bigger. How do we know if the cache is not too large? The reason we may want to set create_engine.query_cache_size to not be higher than a certain number would be because we have an application that may make use of a very large number of different statements, such as an application that is building queries on the fly from a search UX, and we don’t want our host to run out of memory if for example, a hundred thousand different queries were run in the past 24 hours and they were all cached.

It is extremely difficult to measure how much memory is occupied by Python data structures, however using a process to measure growth in memory via top as a successive series of 250 new statements are added to the cache suggest a moderate Core statement takes up about 12K while a small ORM statement takes about 20K, including result-fetching structures which for the ORM will be much greater.

Disabling or using an alternate dictionary to cache some (or all) statements

The internal cache used is known as LRUCache, but this is mostly just a dictionary. Any dictionary may be used as a cache for any series of statements by using the Connection.execution_options.compiled_cache option as an execution option. Execution options may be set on a statement, on an Engine or Connection, as well as when using the ORM Session.execute() method for SQLAlchemy-2.0 style invocations. For example, to run a series of SQL statements and have them cached in a particular dictionary:

my_cache = {}
with engine.connect().execution_options(compiled_cache=my_cache) as conn:
    conn.execute(table.select())

The SQLAlchemy ORM uses the above technique to hold onto per-mapper caches within the unit of work “flush” process that are separate from the default cache configured on the Engine, as well as for some relationship loader queries.

The cache can also be disabled with this argument by sending a value of None:

# disable caching for this connection
with engine.connect().execution_options(compiled_cache=None) as conn:
    conn.execute(table.select())

Using Lambdas to add significant speed gains to statement production

Warning

This technique is generally non-essential except in very performance intensive scenarios, and intended for experienced Python programmers. While fairly straightforward, it involves metaprogramming concepts that are not appropriate for novice Python developers. The lambda approach can be applied to at a later time to existing code with a minimal amount of effort.

The caching system has in its roots the SQLAlchemy “baked query” extension, which made novel use of Python lambdas in order to produce SQL statements that were intrinsically cacheable, while at the same time decreasing not just the overhead involved to compile the statement into SQL, but also the overhead in constructing the statement object from a Python perspective. The new caching in SQLAlchemy by default does not substantially optimize the construction of SQL constructs. This refers to the Python overhead taken up to construct the statement object itself before it is compiled or executed, such as the Select object used in the example below:

def run_my_statement(connection, parameter):
    stmt = select(table)
    stmt = stmt.where(table.c.col == parameter)
    stmt = stmt.order_by(table.c.id)

    return connection.execute(stmt)

Above, in order to construct stmt, we see three Python functions or methods select(), .where() and .order_by() being invoked directly, and additionally there is a Python method invoked when we construct table.c.col == 'foo', as the expression language overrides the __eq__() method to produce a SQL construct. Within each of these calls is a series of argument checking and internal construction logic that makes use of many more Python function calls. With intense production of thousands of statement objects, these function calls can add up. Using the recipe for profiling at Code Profiling, the above Python code within the scope of the select() call down to the .order_by() call uses 73 Python function calls to produce.

Additionally, statement caching requires that a cache key be generated against the above statement, which must be composed of all elements within the statement that uniquely identify the SQL that it would produce. Measuring this process for the above statement takes another 40 Python function calls.

In order to ensure the full performance gains of the prior “baked query” extension are still available, the “lambda:” system used by baked queries has been adapted into a more capable and easier to use system as an intrinsic part of the SQLAlchemy Core expression language (which by extension then includes ORM queries, which as of SQLAlchemy 1.4 using 2.0-style APIs may also be invoked directly from SQLAlchemy Core expression objects). We can adapt our statement above to be built using “lambdas” by making use of the lambda_stmt() element. Using this approach, we indicate that the select() should be returned by a lambda. We can then add new criteria to the statement by composing further lambdas onto the object in a similar manner as how “baked queries” worked:

from sqlalchemy import lambda_stmt

def run_my_statement(connection, parameter):
    stmt = lambda_stmt(lambda: select(table))
    stmt += lambda s: s.where(table.c.col == parameter)
    stmt += lambda s: s.order_by(table.c.id)

    return connection.execute(stmt)

result = run_my_statement(some_connection, "some parameter")

The above code produces a StatementLambdaElement, which behaves like a Core SQL construct but defers the construction of the statement in most cases until it is needed by the compiler. If the statement is already cached, the lambdas will not be called.

The cache key is based on the Python source code location of each lambda itself, which in the Python interpreter is essentially the __code__ element of the Python function. This means that the lambda approach should only be used inside of a function where the lambdas themselves will be the same lambdas each time, from a Python source code perspective.

The execution process for the above lambda will extract literal parameters from the statement each time, without needing to actually run the lambdas. In the above example, each time the variable parameter is used within the lambda to generate the WHERE clause of the statement, while the actual lambda present will not actually be run, the value of parameter will be tracked and the current value of the variable will be used within the statement parameters at execution time. This is a feature that was not possible with the “baked query” extension and involves the use of up-front analysis of the incoming __code__ object to determine how parameters can be extracted from future lambdas against that same code object.

More simply, this means it’s safe for the lambda statement to use arbitrary literal parameters, which don’t modify the structure of the statement, on each invocation:

def run_my_statement(connection, parameter):
    stmt = lambda_stmt(lambda: select(table))
    stmt += lambda s: s.where(table.c.col == parameter)
    stmt += lambda s: s.order_by(table.c.id)

    return connection.execute(stmt)

However, it’s not safe for an individual lambda so modify the SQL structure of the statement across calls:

# incorrect example
def run_my_statement(connection, parameter, add_criteria=False):
    stmt = lambda_stmt(lambda: select(table))

    # will not be cached correctly as add_criteria changes
    stmt += lambda s: s.where(
        and_(add_criteria, table.c.col == parameter)
        if add_criteria
        else s.where(table.c.col == parameter)
    )

    stmt += lambda s: s.order_by(table.c.id)

    return connection.execute(stmt)

The lambda statements indicated above will invoke all of the lambdas the first time they are constructed; subsequent to that, the lambdas will not be invoked. On these subsequent runs, a lambda construct will use far fewer Python function calls in order to construct the un-cached object as well as to generate the cache key after the first call. The above statement using lambdas takes only 41 Python function calls to generate the whole structure as well as to produce the cache key, including the extraction of the bound parameters. This is compared to a total of about 115 Python function calls for the non-lambda version.

For a series of examples of “lambda” caching with performance comparisons, see the “short_selects” test suite within the Performance performance example.

Engine Disposal

The Engine refers to a connection pool, which means under normal circumstances, there are open database connections present while the Engine object is still resident in memory. When an Engine is garbage collected, its connection pool is no longer referred to by that Engine, and assuming none of its connections are still checked out, the pool and its connections will also be garbage collected, which has the effect of closing out the actual database connections as well. But otherwise, the Engine will hold onto open database connections assuming it uses the normally default pool implementation of QueuePool.

The Engine is intended to normally be a permanent fixture established up-front and maintained throughout the lifespan of an application. It is not intended to be created and disposed on a per-connection basis; it is instead a registry that maintains both a pool of connections as well as configurational information about the database and DBAPI in use, as well as some degree of internal caching of per-database resources.

However, there are many cases where it is desirable that all connection resources referred to by the Engine be completely closed out. It’s generally not a good idea to rely on Python garbage collection for this to occur for these cases; instead, the Engine can be explicitly disposed using the Engine.dispose() method. This disposes of the engine’s underlying connection pool and replaces it with a new one that’s empty. Provided that the Engine is discarded at this point and no longer used, all checked-in connections which it refers to will also be fully closed.

Valid use cases for calling Engine.dispose() include:

  • When a program wants to release any remaining checked-in connections held by the connection pool and expects to no longer be connected to that database at all for any future operations.

  • When a program uses multiprocessing or fork(), and an Engine object is copied to the child process, Engine.dispose() should be called so that the engine creates brand new database connections local to that fork. Database connections generally do not travel across process boundaries.

  • Within test suites or multitenancy scenarios where many ad-hoc, short-lived Engine objects may be created and disposed.

Connections that are checked out are not discarded when the engine is disposed or garbage collected, as these connections are still strongly referenced elsewhere by the application. However, after Engine.dispose() is called, those connections are no longer associated with that Engine; when they are closed, they will be returned to their now-orphaned connection pool which will ultimately be garbage collected, once all connections which refer to it are also no longer referenced anywhere. Since this process is not easy to control, it is strongly recommended that Engine.dispose() is called only after all checked out connections are checked in or otherwise de-associated from their pool.

An alternative for applications that are negatively impacted by the Engine object’s use of connection pooling is to disable pooling entirely. This typically incurs only a modest performance impact upon the use of new connections, and means that when a connection is checked in, it is entirely closed out and is not held in memory. See Switching Pool Implementations for guidelines on how to disable pooling.

Working with Driver SQL and Raw DBAPI Connections

The introduction on using Connection.execute() made use of the text() construct in order to illustrate how textual SQL statements may be invoked. When working with SQLAlchemy, textual SQL is actually more of the exception rather than the norm, as the Core expression language and the ORM both abstract away the textual representation of SQL. However, the text() construct itself also provides some abstraction of textual SQL in that it normalizes how bound parameters are passed, as well as that it supports datatyping behavior for parameters and result set rows.

Invoking SQL strings directly to the driver

For the use case where one wants to invoke textual SQL directly passed to the underlying driver (known as the DBAPI) without any intervention from the text() construct, the Connection.exec_driver_sql() method may be used:

with engine.connect() as conn:
    conn.exec_driver_sql("SET param='bar'")

New in version 1.4: Added the Connection.exec_driver_sql() method.

Working with the DBAPI cursor directly

There are some cases where SQLAlchemy does not provide a genericized way at accessing some DBAPI functions, such as calling stored procedures as well as dealing with multiple result sets. In these cases, it’s just as expedient to deal with the raw DBAPI connection directly.

The most common way to access the raw DBAPI connection is to get it from an already present Connection object directly. It is present using the Connection.connection attribute:

connection = engine.connect()
dbapi_conn = connection.connection

The DBAPI connection here is actually a “proxied” in terms of the originating connection pool, however this is an implementation detail that in most cases can be ignored. As this DBAPI connection is still contained within the scope of an owning Connection object, it is best to make use of the Connection object for most features such as transaction control as well as calling the Connection.close() method; if these operations are performed on the DBAPI connection directly, the owning Connection will not be aware of these changes in state.

To overcome the limitations imposed by the DBAPI connection that is maintained by an owning Connection, a DBAPI connection is also available without the need to procure a Connection first, using the Engine.raw_connection() method of Engine:

dbapi_conn = engine.raw_connection()

This DBAPI connection is again a “proxied” form as was the case before. The purpose of this proxying is now apparent, as when we call the .close() method of this connection, the DBAPI connection is typically not actually closed, but instead released back to the engine’s connection pool:

dbapi_conn.close()

While SQLAlchemy may in the future add built-in patterns for more DBAPI use cases, there are diminishing returns as these cases tend to be rarely needed and they also vary highly dependent on the type of DBAPI in use, so in any case the direct DBAPI calling pattern is always there for those cases where it is needed.

Some recipes for DBAPI connection use follow.

Calling Stored Procedures

For stored procedures with special syntactical or parameter concerns, DBAPI-level callproc may be used:

connection = engine.raw_connection()
try:
    cursor = connection.cursor()
    cursor.callproc("my_procedure", ['x', 'y', 'z'])
    results = list(cursor.fetchall())
    cursor.close()
    connection.commit()
finally:
    connection.close()

Multiple Result Sets

Multiple result set support is available from a raw DBAPI cursor using the nextset method:

connection = engine.raw_connection()
try:
    cursor = connection.cursor()
    cursor.execute("select * from table1; select * from table2")
    results_one = cursor.fetchall()
    cursor.nextset()
    results_two = cursor.fetchall()
    cursor.close()
finally:
    connection.close()

Registering New Dialects

The create_engine() function call locates the given dialect using setuptools entrypoints. These entry points can be established for third party dialects within the setup.py script. For example, to create a new dialect “foodialect://”, the steps are as follows:

  1. Create a package called foodialect.

  2. The package should have a module containing the dialect class, which is typically a subclass of sqlalchemy.engine.default.DefaultDialect. In this example let’s say it’s called FooDialect and its module is accessed via foodialect.dialect.

  3. The entry point can be established in setup.py as follows:

    entry_points="""
    [sqlalchemy.dialects]
    foodialect = foodialect.dialect:FooDialect
    """

If the dialect is providing support for a particular DBAPI on top of an existing SQLAlchemy-supported database, the name can be given including a database-qualification. For example, if FooDialect were in fact a MySQL dialect, the entry point could be established like this:

entry_points="""
[sqlalchemy.dialects]
mysql.foodialect = foodialect.dialect:FooDialect
"""

The above entrypoint would then be accessed as create_engine("mysql+foodialect://").

Registering Dialects In-Process

SQLAlchemy also allows a dialect to be registered within the current process, bypassing the need for separate installation. Use the register() function as follows:

from sqlalchemy.dialects import registry
registry.register("mysql.foodialect", "myapp.dialect", "MyMySQLDialect")

The above will respond to create_engine("mysql+foodialect://") and load the MyMySQLDialect class from the myapp.dialect module.

Connection / Engine API

class sqlalchemy.engine.BaseCursorResult(context, cursor_strategy, cursor_description)

Base class for database result objects.

attribute sqlalchemy.engine.BaseCursorResult.inserted_primary_key

Return the primary key for the row just inserted.

The return value is a list of scalar values corresponding to the list of primary key columns in the target table.

This only applies to single row insert() constructs which did not explicitly specify Insert.returning().

Note that primary key columns which specify a server_default clause, or otherwise do not qualify as “autoincrement” columns (see the notes at Column), and were generated using the database-side default, will appear in this list as None unless the backend supports “returning” and the insert statement executed with the “implicit returning” enabled.

Raises InvalidRequestError if the executed statement is not a compiled expression construct or is not an insert() construct.

attribute sqlalchemy.engine.BaseCursorResult.inserted_primary_key_rows

Return a list of tuples, each containing the primary key for each row just inserted.

Usually, this method will return at most a list with a single entry which is the same row one would get back from CursorResult.inserted_primary_key. To support “executemany with INSERT” mode, multiple rows can be part of the list returned.

New in version 1.4.

attribute sqlalchemy.engine.BaseCursorResult.is_insert

True if this CursorResult is the result of a executing an expression language compiled insert() construct.

When True, this implies that the inserted_primary_key attribute is accessible, assuming the statement did not include a user defined “returning” construct.

method sqlalchemy.engine.BaseCursorResult.last_inserted_params()

Return the collection of inserted parameters from this execution.

Raises InvalidRequestError if the executed statement is not a compiled expression construct or is not an insert() construct.

method sqlalchemy.engine.BaseCursorResult.last_updated_params()

Return the collection of updated parameters from this execution.

Raises InvalidRequestError if the executed statement is not a compiled expression construct or is not an update() construct.

method sqlalchemy.engine.BaseCursorResult.lastrow_has_defaults()

Return lastrow_has_defaults() from the underlying ExecutionContext.

See ExecutionContext for details.

attribute sqlalchemy.engine.BaseCursorResult.lastrowid

Return the ‘lastrowid’ accessor on the DBAPI cursor.

This is a DBAPI specific method and is only functional for those backends which support it, for statements where it is appropriate. It’s behavior is not consistent across backends.

Usage of this method is normally unnecessary when using insert() expression constructs; the CursorResult.inserted_primary_key attribute provides a tuple of primary key values for a newly inserted row, regardless of database backend.

method sqlalchemy.engine.BaseCursorResult.postfetch_cols()

Return postfetch_cols() from the underlying ExecutionContext.

See ExecutionContext for details.

Raises InvalidRequestError if the executed statement is not a compiled expression construct or is not an insert() or update() construct.

method sqlalchemy.engine.BaseCursorResult.prefetch_cols()

Return prefetch_cols() from the underlying ExecutionContext.

See ExecutionContext for details.

Raises InvalidRequestError if the executed statement is not a compiled expression construct or is not an insert() or update() construct.

attribute sqlalchemy.engine.BaseCursorResult.returned_defaults

Return the values of default columns that were fetched using the ValuesBase.return_defaults() feature.

The value is an instance of Row, or None if ValuesBase.return_defaults() was not used or if the backend does not support RETURNING.

New in version 0.9.0.

attribute sqlalchemy.engine.BaseCursorResult.returned_defaults_rows

Return a list of rows each containing the values of default columns that were fetched using the ValuesBase.return_defaults() feature.

The return value is a list of Row objects.

New in version 1.4.

attribute sqlalchemy.engine.BaseCursorResult.returns_rows

True if this CursorResult returns zero or more rows.

I.e. if it is legal to call the methods CursorResult.fetchone(), CursorResult.fetchmany() CursorResult.fetchall().

Overall, the value of CursorResult.returns_rows should always be synonymous with whether or not the DBAPI cursor had a .description attribute, indicating the presence of result columns, noting that a cursor that returns zero rows still has a .description if a row-returning statement was emitted.

This attribute should be True for all results that are against SELECT statements, as well as for DML statements INSERT/UPDATE/DELETE that use RETURNING. For INSERT/UPDATE/DELETE statements that were not using RETURNING, the value will usually be False, however there are some dialect-specific exceptions to this, such as when using the MSSQL / pyodbc dialect a SELECT is emitted inline in order to retrieve an inserted primary key value.

attribute sqlalchemy.engine.BaseCursorResult.rowcount

Return the ‘rowcount’ for this result.

The ‘rowcount’ reports the number of rows matched by the WHERE criterion of an UPDATE or DELETE statement.

Note

Notes regarding CursorResult.rowcount:

  • This attribute returns the number of rows matched, which is not necessarily the same as the number of rows that were actually modified - an UPDATE statement, for example, may have no net change on a given row if the SET values given are the same as those present in the row already. Such a row would be matched but not modified. On backends that feature both styles, such as MySQL, rowcount is configured by default to return the match count in all cases.

  • CursorResult.rowcount is only useful in conjunction with an UPDATE or DELETE statement. Contrary to what the Python DBAPI says, it does not return the number of rows available from the results of a SELECT statement as DBAPIs cannot support this functionality when rows are unbuffered.

  • CursorResult.rowcount may not be fully implemented by all dialects. In particular, most DBAPIs do not support an aggregate rowcount result from an executemany call. The CursorResult.supports_sane_rowcount() and CursorResult.supports_sane_multi_rowcount() methods will report from the dialect if each usage is known to be supported.

  • Statements that use RETURNING may not return a correct rowcount.

method sqlalchemy.engine.BaseCursorResult.supports_sane_multi_rowcount()

Return supports_sane_multi_rowcount from the dialect.

See CursorResult.rowcount for background.

method sqlalchemy.engine.BaseCursorResult.supports_sane_rowcount()

Return supports_sane_rowcount from the dialect.

See CursorResult.rowcount for background.

class sqlalchemy.engine.ChunkedIteratorResult(cursor_metadata, chunks, source_supports_scalars=False, raw=None)

Bases: sqlalchemy.engine.IteratorResult

An IteratorResult that works from an iterator-producing callable.

The given chunks argument is a function that is given a number of rows to return in each chunk, or None for all rows. The function should then return an un-consumed iterator of lists, each list of the requested size.

The function can be called at any time again, in which case it should continue from the same result set but adjust the chunk size as given.

New in version 1.4.

method sqlalchemy.engine.ChunkedIteratorResult.yield_per(num)

Configure the row-fetching strategy to fetch num rows at a time.

This impacts the underlying behavior of the result when iterating over the result object, or otherwise making use of methods such as Result.fetchone() that return one row at a time. Data from the underlying cursor or other data source will be buffered up to this many rows in memory, and the buffered collection will then be yielded out one row at at time or as many rows are requested. Each time the buffer clears, it will be refreshed to this many rows or as many rows remain if fewer remain.

The Result.yield_per() method is generally used in conjunction with the Connection.execution_options.stream_results execution option, which will allow the database dialect in use to make use of a server side cursor, if the DBAPI supports it.

Most DBAPIs do not use server side cursors by default, which means all rows will be fetched upfront from the database regardless of the Result.yield_per() setting. However, Result.yield_per() may still be useful in that it batches the SQLAlchemy-side processing of the raw data from the database, and additionally when used for ORM scenarios will batch the conversion of database rows into ORM entity rows.

New in version 1.4.

Parameters

num – number of rows to fetch each time the buffer is refilled. If set to a value below 1, fetches all rows for the next buffer.

class sqlalchemy.engine.Connection(engine, connection=None, close_with_result=False, _branch_from=None, _execution_options=None, _dispatch=None, _has_events=None)

Bases: sqlalchemy.engine.Connectable

Provides high-level functionality for a wrapped DB-API connection.

Provides execution support for string-based SQL statements as well as ClauseElement, Compiled and DefaultGenerator objects. Provides a begin() method to return Transaction objects.

The Connection object is not thread-safe. While a Connection can be shared among threads using properly synchronized access, it is still possible that the underlying DBAPI connection may not support shared access between threads. Check the DBAPI documentation for details.

The Connection object represents a single DBAPI connection checked out from the connection pool. In this state, the connection pool has no affect upon the connection, including its expiration or timeout state. For the connection pool to properly manage connections, connections should be returned to the connection pool (i.e. connection.close()) whenever the connection is not in use.

method sqlalchemy.engine.Connection.__init__(engine, connection=None, close_with_result=False, _branch_from=None, _execution_options=None, _dispatch=None, _has_events=None)

Construct a new Connection.

method sqlalchemy.engine.Connection.begin()

Begin a transaction and return a transaction handle.

The returned object is an instance of Transaction. This object represents the “scope” of the transaction, which completes when either the Transaction.rollback() or Transaction.commit() method is called.

Nested calls to begin() on the same Connection will return new Transaction objects that represent an emulated transaction within the scope of the enclosing transaction, that is:

trans = conn.begin()   # outermost transaction
trans2 = conn.begin()  # "nested"
trans2.commit()        # does nothing
trans.commit()         # actually commits

Calls to Transaction.commit() only have an effect when invoked via the outermost Transaction object, though the Transaction.rollback() method of any of the Transaction objects will roll back the transaction.

See also

Connection.begin_nested() - use a SAVEPOINT

Connection.begin_twophase() - use a two phase /XID transaction

Engine.begin() - context manager available from Engine

method sqlalchemy.engine.Connection.begin_nested()

Begin a nested transaction and return a transaction handle.

The returned object is an instance of NestedTransaction.

Nested transactions require SAVEPOINT support in the underlying database. Any transaction in the hierarchy may commit and rollback, however the outermost transaction still controls the overall commit or rollback of the transaction of a whole.

method sqlalchemy.engine.Connection.begin_twophase(xid=None)

Begin a two-phase or XA transaction and return a transaction handle.

The returned object is an instance of TwoPhaseTransaction, which in addition to the methods provided by Transaction, also provides a TwoPhaseTransaction.prepare() method.

Parameters

xid – the two phase transaction id. If not supplied, a random id will be generated.

method sqlalchemy.engine.Connection.close()

Close this Connection.

This results in a release of the underlying database resources, that is, the DBAPI connection referenced internally. The DBAPI connection is typically restored back to the connection-holding Pool referenced by the Engine that produced this Connection. Any transactional state present on the DBAPI connection is also unconditionally released via the DBAPI connection’s rollback() method, regardless of any Transaction object that may be outstanding with regards to this Connection.

After Connection.close() is called, the Connection is permanently in a closed state, and will allow no further operations.

attribute sqlalchemy.engine.Connection.closed

Return True if this connection is closed.

method sqlalchemy.engine.Connection.connect(close_with_result=False)

Returns a branched version of this Connection.

Deprecated since version 1.4: The Connection.connect() function/method is considered legacy as of the 1.x series of SQLAlchemy and will be removed in 2.0. (Background on SQLAlchemy 2.0 at: SQLAlchemy 2.0 Transition)

The Connection.close() method on the returned Connection can be called and this Connection will remain open.

This method provides usage symmetry with Engine.connect(), including for usage with context managers.

attribute sqlalchemy.engine.Connection.connection

The underlying DB-API connection managed by this Connection.

attribute sqlalchemy.engine.Connection.default_isolation_level

The default isolation level assigned to this Connection.

This is the isolation level setting that the Connection has when first procured via the Engine.connect() method. This level stays in place until the Connection.execution_options.isolation_level is used to change the setting on a per-Connection basis.

Unlike Connection.get_isolation_level(), this attribute is set ahead of time from the first connection procured by the dialect, so SQL query is not invoked when this accessor is called.

New in version 0.9.9.

See also

Connection.get_isolation_level() - view current level

create_engine.isolation_level - set per Engine isolation level

Connection.execution_options.isolation_level - set per Connection isolation level

method sqlalchemy.engine.Connection.detach()

Detach the underlying DB-API connection from its connection pool.

E.g.:

with engine.connect() as conn:
    conn.detach()
    conn.execute(text("SET search_path TO schema1, schema2"))

    # work with connection

# connection is fully closed (since we used "with:", can
# also call .close())

This Connection instance will remain usable. When closed (or exited from a context manager context as above), the DB-API connection will be literally closed and not returned to its originating pool.

This method can be used to insulate the rest of an application from a modified state on a connection (such as a transaction isolation level or similar).

method sqlalchemy.engine.Connection.exec_driver_sql(statement, parameters=None, execution_options=None)

Executes a SQL statement construct and returns a CursorResult.

Parameters
  • statement – The statement str to be executed. Bound parameters must use the underlying DBAPI’s paramstyle, such as “qmark”, “pyformat”, “format”, etc.

  • parameters

    represent bound parameter values to be used in the execution. The format is one of: a dictionary of named parameters, a tuple of positional parameters, or a list containing either dictionaries or tuples for multiple-execute support.

    E.g. multiple dictionaries:

    conn.exec_driver_sql(
        "INSERT INTO table (id, value) VALUES (%(id)s, %(value)s)",
        [{"id":1, "value":"v1"}, {"id":2, "value":"v2"}]
    )

    Single dictionary:

    conn.exec_driver_sql(
        "INSERT INTO table (id, value) VALUES (%(id)s, %(value)s)",
        dict(id=1, value="v1")
    )

    Single tuple:

    conn.exec_driver_sql(
        "INSERT INTO table (id, value) VALUES (?, ?)",
        (1, 'v1')
    )

    See also

    PEP 249

method sqlalchemy.engine.Connection.execute(object_, *multiparams, **params)

Executes a SQL statement construct and returns a CursorResult.

Parameters
  • object

    The statement to be executed. May be one of:

    Deprecated since version 2.0: passing a string to Connection.execute() is deprecated and will be removed in version 2.0. Use the text() construct with Connection.execute(), or the Connection.exec_driver_sql() method to invoke a driver-level SQL string.

  • *multiparams/**params

    represent bound parameter values to be used in the execution. Typically, the format is either a collection of one or more dictionaries passed to *multiparams:

    conn.execute(
        table.insert(),
        {"id":1, "value":"v1"},
        {"id":2, "value":"v2"}
    )

    …or individual key/values interpreted by **params:

    conn.execute(
        table.insert(), id=1, value="v1"
    )

    In the case that a plain SQL string is passed, and the underlying DBAPI accepts positional bind parameters, a collection of tuples or individual values in *multiparams may be passed:

    conn.execute(
        "INSERT INTO table (id, value) VALUES (?, ?)",
        (1, "v1"), (2, "v2")
    )
    
    conn.execute(
        "INSERT INTO table (id, value) VALUES (?, ?)",
        1, "v1"
    )

    Note above, the usage of a question mark “?” or other symbol is contingent upon the “paramstyle” accepted by the DBAPI in use, which may be any of “qmark”, “named”, “pyformat”, “format”, “numeric”. See pep-249 for details on paramstyle.

    To execute a textual SQL statement which uses bound parameters in a DBAPI-agnostic way, use the text() construct.

    Deprecated since version 2.0: use of tuple or scalar positional parameters is deprecated. All params should be dicts or sequences of dicts. Use exec_driver_sql() to execute a plain string with tuple or scalar positional parameters.

method sqlalchemy.engine.Connection.execution_options(**opt)

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

The method returns a copy of this Connection which references the same underlying DBAPI connection, but also defines the given execution options which will take effect for a call to execute(). As the new Connection references the same underlying resource, it’s usually a good idea to ensure that the copies will be discarded immediately, which is implicit if used as in:

result = connection.execution_options(stream_results=True).\
                    execute(stmt)

Note that any key/value can be passed to Connection.execution_options(), and it will be stored in the _execution_options dictionary of the Connection. It is suitable for usage by end-user schemes to communicate with event listeners, for example.

The keywords that are currently recognized by SQLAlchemy itself include all those listed under Executable.execution_options(), as well as others that are specific to Connection.

Parameters
  • autocommit – Available on: Connection, statement. When True, a COMMIT will be invoked after execution when executed in ‘autocommit’ mode, i.e. when an explicit transaction is not begun on the connection. Note that DBAPI connections by default are always in a transaction - SQLAlchemy uses rules applied to different kinds of statements to determine if COMMIT will be invoked in order to provide its “autocommit” feature. Typically, all INSERT/UPDATE/DELETE statements as well as CREATE/DROP statements have autocommit behavior enabled; SELECT constructs do not. Use this option when invoking a SELECT or other specific SQL construct where COMMIT is desired (typically when calling stored procedures and such), and an explicit transaction is not in progress.

  • compiled_cache

    Available on: Connection. A dictionary where Compiled objects will be cached when the Connection compiles a clause expression into a Compiled object. This dictionary will supersede the statement cache that may be configured on the Engine itself. If set to None, caching is disabled, even if the engine has a configured cache size.

    Note that the ORM makes use of its own “compiled” caches for some operations, including flush operations. The caching used by the ORM internally supersedes a cache dictionary specified here.

  • isolation_level

    Available on: Connection.

    Set the transaction isolation level for the lifespan of this Connection object. Valid values include those string values accepted by the create_engine.isolation_level parameter passed to create_engine(). These levels are semi-database specific; see individual dialect documentation for valid levels.

    The isolation level option applies the isolation level by emitting statements on the DBAPI connection, and necessarily affects the original Connection object overall, not just the copy that is returned by the call to Connection.execution_options() method. The isolation level will remain at the given setting until the DBAPI connection itself is returned to the connection pool, i.e. the Connection.close() method on the original Connection is called, where an event handler will emit additional statements on the DBAPI connection in order to revert the isolation level change.

    Warning

    The isolation_level execution option should not be used when a transaction is already established, that is, the Connection.begin() method or similar has been called. A database cannot change the isolation level on a transaction in progress, and different DBAPIs and/or SQLAlchemy dialects may implicitly roll back or commit the transaction, or not affect the connection at all.

    Note

    The isolation_level execution option is implicitly reset if the Connection is invalidated, e.g. via the Connection.invalidate() method, or if a disconnection error occurs. The new connection produced after the invalidation will not have the isolation level re-applied to it automatically.

  • no_parameters – When True, if the final parameter list or dictionary is totally empty, will invoke the statement on the cursor as cursor.execute(statement), not passing the parameter collection at all. Some DBAPIs such as psycopg2 and mysql-python consider percent signs as significant only when parameters are present; this option allows code to generate SQL containing percent signs (and possibly other characters) that is neutral regarding whether it’s executed by the DBAPI or piped into a script that’s later invoked by command line tools.

  • stream_results – Available on: Connection, statement. Indicate to the dialect that results should be “streamed” and not pre-buffered, if possible. This is a limitation of many DBAPIs. The flag is currently understood only by the psycopg2, mysqldb and pymysql dialects.

  • schema_translate_map

    Available on: Connection, Engine. A dictionary mapping schema names to schema names, that will be applied to the Table.schema element of each Table encountered when SQL or DDL expression elements are compiled into strings; the resulting schema name will be converted based on presence in the map of the original name.

    New in version 1.1.

method sqlalchemy.engine.Connection.get_execution_options()

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

New in version 1.3.

method sqlalchemy.engine.Connection.get_isolation_level()

Return the current isolation level assigned to this Connection.

This will typically be the default isolation level as determined by the dialect, unless if the Connection.execution_options.isolation_level feature has been used to alter the isolation level on a per-Connection basis.

This attribute will typically perform a live SQL operation in order to procure the current isolation level, so the value returned is the actual level on the underlying DBAPI connection regardless of how this state was set. Compare to the Connection.default_isolation_level accessor which returns the dialect-level setting without performing a SQL query.

New in version 0.9.9.

See also

Connection.default_isolation_level - view default level

create_engine.isolation_level - set per Engine isolation level

Connection.execution_options.isolation_level - set per Connection isolation level

method sqlalchemy.engine.Connection.in_transaction()

Return True if a transaction is in progress.

attribute sqlalchemy.engine.Connection.info

Info dictionary associated with the underlying DBAPI connection referred to by this Connection, allowing user-defined data to be associated with the connection.

The data here will follow along with the DBAPI connection including after it is returned to the connection pool and used again in subsequent instances of Connection.

method sqlalchemy.engine.Connection.invalidate(exception=None)

Invalidate the underlying DBAPI connection associated with this Connection.

The underlying DBAPI connection is literally closed (if possible), and is discarded. Its source connection pool will typically lazily create a new connection to replace it.

Upon the next use (where “use” typically means using the Connection.execute() method or similar), this Connection will attempt to procure a new DBAPI connection using the services of the Pool as a source of connectivity (e.g. a “reconnection”).

If a transaction was in progress (e.g. the Connection.begin() method has been called) when Connection.invalidate() method is called, at the DBAPI level all state associated with this transaction is lost, as the DBAPI connection is closed. The Connection will not allow a reconnection to proceed until the Transaction object is ended, by calling the Transaction.rollback() method; until that point, any attempt at continuing to use the Connection will raise an InvalidRequestError. This is to prevent applications from accidentally continuing an ongoing transactional operations despite the fact that the transaction has been lost due to an invalidation.

The Connection.invalidate() method, just like auto-invalidation, will at the connection pool level invoke the PoolEvents.invalidate() event.

attribute sqlalchemy.engine.Connection.invalidated

Return True if this connection was invalidated.

method sqlalchemy.engine.Connection.run_callable(callable_, *args, **kwargs)

Given a callable object or function, execute it, passing a Connection as the first argument.

Deprecated since version 1.4: The Connection.run_callable() method is deprecated and will be removed in a future release. Use a context manager instead.

The given *args and **kwargs are passed subsequent to the Connection argument.

This function, along with Engine.run_callable(), allows a function to be run with a Connection or Engine object without the need to know which one is being dealt with.

method sqlalchemy.engine.Connection.scalar(object_, *multiparams, **params)

Executes and returns the first column of the first row.

The underlying result/cursor is closed after execution.

method sqlalchemy.engine.Connection.schema_for_object(obj)

Return the schema name for the given schema item taking into account current schema translate map.

method sqlalchemy.engine.Connection.transaction(callable_, *args, **kwargs)

Execute the given function within a transaction boundary.

Deprecated since version 1.4: The Connection.transaction() method is deprecated and will be removed in a future release. Use the Engine.begin() context manager instead.

The function is passed this Connection as the first argument, followed by the given *args and **kwargs, e.g.:

def do_something(conn, x, y):
    conn.execute(text("some statement"), {'x':x, 'y':y})

conn.transaction(do_something, 5, 10)

The operations inside the function are all invoked within the context of a single Transaction. Upon success, the transaction is committed. If an exception is raised, the transaction is rolled back before propagating the exception.

Note

The transaction() method is superseded by the usage of the Python with: statement, which can be used with Connection.begin():

with conn.begin():
    conn.execute(text("some statement"), {'x':5, 'y':10})

As well as with Engine.begin():

with engine.begin() as conn:
    conn.execute(text("some statement"), {'x':5, 'y':10})

See also

Engine.begin() - engine-level transactional context

Engine.transaction() - engine-level version of Connection.transaction()

class sqlalchemy.engine.Connectable

Interface for an object which supports execution of SQL constructs.

Deprecated since version 1.4: The Connectable class is considered legacy as of the 1.x series of SQLAlchemy and will be removed in 2.0. The Engine will be the only Core object that features a .connect() method, and the Connection will be the only object that features an .execute() method. (Background on SQLAlchemy 2.0 at: SQLAlchemy 2.0 Transition)

The two implementations of Connectable are Connection and Engine.

Connectable must also implement the ‘dialect’ member which references a Dialect instance.

method sqlalchemy.engine.Connectable.connect(**kwargs)

Return a Connection object.

Depending on context, this may be self if this object is already an instance of Connection, or a newly procured Connection if this object is an instance of Engine.

attribute sqlalchemy.engine.Connectable.engine = None

The Engine instance referred to by this Connectable.

May be self if this is already an Engine.

method sqlalchemy.engine.Connectable.execute(object_, *multiparams, **params)

Executes the given construct and returns a

method sqlalchemy.engine.Connectable.scalar(object_, *multiparams, **params)

Executes and returns the first column of the first row.

The underlying cursor is closed after execution.

class sqlalchemy.engine.CreateEnginePlugin(url, kwargs)

A set of hooks intended to augment the construction of an Engine object based on entrypoint names in a URL.

The purpose of CreateEnginePlugin is to allow third-party systems to apply engine, pool and dialect level event listeners without the need for the target application to be modified; instead, the plugin names can be added to the database URL. Target applications for CreateEnginePlugin include:

  • connection and SQL performance tools, e.g. which use events to track number of checkouts and/or time spent with statements

  • connectivity plugins such as proxies

Plugins are registered using entry points in a similar way as that of dialects:

entry_points={
    'sqlalchemy.plugins': [
        'myplugin = myapp.plugins:MyPlugin'
    ]

A plugin that uses the above names would be invoked from a database URL as in:

from sqlalchemy import create_engine

engine = create_engine(
  "mysql+pymysql://scott:tiger@localhost/test?plugin=myplugin")

Alternatively, the plugins" argument may be passed as a list to :func:`_sa.create_engine:

engine = create_engine(
  "mysql+pymysql://scott:tiger@localhost/test",
  plugins=["myplugin"])

New in version 1.2.3: plugin names can also be specified to create_engine() as a list

The plugin argument supports multiple instances, so that a URL may specify multiple plugins; they are loaded in the order stated in the URL:

engine = create_engine(
  "mysql+pymysql://scott:tiger@localhost/"
  "test?plugin=plugin_one&plugin=plugin_twp&plugin=plugin_three")

A plugin can receive additional arguments from the URL string as well as from the keyword arguments passed to create_engine(). The URL object and the keyword dictionary are passed to the constructor so that these arguments can be extracted from the url’s URL.query collection as well as from the dictionary:

class MyPlugin(CreateEnginePlugin):
    def __init__(self, url, kwargs):
        self.my_argument_one = url.query.pop('my_argument_one')
        self.my_argument_two = url.query.pop('my_argument_two')
        self.my_argument_three = kwargs.pop('my_argument_three', None)

Arguments like those illustrated above would be consumed from the following:

from sqlalchemy import create_engine

engine = create_engine(
  "mysql+pymysql://scott:tiger@localhost/"
  "test?plugin=myplugin&my_argument_one=foo&my_argument_two=bar",
  my_argument_three='bat')

The URL and dictionary are used for subsequent setup of the engine as they are, so the plugin can modify their arguments in-place. Arguments that are only understood by the plugin should be popped or otherwise removed so that they aren’t interpreted as erroneous arguments afterwards.

When the engine creation process completes and produces the Engine object, it is again passed to the plugin via the CreateEnginePlugin.engine_created() hook. In this hook, additional changes can be made to the engine, most typically involving setup of events (e.g. those defined in Core Events).

New in version 1.1.

method sqlalchemy.engine.CreateEnginePlugin.__init__(url, kwargs)

Construct a new CreateEnginePlugin.

The plugin object is instantiated individually for each call to create_engine(). A single Engine will be passed to the CreateEnginePlugin.engine_created() method corresponding to this URL.

Parameters
  • url – the URL object. The plugin should inspect what it needs here as well as remove its custom arguments from the URL.query collection. The URL can be modified in-place in any other way as well.

  • kwargs – The keyword arguments passed to create_engine(). The plugin can read and modify this dictionary in-place, to affect the ultimate arguments used to create the engine. It should remove its custom arguments from the dictionary as well.

method sqlalchemy.engine.CreateEnginePlugin.engine_created(engine)

Receive the Engine object when it is fully constructed.

The plugin may make additional changes to the engine, such as registering engine or connection pool events.

method sqlalchemy.engine.CreateEnginePlugin.handle_dialect_kwargs(dialect_cls, dialect_args)

parse and modify dialect kwargs

method sqlalchemy.engine.CreateEnginePlugin.handle_pool_kwargs(pool_cls, pool_args)

parse and modify pool kwargs

class sqlalchemy.engine.Engine(pool, dialect, url, logging_name=None, echo=None, query_cache_size=500, execution_options=None, hide_parameters=False)

Bases: sqlalchemy.engine.Connectable, sqlalchemy.log.Identified

Connects a Pool and Dialect together to provide a source of database connectivity and behavior.

An Engine object is instantiated publicly using the create_engine() function.

method sqlalchemy.engine.Engine.begin(close_with_result=False)

Return a context manager delivering a Connection with a Transaction established.

E.g.:

with engine.begin() as conn:
    conn.execute(
        text("insert into table (x, y, z) values (1, 2, 3)")
    )
    conn.execute(text("my_special_procedure(5)"))

Upon successful operation, the Transaction is committed. If an error is raised, the Transaction is rolled back.

The close_with_result flag is normally False, and indicates that the Connection will be closed when the operation is complete. When set to True, it indicates the Connection is in “single use” mode, where the CursorResult returned by the first call to Connection.execute() will close the Connection when that CursorResult has exhausted all result rows.

See also

Engine.connect() - procure a Connection from an Engine.

Connection.begin() - start a Transaction for a particular Connection.

method sqlalchemy.engine.Engine.clear_compiled_cache()

Clear the compiled cache associated with the dialect.

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.engine.Engine.connect(close_with_result=False)

Return a new Connection object.

The Connection object is a facade that uses a DBAPI connection internally in order to communicate with the database. This connection is procured from the connection-holding Pool referenced by this Engine. When the Connection.close() method of the Connection object is called, the underlying DBAPI connection is then returned to the connection pool, where it may be used again in a subsequent call to Engine.connect().

method sqlalchemy.engine.Engine.dispose()

Dispose of the connection pool used by this Engine.

This has the effect of fully closing all currently checked in database connections. Connections that are still checked out will not be closed, however they will no longer be associated with this Engine, so when they are closed individually, eventually the Pool which they are associated with will be garbage collected and they will be closed out fully, if not already closed on checkin.

A new connection pool is created immediately after the old one has been disposed. This new pool, like all SQLAlchemy connection pools, does not make any actual connections to the database until one is first requested, so as long as the Engine isn’t used again, no new connections will be made.

See also

Engine Disposal

attribute sqlalchemy.engine.Engine.driver

Driver name of the Dialect in use by this Engine.

method sqlalchemy.engine.Engine.execute(statement, *multiparams, **params)

Executes the given construct and returns a CursorResult.

Deprecated since version 1.4: The Engine.execute() function/method is considered legacy as of the 1.x series of SQLAlchemy and will be removed in 2.0. All statement execution in SQLAlchemy 2.0 is performed by the Connection.execute() method of Connection, or in the ORM by the Session.execute() method of Session. (Background on SQLAlchemy 2.0 at: SQLAlchemy 2.0 Transition)

The arguments are the same as those used by Connection.execute().

Here, a Connection is acquired using the Engine.connect() method, and the statement executed with that connection. The returned CursorResult is flagged such that when the CursorResult is exhausted and its underlying cursor is closed, the Connection created here will also be closed, which allows its associated DBAPI connection resource to be returned to the connection pool.

method sqlalchemy.engine.Engine.execution_options(**opt)

Return a new Engine that will provide Connection objects with the given execution options.

The returned Engine remains related to the original Engine in that it shares the same connection pool and other state:

  • The Pool used by the new Engine is the same instance. The Engine.dispose() method will replace the connection pool instance for the parent engine as well as this one.

  • Event listeners are “cascaded” - meaning, the new Engine inherits the events of the parent, and new events can be associated with the new Engine individually.

  • The logging configuration and logging_name is copied from the parent Engine.

The intent of the Engine.execution_options() method is to implement “sharding” schemes where multiple Engine objects refer to the same connection pool, but are differentiated by options that would be consumed by a custom event:

primary_engine = create_engine("mysql://")
shard1 = primary_engine.execution_options(shard_id="shard1")
shard2 = primary_engine.execution_options(shard_id="shard2")

Above, the shard1 engine serves as a factory for Connection objects that will contain the execution option shard_id=shard1, and shard2 will produce Connection objects that contain the execution option shard_id=shard2.

An event handler can consume the above execution option to perform a schema switch or other operation, given a connection. Below we emit a MySQL use statement to switch databases, at the same time keeping track of which database we’ve established using the Connection.info dictionary, which gives us a persistent storage space that follows the DBAPI connection:

from sqlalchemy import event
from sqlalchemy.engine import Engine

shards = {"default": "base", shard_1: "db1", "shard_2": "db2"}

@event.listens_for(Engine, "before_cursor_execute")
def _switch_shard(conn, cursor, stmt,
        params, context, executemany):
    shard_id = conn._execution_options.get('shard_id', "default")
    current_shard = conn.info.get("current_shard", None)

    if current_shard != shard_id:
        cursor.execute("use %s" % shards[shard_id])
        conn.info["current_shard"] = shard_id

See also

Connection.execution_options() - update execution options on a Connection object.

Engine.update_execution_options() - update the execution options for a given Engine in place.

Engine.get_execution_options()

method sqlalchemy.engine.Engine.get_execution_options()

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

method sqlalchemy.engine.Engine.has_table(table_name, schema=None)

Return True if the given backend has a table of the given name.

Deprecated since version 1.4: The Engine.has_table() method is deprecated and will be removed in a future release. Please refer to Inspector.has_table().

See also

Fine Grained Reflection with Inspector - detailed schema inspection using the Inspector interface.

quoted_name - used to pass quoting information along with a schema identifier.

attribute sqlalchemy.engine.Engine.name

String name of the Dialect in use by this Engine.

method sqlalchemy.engine.Engine.raw_connection(_connection=None)

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

The returned object is a proxied version of the DBAPI connection object used by the underlying driver in use. The object will have all the same behavior as the real DBAPI connection, except that its close() method will result in the connection being returned to the pool, rather than being closed for real.

This method provides direct DBAPI connection access for special situations when the API provided by Connection is not needed. When a Connection object is already present, the DBAPI connection is available using the Connection.connection accessor.

method sqlalchemy.engine.Engine.run_callable(callable_, *args, **kwargs)

Given a callable object or function, execute it, passing a Connection as the first argument.

Deprecated since version 1.4: The Engine.run_callable() method is deprecated and will be removed in a future release. Use the Engine.connect() context manager instead.

The given *args and **kwargs are passed subsequent to the Connection argument.

This function, along with Connection.run_callable(), allows a function to be run with a Connection or Engine object without the need to know which one is being dealt with.

method sqlalchemy.engine.Engine.scalar(statement, *multiparams, **params)

Executes and returns the first column of the first row.

Deprecated since version 1.4: The Engine.scalar() function/method is considered legacy as of the 1.x series of SQLAlchemy and will be removed in 2.0. All statement execution in SQLAlchemy 2.0 is performed by the Connection.execute() method of Connection, or in the ORM by the Session.execute() method of Session; the Result.scalar() method can then be used to return a scalar result. (Background on SQLAlchemy 2.0 at: SQLAlchemy 2.0 Transition)

The underlying result/cursor is closed after execution.

method sqlalchemy.engine.Engine.table_names(schema=None, connection=None)

Return a list of all table names available in the database.

Deprecated since version 1.4: The Engine.table_names() method is deprecated and will be removed in a future release. Please refer to Inspector.get_table_names().

Parameters
  • schema – Optional, retrieve names from a non-default schema.

  • connection – Optional, use a specified connection.

method sqlalchemy.engine.Engine.transaction(callable_, *args, **kwargs)

Execute the given function within a transaction boundary.

Deprecated since version 1.4: The Engine.transaction() method is deprecated and will be removed in a future release. Use the Engine.begin() context manager instead.

The function is passed a Connection newly procured from Engine.connect() as the first argument, followed by the given *args and **kwargs.

e.g.:

def do_something(conn, x, y):
    conn.execute(text("some statement"), {'x':x, 'y':y})

engine.transaction(do_something, 5, 10)

The operations inside the function are all invoked within the context of a single Transaction. Upon success, the transaction is committed. If an exception is raised, the transaction is rolled back before propagating the exception.

Note

The transaction() method is superseded by the usage of the Python with: statement, which can be used with Engine.begin():

with engine.begin() as conn:
    conn.execute(text("some statement"), {'x':5, 'y':10})

See also

Engine.begin() - engine-level transactional context

Connection.transaction() - connection-level version of Engine.transaction()

method sqlalchemy.engine.Engine.update_execution_options(**opt)

Update the default execution_options dictionary of this Engine.

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.engine.ExceptionContext

Encapsulate information about an error condition in progress.

This object exists solely to be passed to the ConnectionEvents.handle_error() event, supporting an interface that can be extended without backwards-incompatibility.

New in version 0.9.7.

attribute sqlalchemy.engine.ExceptionContext.chained_exception = None

The exception that was returned by the previous handler in the exception chain, if any.

If present, this exception will be the one ultimately raised by SQLAlchemy unless a subsequent handler replaces it.

May be None.

attribute sqlalchemy.engine.ExceptionContext.connection = None

The Connection in use during the exception.

This member is present, except in the case of a failure when first connecting.

attribute sqlalchemy.engine.ExceptionContext.cursor = None

The DBAPI cursor object.

May be None.

attribute sqlalchemy.engine.ExceptionContext.engine = None

The Engine in use during the exception.

This member should always be present, even in the case of a failure when first connecting.

New in version 1.0.0.

attribute sqlalchemy.engine.ExceptionContext.execution_context = None

The ExecutionContext corresponding to the execution operation in progress.

This is present for statement execution operations, but not for operations such as transaction begin/end. It also is not present when the exception was raised before the ExecutionContext could be constructed.

Note that the ExceptionContext.statement and ExceptionContext.parameters members may represent a different value than that of the ExecutionContext, potentially in the case where a ConnectionEvents.before_cursor_execute() event or similar modified the statement/parameters to be sent.

May be None.

attribute sqlalchemy.engine.ExceptionContext.invalidate_pool_on_disconnect = True

Represent whether all connections in the pool should be invalidated when a “disconnect” condition is in effect.

Setting this flag to False within the scope of the ConnectionEvents.handle_error() event will have the effect such that the full collection of connections in the pool will not be invalidated during a disconnect; only the current connection that is the subject of the error will actually be invalidated.

The purpose of this flag is for custom disconnect-handling schemes where the invalidation of other connections in the pool is to be performed based on other conditions, or even on a per-connection basis.

New in version 1.0.3.

attribute sqlalchemy.engine.ExceptionContext.is_disconnect = None

Represent whether the exception as occurred represents a “disconnect” condition.

This flag will always be True or False within the scope of the ConnectionEvents.handle_error() handler.

SQLAlchemy will defer to this flag in order to determine whether or not the connection should be invalidated subsequently. That is, by assigning to this flag, a “disconnect” event which then results in a connection and pool invalidation can be invoked or prevented by changing this flag.

attribute sqlalchemy.engine.ExceptionContext.original_exception = None

The exception object which was caught.

This member is always present.

attribute sqlalchemy.engine.ExceptionContext.parameters = None

Parameter collection that was emitted directly to the DBAPI.

May be None.

attribute sqlalchemy.engine.ExceptionContext.sqlalchemy_exception = None

The sqlalchemy.exc.StatementError which wraps the original, and will be raised if exception handling is not circumvented by the event.

May be None, as not all exception types are wrapped by SQLAlchemy. For DBAPI-level exceptions that subclass the dbapi’s Error class, this field will always be present.

attribute sqlalchemy.engine.ExceptionContext.statement = None

String SQL statement that was emitted directly to the DBAPI.

May be None.

class sqlalchemy.engine.FrozenResult(result)

Represents a Result object in a “frozen” state suitable for caching.

The FrozenResult object is returned from the Result.freeze() method of any Result object.

A new iterable Result object is generatged from a fixed set of data each time the FrozenResult is invoked as a callable:

result = connection.execute(query)

frozen = result.freeze()

r1 = frozen()
r2 = frozen()
# ... etc

New in version 1.4.

class sqlalchemy.engine.IteratorResult(cursor_metadata, iterator, raw=None)

Bases: sqlalchemy.engine.Result

A Result that gets data from a Python iterator of Row objects.

New in version 1.4.

class sqlalchemy.engine.LegacyRow(parent, processors, keymap, key_style, data)

Bases: sqlalchemy.engine.Row

A subclass of Row that delivers 1.x SQLAlchemy behaviors for Core.

The LegacyRow class is where most of the Python mapping (i.e. dictionary-like) behaviors are implemented for the row object. The mapping behavior of Row going forward is accessible via the _mapping attribute.

New in version 1.4: - added LegacyRow which encapsulates most of the deprecated behaviors of Row.

method sqlalchemy.engine.LegacyRow.has_key(key)

Return True if this LegacyRow contains the given key.

Deprecated since version 1.4: The LegacyRow.has_key() method is deprecated and will be removed in a future release. To test for key membership, use the Row._mapping attribute, i.e. ‘key in row._mapping`.

Through the SQLAlchemy 1.x series, the __contains__() method of Row (or LegacyRow as of SQLAlchemy 1.4) also links to Row.has_key(), in that an expression such as

"some_col" in row

Will return True if the row contains a column named "some_col", in the way that a Python mapping works.

However, it is planned that the 2.0 series of SQLAlchemy will reverse this behavior so that __contains__() will refer to a value being present in the row, in the way that a Python tuple works.

method sqlalchemy.engine.LegacyRow.items()

Return a list of tuples, each tuple containing a key/value pair.

Deprecated since version 1.4: The LegacyRow.items() method is deprecated and will be removed in a future release. Use the Row._mapping attribute, i.e., ‘row._mapping.items()’.

This method is analogous to the Python dictionary .items() method, except that it returns a list, not an iterator.

method sqlalchemy.engine.LegacyRow.iterkeys()

Return a an iterator against the Row.keys() method.

Deprecated since version 1.4: The LegacyRow.iterkeys() method is deprecated and will be removed in a future release. Use the Row._mapping attribute, i.e., ‘row._mapping.keys()’.

This method is analogous to the Python-2-only dictionary .iterkeys() method.

method sqlalchemy.engine.LegacyRow.itervalues()

Return a an iterator against the Row.values() method.

Deprecated since version 1.4: The LegacyRow.itervalues() method is deprecated and will be removed in a future release. Use the Row._mapping attribute, i.e., ‘row._mapping.values()’.

This method is analogous to the Python-2-only dictionary .itervalues() method.

method sqlalchemy.engine.LegacyRow.values()

Return the values represented by this Row as a list.

Deprecated since version 1.4: The LegacyRow.values() method is deprecated and will be removed in a future release. Use the Row._mapping attribute, i.e., ‘row._mapping.values()’.

This method is analogous to the Python dictionary .values() method, except that it returns a list, not an iterator.

class sqlalchemy.engine.MergedResult(cursor_metadata, results)

Bases: sqlalchemy.engine.IteratorResult

A Result that is merged from any number of Result objects.

Returned by the Result.merge() method.

New in version 1.4.

class sqlalchemy.engine.NestedTransaction(connection)

Bases: sqlalchemy.engine.Transaction

Represent a ‘nested’, or SAVEPOINT transaction.

A new NestedTransaction object may be procured using the Connection.begin_nested() method.

The interface is the same as that of Transaction.

class sqlalchemy.engine.Result(cursor_metadata)

Bases: sqlalchemy.sql.expression.InPlaceGenerative

Represent a set of database results.

New in version 1.4: The Result object provides a completely updated usage model and calling facade for SQLAlchemy Core and SQLAlchemy ORM. In Core, it forms the basis of the CursorResult object which replaces the previous ResultProxy interface.

method sqlalchemy.engine.Result.all()

Return all rows in a list.

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

New in version 1.4.

Returns

a list of Row objects if no filters are applied. When filters are applied, such as Result.mappings() or Result.scalar(), different kinds of objects may be returned.

method sqlalchemy.engine.Result.columns(*col_expressions)

Establish the columns that should be returned in each row.

This method may be used to limit the columns returned as well as to reorder them. The given list of expressions are normally a series of integers or string key names. They may also be appropriate ColumnElement objects which correspond to a given statement construct.

E.g.:

statement = select(table.c.x, table.c.y, table.c.z)
result = connection.execute(statement)

for z, y in result.columns('z', 'y'):
    # ...

Example of using the column objects from the statement itself:

for z, y in result.columns(
        statement.selected_columns.c.z,
        statement.selected_columns.c.y
):
    # ...

New in version 1.4.

Parameters

*col_expressions – indicates columns to be returned. Elements may be integer row indexes, string column names, or appropriate ColumnElement objects corresponding to a select construct.

Returns

this Result object with the modifications given.

method sqlalchemy.engine.Result.fetchall()

A synonym for the Result.all() method.

method sqlalchemy.engine.Result.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 Result.partitions() method.

Returns

a list of Row objects if no filters are applied. When filters are applied, such as Result.mappings() or Result.scalar(), different kinds of objects may be returned.

method sqlalchemy.engine.Result.fetchone()

Fetch one row.

When all rows are exhausted, returns None.

Note

This method is not compatible with the Result.scalars() filter, as there is no way to distinguish between a data value of None and the ending value. Prefer to use iterative / collection methods which support scalar None values.

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. When filters are applied, such as Result.mappings() or Result.scalar(), different kinds of objects may be returned.

method sqlalchemy.engine.Result.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 Result.scalar() method, or combine Result.scalars() and Result.first().

Returns

a Row object if no filters are applied, or None if no rows remain. When filters are applied, such as Result.mappings() or Result.scalars(), different kinds of objects may be returned.

method sqlalchemy.engine.Result.freeze()

Return a callable object that will produce copies of this Result 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.

method sqlalchemy.engine.Result.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.

method sqlalchemy.engine.Result.mappings()

Apply a mappings filter to returned rows.

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

This filter cancels out other filters that may be established such as that of Result.scalars().

New in version 1.4.

Returns

this Result object with modifications.

method sqlalchemy.engine.Result.merge(*others)

Merge this Result 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.

method sqlalchemy.engine.Result.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 Result.scalar_one() method, or combine Result.scalars() and Result.one().

New in version 1.4.

Returns

The first Row. When filters are applied, such as Result.mappings() or Result.scalar(), different kinds of objects may be returned.

Raises

MultipleResultsFound, NoResultFound

method sqlalchemy.engine.Result.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. When filters are applied, such as Result.mappings() or Result.scalar(), different kinds of objects may be returned.

Raises

MultipleResultsFound

method sqlalchemy.engine.Result.partitions(size=None)

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

Each list will be of the size given, excluding the last list to be yielded, which may have a small number of rows. No empty lists will be yielded.

The result object is automatically closed when the iterator is fully consumed.

Note that the backend driver will usually buffer the entire result ahead of time unless the Connection.execution_options.stream_results execution option is used indicating that the driver should not pre-buffer results, if possible. Not all drivers support this option and the option is silently ignored for those who do.

New in version 1.4.

Parameters

size – indicate the maximum number of rows to be present in each list yielded. If None, makes use of the value set by Result.yield_per(), if present, otherwise uses the Result.fetchmany() default which may be backend specific.

Returns

iterator of lists

method sqlalchemy.engine.Result.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.

method sqlalchemy.engine.Result.scalar_one()

Return exactly one scalar result or raise an exception.

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

method sqlalchemy.engine.Result.scalar_one_or_none()

Return exactly one or no scalar result.

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

method sqlalchemy.engine.Result.scalars(index=0)

Apply a scalars filter to returned rows.

When this filter is applied, fetching results will return Python scalar objects from exactly one column of each row, rather than Row objects or mappings.

This filter cancels out other filters that may be established such as that of Result.mappings().

New in version 1.4.

Parameters

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

Returns

this Result object with modifications.

method sqlalchemy.engine.Result.unique(strategy=None)

Apply unique filtering to the objects returned by this Result.

When this filter is applied with no arguments, the rows or objects returned will filtered such that each row is returned uniquely. The algorithm used to determine this uniqueness is by default the Python hashing identity of the whole tuple. In some cases a specialized per-entity hashing scheme may be used, such as when using the ORM, a scheme is applied which works against the primary key identity of returned objects.

The unique filter is applied after all other filters, which means if the columns returned have been refined using a method such as the Result.columns() or Result.scalars() method, the uniquing is applied to only the column or columns returned. This occurs regardless of the order in which these methods have been called upon the Result object.

The unique filter also changes the calculus used for methods like Result.fetchmany() and Result.partitions(). When using Result.unique(), these methods will continue to yield the number of rows or objects requested, after uniquing has been applied. However, this necessarily impacts the buffering behavior of the underlying cursor or datasource, such that multiple underlying calls to cursor.fetchmany() may be necessary in order to accumulate enough objects in order to provide a unique collection of the requested size.

Parameters

strategy – a callable that will be applied to rows or objects being iterated, which should return an object that represents the unique value of the row. A Python set() is used to store these identities. If not passed, a default uniqueness strategy is used which may have been assembled by the source of this Result object.

method sqlalchemy.engine.Result.yield_per(num)

Configure the row-fetching strategy to fetch num rows at a time.

This impacts the underlying behavior of the result when iterating over the result object, or otherwise making use of methods such as Result.fetchone() that return one row at a time. Data from the underlying cursor or other data source will be buffered up to this many rows in memory, and the buffered collection will then be yielded out one row at at time or as many rows are requested. Each time the buffer clears, it will be refreshed to this many rows or as many rows remain if fewer remain.

The Result.yield_per() method is generally used in conjunction with the Connection.execution_options.stream_results execution option, which will allow the database dialect in use to make use of a server side cursor, if the DBAPI supports it.

Most DBAPIs do not use server side cursors by default, which means all rows will be fetched upfront from the database regardless of the Result.yield_per() setting. However, Result.yield_per() may still be useful in that it batches the SQLAlchemy-side processing of the raw data from the database, and additionally when used for ORM scenarios will batch the conversion of database rows into ORM entity rows.

New in version 1.4.

Parameters

num – number of rows to fetch each time the buffer is refilled. If set to a value below 1, fetches all rows for the next buffer.

class sqlalchemy.engine.CursorResult(context, cursor_strategy, cursor_description)

Bases: sqlalchemy.engine.BaseCursorResult, sqlalchemy.engine.Result

A Result that is representing state from a DBAPI cursor.

Changed in version 1.4: The CursorResult and LegacyCursorResult classes replace the previous ResultProxy interface. These classes are based on the Result calling API which provides an updated usage model and calling facade for SQLAlchemy Core and SQLAlchemy ORM.

Returns database rows via the Row class, which provides additional API features and behaviors on top of the raw data returned by the DBAPI. Through the use of filters such as the Result.scalars() method, other kinds of objects may also be returned.

Within the scope of the 1.x series of SQLAlchemy, Core SQL results in version 1.4 return an instance of LegacyCursorResult which takes the place of the CursorResult class used for the 1.3 series and previously. This object returns rows as LegacyRow objects, which maintains Python mapping (i.e. dictionary) like behaviors upon the object itself. Going forward, the Row._mapping attribute should be used for dictionary behaviors.

See also

Selecting - introductory material for accessing CursorResult and Row objects.

method sqlalchemy.engine.CursorResult.all()

inherited from the Result.all() method of Result

Return all rows in a list.

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

New in version 1.4.

Returns

a list of Row objects if no filters are applied. When filters are applied, such as Result.mappings() or Result.scalar(), different kinds of objects may be returned.

method sqlalchemy.engine.CursorResult.close()

Close this CursorResult.

This closes out the underlying DBAPI cursor corresponding to the statement execution, if one is still present. Note that the DBAPI cursor is automatically released when the CursorResult exhausts all available rows. CursorResult.close() is generally an optional method except in the case when discarding a CursorResult that still has additional rows pending for fetch.

After this method is called, it is no longer valid to call upon the fetch methods, which will raise a ResourceClosedError on subsequent use.

method sqlalchemy.engine.CursorResult.columns(*col_expressions)

inherited from the Result.columns() method of Result

Establish the columns that should be returned in each row.

This method may be used to limit the columns returned as well as to reorder them. The given list of expressions are normally a series of integers or string key names. They may also be appropriate ColumnElement objects which correspond to a given statement construct.

E.g.:

statement = select(table.c.x, table.c.y, table.c.z)
result = connection.execute(statement)

for z, y in result.columns('z', 'y'):
    # ...

Example of using the column objects from the statement itself:

for z, y in result.columns(
        statement.selected_columns.c.z,
        statement.selected_columns.c.y
):
    # ...

New in version 1.4.

Parameters

*col_expressions – indicates columns to be returned. Elements may be integer row indexes, string column names, or appropriate ColumnElement objects corresponding to a select construct.

Returns

this Result object with the modifications given.

method sqlalchemy.engine.CursorResult.fetchall()

inherited from the Result.fetchall() method of Result

A synonym for the Result.all() method.

method sqlalchemy.engine.CursorResult.fetchmany(size=None)

inherited from the Result.fetchmany() method of Result

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 Result.partitions() method.

Returns

a list of Row objects if no filters are applied. When filters are applied, such as Result.mappings() or Result.scalar(), different kinds of objects may be returned.

method sqlalchemy.engine.CursorResult.fetchone()

inherited from the Result.fetchone() method of Result

Fetch one row.

When all rows are exhausted, returns None.

Note

This method is not compatible with the Result.scalars() filter, as there is no way to distinguish between a data value of None and the ending value. Prefer to use iterative / collection methods which support scalar None values.

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. When filters are applied, such as Result.mappings() or Result.scalar(), different kinds of objects may be returned.

method sqlalchemy.engine.CursorResult.first()

inherited from the Result.first() method of Result

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 Result.scalar() method, or combine Result.scalars() and Result.first().

Returns

a Row object if no filters are applied, or None if no rows remain. When filters are applied, such as Result.mappings() or Result.scalars(), different kinds of objects may be returned.

method sqlalchemy.engine.CursorResult.freeze()

inherited from the Result.freeze() method of Result

Return a callable object that will produce copies of this Result 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.

attribute sqlalchemy.engine.CursorResult.inserted_primary_key

Return the primary key for the row just inserted.

The return value is a list of scalar values corresponding to the list of primary key columns in the target table.

This only applies to single row insert() constructs which did not explicitly specify Insert.returning().

Note that primary key columns which specify a server_default clause, or otherwise do not qualify as “autoincrement” columns (see the notes at Column), and were generated using the database-side default, will appear in this list as None unless the backend supports “returning” and the insert statement executed with the “implicit returning” enabled.

Raises InvalidRequestError if the executed statement is not a compiled expression construct or is not an insert() construct.

attribute sqlalchemy.engine.CursorResult.inserted_primary_key_rows

Return a list of tuples, each containing the primary key for each row just inserted.

Usually, this method will return at most a list with a single entry which is the same row one would get back from CursorResult.inserted_primary_key. To support “executemany with INSERT” mode, multiple rows can be part of the list returned.

New in version 1.4.

attribute sqlalchemy.engine.CursorResult.is_insert

inherited from the BaseCursorResult.is_insert attribute of BaseCursorResult

True if this CursorResult is the result of a executing an expression language compiled insert() construct.

When True, this implies that the inserted_primary_key attribute is accessible, assuming the statement did not include a user defined “returning” construct.

method sqlalchemy.engine.CursorResult.keys()

inherited from the Result.keys() method of Result

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.

method sqlalchemy.engine.CursorResult.last_inserted_params()

Return the collection of inserted parameters from this execution.

Raises InvalidRequestError if the executed statement is not a compiled expression construct or is not an insert() construct.

method sqlalchemy.engine.CursorResult.last_updated_params()

Return the collection of updated parameters from this execution.

Raises InvalidRequestError if the executed statement is not a compiled expression construct or is not an update() construct.

method sqlalchemy.engine.CursorResult.lastrow_has_defaults()

Return lastrow_has_defaults() from the underlying ExecutionContext.

See ExecutionContext for details.

attribute sqlalchemy.engine.CursorResult.lastrowid

inherited from the BaseCursorResult.lastrowid attribute of BaseCursorResult

Return the ‘lastrowid’ accessor on the DBAPI cursor.

This is a DBAPI specific method and is only functional for those backends which support it, for statements where it is appropriate. It’s behavior is not consistent across backends.

Usage of this method is normally unnecessary when using insert() expression constructs; the CursorResult.inserted_primary_key attribute provides a tuple of primary key values for a newly inserted row, regardless of database backend.

method sqlalchemy.engine.CursorResult.mappings()

inherited from the Result.mappings() method of Result

Apply a mappings filter to returned rows.

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

This filter cancels out other filters that may be established such as that of Result.scalars().

New in version 1.4.

Returns

this Result object with modifications.

method sqlalchemy.engine.CursorResult.merge(*others)

Merge this Result 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.

method sqlalchemy.engine.CursorResult.one()

inherited from the Result.one() method of Result

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 Result.scalar_one() method, or combine Result.scalars() and Result.one().

New in version 1.4.

Returns

The first Row. When filters are applied, such as Result.mappings() or Result.scalar(), different kinds of objects may be returned.

Raises

MultipleResultsFound, NoResultFound

method sqlalchemy.engine.CursorResult.one_or_none()

inherited from the Result.one_or_none() method of Result

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. When filters are applied, such as Result.mappings() or Result.scalar(), different kinds of objects may be returned.

Raises

MultipleResultsFound

method sqlalchemy.engine.CursorResult.partitions(size=None)

inherited from the Result.partitions() method of Result

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

Each list will be of the size given, excluding the last list to be yielded, which may have a small number of rows. No empty lists will be yielded.

The result object is automatically closed when the iterator is fully consumed.

Note that the backend driver will usually buffer the entire result ahead of time unless the Connection.execution_options.stream_results execution option is used indicating that the driver should not pre-buffer results, if possible. Not all drivers support this option and the option is silently ignored for those who do.

New in version 1.4.

Parameters

size – indicate the maximum number of rows to be present in each list yielded. If None, makes use of the value set by Result.yield_per(), if present, otherwise uses the Result.fetchmany() default which may be backend specific.

Returns

iterator of lists

method sqlalchemy.engine.CursorResult.postfetch_cols()

Return postfetch_cols() from the underlying ExecutionContext.

See ExecutionContext for details.

Raises InvalidRequestError if the executed statement is not a compiled expression construct or is not an insert() or update() construct.

method sqlalchemy.engine.CursorResult.prefetch_cols()

inherited from the BaseCursorResult.prefetch_cols() method of BaseCursorResult

Return prefetch_cols() from the underlying ExecutionContext.

See ExecutionContext for details.

Raises InvalidRequestError if the executed statement is not a compiled expression construct or is not an insert() or update() construct.

attribute sqlalchemy.engine.CursorResult.returned_defaults

inherited from the BaseCursorResult.returned_defaults attribute of BaseCursorResult

Return the values of default columns that were fetched using the ValuesBase.return_defaults() feature.

The value is an instance of Row, or None if ValuesBase.return_defaults() was not used or if the backend does not support RETURNING.

New in version 0.9.0.

attribute sqlalchemy.engine.CursorResult.returned_defaults_rows

Return a list of rows each containing the values of default columns that were fetched using the ValuesBase.return_defaults() feature.

The return value is a list of Row objects.

New in version 1.4.

attribute sqlalchemy.engine.CursorResult.returns_rows

inherited from the BaseCursorResult.returns_rows attribute of BaseCursorResult

True if this CursorResult returns zero or more rows.

I.e. if it is legal to call the methods CursorResult.fetchone(), CursorResult.fetchmany() CursorResult.fetchall().

Overall, the value of CursorResult.returns_rows should always be synonymous with whether or not the DBAPI cursor had a .description attribute, indicating the presence of result columns, noting that a cursor that returns zero rows still has a .description if a row-returning statement was emitted.

This attribute should be True for all results that are against SELECT statements, as well as for DML statements INSERT/UPDATE/DELETE that use RETURNING. For INSERT/UPDATE/DELETE statements that were not using RETURNING, the value will usually be False, however there are some dialect-specific exceptions to this, such as when using the MSSQL / pyodbc dialect a SELECT is emitted inline in order to retrieve an inserted primary key value.

attribute sqlalchemy.engine.CursorResult.rowcount

inherited from the BaseCursorResult.rowcount attribute of BaseCursorResult

Return the ‘rowcount’ for this result.

The ‘rowcount’ reports the number of rows matched by the WHERE criterion of an UPDATE or DELETE statement.

Note

Notes regarding CursorResult.rowcount:

  • This attribute returns the number of rows matched, which is not necessarily the same as the number of rows that were actually modified - an UPDATE statement, for example, may have no net change on a given row if the SET values given are the same as those present in the row already. Such a row would be matched but not modified. On backends that feature both styles, such as MySQL, rowcount is configured by default to return the match count in all cases.

  • CursorResult.rowcount is only useful in conjunction with an UPDATE or DELETE statement. Contrary to what the Python DBAPI says, it does not return the number of rows available from the results of a SELECT statement as DBAPIs cannot support this functionality when rows are unbuffered.

  • CursorResult.rowcount may not be fully implemented by all dialects. In particular, most DBAPIs do not support an aggregate rowcount result from an executemany call. The CursorResult.supports_sane_rowcount() and CursorResult.supports_sane_multi_rowcount() methods will report from the dialect if each usage is known to be supported.

  • Statements that use RETURNING may not return a correct rowcount.

method sqlalchemy.engine.CursorResult.scalar()

inherited from the Result.scalar() method of Result

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.

method sqlalchemy.engine.CursorResult.scalar_one()

inherited from the Result.scalar_one() method of Result

Return exactly one scalar result or raise an exception.

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

method sqlalchemy.engine.CursorResult.scalar_one_or_none()

inherited from the Result.scalar_one_or_none() method of Result

Return exactly one or no scalar result.

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

method sqlalchemy.engine.CursorResult.scalars(index=0)

inherited from the Result.scalars() method of Result

Apply a scalars filter to returned rows.

When this filter is applied, fetching results will return Python scalar objects from exactly one column of each row, rather than Row objects or mappings.

This filter cancels out other filters that may be established such as that of Result.mappings().

New in version 1.4.

Parameters

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

Returns

this Result object with modifications.

method sqlalchemy.engine.CursorResult.supports_sane_multi_rowcount()

Return supports_sane_multi_rowcount from the dialect.

See CursorResult.rowcount for background.

method sqlalchemy.engine.CursorResult.supports_sane_rowcount()

Return supports_sane_rowcount from the dialect.

See CursorResult.rowcount for background.

method sqlalchemy.engine.CursorResult.unique(strategy=None)

inherited from the Result.unique() method of Result

Apply unique filtering to the objects returned by this Result.

When this filter is applied with no arguments, the rows or objects returned will filtered such that each row is returned uniquely. The algorithm used to determine this uniqueness is by default the Python hashing identity of the whole tuple. In some cases a specialized per-entity hashing scheme may be used, such as when using the ORM, a scheme is applied which works against the primary key identity of returned objects.

The unique filter is applied after all other filters, which means if the columns returned have been refined using a method such as the Result.columns() or Result.scalars() method, the uniquing is applied to only the column or columns returned. This occurs regardless of the order in which these methods have been called upon the Result object.

The unique filter also changes the calculus used for methods like Result.fetchmany() and Result.partitions(). When using Result.unique(), these methods will continue to yield the number of rows or objects requested, after uniquing has been applied. However, this necessarily impacts the buffering behavior of the underlying cursor or datasource, such that multiple underlying calls to cursor.fetchmany() may be necessary in order to accumulate enough objects in order to provide a unique collection of the requested size.

Parameters

strategy – a callable that will be applied to rows or objects being iterated, which should return an object that represents the unique value of the row. A Python set() is used to store these identities. If not passed, a default uniqueness strategy is used which may have been assembled by the source of this Result object.

method sqlalchemy.engine.CursorResult.yield_per(num)

Configure the row-fetching strategy to fetch num rows at a time.

This impacts the underlying behavior of the result when iterating over the result object, or otherwise making use of methods such as Result.fetchone() that return one row at a time. Data from the underlying cursor or other data source will be buffered up to this many rows in memory, and the buffered collection will then be yielded out one row at at time or as many rows are requested. Each time the buffer clears, it will be refreshed to this many rows or as many rows remain if fewer remain.

The Result.yield_per() method is generally used in conjunction with the Connection.execution_options.stream_results execution option, which will allow the database dialect in use to make use of a server side cursor, if the DBAPI supports it.

Most DBAPIs do not use server side cursors by default, which means all rows will be fetched upfront from the database regardless of the Result.yield_per() setting. However, Result.yield_per() may still be useful in that it batches the SQLAlchemy-side processing of the raw data from the database, and additionally when used for ORM scenarios will batch the conversion of database rows into ORM entity rows.

New in version 1.4.

Parameters

num – number of rows to fetch each time the buffer is refilled. If set to a value below 1, fetches all rows for the next buffer.

class sqlalchemy.engine.LegacyCursorResult(context, cursor_strategy, cursor_description)

Bases: sqlalchemy.engine.CursorResult

Legacy version of CursorResult.

This class includes connection “connection autoclose” behavior for use with “connectionless” execution, as well as delivers rows using the LegacyRow row implementation.

New in version 1.4.

method sqlalchemy.engine.LegacyCursorResult.close()

Close this LegacyCursorResult.

This method has the same behavior as that of sqlalchemy.engine.CursorResult(), but it also may close the underlying Connection for the case of “connectionless” execution.

Deprecated since version 2.0: “connectionless” execution is deprecated and will be removed in version 2.0. Version 2.0 will feature the Result object that will no longer affect the status of the originating connection in any case.

After this method is called, it is no longer valid to call upon the fetch methods, which will raise a ResourceClosedError on subsequent use.

class sqlalchemy.engine.Row(parent, processors, keymap, key_style, data)

Bases: sqlalchemy.engine.BaseRow, collections.abc.Sequence

Represent a single result row.

The Row object represents a row of a database result. It is typically associated in the 1.x series of SQLAlchemy with the CursorResult object, however is also used by the ORM for tuple-like results as of SQLAlchemy 1.4.

The Row object seeks to act as much like a Python named tuple as possible. For mapping (i.e. dictionary) behavior on a row, such as testing for containment of keys, refer to the Row._mapping attribute.

See also

Selecting - includes examples of selecting rows from SELECT statements.

LegacyRow - Compatibility interface introduced in SQLAlchemy 1.4.

Changed in version 1.4: Renamed RowProxy to Row. Row is no longer a “proxy” object in that it contains the final form of data within it, and now acts mostly like a named tuple. Mapping-like functionality is moved to the Row._mapping attribute, but will remain available in SQLAlchemy 1.x series via the LegacyRow class that is used by LegacyCursorResult. See RowProxy is no longer a “proxy”; is now called Row and behaves like an enhanced named tuple for background on this change.

method sqlalchemy.engine.Row._asdict()

Return a new dict which maps field names to their corresponding values.

This method is analogous to the Python named tuple ._asdict() method, and works by applying the dict() constructor to the Row._mapping attribute.

New in version 1.4.

See also

Row._mapping

attribute sqlalchemy.engine.Row._fields

Return a tuple of string keys as represented by this Row.

This attribute is analogous to the Python named tuple ._fields attribute.

New in version 1.4.

See also

Row._mapping

attribute sqlalchemy.engine.Row._mapping

Return a RowMapping for this Row.

This object provides a consistent Python mapping (i.e. dictionary) interface for the data contained within the row. The Row by itself behaves like a named tuple, however in the 1.4 series of SQLAlchemy, the LegacyRow class is still used by Core which continues to have mapping-like behaviors against the row object itself.

See also

Row._fields

New in version 1.4.

method sqlalchemy.engine.Row.keys()

Return the list of keys as strings represented by this Row.

Deprecated since version 1.4: The Row.keys() function/method is considered legacy as of the 1.x series of SQLAlchemy and will be removed in 2.0. Use the namedtuple standard accessor Row._fields, or for full mapping behavior use row._mapping.keys() (Background on SQLAlchemy 2.0 at: SQLAlchemy 2.0 Transition)

This method is analogous to the Python dictionary .keys() method, except that it returns a list, not an iterator.

class sqlalchemy.engine.RowMapping(parent, processors, keymap, key_style, data)

Bases: sqlalchemy.engine.BaseRow, collections.abc.Mapping

A Mapping that maps column names and objects to Row values.

The RowMapping is available from a Row via the Row._mapping attribute and supplies Python mapping (i.e. dictionary) access to the contents of the row. This includes support for testing of containment of specific keys (string column names or objects), as well as iteration of keys, values, and items:

for row in result:
    if 'a' in row._mapping:
        print("Column 'a': %s" % row._mapping['a'])

    print("Column b: %s" % row._mapping[table.c.b])

New in version 1.4: The RowMapping object replaces the mapping-like access previously provided by a database result row, which now seeks to behave mostly like a named tuple.

method sqlalchemy.engine.RowMapping.items()

Return a view of key/value tuples for the elements in the underlying Row.

method sqlalchemy.engine.RowMapping.keys()

Return a view of ‘keys’ for string column names represented by the underlying Row.

method sqlalchemy.engine.RowMapping.values()

Return a view of values for the values represented in the underlying Row.

class sqlalchemy.engine.Transaction(connection)

Represent a database transaction in progress.

The Transaction object is procured by calling the Connection.begin() method of Connection:

from sqlalchemy import create_engine
engine = create_engine("postgresql://scott:tiger@localhost/test")
connection = engine.connect()
trans = connection.begin()
connection.execute(text("insert into x (a, b) values (1, 2)"))
trans.commit()

The object provides rollback() and commit() methods in order to control transaction boundaries. It also implements a context manager interface so that the Python with statement can be used with the Connection.begin() method:

with connection.begin():
    connection.execute(text("insert into x (a, b) values (1, 2)"))

The Transaction object is not threadsafe.

method sqlalchemy.engine.Transaction.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.

method sqlalchemy.engine.Transaction.commit()

Commit this Transaction.

method sqlalchemy.engine.Transaction.rollback()

Roll back this Transaction.

class sqlalchemy.engine.TwoPhaseTransaction(connection, xid)

Bases: sqlalchemy.engine.RootTransaction

Represent a two-phase transaction.

A new TwoPhaseTransaction object may be procured using the Connection.begin_twophase() method.

The interface is the same as that of Transaction with the addition of the prepare() method.

method sqlalchemy.engine.TwoPhaseTransaction.prepare()

Prepare this TwoPhaseTransaction.

After a PREPARE, the transaction can be committed.

Previous: Engine Configuration Next: Connection Pooling