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.
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.
tip
When using an Engine
with multiple Python processes, such as when
using os.fork
or Python multiprocessing
, it’s important that the
engine is initialized per process. See Using Connection Pools with Multiprocessing or os.fork() for
details.
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 (1.x API).
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.
Deprecated since version 1.4: The “transaction nesting” feature of SQLAlchemy is a legacy feature that is deprecated in the 1.4 release and will be removed in SQLAlchemy 2.0. The pattern has proven to be a little too awkward and complicated, unless an application makes more of a first-class framework around the behavior. See the following subsection Arbitrary Transaction Nesting as an Antipattern.
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.
With many years of experience, the above “nesting” pattern has not proven to be very popular, and where it has been observed in large projects such as Openstack, it tends to be complicated.
The most ideal way to organize an application would have a single, or at least very few, points at which the “beginning” and “commit” of all database transactions is demarcated. This is also the general idea discussed in terms of the ORM at When do I construct a Session, when do I commit it, and when do I close it?. To adapt the example from the previous section to this practice looks like:
# method_a calls method_b
def method_a(connection):
method_b(connection)
# method_b uses the connection and assumes the transaction
# is external
def method_b(connection):
connection.execute(text("insert into mytable values ('bat', 'lala')"))
connection.execute(mytable.insert(), {"col1": "bat", "col2": "lala"})
# open a Connection inside of a transaction and call method_a
with engine.begin() as conn:
method_a(conn)
That is, method_a()
and method_b()
do not deal with the details
of the transaction at all; the transactional scope of the connection is
defined externally to the functions that have a SQL dialogue with the
connection.
It may be observed that the above code has fewer lines, and less indentation
which tends to correlate with lower cyclomatic complexity. The
above code is organized such that method_a()
and method_b()
are always
invoked from a point at which a transaction is begun. The previous
version of the example features a method_a()
and a method_b()
that are
trying to be agnostic of this fact, which suggests they are prepared for
at least twice as many potential codepaths through them.
As SQLAlchemy’s intrinsic-nested pattern is considered legacy, an application that for either legacy or novel reasons still seeks to have a context that automatically frames transactions should seek to maintain this functionality through the use of a custom Python context manager. A similar example is also provided in terms of the ORM in the “seealso” section below.
To provide backwards compatibility for applications that make use of this pattern, the following context manager or a similar implementation based on a decorator may be used:
import contextlib
@contextlib.contextmanager
def transaction(connection):
if not connection.in_transaction():
with connection.begin():
yield connection
else:
yield connection
The above contextmanager would be used as:
# method_a starts a transaction and calls method_b
def method_a(connection):
with transaction(connection): # open a transaction
method_b(connection)
# method_b either starts a transaction, or uses the one already
# present
def method_b(connection):
with transaction(connection): # open a 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)
A similar approach may be taken such that connectivity is established on demand as well; the below approach features a single-use context manager that accesses an enclosing state in order to test if connectivity is already present:
import contextlib
def connectivity(engine):
connection = None
@contextlib.contextmanager
def connect():
nonlocal connection
if connection is None:
connection = engine.connect()
with connection:
with connection.begin():
yield connection
else:
yield connection
return connect
Using the above would look like:
# method_a passes along connectivity context, at the same time
# it chooses to establish a connection by calling "with"
def method_a(connectivity):
with connectivity():
method_b(connectivity)
# method_b also wants to use a connection from the context, so it
# also calls "with:", but also it actually uses the connection.
def method_b(connectivity):
with connectivity() as connection:
connection.execute(text("insert into mytable values ('bat', 'lala')"))
connection.execute(mytable.insert(), {"col1": "bat", "col2": "lala"})
# create a new connection/transaction context object and call
# method_a
method_a(connectivity(engine))
The above context manager acts not only as a “transaction” context but also
as a context that manages having an open connection against a particular
Engine
. When using the ORM Session
, this
connectivty management is provided by the Session
itself.
An overview of ORM connectivity patterns is at Managing Transactions.
See also
Migrating from the “subtransaction” pattern - ORM version
Deprecated since version 1.4: The “autocommit” feature of SQLAlchemy Core is deprecated and will not be present in version 2.0 of SQLAlchemy. DBAPI-level AUTOCOMMIT is now widely available which offers superior performance and occurs transparently. See Library-level (but not driver level) “Autocommit” removed from both Core and ORM for background.
Note
This section discusses the feature within SQLAlchemy that automatically
invokes the .commit()
method on a DBAPI connection, however this is against
a DBAPI connection that is itself transactional. For true AUTOCOMMIT,
see the next section Setting Transaction Isolation Levels including DBAPI Autocommit.
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()"))
Most DBAPIs support the concept of configurable transaction isolation levels. These are traditionally the four levels “READ UNCOMMITTED”, “READ COMMITTED”, “REPEATABLE READ” and “SERIALIZABLE”. These are usually applied to a DBAPI connection before it begins a new transaction, noting that most DBAPIs will begin this transaction implicitly when SQL statements are first emitted.
DBAPIs that support isolation levels also usually support the concept of true “autocommit”, which means that the DBAPI connection itself will be placed into a non-transactional autocommit mode. This usually means that the typical DBAPI behavior of emitting “BEGIN” to the database automatically no longer occurs, but it may also include other directives. SQLAlchemy treats the concept of “autocommit” like any other isolation level; in that it is an isolation level that loses not only “read committed” but also loses atomicity.
Tip
It is important to note, as will be discussed further in the section below at
Understanding the DBAPI-Level Autocommit Isolation Level, that “autocommit” isolation level like
any other isolation level does not affect the “transactional” behavior of
the Connection
object, which continues to call upon DBAPI
.commit()
and .rollback()
methods (they just have no effect under
autocommit), and for which the .begin()
method assumes the DBAPI will
start a transaction implicitly (which means that SQLAlchemy’s “begin” does
not change autocommit mode).
SQLAlchemy dialects should support these isolation levels as well as autocommit
to as great a degree as possible. The levels are set via family of
“execution_options” parameters and methods that are throughout the Core, such
as the Connection.execution_options()
method. The parameter is
known as Connection.execution_options.isolation_level
and
the values are strings which are typically a subset of the following names:
# possible values for Connection.execution_options(isolation_level="<value>")
"AUTOCOMMIT"
"READ COMMITTED"
"READ UNCOMMITTED"
"REPEATABLE READ"
"SERIALIZABLE"
Not every DBAPI supports every value; if an unsupported value is used for a certain backend, an error is raised.
For example, to force REPEATABLE READ on a specific connection, then begin a transaction:
with engine.connect().execution_options(isolation_level="REPEATABLE READ") as connection:
with connection.begin():
connection.execute(<statement>)
Note
The return value of
the Connection.execution_options()
method is a so-called
“branched” connection under the SQLAlchemy 1.x series when not using
create_engine.future
mode, which is a shallow
copy of the original Connection
object. Despite this,
the isolation_level
execution option applies to the
original Connection
object and all “branches” overall.
When using create_engine.future
mode (i.e. 2.0 style
usage), the concept of these so-called “branched” connections is removed,
and Connection.execution_options()
returns the same
Connection
object without creating any copies.
The Connection.execution_options.isolation_level
option may
also be set engine wide, as is often preferable. This is achieved by
passing it within the create_engine.execution_options
parameter to create_engine()
:
from sqlalchemy import create_engine
eng = create_engine(
"postgresql://scott:tiger@localhost/test",
execution_options={
"isolation_level": "REPEATABLE READ"
}
)
With the above setting, the DBAPI connection will be set to use a
"REPEATABLE READ"
isolation level setting for each new transaction
begun.
An application that frequently chooses to run operations within different
isolation levels may wish to create multiple “sub-engines” of a lead
Engine
, each of which will be configured to a different
isolation level. One such use case is an application that has operations
that break into “transactional” and “read-only” operations, a separate
Engine
that makes use of "AUTOCOMMIT"
may be
separated off from the main engine:
from sqlalchemy import create_engine
eng = create_engine("postgresql://scott:tiger@localhost/test")
autocommit_engine = eng.execution_options(isolation_level="AUTOCOMMIT")
Above, the Engine.execution_options()
method creates a shallow
copy of the original Engine
. Both eng
and
autocommit_engine
share the same dialect and connection pool. However, the
“AUTOCOMMIT” mode will be set upon connections when they are acquired from the
autocommit_engine
.
The isolation level setting, regardless of which one it is, is unconditionally reverted when a connection is returned to the connection pool.
Note
The Connection.execution_options.isolation_level
parameter necessarily does not apply to statement level options, such as
that of Executable.execution_options()
. This because the option
must be set on a DBAPI connection on a per-transaction basis.
See also
PostgreSQL Transaction Isolation
SQL Server Transaction Isolation
Setting Transaction Isolation Levels / DBAPI AUTOCOMMIT - for the ORM
Using DBAPI Autocommit Allows for a Readonly Version of Transparent Reconnect - a recipe that uses DBAPI autocommit to transparently reconnect to the database for read-only operations
In the parent section, we introduced the concept of the Connection.execution_options.isolation_level
parameter and how it can be used to set database isolation levels, including
DBAPI-level “autocommit” which is treated by SQLAlchemy as another transaction
isolation level. In this section we will attempt to clarify the implications
of this approach.
If we wanted to check out a Connection
object and use it
“autocommit” mode, we would proceed as follows:
with engine.connect() as connection:
connection.execution_options(isolation_level="AUTOCOMMIT")
connection.execute(<statement>)
connection.execute(<statement>)
Above illustrates normal usage of “DBAPI autocommit” mode. There is no
need to make use of methods such as Connection.begin()
or Connection.commit()
(noting the latter applies to 2.0 style usage).
What’s important to note however is that the above autocommit mode is
persistent on that particular Connection until we change it directly using
isolation_level again. The isolation level is also reset on the DBAPI
connection when we release the connection
back to the connection pool. However, calling upon Connection.begin()
will not change the isolation level, meaning we stay in autocommit. The
example below illustrates this:
with engine.connect() as connection:
connection = connection.execution_options(isolation_level="AUTOCOMMIT")
# this begin() does nothing, isolation stays at AUTOCOMMIT
with connection.begin() as trans:
connection.execute(<statement>)
connection.execute(<statement>)
When we run a block like the above with logging turned on, the logging
will attempt to indicate that while a DBAPI level .commit()
is called,
it probably will have no effect due to autocommit mode:
INFO sqlalchemy.engine.Engine BEGIN (implicit)
...
INFO sqlalchemy.engine.Engine COMMIT using DBAPI connection.commit(), DBAPI should ignore due to autocommit mode
Similarly, when using 2.0 style create_engine.future
mode, the Connection
will use autobegin
behavior, meaning that the pattern below will raise an error:
engine = create_engine(..., future=True)
with engine.connect() as connection:
connection = connection.execution_options(isolation_level="AUTOCOMMIT")
# "transaction" is autobegin (but has no effect due to autocommit)
connection.execute(<statement>)
# this will raise; "transaction" is already begun
with connection.begin() as trans:
connection.execute(<statement>)
This is all to demonstrate that the autocommit isolation level setting is
completely independent from the begin/commit behavior of the SQLAlchemy
Connection object. The “autocommit” mode will not interact with Connection.begin()
in any way and the Connection
does not consult this status
when performing its own state changes with regards to the transaction (with
the exception of suggesting within engine logging that these blocks are not
actually committing). The rationale for this design is to maintain a
completely consistent usage pattern with the Connection
where
DBAPI-autocommit mode can be changed independently without indicating any code
changes elsewhere.
Isolation level settings, including autocommit mode, are reset automatically
when the connection is released back to the connection pool. Therefore it is
preferable to avoid trying to switch isolation levels on a single
Connection
object as this leads to excess verbosity.
To illustrate how to use “autocommit” in an ad-hoc mode within the scope of a
single Connection
checkout, the
Connection.execution_options.isolation_level
parameter
must be re-applied with the previous isolation level.
We can write our above block “correctly” as (noting 2.0 style usage below):
# if we wanted to flip autocommit on and off on a single connection/
# which... we usually don't.
engine = create_engine(..., future=True)
with engine.connect() as connection:
connection.execution_options(isolation_level="AUTOCOMMIT")
# run statement(s) in autocommit mode
connection.execute(<statement>)
# "commit" the autobegun "transaction" (2.0/future mode only)
connection.commit()
# switch to default isolation level
connection.execution_options(isolation_level=connection.default_isolation_level)
# use a begin block
with connection.begin() as trans:
connection.execute(<statement>)
Above, to manually revert the isolation level we made use of
Connection.default_isolation_level
to restore the default
isolation level (assuming that’s what we want here). However, it’s
probably a better idea to work with the architecture of of the
Connection
which already handles resetting of isolation level
automatically upon checkin. The preferred way to write the above is to
use two blocks
engine = create_engine(..., future=True)
# use an autocommit block
with engine.connect().execution_options(isolation_level="AUTOCOMMIT") as connection:
# run statement in autocommit mode
connection.execute(<statement>)
# use a regular block
with engine.begin() as connection:
connection.execute(<statement>)
To sum up:
“DBAPI level autocommit” isolation level is entirely independent of the
Connection
object’s notion of “begin” and “commit”
use individual Connection
checkouts per isolation level.
Avoid trying to change back and forth between “autocommit” on a single
connection checkout; let the engine do the work of restoring default
isolation levels
A limited number of dialects have explicit support for the concept of “server
side cursors” vs. “buffered cursors”. While a server side cursor implies a
variety of different capabilities, within SQLAlchemy’s engine and dialect
implementation, it refers only to whether or not a particular set of results is
fully buffered in memory before they are fetched from the cursor, using a
method such as cursor.fetchall()
. SQLAlchemy has no direct support
for cursor behaviors such as scrolling; to make use of these features for
a particular DBAPI, use the cursor directly as documented at
Working with Driver SQL and Raw DBAPI Connections.
Some DBAPIs, such as the cx_Oracle DBAPI, exclusively use server side cursors internally. All result sets are essentially unbuffered across the total span of a result set, utilizing only a smaller buffer that is of a fixed size such as 100 rows at a time.
For those dialects that have conditional support for buffered or unbuffered results, there are usually caveats to the use of the “unbuffered”, or server side cursor mode. When using the psycopg2 dialect for example, an error is raised if a server side cursor is used with any kind of DML or DDL statement. When using MySQL drivers with a server side cursor, the DBAPI connection is in a more fragile state and does not recover as gracefully from error conditions nor will it allow a rollback to proceed until the cursor is fully closed.
For this reason, SQLAlchemy’s dialects will always default to the less error prone version of a cursor, which means for PostgreSQL and MySQL dialects it defaults to a buffered, “client side” cursor where the full set of results is pulled into memory before any fetch methods are called from the cursor. This mode of operation is appropriate in the vast majority of cases; unbuffered cursors are not generally useful except in the uncommon case of an application fetching a very large number of rows in chunks, where the processing of these rows can be complete before more rows are fetched.
To make use of a server side cursor for a particular execution, the
Connection.execution_options.stream_results
option
is used, which may be called on the Connection
object,
on the statement object, or in the ORM-level contexts mentioned below.
When using this option for a statement, it’s usually appropriate to use
a method like Result.partitions()
to work on small sections
of the result set at a time, while also fetching enough rows for each
pull so that the operation is efficient:
with engine.connect() as conn:
result = conn.execution_options(stream_results=True).execute(text("select * from table"))
for partition in result.partitions(100):
_process_rows(partition)
If the Result
is iterated directly, rows are fetched internally
using a default buffering scheme that buffers first a small set of rows,
then a larger and larger buffer on each fetch up to a pre-configured limit
of 1000 rows. This can be affected using the max_row_buffer
execution
option:
with engine.connect() as conn:
conn = conn.execution_options(stream_results=True, max_row_buffer=100)
result = conn.execute(text("select * from table"))
for row in result:
_process_row(row)
The size of the buffer may also be set to a fixed size using the
Result.yield_per()
method. Calling this method with a number
of rows will cause all result-fetching methods to work from
buffers of the given size, only fetching new rows when the buffer is empty:
with engine.connect() as conn:
result = conn.execution_options(stream_results=True).execute(text("select * from table"))
for row in result.yield_per(100):
_process_row(row)
The stream_results
option is also available with the ORM. When using the
ORM, either the Result.yield_per()
or Result.partitions()
methods should be used to set the number of ORM rows to be buffered each time
while yielding:
with orm.Session(engine) as session:
result = session.execute(
select(User).order_by(User_id).execution_options(stream_results=True),
)
for partition in result.partitions(100):
_process_rows(partition)
Note
ORM result sets currently must make use of Result.yield_per()
or Result.partitions()
in order to achieve streaming ORM results.
If either of these methods are not used to set the number of rows to
fetch before yielding, the entire result is fetched before rows are yielded.
This may change in a future release so that the automatic buffer size used
by Connection
takes place for ORM results as well.
When using a 1.x style ORM query with Query
, yield_per is
available via Query.yield_per()
- this also sets the stream_results
execution option:
for row in session.query(User).yield_per(100):
# process row
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
metadata_obj = MetaData()
users_table = Table('users', metadata_obj,
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')
metadata_obj.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.
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_obj,
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.
Tip
To use the schema translation feature with the ORM Session
,
set this option at the level of the Engine
, then pass that engine
to the Session
. The Session
uses a new
Connection
for each transaction:
schema_engine = engine.execution_options(schema_translate_map = { ... } )
session = Session(schema_engine)
...
New in version 1.1.
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 data 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.
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.
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.
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.
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())
The caching feature requires that the dialect’s compiler produces SQL
strings that are safe to reuse for many statement invocations, given
a particular cache key that is keyed to that SQL string. This means
that any literal values in a statement, such as the LIMIT/OFFSET values for
a SELECT, can not be hardcoded in the dialect’s compilation scheme, as
the compiled string will not be re-usable. SQLAlchemy supports rendered
bound parameters using the BindParameter.render_literal_execute()
method which can be applied to the existing Select._limit_clause
and
Select._offset_clause
attributes by a custom compiler, which
are illustrated later in this section.
As there are many third party dialects, many of which may be generating literal
values from SQL statements without the benefit of the newer “literal execute”
feature, SQLAlchemy as of version 1.4.5 has added an attribute to dialects
known as Dialect.supports_statement_cache
. This attribute is
checked at runtime for its presence directly on a particular dialect’s class,
even if it’s already present on a superclass, so that even a third party
dialect that subclasses an existing cacheable SQLAlchemy dialect such as
sqlalchemy.dialects.postgresql.PGDialect
must still explicitly include this
attribute for caching to be enabled. The attribute should only be enabled
once the dialect has been altered as needed and tested for reusability of
compiled SQL statements with differing parameters.
For all third party dialects that don’t support this attribute, the logging for
such a dialect will indicate dialect does not support caching
.
When a dialect has been tested against caching, and in particular the SQL compiler has been updated to not render any literal LIMIT / OFFSET within a SQL string directly, dialect authors can apply the attribute as follows:
from sqlalchemy.engine.default import DefaultDialect
class MyDialect(DefaultDialect):
supports_statement_cache = True
The flag needs to be applied to all subclasses of the dialect as well:
class MyDBAPIForMyDialect(MyDialect):
supports_statement_cache = True
New in version 1.4.5: Added the Dialect.supports_statement_cache
attribute.
The typical case for dialect modification follows.
As an example, suppose a dialect overrides the SQLCompiler.limit_clause()
method, which produces the “LIMIT / OFFSET” clause for a SQL statement,
like this:
# pre 1.4 style code
def limit_clause(self, select, **kw):
text = ""
if select._limit is not None:
text += " \n LIMIT %d" % (select._limit, )
if select._offset is not None:
text += " \n OFFSET %d" % (select._offset, )
return text
The above routine renders the Select._limit
and
Select._offset
integer values as literal integers embedded in the SQL
statement. This is a common requirement for databases that do not support using
a bound parameter within the LIMIT/OFFSET clauses of a SELECT statement.
However, rendering the integer value within the initial compilation stage is
directly incompatible with caching as the limit and offset integer values
of a Select
object are not part of the cache key, so that many
Select
statements with different limit/offset values would not render
with the correct value.
The correction for the above code is to move the literal integer into
SQLAlchemy’s post-compile facility, which will render the
literal integer outside of the initial compilation stage, but instead at
execution time before the statement is sent to the DBAPI. This is accessed
within the compilation stage using the BindParameter.render_literal_execute()
method, in conjunction with using the Select._limit_clause
and
Select._offset_clause
attributes, which represent the LIMIT/OFFSET
as a complete SQL expression, as follows:
# 1.4 cache-compatible code
def limit_clause(self, select, **kw):
text = ""
limit_clause = select._limit_clause
offset_clause = select._offset_clause
if select._simple_int_clause(limit_clause):
text += " \n LIMIT %s" % (
self.process(limit_clause.render_literal_execute(), **kw)
)
elif limit_clause is not None:
# assuming the DB doesn't support SQL expressions for LIMIT.
# Otherwise render here normally
raise exc.CompileError(
"dialect 'mydialect' can only render simple integers for LIMIT"
)
if select._simple_int_clause(offset_clause):
text += " \n OFFSET %s" % (
self.process(offset_clause.render_literal_execute(), **kw)
)
elif offset_clause is not None:
# assuming the DB doesn't support SQL expressions for OFFSET.
# Otherwise render here normally
raise exc.CompileError(
"dialect 'mydialect' can only render simple integers for OFFSET"
)
return text
The approach above will generate a compiled SELECT statement that looks like:
SELECT x FROM y
LIMIT __[POSTCOMPILE_param_1]
OFFSET __[POSTCOMPILE_param_2]
Where above, the __[POSTCOMPILE_param_1]
and __[POSTCOMPILE_param_2]
indicators will be populated with their corresponding integer values at
statement execution time, after the SQL string has been retrieved from the
cache.
After changes like the above have been made as appropriate, the
Dialect.supports_statement_cache
flag should be set to True
.
It is strongly recommended that third party dialects make use of the
dialect third party test suite
which will assert that operations like
SELECTs with LIMIT/OFFSET are correctly rendered and cached.
See also
Why is my application slow after upgrading to 1.4 and/or 2.x? - in the Frequently Asked Questions section
Deep Alchemy
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.
Python functions, typically expressed as lambdas, may be used to generate SQL expressions which are cacheable based on the Python code location of the lambda function itself as well as the closure variables within the lambda. The rationale is to allow caching of not only the SQL string-compiled form of a SQL expression construct as is SQLAlchemy’s normal behavior when the lambda system isn’t used, but also the in-Python composition of the SQL expression construct itself, which also has some degree of Python overhead.
The lambda SQL expression feature is available as a performance enhancing
feature, and is also optionally used in the with_loader_criteria()
ORM option in order to provide a generic SQL fragment.
Lambda statements are constructed using the lambda_stmt()
function,
which returns an instance of StatementLambdaElement
, which is
itself an executable statement construct. Additional modifiers and criteria
are added to the object using the Python addition operator +
, or
alternatively the StatementLambdaElement.add_criteria()
method which
allows for more options.
It is assumed that the lambda_stmt()
construct is being invoked
within an enclosing function or method that expects to be used many times
within an application, so that subsequent executions beyond the first one
can take advantage of the compiled SQL being cached. When the lambda is
constructed inside of an enclosing function in Python it is then subject
to also having closure variables, which are significant to the whole
approach:
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)
with engine.connect() as conn:
result = run_my_statement(some_connection, "some parameter")
Above, the three lambda
callables that are used to define the structure
of a SELECT statement are invoked exactly once, and the resulting SQL
string cached in the compilation cache of the engine. From that point
forward, the run_my_statement()
function may be invoked any number
of times and the lambda
callables within it will not be called, only
used as cache keys to retrieve the already-compiled SQL.
Note
It is important to note that there is already SQL caching in place when the lambda system is not used. The lambda system only adds an additional layer of work reduction per SQL statement invoked by caching the building up of the SQL construct itself and also using a simpler cache key.
Above all, the emphasis within the lambda SQL system is ensuring that there
is never a mismatch between the cache key generated for a lambda and the
SQL string it will produce. The LamdaElement
and related
objects will run and analyze the given lambda in order to calculate how
it should be cached on each run, trying to detect any potential problems.
Basic guidelines include:
Any kind of statement is supported - while it’s expected that
select()
constructs are the prime use case for lambda_stmt()
,
DML statements such as insert()
and update()
are
equally usable:
def upd(id_, newname):
stmt = lambda_stmt(lambda: users.update())
stmt += lambda s: s.values(name=newname)
stmt += lambda s: s.where(users.c.id==id_)
return stmt
with engine.begin() as conn:
conn.execute(upd(7, "foo"))
ORM use cases directly supported as well - the lambda_stmt()
can accommodate ORM functionality completely and used directly with
Session.execute()
:
def select_user(session, name):
stmt = lambda_stmt(lambda: select(User))
stmt += lambda s: s.where(User.name == name)
row = session.execute(stmt).first()
return row
Bound parameters are automatically accommodated - in contrast to SQLAlchemy’s previous “baked query” system, the lambda SQL system accommodates for Python literal values which become SQL bound parameters automatically. This means that even though a given lambda runs only once, the values that become bound parameters are extracted from the closure of the lambda on every run:
>>> def my_stmt(x, y):
... stmt = lambda_stmt(lambda: select(func.max(x, y)))
... return stmt
...
>>> engine = create_engine("sqlite://", echo=True)
>>> with engine.connect() as conn:
... print(conn.scalar(my_stmt(5, 10)))
... print(conn.scalar(my_stmt(12, 8)))
...
SELECT max(?, ?) AS max_1
[generated in 0.00057s] (5, 10)
10
SELECT max(?, ?) AS max_1
[cached since 0.002059s ago] (12, 8)
12
Above, StatementLambdaElement
extracted the values of x
and y
from the closure of the lambda that is generated each time
my_stmt()
is invoked; these were substituted into the cached SQL
construct as the values of the parameters.
The lambda should ideally produce an identical SQL structure in all cases - Avoid using conditionals or custom callables inside of lambdas that might make it produce different SQL based on inputs; if a function might conditionally use two different SQL fragments, use two separate lambdas:
# **Don't** do this:
def my_stmt(parameter, thing=False):
stmt = lambda_stmt(lambda: select(table))
stmt += (
lambda s: s.where(table.c.x > parameter) if thing
else s.where(table.c.y == parameter)
return stmt
# **Do** do this:
def my_stmt(parameter, thing=False):
stmt = lambda_stmt(lambda: select(table))
if thing:
stmt += s.where(table.c.x > parameter)
else:
stmt += s.where(table.c.y == parameter)
return stmt
There are a variety of failures which can occur if the lambda does not produce a consistent SQL construct and some are not trivially detectable right now.
Don’t use functions inside the lambda to produce bound values - the
bound value tracking approach requires that the actual value to be used in
the SQL statement be locally present in the closure of the lambda. This is
not possible if values are generated from other functions, and the
LambdaElement
should normally raise an error if this is
attempted:
>>> def my_stmt(x, y):
... def get_x():
... return x
... def get_y():
... return y
...
... stmt = lambda_stmt(lambda: select(func.max(get_x(), get_y())))
... return stmt
...
>>> with engine.connect() as conn:
... print(conn.scalar(my_stmt(5, 10)))
...
Traceback (most recent call last):
# ...
sqlalchemy.exc.InvalidRequestError: Can't invoke Python callable get_x()
inside of lambda expression argument at
<code object <lambda> at 0x7fed15f350e0, file "<stdin>", line 6>;
lambda SQL constructs should not invoke functions from closure variables
to produce literal values since the lambda SQL system normally extracts
bound values without actually invoking the lambda or any functions within it.
Above, the use of get_x()
and get_y()
, if they are necessary, should
occur outside of the lambda and assigned to a local closure variable:
>>> def my_stmt(x, y):
... def get_x():
... return x
... def get_y():
... return y
...
... x_param, y_param = get_x(), get_y()
... stmt = lambda_stmt(lambda: select(func.max(x_param, y_param)))
... return stmt
Avoid referring to non-SQL constructs inside of lambdas as they are not
cacheable by default - this issue refers to how the LambdaElement
creates a cache key from other closure variables within the statement. In order
to provide the best guarantee of an accurate cache key, all objects located
in the closure of the lambda are considered to be significant, and none
will be assumed to be appropriate for a cache key by default.
So the following example will also raise a rather detailed error message:
>>> class Foo:
... def __init__(self, x, y):
... self.x = x
... self.y = y
...
>>> def my_stmt(foo):
... stmt = lambda_stmt(lambda: select(func.max(foo.x, foo.y)))
... return stmt
...
>>> with engine.connect() as conn:
... print(conn.scalar(my_stmt(Foo(5, 10))))
...
Traceback (most recent call last):
# ...
sqlalchemy.exc.InvalidRequestError: Closure variable named 'foo' inside of
lambda callable <code object <lambda> at 0x7fed15f35450, file
"<stdin>", line 2> does not refer to a cacheable SQL element, and also
does not appear to be serving as a SQL literal bound value based on the
default SQL expression returned by the function. This variable needs to
remain outside the scope of a SQL-generating lambda so that a proper cache
key may be generated from the lambda's state. Evaluate this variable
outside of the lambda, set track_on=[<elements>] to explicitly select
closure elements to track, or set track_closure_variables=False to exclude
closure variables from being part of the cache key.
The above error indicates that LambdaElement
will not assume
that the Foo
object passed in will continue to behave the same in all
cases. It also won’t assume it can use Foo
as part of the cache key
by default; if it were to use the Foo
object as part of the cache key,
if there were many different Foo
objects this would fill up the cache
with duplicate information, and would also hold long-lasting references to
all of these objects.
The best way to resolve the above situation is to not refer to foo
inside of the lambda, and refer to it outside instead:
>>> def my_stmt(foo):
... x_param, y_param = foo.x, foo.y
... stmt = lambda_stmt(lambda: select(func.max(x_param, y_param)))
... return stmt
In some situations, if the SQL structure of the lambda is guaranteed to
never change based on input, to pass track_closure_variables=False
which will disable any tracking of closure variables other than those
used for bound parameters:
>>> def my_stmt(foo):
... stmt = lambda_stmt(
... lambda: select(func.max(foo.x, foo.y)),
... track_closure_variables=False
... )
... return stmt
There is also the option to add objects to the element to explicitly form
part of the cache key, using the track_on
parameter; using this parameter
allows specific values to serve as the cache key and will also prevent other
closure variables from being considered. This is useful for cases where part
of the SQL being constructed originates from a contextual object of some sort
that may have many different values. In the example below, the first
segment of the SELECT statement will disable tracking of the foo
variable,
whereas the second segment will explicitly track self
as part of the
cache key:
>>> def my_stmt(self, foo):
... stmt = lambda_stmt(
... lambda: select(*self.column_expressions),
... track_closure_variables=False
... )
... stmt = stmt.add_criteria(
... lambda: self.where_criteria,
... track_on=[self]
... )
... return stmt
Using track_on
means the given objects will be stored long term in the
lambda’s internal cache and will have strong references for as long as the
cache doesn’t clear out those objects (an LRU scheme of 1000 entries is used
by default).
In order to understand some of the options and behaviors which occur with lambda SQL constructs, an understanding of the caching system is helpful.
SQLAlchemy’s caching system normally generates a cache key from a given SQL expression construct by producing a structure that represents all the state within the construct:
>>> from sqlalchemy import select, column
>>> stmt = select(column('q'))
>>> cache_key = stmt._generate_cache_key()
>>> print(cache_key) # somewhat paraphrased
CacheKey(key=(
'0',
<class 'sqlalchemy.sql.selectable.Select'>,
'_raw_columns',
(
(
'1',
<class 'sqlalchemy.sql.elements.ColumnClause'>,
'name',
'q',
'type',
(
<class 'sqlalchemy.sql.sqltypes.NullType'>,
),
),
),
# a few more elements are here, and many more for a more
# complicated SELECT statement
),)
The above key is stored in the cache which is essentially a dictionary, and the value is a construct that among other things stores the string form of the SQL statement, in this case the phrase “SELECT q”. We can observe that even for an extremely short query the cache key is pretty verbose as it has to represent everything that may vary about what’s being rendered and potentially executed.
The lambda construction system by contrast creates a different kind of cache key:
>>> from sqlalchemy import lambda_stmt
>>> stmt = lambda_stmt(lambda: select(column("q")))
>>> cache_key = stmt._generate_cache_key()
>>> print(cache_key)
CacheKey(key=(
<code object <lambda> at 0x7fed1617c710, file "<stdin>", line 1>,
<class 'sqlalchemy.sql.lambdas.StatementLambdaElement'>,
),)
Above, we see a cache key that is vastly shorter than that of the non-lambda
statement, and additionally that production of the select(column("q"))
construct itself was not even necessary; the Python lambda itself contains
an attribute called __code__
which refers to a Python code object that
within the runtime of the application is immutable and permanent.
When the lambda also includes closure variables, in the normal case that these variables refer to SQL constructs such as column objects, they become part of the cache key, or if they refer to literal values that will be bound parameters, they are placed in a separate element of the cache key:
>>> def my_stmt(parameter):
... col = column("q")
... stmt = lambda_stmt(lambda: select(col))
... stmt += lambda s: s.where(col == parameter)
... return stmt
The above StatementLambdaElement
includes two lambdas, both
of which refer to the col
closure variable, so the cache key will
represent both of these segments as well as the column()
object:
>>> stmt = my_stmt(5)
>>> key = stmt._generate_cache_key()
>>> print(key)
CacheKey(key=(
<code object <lambda> at 0x7f07323c50e0, file "<stdin>", line 3>,
(
'0',
<class 'sqlalchemy.sql.elements.ColumnClause'>,
'name',
'q',
'type',
(
<class 'sqlalchemy.sql.sqltypes.NullType'>,
),
),
<code object <lambda> at 0x7f07323c5190, file "<stdin>", line 4>,
<class 'sqlalchemy.sql.lambdas.LinkedLambdaElement'>,
(
'0',
<class 'sqlalchemy.sql.elements.ColumnClause'>,
'name',
'q',
'type',
(
<class 'sqlalchemy.sql.sqltypes.NullType'>,
),
),
(
'0',
<class 'sqlalchemy.sql.elements.ColumnClause'>,
'name',
'q',
'type',
(
<class 'sqlalchemy.sql.sqltypes.NullType'>,
),
),
),)
The second part of the cache key has retrieved the bound parameters that will be used when the statement is invoked:
>>> key.bindparams
[BindParameter('%(139668884281280 parameter)s', 5, type_=Integer())]
For a series of examples of “lambda” caching with performance comparisons, see the “short_selects” test suite within the Performance performance example.
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.
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.
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.
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.
See also
How do I get at the raw DBAPI connection when using an Engine? - includes additional details about how the DBAPI connection is accessed as well as the “driver” connection when using asyncio drivers.
Some recipes for DBAPI connection use follow.
SQLAlchemy supports calling stored procedures and user defined functions several ways. Please note that all DBAPIs have different practices, so you must consult your underlying DBAPI’s documentation for specifics in relation to your particular usage. The following examples are hypothetical and may not work with your underlying DBAPI.
For stored procedures or functions with special syntactical or parameter concerns, DBAPI-level callproc may potentially be used with your DBAPI. An example of this pattern is:
connection = engine.raw_connection()
try:
cursor_obj = connection.cursor()
cursor_obj.callproc("my_procedure", ['x', 'y', 'z'])
results = list(cursor_obj.fetchall())
cursor_obj.close()
connection.commit()
finally:
connection.close()
Note
Not all DBAPIs use callproc and overall usage details will vary. The above example is only an illustration of how it might look to use a particular DBAPI function.
Your DBAPI may not have a callproc
requirement or may require a stored
procedure or user defined function to be invoked with another pattern, such as
normal SQLAlchemy connection usage. One example of this usage pattern is,
at the time of this documentation’s writing, executing a stored procedure in
the PostgreSQL database with the psycopg2 DBAPI, which should be invoked
with normal connection usage:
connection.execute("CALL my_procedure();")
This above example is hypothetical. The underlying database is not guaranteed to support “CALL” or “SELECT” in these situations, and the keyword may vary dependent on the function being a stored procedure or a user defined function. You should consult your underlying DBAPI and database documentation in these situations to determine the correct syntax and patterns to use.
Multiple result set support is available from a raw DBAPI cursor using the nextset method:
connection = engine.raw_connection()
try:
cursor_obj = connection.cursor()
cursor_obj.execute("select * from table1; select * from table2")
results_one = cursor_obj.fetchall()
cursor_obj.nextset()
results_two = cursor_obj.fetchall()
cursor_obj.close()
finally:
connection.close()
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:
Create a package called foodialect
.
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
.
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://")
.
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.
Object Name | Description |
---|---|
Provides high-level functionality for a wrapped DB-API connection. |
|
A set of hooks intended to augment the construction of an
|
|
Connects a |
|
Encapsulate information about an error condition in progress. |
|
Represent a ‘nested’, or SAVEPOINT transaction. |
|
Represent the “root” transaction on a |
|
Represent a database transaction in progress. |
|
Represent a two-phase transaction. |
sqlalchemy.engine.
Connection
(engine, connection=None, close_with_result=False, _branch_from=None, _execution_options=None, _dispatch=None, _has_events=None, _allow_revalidate=True)¶Provides high-level functionality for a wrapped DB-API connection.
This is the SQLAlchemy 1.x.x version of the Connection
class. For the 2.0 style version, which features some API
differences, see Connection
.
The Connection
object is procured by calling
the Engine.connect()
method of the Engine
object, and provides services for execution of SQL statements as well
as transaction control.
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.
Class signature
class sqlalchemy.engine.Connection
(sqlalchemy.engine.Connectable
)
sqlalchemy.engine.Connection.
__init__
(engine, connection=None, close_with_result=False, _branch_from=None, _execution_options=None, _dispatch=None, _has_events=None, _allow_revalidate=True)¶Construct a new Connection.
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.
Tip
The Connection.begin()
method is invoked when using
the Engine.begin()
context manager method as well.
All documentation that refers to behaviors specific to the
Connection.begin()
method also apply to use of the
Engine.begin()
method.
Legacy use: 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.
Tip
The above “nesting” behavior is a legacy behavior specific to
1.x style use and will be removed in SQLAlchemy 2.0. For
notes on 2.0 style use, see
Connection.begin()
.
See also
Connection.begin_nested()
- use a SAVEPOINT
Connection.begin_twophase()
-
use a two phase /XID transaction
Engine.begin()
- context manager available from
Engine
sqlalchemy.engine.Connection.
begin_nested
()¶Begin a nested transaction (i.e. SAVEPOINT) and return a transaction handle, assuming an outer transaction is already established.
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.
The legacy form of Connection.begin_nested()
method has
alternate behaviors based on whether or not the
Connection.begin()
method was called previously. If
Connection.begin()
was not called, then this method will
behave the same as the Connection.begin()
method and
return a RootTransaction
object that begins and commits a
real transaction - no savepoint is invoked. If
Connection.begin()
has been called, and a
RootTransaction
is already established, then this method
returns an instance of NestedTransaction
which will invoke
and manage the scope of a SAVEPOINT.
Tip
The above mentioned behavior of
Connection.begin_nested()
is a legacy behavior
specific to 1.x style use. In 2.0 style use, the
Connection.begin_nested()
method instead autobegins
the outer transaction that can be committed using
“commit-as-you-go” style; see
Connection.begin_nested()
for migration details.
Changed in version 1.4.13: The behavior of
Connection.begin_nested()
as returning a RootTransaction
if
Connection.begin()
were not called has been restored
as was the case in 1.3.x versions; in previous 1.4.x versions, an
outer transaction would be “autobegun” but would not be committed.
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.
xid¶ – the two phase transaction id. If not supplied, a random id will be generated.
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.
sqlalchemy.engine.Connection.
closed
¶Return True if this connection is closed.
sqlalchemy.engine.Connection.
connect
(close_with_result=False)¶Returns a branched version of this Connection
.
Deprecated since version 1.4: The Connection.connect()
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: Migrating to SQLAlchemy 2.0)
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.
sqlalchemy.engine.Connection.
connection
¶The underlying DB-API connection managed by this Connection.
This is a SQLAlchemy connection-pool proxied connection
which then has the attribute
_ConnectionFairy.dbapi_connection
that refers to the
actual driver connection.
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
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).
sqlalchemy.engine.Connection.
exec_driver_sql
(statement, parameters=None, execution_options=None)¶Executes a SQL statement construct and returns a
CursorResult
.
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')
)
Note
The Connection.exec_driver_sql()
method does
not participate in the
ConnectionEvents.before_execute()
and
ConnectionEvents.after_execute()
events. To
intercept calls to Connection.exec_driver_sql()
, use
ConnectionEvents.before_cursor_execute()
and
ConnectionEvents.after_cursor_execute()
.
See also
sqlalchemy.engine.Connection.
execute
(statement, *multiparams, **params)¶Executes a SQL statement construct and returns a
CursorResult
.
statement¶ –
The statement to be executed. May be one of:
a plain string (deprecated)
any ClauseElement
construct that is also
a subclass of Executable
, such as a
select()
construct
a FunctionElement
, such as that generated
by func
, will be automatically wrapped in
a SELECT statement, which is then executed.
a DDLElement
object
a DefaultGenerator
object
a Compiled
object
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.
sqlalchemy.engine.Connection.
execution_options
(**opt)¶Set non-SQL options for the connection which take effect during execution.
For a “future” style connection, this method returns this same
Connection
object with the new options added.
For a legacy connection, this 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
.
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 this is library level, not DBAPI level autocommit. The DBAPI connection will remain in a real transaction unless the “AUTOCOMMIT” isolation level is used.
Deprecated since version 1.4: The “autocommit” execution option is deprecated and will be removed in SQLAlchemy 2.0. See Library-level (but not driver level) “Autocommit” removed from both Core and ORM for discussion.
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.
logging_token¶ –
Available on: Connection
,
Engine
.
Adds the specified string token surrounded by brackets in log
messages logged by the connection, i.e. the logging that’s enabled
either via the create_engine.echo
flag or via the
logging.getLogger("sqlalchemy.engine")
logger. This allows a
per-connection or per-sub-engine token to be available which is
useful for debugging concurrent connection scenarios.
New in version 1.4.0b2.
See also
Setting Per-Connection / Sub-Engine Tokens - usage example
create_engine.logging_name
- adds a name to the
name used by the Python logger object itself.
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.
See also
create_engine.isolation_level
- set per Engine
isolation level
Connection.get_isolation_level()
- view current level
PostgreSQL Transaction Isolation
SQL Server Transaction Isolation
Setting Transaction Isolation Levels / DBAPI AUTOCOMMIT - for the ORM
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 within a subset of dialects within the PostgreSQL and MySQL categories, and may be supported by other third party dialects as well.
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.
See also
sqlalchemy.engine.Connection.
get_execution_options
()¶Get the non-SQL options which will take effect during execution.
New in version 1.3.
See also
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
sqlalchemy.engine.Connection.
get_nested_transaction
()¶Return the current nested transaction in progress, if any.
New in version 1.4.
sqlalchemy.engine.Connection.
get_transaction
()¶Return the current root transaction in progress, if any.
New in version 1.4.
sqlalchemy.engine.Connection.
in_nested_transaction
()¶Return True if a transaction is in progress.
sqlalchemy.engine.Connection.
in_transaction
()¶Return True if a transaction is in progress.
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
.
sqlalchemy.engine.Connection.
invalidate
(exception=None)¶Invalidate the underlying DBAPI connection associated with
this Connection
.
An attempt will be made to close the underlying DBAPI connection immediately; however if this operation fails, the error is logged but not raised. The connection is then discarded whether or not close() succeeded.
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.
exception¶ – an optional Exception
instance that’s the
reason for the invalidation. is passed along to event handlers
and logging functions.
See also
sqlalchemy.engine.Connection.
invalidated
¶Return True if this connection was invalidated.
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. Invoke the callable function directly, passing the Connection.
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.
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.
sqlalchemy.engine.Connection.
scalars
(object_, *multiparams, **params)¶Executes and returns a scalar result set, which yields scalar values from the first column of each row.
This method is equivalent to calling Connection.execute()
to receive a Result
object, then invoking the
Result.scalars()
method to produce a
ScalarResult
instance.
New in version 1.4.24.
sqlalchemy.engine.Connection.
schema_for_object
(obj)¶Return the schema name for the given schema item taking into account current schema translate map.
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()
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
A rudimentary CreateEnginePlugin
that attaches a logger
to an Engine
object might look like:
import logging
from sqlalchemy.engine import CreateEnginePlugin
from sqlalchemy import event
class LogCursorEventsPlugin(CreateEnginePlugin):
def __init__(self, url, kwargs):
# consume the parameter "log_cursor_logging_name" from the
# URL query
logging_name = url.query.get("log_cursor_logging_name", "log_cursor")
self.log = logging.getLogger(logging_name)
def update_url(self, url):
"update the URL to one that no longer includes our parameters"
return url.difference_update_query(["log_cursor_logging_name"])
def engine_created(self, engine):
"attach an event listener after the new Engine is constructed"
event.listen(engine, "before_cursor_execute", self._log_event)
def _log_event(
self,
conn,
cursor,
statement,
parameters,
context,
executemany):
self.log.info("Plugin logged cursor event: %s", statement)
Plugins are registered using entry points in a similar way as that of dialects:
entry_points={
'sqlalchemy.plugins': [
'log_cursor_plugin = myapp.plugins:LogCursorEventsPlugin'
]
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=log_cursor_plugin&log_cursor_logging_name=mylogger"
)
The plugin
URL parameter 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")
The plugin names may also be passed directly to create_engine()
using the create_engine.plugins
argument:
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
A plugin may consume plugin-specific arguments from the
URL
object as well as the kwargs
dictionary, which is
the dictionary of arguments passed to the create_engine()
call. “Consuming” these arguments includes that they must be removed
when the plugin initializes, so that the arguments are not passed along
to the Dialect
constructor, where they will raise an
ArgumentError
because they are not known by the dialect.
As of version 1.4 of SQLAlchemy, arguments should continue to be consumed
from the kwargs
dictionary directly, by removing the values with a
method such as dict.pop
. Arguments from the URL
object
should be consumed by implementing the
CreateEnginePlugin.update_url()
method, returning a new copy
of the URL
with plugin-specific parameters removed:
class MyPlugin(CreateEnginePlugin):
def __init__(self, url, kwargs):
self.my_argument_one = url.query['my_argument_one']
self.my_argument_two = url.query['my_argument_two']
self.my_argument_three = kwargs.pop('my_argument_three', None)
def update_url(self, url):
return url.difference_update_query(
["my_argument_one", "my_argument_two"]
)
Arguments like those illustrated above would be consumed from a
create_engine()
call such as:
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'
)
Changed in version 1.4: The URL
object is now immutable; a
CreateEnginePlugin
that needs to alter the
URL
should implement the newly added
CreateEnginePlugin.update_url()
method, which
is invoked after the plugin is constructed.
For migration, construct the plugin in the following way, checking
for the existence of the CreateEnginePlugin.update_url()
method to detect which version is running:
class MyPlugin(CreateEnginePlugin):
def __init__(self, url, kwargs):
if hasattr(CreateEnginePlugin, "update_url"):
# detect the 1.4 API
self.my_argument_one = url.query['my_argument_one']
self.my_argument_two = url.query['my_argument_two']
else:
# detect the 1.3 and earlier API - mutate the
# URL directly
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)
def update_url(self, url):
# this method is only called in the 1.4 version
return url.difference_update_query(
["my_argument_one", "my_argument_two"]
)
See also
The URL object is now immutable - overview of the URL
change which
also includes notes regarding CreateEnginePlugin
.
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.
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.
url¶ –
the URL
object. The plugin may inspect
the URL
for arguments. Arguments used by the
plugin should be removed, by returning an updated URL
from the CreateEnginePlugin.update_url()
method.
Changed in version 1.4: The URL
object is now immutable, so a
CreateEnginePlugin
that needs to alter the
URL
object should implement the
CreateEnginePlugin.update_url()
method.
kwargs¶ – The keyword arguments passed to
create_engine()
.
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.
sqlalchemy.engine.CreateEnginePlugin.
handle_dialect_kwargs
(dialect_cls, dialect_args)¶parse and modify dialect kwargs
sqlalchemy.engine.CreateEnginePlugin.
handle_pool_kwargs
(pool_cls, pool_args)¶parse and modify pool kwargs
sqlalchemy.engine.CreateEnginePlugin.
update_url
(url)¶Update the URL
.
A new URL
should be returned. This method is
typically used to consume configuration arguments from the
URL
which must be removed, as they will not be
recognized by the dialect. The
URL.difference_update_query()
method is available
to remove these arguments. See the docstring at
CreateEnginePlugin
for an example.
New in version 1.4.
sqlalchemy.engine.
Engine
(pool, dialect, url, logging_name=None, echo=None, query_cache_size=500, execution_options=None, hide_parameters=False)¶Connects a Pool
and
Dialect
together to provide a
source of database connectivity and behavior.
This is the SQLAlchemy 1.x version of Engine
. For
the 2.0 style version, which includes some API differences,
see Engine
.
An Engine
object is instantiated publicly using the
create_engine()
function.
Class signature
class sqlalchemy.engine.Engine
(sqlalchemy.engine.Connectable
, sqlalchemy.log.Identified
)
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.
Legacy use only: 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
.
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.
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()
.
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
sqlalchemy.engine.Engine.
driver
¶Driver name of the Dialect
in use by this Engine
.
sqlalchemy.engine.Engine.
execute
(statement, *multiparams, **params)¶Executes the given construct and returns a
CursorResult
.
Deprecated since version 1.4: The Engine.execute()
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: Migrating to SQLAlchemy 2.0)
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.
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.
sqlalchemy.engine.Engine.
get_execution_options
()¶Get the non-SQL options which will take effect during execution.
See also
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.
sqlalchemy.engine.Engine.
name
¶String name of the Dialect
in use by this Engine
.
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.
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.begin()
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.
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()
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: Migrating to SQLAlchemy 2.0)
The underlying result/cursor is closed after execution.
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()
.
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()
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()
.
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.
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.
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.
See also
sqlalchemy.engine.ExceptionContext.
cursor
= None¶The DBAPI cursor object.
May be None.
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.
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.
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.
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.
Note
The pool “pre_ping” handler enabled using the
create_engine.pool_pre_ping
parameter does not
consult this event before deciding if the “ping” returned false,
as opposed to receiving an unhandled error. For this use case, the
legacy recipe based on engine_connect() may be used. A future API allow more
comprehensive customization of the “disconnect” detection mechanism
across all functions.
sqlalchemy.engine.ExceptionContext.
original_exception
= None¶The exception object which was caught.
This member is always present.
sqlalchemy.engine.ExceptionContext.
parameters
= None¶Parameter collection that was emitted directly to the DBAPI.
May be None.
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.
sqlalchemy.engine.ExceptionContext.
statement
= None¶String SQL statement that was emitted directly to the DBAPI.
May be None.
sqlalchemy.engine.
NestedTransaction
(connection)¶Represent a ‘nested’, or SAVEPOINT transaction.
The NestedTransaction
object is created by calling the
Connection.begin_nested()
method of
Connection
.
When using NestedTransaction
, the semantics of “begin” /
“commit” / “rollback” are as follows:
the “begin” operation corresponds to the “BEGIN SAVEPOINT” command, where the savepoint is given an explicit name that is part of the state of this object.
The NestedTransaction.commit()
method corresponds to a
“RELEASE SAVEPOINT” operation, using the savepoint identifier associated
with this NestedTransaction
.
The NestedTransaction.rollback()
method corresponds to a
“ROLLBACK TO SAVEPOINT” operation, using the savepoint identifier
associated with this NestedTransaction
.
The rationale for mimicking the semantics of an outer transaction in terms of savepoints so that code may deal with a “savepoint” transaction and an “outer” transaction in an agnostic way.
See also
Using SAVEPOINT - ORM version of the SAVEPOINT API.
Class signature
class sqlalchemy.engine.NestedTransaction
(sqlalchemy.engine.Transaction
)
sqlalchemy.engine.NestedTransaction.
close
()¶inherited from the Transaction.close()
method of Transaction
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.
sqlalchemy.engine.NestedTransaction.
commit
()¶inherited from the Transaction.commit()
method of Transaction
Commit this Transaction
.
The implementation of this may vary based on the type of transaction in use:
For a simple database transaction (e.g. RootTransaction
),
it corresponds to a COMMIT.
For a NestedTransaction
, it corresponds to a
“RELEASE SAVEPOINT” operation.
For a TwoPhaseTransaction
, DBAPI-specific methods for two
phase transactions may be used.
sqlalchemy.engine.NestedTransaction.
rollback
()¶inherited from the Transaction.rollback()
method of Transaction
Roll back this Transaction
.
The implementation of this may vary based on the type of transaction in use:
For a simple database transaction (e.g. RootTransaction
),
it corresponds to a ROLLBACK.
For a NestedTransaction
, it corresponds to a
“ROLLBACK TO SAVEPOINT” operation.
For a TwoPhaseTransaction
, DBAPI-specific methods for two
phase transactions may be used.
sqlalchemy.engine.
RootTransaction
(connection)¶Represent the “root” transaction on a Connection
.
This corresponds to the current “BEGIN/COMMIT/ROLLBACK” that’s occurring
for the Connection
. The RootTransaction
is created by calling upon the Connection.begin()
method, and
remains associated with the Connection
throughout its
active span. The current RootTransaction
in use is
accessible via the Connection.get_transaction
method of
Connection
.
In 2.0 style use, the Connection
also employs
“autobegin” behavior that will create a new
RootTransaction
whenever a connection in a
non-transactional state is used to emit commands on the DBAPI connection.
The scope of the RootTransaction
in 2.0 style
use can be controlled using the Connection.commit()
and
Connection.rollback()
methods.
Class signature
class sqlalchemy.engine.RootTransaction
(sqlalchemy.engine.Transaction
)
sqlalchemy.engine.RootTransaction.
close
()¶inherited from the Transaction.close()
method of Transaction
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.
sqlalchemy.engine.RootTransaction.
commit
()¶inherited from the Transaction.commit()
method of Transaction
Commit this Transaction
.
The implementation of this may vary based on the type of transaction in use:
For a simple database transaction (e.g. RootTransaction
),
it corresponds to a COMMIT.
For a NestedTransaction
, it corresponds to a
“RELEASE SAVEPOINT” operation.
For a TwoPhaseTransaction
, DBAPI-specific methods for two
phase transactions may be used.
sqlalchemy.engine.RootTransaction.
rollback
()¶inherited from the Transaction.rollback()
method of Transaction
Roll back this Transaction
.
The implementation of this may vary based on the type of transaction in use:
For a simple database transaction (e.g. RootTransaction
),
it corresponds to a ROLLBACK.
For a NestedTransaction
, it corresponds to a
“ROLLBACK TO SAVEPOINT” operation.
For a TwoPhaseTransaction
, DBAPI-specific methods for two
phase transactions may be used.
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.
Class signature
class sqlalchemy.engine.Transaction
(sqlalchemy.engine.util.TransactionalContext
)
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.
sqlalchemy.engine.Transaction.
commit
()¶Commit this Transaction
.
The implementation of this may vary based on the type of transaction in use:
For a simple database transaction (e.g. RootTransaction
),
it corresponds to a COMMIT.
For a NestedTransaction
, it corresponds to a
“RELEASE SAVEPOINT” operation.
For a TwoPhaseTransaction
, DBAPI-specific methods for two
phase transactions may be used.
sqlalchemy.engine.Transaction.
rollback
()¶Roll back this Transaction
.
The implementation of this may vary based on the type of transaction in use:
For a simple database transaction (e.g. RootTransaction
),
it corresponds to a ROLLBACK.
For a NestedTransaction
, it corresponds to a
“ROLLBACK TO SAVEPOINT” operation.
For a TwoPhaseTransaction
, DBAPI-specific methods for two
phase transactions may be used.
sqlalchemy.engine.
TwoPhaseTransaction
(connection, xid)¶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.
Class signature
class sqlalchemy.engine.TwoPhaseTransaction
(sqlalchemy.engine.RootTransaction
)
sqlalchemy.engine.TwoPhaseTransaction.
close
()¶inherited from the Transaction.close()
method of Transaction
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.
sqlalchemy.engine.TwoPhaseTransaction.
commit
()¶inherited from the Transaction.commit()
method of Transaction
Commit this Transaction
.
The implementation of this may vary based on the type of transaction in use:
For a simple database transaction (e.g. RootTransaction
),
it corresponds to a COMMIT.
For a NestedTransaction
, it corresponds to a
“RELEASE SAVEPOINT” operation.
For a TwoPhaseTransaction
, DBAPI-specific methods for two
phase transactions may be used.
sqlalchemy.engine.TwoPhaseTransaction.
prepare
()¶Prepare this TwoPhaseTransaction
.
After a PREPARE, the transaction can be committed.
sqlalchemy.engine.TwoPhaseTransaction.
rollback
()¶inherited from the Transaction.rollback()
method of Transaction
Roll back this Transaction
.
The implementation of this may vary based on the type of transaction in use:
For a simple database transaction (e.g. RootTransaction
),
it corresponds to a ROLLBACK.
For a NestedTransaction
, it corresponds to a
“ROLLBACK TO SAVEPOINT” operation.
For a TwoPhaseTransaction
, DBAPI-specific methods for two
phase transactions may be used.
Object Name | Description |
---|---|
Base class for database result objects. |
|
An |
|
A Result that is representing state from a DBAPI cursor. |
|
Represents a |
|
A |
|
Legacy version of |
|
A subclass of |
|
A wrapper for a |
|
Represent a set of database results. |
|
Represent a single result row. |
|
A |
|
A wrapper for a |
sqlalchemy.engine.
BaseCursorResult
(context, cursor_strategy, cursor_description)¶Base class for database result objects.
sqlalchemy.engine.BaseCursorResult.
inserted_primary_key
¶Return the primary key for the row just inserted.
The return value is a Row
object representing
a named tuple of primary key values in the order in which the
primary key columns are configured in the source
Table
.
Changed in version 1.4.8: - the
CursorResult.inserted_primary_key
value is now a named tuple via the Row
class,
rather than a plain tuple.
This accessor only applies to single row insert()
constructs which did not explicitly specify
Insert.returning()
. Support for multirow inserts,
while not yet available for most backends, would be accessed using
the CursorResult.inserted_primary_key_rows
accessor.
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.
sqlalchemy.engine.BaseCursorResult.
inserted_primary_key_rows
¶Return the value of CursorResult.inserted_primary_key
as a row contained within a list; some dialects may support a
multiple row form as well.
Note
As indicated below, in current SQLAlchemy versions this
accessor is only useful beyond what’s already supplied by
CursorResult.inserted_primary_key
when using the
psycopg2 dialect. Future versions hope to
generalize this feature to more dialects.
This accessor is added to support dialects that offer the feature that is currently implemented by the Psycopg2 Fast Execution Helpers feature, currently only the psycopg2 dialect, which provides for many rows to be INSERTed at once while still retaining the behavior of being able to return server-generated primary key values.
When using the psycopg2 dialect, or other dialects that may support
“fast executemany” style inserts in upcoming releases : When
invoking an INSERT statement while passing a list of rows as the
second argument to Connection.execute()
, this accessor
will then provide a list of rows, where each row contains the primary
key value for each row that was INSERTed.
When using all other dialects / backends that don’t yet support
this feature: This accessor is only useful for single row INSERT
statements, and returns the same information as that of the
CursorResult.inserted_primary_key
within a
single-element list. When an INSERT statement is executed in
conjunction with a list of rows to be INSERTed, the list will contain
one row per row inserted in the statement, however it will contain
None
for any server-generated values.
Future releases of SQLAlchemy will further generalize the “fast execution helper” feature of psycopg2 to suit other dialects, thus allowing this accessor to be of more general use.
New in version 1.4.
See also
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.
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.
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.
sqlalchemy.engine.BaseCursorResult.
lastrow_has_defaults
()¶Return lastrow_has_defaults()
from the underlying
ExecutionContext
.
See ExecutionContext
for details.
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.
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.
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.
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.
See also
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.
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.
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.
See also
Getting Affected Row Count from UPDATE, DELETE - in the SQLAlchemy 1.4 / 2.0 Tutorial
sqlalchemy.engine.BaseCursorResult.
supports_sane_multi_rowcount
()¶Return supports_sane_multi_rowcount
from the dialect.
See CursorResult.rowcount
for background.
sqlalchemy.engine.BaseCursorResult.
supports_sane_rowcount
()¶Return supports_sane_rowcount
from the dialect.
See CursorResult.rowcount
for background.
sqlalchemy.engine.
ChunkedIteratorResult
(cursor_metadata, chunks, source_supports_scalars=False, raw=None, dynamic_yield_per=False)¶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.
Class signature
class sqlalchemy.engine.ChunkedIteratorResult
(sqlalchemy.engine.IteratorResult
)
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.
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.
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 generated from a fixed
set of data each time the FrozenResult
is invoked as
a callable:
result = connection.execute(query)
frozen = result.freeze()
unfrozen_result_one = frozen()
for row in unfrozen_result_one:
print(row)
unfrozen_result_two = frozen()
rows = unfrozen_result_two.all()
# ... etc
New in version 1.4.
See also
Re-Executing Statements - example usage within the ORM to implement a result-set cache.
merge_frozen_result()
- ORM function to merge
a frozen result back into a Session
.
sqlalchemy.engine.
IteratorResult
(cursor_metadata, iterator, raw=None, _source_supports_scalars=False)¶A Result
that gets data from a Python iterator of
Row
objects.
New in version 1.4.
Class signature
class sqlalchemy.engine.IteratorResult
(sqlalchemy.engine.Result
)
sqlalchemy.engine.
LegacyRow
(parent, processors, keymap, key_style, data)¶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.
Class signature
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.
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.
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.
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.
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.
sqlalchemy.engine.
MergedResult
(cursor_metadata, results)¶A Result
that is merged from any number of
Result
objects.
Returned by the Result.merge()
method.
New in version 1.4.
Class signature
class sqlalchemy.engine.MergedResult
(sqlalchemy.engine.IteratorResult
)
sqlalchemy.engine.
Result
(cursor_metadata)¶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. When using the ORM, a higher level
object called ChunkedIteratorResult
is normally used.
Note
In SQLAlchemy 1.4 and above, this object is
used for ORM results returned by Session.execute()
, which can
yield instances of ORM mapped objects either individually or within
tuple-like rows. Note that the Result
object does not
deduplicate instances or rows automatically as is the case with the
legacy Query
object. For in-Python de-duplication of
instances or rows, use the Result.unique()
modifier
method.
See also
Class signature
class sqlalchemy.engine.Result
(sqlalchemy.engine._WithKeys
, sqlalchemy.engine.ResultInternal
)
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.
a list of Row
objects.
sqlalchemy.engine.Result.
close
()¶close this Result
.
The behavior of this method is implementation specific, and is
not implemented by default. The method should generally end
the resources in use by the result object and also cause any
subsequent iteration or row fetching to raise
ResourceClosedError
.
New in version 1.4.27: - .close()
was previously not generally
available for all Result
classes, instead only
being available on the CursorResult
returned for
Core statement executions. As most other result objects, namely the
ones used by the ORM, are proxying a CursorResult
in any case, this allows the underlying cursor result to be closed
from the outside facade for the case when the ORM query is using
the yield_per
execution option where it does not immediately
exhaust and autoclose the database cursor.
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.
*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.
this Result
object with the modifications
given.
sqlalchemy.engine.Result.
fetchall
()¶A synonym for the Result.all()
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.
a list of Row
objects.
sqlalchemy.engine.Result.
fetchone
()¶Fetch one row.
When all rows are exhausted, returns None.
This method is provided for backwards compatibility with SQLAlchemy 1.x.x.
To fetch the first row of a result only, use the
Result.first()
method. To iterate through all
rows, iterate the Result
object directly.
a Row
object if no filters are applied, or None
if no rows remain.
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()
.
Additionally, in contrast to the behavior of the legacy ORM
Query.first()
method, no limit is applied to the
SQL query which was invoked to produce this Result
;
for a DBAPI driver that buffers results in memory before yielding
rows, all rows will be sent to the Python process and all but
the first row will be discarded.
See also
a Row
object, or None
if no rows remain.
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.
See also
Re-Executing Statements - example usage within the ORM to implement a result-set cache.
sqlalchemy.engine.Result.
keys
()¶inherited from the sqlalchemy.engine._WithKeys.keys
method of sqlalchemy.engine._WithKeys
Return an iterable view which yields the string keys that would
be represented by each Row
.
The keys can represent the labels of the columns returned by a core statement or the names of the orm classes returned by an orm execution.
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.
sqlalchemy.engine.Result.
mappings
()¶Apply a mappings filter to returned rows, returning an instance of
MappingResult
.
When this filter is applied, fetching rows will return
RowMapping
objects instead of Row
objects.
New in version 1.4.
a new MappingResult
filtering object
referring to this Result
object.
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.
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.
The first Row
.
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.
The first Row
or None if no row is available.
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 not.
New in version 1.4.
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.
iterator of lists
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.
a Python scalar value , or None if no rows remain.
sqlalchemy.engine.Result.
scalar_one
()¶Return exactly one scalar result or raise an exception.
This is equivalent to calling Result.scalars()
and then
Result.one()
.
sqlalchemy.engine.Result.
scalar_one_or_none
()¶Return exactly one or no scalar result.
This is equivalent to calling Result.scalars()
and then
Result.one_or_none()
.
sqlalchemy.engine.Result.
scalars
(index=0)¶Return a ScalarResult
filtering object which
will return single elements rather than Row
objects.
E.g.:
>>> result = conn.execute(text("select int_id from table"))
>>> result.scalars().all()
[1, 2, 3]
When results are fetched from the ScalarResult
filtering object, the single column-row that would be returned by the
Result
is instead returned as the column’s value.
New in version 1.4.
index¶ – integer or row key indicating the column to be fetched
from each row, defaults to 0
indicating the first column.
a new ScalarResult
filtering object referring
to this Result
object.
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.
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.
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.
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.
sqlalchemy.engine.
ScalarResult
(real_result, index)¶A wrapper for a Result
that returns scalar values
rather than Row
values.
The ScalarResult
object is acquired by calling the
Result.scalars()
method.
A special limitation of ScalarResult
is that it has
no fetchone()
method; since the semantics of fetchone()
are that
the None
value indicates no more results, this is not compatible
with ScalarResult
since there is no way to distinguish
between None
as a row value versus None
as an indicator. Use
next(result)
to receive values individually.
Class signature
class sqlalchemy.engine.ScalarResult
(sqlalchemy.engine.FilterResult
)
sqlalchemy.engine.ScalarResult.
all
()¶Return all scalar values in a list.
Equivalent to Result.all()
except that
scalar values, rather than Row
objects,
are returned.
sqlalchemy.engine.ScalarResult.
fetchall
()¶A synonym for the ScalarResult.all()
method.
sqlalchemy.engine.ScalarResult.
fetchmany
(size=None)¶Fetch many objects.
Equivalent to Result.fetchmany()
except that
scalar values, rather than Row
objects,
are returned.
sqlalchemy.engine.ScalarResult.
first
()¶Fetch the first object or None if no object is present.
Equivalent to Result.first()
except that
scalar values, rather than Row
objects,
are returned.
sqlalchemy.engine.ScalarResult.
one
()¶Return exactly one object or raise an exception.
Equivalent to Result.one()
except that
scalar values, rather than Row
objects,
are returned.
sqlalchemy.engine.ScalarResult.
one_or_none
()¶Return at most one object or raise an exception.
Equivalent to Result.one_or_none()
except that
scalar values, rather than Row
objects,
are returned.
sqlalchemy.engine.ScalarResult.
partitions
(size=None)¶Iterate through sub-lists of elements of the size given.
Equivalent to Result.partitions()
except that
scalar values, rather than Row
objects,
are returned.
sqlalchemy.engine.ScalarResult.
unique
(strategy=None)¶Apply unique filtering to the objects returned by this
ScalarResult
.
See Result.unique()
for usage details.
sqlalchemy.engine.
MappingResult
(result)¶A wrapper for a Result
that returns dictionary values
rather than Row
values.
The MappingResult
object is acquired by calling the
Result.mappings()
method.
Class signature
class sqlalchemy.engine.MappingResult
(sqlalchemy.engine._WithKeys
, sqlalchemy.engine.FilterResult
)
sqlalchemy.engine.MappingResult.
all
()¶Return all scalar values in a list.
Equivalent to Result.all()
except that
mapping values, rather than Row
objects,
are returned.
sqlalchemy.engine.MappingResult.
columns
(*col_expressions)¶Establish the columns that should be returned in each row.
sqlalchemy.engine.MappingResult.
fetchall
()¶A synonym for the MappingResult.all()
method.
sqlalchemy.engine.MappingResult.
fetchmany
(size=None)¶Fetch many objects.
Equivalent to Result.fetchmany()
except that
mapping values, rather than Row
objects,
are returned.
sqlalchemy.engine.MappingResult.
fetchone
()¶Fetch one object.
Equivalent to Result.fetchone()
except that
mapping values, rather than Row
objects,
are returned.
sqlalchemy.engine.MappingResult.
first
()¶Fetch the first object or None if no object is present.
Equivalent to Result.first()
except that
mapping values, rather than Row
objects,
are returned.
sqlalchemy.engine.MappingResult.
keys
()¶inherited from the sqlalchemy.engine._WithKeys.keys
method of sqlalchemy.engine._WithKeys
Return an iterable view which yields the string keys that would
be represented by each Row
.
The keys can represent the labels of the columns returned by a core statement or the names of the orm classes returned by an orm execution.
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.
sqlalchemy.engine.MappingResult.
one
()¶Return exactly one object or raise an exception.
Equivalent to Result.one()
except that
mapping values, rather than Row
objects,
are returned.
sqlalchemy.engine.MappingResult.
one_or_none
()¶Return at most one object or raise an exception.
Equivalent to Result.one_or_none()
except that
mapping values, rather than Row
objects,
are returned.
sqlalchemy.engine.MappingResult.
partitions
(size=None)¶Iterate through sub-lists of elements of the size given.
Equivalent to Result.partitions()
except that
mapping values, rather than Row
objects,
are returned.
sqlalchemy.engine.MappingResult.
unique
(strategy=None)¶Apply unique filtering to the objects returned by this
MappingResult
.
See Result.unique()
for usage details.
sqlalchemy.engine.
CursorResult
(context, cursor_strategy, cursor_description)¶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.
Class signature
class sqlalchemy.engine.CursorResult
(sqlalchemy.engine.BaseCursorResult
, sqlalchemy.engine.Result
)
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.
a list of Row
objects.
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.
See also
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.
*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.
this Result
object with the modifications
given.
sqlalchemy.engine.CursorResult.
fetchall
()¶inherited from the Result.fetchall()
method of Result
A synonym for the Result.all()
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.
a list of Row
objects.
sqlalchemy.engine.CursorResult.
fetchone
()¶inherited from the Result.fetchone()
method of Result
Fetch one row.
When all rows are exhausted, returns None.
This method is provided for backwards compatibility with SQLAlchemy 1.x.x.
To fetch the first row of a result only, use the
Result.first()
method. To iterate through all
rows, iterate the Result
object directly.
a Row
object if no filters are applied, or None
if no rows remain.
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()
.
Additionally, in contrast to the behavior of the legacy ORM
Query.first()
method, no limit is applied to the
SQL query which was invoked to produce this Result
;
for a DBAPI driver that buffers results in memory before yielding
rows, all rows will be sent to the Python process and all but
the first row will be discarded.
See also
a Row
object, or None
if no rows remain.
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.
See also
Re-Executing Statements - example usage within the ORM to implement a result-set cache.
sqlalchemy.engine.CursorResult.
inserted_primary_key
¶inherited from the BaseCursorResult.inserted_primary_key
attribute of BaseCursorResult
Return the primary key for the row just inserted.
The return value is a Row
object representing
a named tuple of primary key values in the order in which the
primary key columns are configured in the source
Table
.
Changed in version 1.4.8: - the
CursorResult.inserted_primary_key
value is now a named tuple via the Row
class,
rather than a plain tuple.
This accessor only applies to single row insert()
constructs which did not explicitly specify
Insert.returning()
. Support for multirow inserts,
while not yet available for most backends, would be accessed using
the CursorResult.inserted_primary_key_rows
accessor.
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.
sqlalchemy.engine.CursorResult.
inserted_primary_key_rows
¶inherited from the BaseCursorResult.inserted_primary_key_rows
attribute of BaseCursorResult
Return the value of CursorResult.inserted_primary_key
as a row contained within a list; some dialects may support a
multiple row form as well.
Note
As indicated below, in current SQLAlchemy versions this
accessor is only useful beyond what’s already supplied by
CursorResult.inserted_primary_key
when using the
psycopg2 dialect. Future versions hope to
generalize this feature to more dialects.
This accessor is added to support dialects that offer the feature that is currently implemented by the Psycopg2 Fast Execution Helpers feature, currently only the psycopg2 dialect, which provides for many rows to be INSERTed at once while still retaining the behavior of being able to return server-generated primary key values.
When using the psycopg2 dialect, or other dialects that may support
“fast executemany” style inserts in upcoming releases : When
invoking an INSERT statement while passing a list of rows as the
second argument to Connection.execute()
, this accessor
will then provide a list of rows, where each row contains the primary
key value for each row that was INSERTed.
When using all other dialects / backends that don’t yet support
this feature: This accessor is only useful for single row INSERT
statements, and returns the same information as that of the
CursorResult.inserted_primary_key
within a
single-element list. When an INSERT statement is executed in
conjunction with a list of rows to be INSERTed, the list will contain
one row per row inserted in the statement, however it will contain
None
for any server-generated values.
Future releases of SQLAlchemy will further generalize the “fast execution helper” feature of psycopg2 to suit other dialects, thus allowing this accessor to be of more general use.
New in version 1.4.
See also
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.
sqlalchemy.engine.CursorResult.
keys
()¶inherited from the sqlalchemy.engine._WithKeys.keys
method of sqlalchemy.engine._WithKeys
Return an iterable view which yields the string keys that would
be represented by each Row
.
The keys can represent the labels of the columns returned by a core statement or the names of the orm classes returned by an orm execution.
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.
sqlalchemy.engine.CursorResult.
last_inserted_params
()¶inherited from the BaseCursorResult.last_inserted_params()
method of BaseCursorResult
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.
sqlalchemy.engine.CursorResult.
last_updated_params
()¶inherited from the BaseCursorResult.last_updated_params()
method of BaseCursorResult
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.
sqlalchemy.engine.CursorResult.
lastrow_has_defaults
()¶inherited from the BaseCursorResult.lastrow_has_defaults()
method of BaseCursorResult
Return lastrow_has_defaults()
from the underlying
ExecutionContext
.
See ExecutionContext
for details.
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.
sqlalchemy.engine.CursorResult.
mappings
()¶inherited from the Result.mappings()
method of Result
Apply a mappings filter to returned rows, returning an instance of
MappingResult
.
When this filter is applied, fetching rows will return
RowMapping
objects instead of Row
objects.
New in version 1.4.
a new MappingResult
filtering object
referring to this Result
object.
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.
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.
The first Row
.
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.
The first Row
or None if no row is available.
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 not.
New in version 1.4.
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.
iterator of lists
sqlalchemy.engine.CursorResult.
postfetch_cols
()¶inherited from the BaseCursorResult.postfetch_cols()
method of BaseCursorResult
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.
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.
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.
See also
sqlalchemy.engine.CursorResult.
returned_defaults_rows
¶inherited from the BaseCursorResult.returned_defaults_rows
attribute of BaseCursorResult
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.
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.
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.
See also
Getting Affected Row Count from UPDATE, DELETE - in the SQLAlchemy 1.4 / 2.0 Tutorial
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.
a Python scalar value , or None if no rows remain.
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 equivalent to calling Result.scalars()
and then
Result.one()
.
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 equivalent to calling Result.scalars()
and then
Result.one_or_none()
.
sqlalchemy.engine.CursorResult.
scalars
(index=0)¶inherited from the Result.scalars()
method of Result
Return a ScalarResult
filtering object which
will return single elements rather than Row
objects.
E.g.:
>>> result = conn.execute(text("select int_id from table"))
>>> result.scalars().all()
[1, 2, 3]
When results are fetched from the ScalarResult
filtering object, the single column-row that would be returned by the
Result
is instead returned as the column’s value.
New in version 1.4.
index¶ – integer or row key indicating the column to be fetched
from each row, defaults to 0
indicating the first column.
a new ScalarResult
filtering object referring
to this Result
object.
sqlalchemy.engine.CursorResult.
supports_sane_multi_rowcount
()¶inherited from the BaseCursorResult.supports_sane_multi_rowcount()
method of BaseCursorResult
Return supports_sane_multi_rowcount
from the dialect.
See CursorResult.rowcount
for background.
sqlalchemy.engine.CursorResult.
supports_sane_rowcount
()¶inherited from the BaseCursorResult.supports_sane_rowcount()
method of BaseCursorResult
Return supports_sane_rowcount
from the dialect.
See CursorResult.rowcount
for background.
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.
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.
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.
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.
sqlalchemy.engine.
LegacyCursorResult
(context, cursor_strategy, cursor_description)¶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.
Class signature
class sqlalchemy.engine.LegacyCursorResult
(sqlalchemy.engine.CursorResult
)
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.
sqlalchemy.engine.
Row
(parent, processors, keymap, key_style, data)¶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.
Class signature
class sqlalchemy.engine.Row
(sqlalchemy.engine.BaseRow
, collections.abc.Sequence
)
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
sqlalchemy.engine.Row.
_fields
¶Return a tuple of string keys as represented by this
Row
.
The keys can represent the labels of the columns returned by a core statement or the names of the orm classes returned by an orm execution.
This attribute is analogous to the Python named tuple ._fields
attribute.
New in version 1.4.
See also
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
New in version 1.4.
sqlalchemy.engine.Row.
keys
()¶Return the list of keys as strings represented by this
Row
.
Deprecated since version 1.4: The Row.keys()
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: Migrating to SQLAlchemy 2.0)
The keys can represent the labels of the columns returned by a core statement or the names of the orm classes returned by an orm execution.
This method is analogous to the Python dictionary .keys()
method,
except that it returns a list, not an iterator.
sqlalchemy.engine.
RowMapping
(parent, processors, keymap, key_style, data)¶A Mapping
that maps column names and objects to Row
values.
The RowMapping
is available from a Row
via the
Row._mapping
attribute, as well as from the iterable interface
provided by the MappingResult
object returned by the
Result.mappings()
method.
RowMapping
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.
Class signature
class sqlalchemy.engine.RowMapping
(sqlalchemy.engine.BaseRow
, collections.abc.Mapping
)
sqlalchemy.engine.RowMapping.
items
()¶Return a view of key/value tuples for the elements in the
underlying Row
.
sqlalchemy.engine.RowMapping.
keys
()¶Return a view of ‘keys’ for string column names represented
by the underlying Row
.
sqlalchemy.engine.RowMapping.
values
()¶Return a view of values for the values represented in the
underlying Row
.
flambé! the dragon and The Alchemist image designs created and generously donated by Rotem Yaari.
Created using Sphinx 3.5.4.