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.
The typical usage of create_engine() is once per particular database
URL, held globally for the lifetime of a single application process. A single
Engine manages many individual DBAPI connections on behalf of
the process and is intended to be called upon in a concurrent fashion. The
Engine is not synonymous to the DBAPI connect() function, which
represents just one connection resource - the Engine is most
efficient when created just once at the module level of an application, not
per-object or per-function call.
The most basic function of the Engine is to provide access to a
Connection, which can then invoke SQL statements. To emit
a textual statement to the database looks like:
fromsqlalchemyimporttextwithengine.connect()asconnection:result=connection.execute(text("select username from users"))forrowinresult: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 (this is known as
Reset On Return), and the connection is ready for its next use.
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; see Working with Data
in the SQLAlchemy Unified Tutorial for a tutorial.
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 always emits SQL statements
within the context of a transaction block. The first time the
Connection.execute() method is called to execute a SQL
statement, this transaction is begun automatically, using a behavior known
as autobegin. The transaction remains in place for the scope of the
Connection object until the Connection.commit()
or Connection.rollback() methods are called. Subsequent
to the transaction ending, the Connection waits for the
Connection.execute() method to be called again, at which point
it autobegins again.
This calling style is referred towards as commit as you go, and is
illustrated in the example below:
withengine.connect()asconnection:connection.execute(some_table.insert(),{"x":7,"y":"this is some data"})connection.execute(some_other_table.insert(),{"q":8,"p":"this is some more data"})connection.commit()# commit the transaction
In “commit as you go” style, we can call upon Connection.commit()
and Connection.rollback() methods freely within an ongoing
sequence of other statements emitted using Connection.execute();
each time the transaction is ended, and a new statement is
emitted, a new transaction begins implicitly:
withengine.connect()asconnection:connection.execute("<some statement>")connection.commit()# commits "some statement"# new transaction startsconnection.execute("<some other statement>")connection.rollback()# rolls back "some other statement"# new transaction startsconnection.execute("<a third statement>")connection.commit()# commits "a third statement"
New in version 2.0: “commit as you go” style is a new feature of
SQLAlchemy 2.0. It is also available in SQLAlchemy 1.4’s “transitional”
mode when using a “future” style engine.
The Connection object provides a more explicit transaction
management style referred towards as begin once. In contrast to “commit as
you go”, “begin once” allows the start point of the transaction to be
stated explicitly,
and allows that the transaction itself may be framed out as a context manager
block so that the end of the transaction is instead implicit. To use
“begin once”, the Connection.begin() method is used, which returns a
Transaction object which represents the DBAPI transaction.
This object also supports explicit management via its own
Transaction.commit() and Transaction.rollback()
methods, but as a preferred practice also supports the context manager interface,
where it will commit itself when
the block ends normally and emit a rollback if an exception is raised, before
propagating the exception outwards. Below illustrates the form of a “begin
once” block:
withengine.connect()asconnection:withconnection.begin():connection.execute(some_table.insert(),{"x":7,"y":"this is some data"})connection.execute(some_other_table.insert(),{"q":8,"p":"this is some more data"})# transaction is committed
A convenient shorthand form for the above “begin once” block is to use
the Engine.begin() method at the level of the originating
Engine object, rather than performing the two separate
steps of Engine.connect() and Connection.begin();
the Engine.begin() method returns a special context manager
that internally maintains both the context manager for the Connection
as well as the context manager for the Transaction normally
returned by the Connection.begin() method:
withengine.begin()asconnection:connection.execute(some_table.insert(),{"x":7,"y":"this is some data"})connection.execute(some_other_table.insert(),{"q":8,"p":"this is some more data"})# transaction is committed, and Connection is released to the connection# pool
Tip
Within the Engine.begin() block, we can call upon the
Connection.commit() or Connection.rollback()
methods, which will end the transaction normally demarcated by the block
ahead of time. However, if we do so, no further SQL operations may be
emitted on the Connection until the block ends:
>>> fromsqlalchemyimportcreate_engine>>> e=create_engine("sqlite://",echo=True)>>> withe.begin()asconn:... conn.commit()... conn.begin()2021-11-08 09:49:07,517 INFO sqlalchemy.engine.Engine BEGIN (implicit)2021-11-08 09:49:07,517 INFO sqlalchemy.engine.Engine COMMITTraceback (most recent call last):...sqlalchemy.exc.InvalidRequestError: Can't operate on closed transaction insidecontext manager. Please complete the context manager before emittingfurther commands.
The “commit as you go” and “begin once” styles can be freely mixed within
a single Engine.connect() block, provided that the call to
Connection.begin() does not conflict with the “autobegin”
behavior. To accomplish this, Connection.begin() should only
be called either before any SQL statements have been emitted, or directly
after a previous call to Connection.commit() or Connection.rollback():
withengine.connect()asconnection:withconnection.begin():# run statements in a "begin once" blockconnection.execute(some_table.insert(),{"x":7,"y":"this is some data"})# transaction is committed# run a new statement outside of a block. The connection# autobeginsconnection.execute(some_other_table.insert(),{"q":8,"p":"this is some more data"})# commit explicitlyconnection.commit()# can use a "begin once" block herewithconnection.begin():# run more statementsconnection.execute(...)
When developing code that uses “begin once”, the library will raise
InvalidRequestError if a transaction was already “autobegun”.
Setting Transaction Isolation Levels including DBAPI Autocommit¶
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.
Setting Isolation Level or DBAPI Autocommit for a Connection¶
# 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:
The return value of
the Connection.execution_options() method is the same
Connection object upon which the method was called,
meaning, it modifies the state of the Connection
object in place. This is a new behavior as of SQLAlchemy 2.0.
This behavior does not apply to the Engine.execution_options()
method; that method still returns a copy of the Engine and
as described below may be used to construct multiple Engine
objects with different execution options, which nonetheless share the same
dialect and connection pool.
With the above setting, each new DBAPI connection the moment it’s created will
be set to use a "REPEATABLEREAD" isolation level setting for all
subsequent operations.
Maintaining Multiple Isolation Levels for a Single Engine¶
The isolation level may also be set per engine, with a potentially greater
level of flexibility, using either the
create_engine.execution_options parameter to
create_engine() or the Engine.execution_options()
method, the latter of which will create a copy of the Engine that
shares the dialect and connection pool of the original engine, but has its own
per-connection isolation level setting:
With the above setting, the DBAPI connection will be set to use a
"REPEATABLEREAD" isolation level setting for each new transaction
begun; but the connection as pooled will be reset to the original isolation
level that was present when the connection first occurred. At the level
of create_engine(), the end effect is not any different
from using the create_engine.isolation_level parameter.
However, 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:
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.
Understanding the DBAPI-Level Autocommit Isolation Level¶
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:
Above illustrates normal usage of “DBAPI autocommit” mode. There is no
need to make use of methods such as Connection.begin()
or Connection.commit(), as all statements are committed
to the database immediately. When the block ends, the Connection
object will revert the “autocommit” isolation level, and the DBAPI connection
is released to the connection pool where the DBAPI connection.rollback()
method will normally be invoked, but as the above statements were already
committed, this rollback has no change on the state of the database.
It is important to note that “autocommit” mode
persists even when the Connection.begin() method is called;
the DBAPI will not emit any BEGIN to the database, nor will it emit
COMMIT when Connection.commit() is called. This usage is also
not an error scenario, as it is expected that the “autocommit” isolation level
may be applied to code that otherwise was written assuming a transactional context;
the “isolation level” is, after all, a configurational detail of the transaction
itself just like any other isolation level.
In the example below, statements remain
autocommitting regardless of SQLAlchemy-level transaction blocks:
withengine.connect()asconnection:connection=connection.execution_options(isolation_level="AUTOCOMMIT")# this begin() does not affect the DBAPI connection, isolation stays at AUTOCOMMITwithconnection.begin()astrans: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
At the same time, even though we are using “DBAPI autocommit”, SQLAlchemy’s
transactional semantics, that is, the in-Python behavior of Connection.begin()
as well as the behavior of “autobegin”, remain in place, even though these
don’t impact the DBAPI connection itself. To illustrate, the code
below will raise an error, as Connection.begin() is being
called after autobegin has already occurred:
withengine.connect()asconnection: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 begunwithconnection.begin()astrans:connection.execute("<statement>")
The above example also demonstrates the same theme that the “autocommit”
isolation level is a configurational detail of the underlying database
transaction, and is independent of 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.
The previous section illustrated an attempt to call Connection.begin()
in order to start a transaction while autocommit was taking place; we can
rewrite that example to actually do so by first reverting the isolation level
before we call upon Connection.begin():
# if we wanted to flip autocommit on and off on a single connection/# which... we usually don't.withengine.connect()asconnection:connection.execution_options(isolation_level="AUTOCOMMIT")# run statement(s) in autocommit modeconnection.execute("<statement>")# "commit" the autobegun "transaction"connection.commit()# switch to default isolation levelconnection.execution_options(isolation_level=connection.default_isolation_level)# use a begin blockwithconnection.begin()astrans: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
# use an autocommit blockwithengine.connect().execution_options(isolation_level="AUTOCOMMIT")asconnection:# run statement in autocommit modeconnection.execute("<statement>")# use a regular blockwithengine.begin()asconnection: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
Using Server Side Cursors (a.k.a. stream results)¶
Some backends feature explicit support for the concept of “server
side cursors” versus “client side cursors”. A client side cursor here
means that the database driver fully fetches all rows from a result set
into memory before returning from a statement execution. Drivers such as
those of PostgreSQL and MySQL/MariaDB generally use client side cursors
by default. A server side cursor, by contrast, indicates that result rows
remain pending within the database server’s state as result rows are consumed
by the client. The drivers for Oracle generally use a “server side” model,
for example, and the SQLite dialect, while not using a real “client / server”
architecture, still uses an unbuffered result fetching approach that will
leave result rows outside of process memory before they are consumed.
From this basic architecture it follows that a “server side cursor” is more
memory efficient when fetching very large result sets, while at the same time
may introduce more complexity in the client/server communication process
and be less efficient for small result sets (typically less than 10000 rows).
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.
As individual row-fetch operations with fully unbuffered server side cursors
are typically more expensive than fetching batches of rows at once, The
Connection.execution_options.yield_per execution option
configures a Connection or statement to make use of
server-side cursors as are available, while at the same time configuring a
fixed-size buffer of rows that will retrieve rows from the server in batches as
they are consumed. This parameter may be to a positive integer value using the
Connection.execution_options() method on
Connection or on a statement using the
Executable.execution_options() method.
Using this option is equivalent to manually setting the
Connection.execution_options.stream_results option,
described in the next section, and then invoking the
Result.yield_per() method on the Result
object with the given integer value. In both cases, the effect this
combination has includes:
server side cursors mode is selected for the given backend, if available
and not already the default behavior for that backend
as result rows are fetched, they will be buffered in batches, where the
size of each batch up until the last batch will be equal to the integer
argument passed to the
Connection.execution_options.yield_per option or the
Result.yield_per() method; the last batch is then sized against
the remaining rows fewer than this size
The default partition size used by the Result.partitions()
method, if used, will be made equal to this integer size as well.
These three behaviors are illustrated in the example below:
withengine.connect()asconn:withconn.execution_options(yield_per=100).execute(text("select * from table"))asresult:forpartitioninresult.partitions():# partition is an iterable that will be at most 100 itemsforrowinpartition:print(f"{row}")
The above example illustrates the combination of yield_per=100 along
with using the Result.partitions() method to run processing
on rows in batches that match the size fetched from the server. The
use of Result.partitions() is optional, and if the
Result is iterated directly, a new batch of rows will be
buffered for each 100 rows fetched. Calling a method such as
Result.all() should not be used, as this will fully
fetch all remaining rows at once and defeat the purpose of using yield_per.
Tip
The Result object may be used as a context manager as illustrated
above. When iterating with a server-side cursor, this is the best way to
ensure the Result object is closed, even if exceptions are
raised within the iteration process.
New in version 1.4.40: Added
Connection.execution_options.yield_per
as a Core level execution option to conveniently set streaming results,
buffer size, and partition size all at once in a manner that is transferrable
to that of the ORM’s similar use case.
Streaming with a dynamically growing buffer using stream_results¶
When a Result object delivered using the
Connection.execution_options.stream_results option
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. The maximum size of this buffer can be affected using the
Connection.execution_options.max_row_buffer execution option:
withengine.connect()asconn:withconn.execution_options(stream_results=True,max_row_buffer=100).execute(text("select * from table"))asresult:forrowinresult:print(f"{row}")
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.
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 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:
When using the ORM Session without extensions, the schema
translate feature is only supported as
a single schema translate map per Session. It will not work if
different schema translate maps are given on a per-statement basis, as
the ORM Session does not take current schema translate
values into account for individual objects.
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:
The above statement will generate SQL resembling
SELECTid,colFROMtableWHEREcol=:colORDERBYid, 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:
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:
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:
[rawsql] - the driver or the end-user emitted raw SQL using
Connection.exec_driver_sql() - caching does not apply
[nokey] - 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.
[generatedinXs] - 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.
[cachedsinceXsago] - 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 [rawsql],
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:
For each of these statements, the badge reads [nokey0.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 [nokey] 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:
Above, we see essentially two unique SQL strings; "INSERTINTOa(data)VALUES(?)"
and "INSERTINTOb(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 [generatedin0.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 [cachedsince0.0003533sago]. 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 [cachedsince] 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 [cachedsince], 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 [cachedsince21600secondsago], 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.
Disabling or using an alternate dictionary to cache some (or all) statements¶
The internal cache used is known as LRUCache, but this is mostly just
a dictionary. Any dictionary may be used as a cache for any series of
statements by using the Connection.execution_options.compiled_cache
option as an execution option. Execution options may be set on a statement,
on an Engine or Connection, as well as
when using the ORM Session.execute() method for SQLAlchemy-2.0
style invocations. For example, to run a series of SQL statements and have
them cached in a particular dictionary:
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 connectionwithengine.connect().execution_options(compiled_cache=None)asconn: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 dialectdoesnotsupportcaching.
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:
The typical case for dialect modification follows.
Example: Rendering LIMIT / OFFSET with post compile parameters¶
As an example, suppose a dialect overrides the SQLCompiler.limit_clause()
method, which produces the “LIMIT / OFFSET” clause for a SQL statement,
like this:
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 codedeflimit_clause(self,select,**kw):text=""limit_clause=select._limit_clauseoffset_clause=select._offset_clauseifselect._simple_int_clause(limit_clause):text+=" \n LIMIT %s"%(self.process(limit_clause.render_literal_execute(),**kw))eliflimit_clauseisnotNone:# assuming the DB doesn't support SQL expressions for LIMIT.# Otherwise render here normallyraiseexc.CompileError("dialect 'mydialect' can only render simple integers for LIMIT")ifselect._simple_int_clause(offset_clause):text+=" \n OFFSET %s"%(self.process(offset_clause.render_literal_execute(),**kw))elifoffset_clauseisnotNone:# assuming the DB doesn't support SQL expressions for OFFSET.# Otherwise render here normallyraiseexc.CompileError("dialect 'mydialect' can only render simple integers for OFFSET")returntext
The approach above will generate a compiled SELECT statement that looks like:
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.
Using Lambdas to add significant speed gains to statement production¶
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:
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 LambdaElement 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:
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:
>>> defmy_stmt(x,y):... stmt=lambda_stmt(lambda:select(func.max(x,y)))... returnstmt>>> engine=create_engine("sqlite://",echo=True)>>> withengine.connect()asconn:... print(conn.scalar(my_stmt(5,10)))... print(conn.scalar(my_stmt(12,8))){execsql}SELECT max(?, ?) AS max_1[generated in 0.00057s] (5, 10){stop}10{execsql}SELECT max(?, ?) AS max_1[cached since 0.002059s ago] (12, 8){stop}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:defmy_stmt(parameter,thing=False):stmt=lambda_stmt(lambda:select(table))stmt+=(lambdas:s.where(table.c.x>parameter)ifthingelses.where(table.c.y==parameter))returnstmt# **Do** do this:defmy_stmt(parameter,thing=False):stmt=lambda_stmt(lambda:select(table))ifthing:stmt+=lambdas:s.where(table.c.x>parameter)else:stmt+=lambdas:s.where(table.c.y==parameter)returnstmt
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:
>>> defmy_stmt(x,y):... defget_x():... returnx...... defget_y():... returny...... stmt=lambda_stmt(lambda:select(func.max(get_x(),get_y())))... returnstmt>>> withengine.connect()asconn:... 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 variablesto produce literal values since the lambda SQL system normally extractsbound 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:
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:
>>> classFoo:... def__init__(self,x,y):... self.x=x... self.y=y>>> defmy_stmt(foo):... stmt=lambda_stmt(lambda:select(func.max(foo.x,foo.y)))... returnstmt>>> withengine.connect()asconn:... print(conn.scalar(my_stmt(Foo(5,10))))Traceback (most recent call last): # ...sqlalchemy.exc.InvalidRequestError: Closure variable named 'foo' inside oflambda callable <code object <lambda> at 0x7fed15f35450, file"<stdin>", line 2> does not refer to a cacheable SQL element, and alsodoes not appear to be serving as a SQL literal bound value based on thedefault SQL expression returned by the function. This variable needs toremain outside the scope of a SQL-generating lambda so that a proper cachekey may be generated from the lambda's state. Evaluate this variableoutside of the lambda, set track_on=[<elements>] to explicitly selectclosure elements to track, or set track_closure_variables=False to excludeclosure 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:
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:
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:
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:
>>> fromsqlalchemyimportselect,column>>> stmt=select(column("q"))>>> cache_key=stmt._generate_cache_key()>>> print(cache_key)# somewhat paraphrasedCacheKey(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:
>>> fromsqlalchemyimportlambda_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:
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:
For a series of examples of “lambda” caching with performance comparisons,
see the “short_selects” test suite within the Performance
performance example.
“Insert Many Values” Behavior for INSERT statements¶
The insertmanyvalues feature is a transparently available
performance feature which requires no end-user intervention in order for
it to take place as needed. This section describes the architecture
of the feature as well as how to measure its performance and tune its
behavior in order to optimize the speed of bulk INSERT statements,
particularly as used by the ORM.
As more databases have added support for INSERT..RETURNING, SQLAlchemy has
undergone a major change in how it approaches the subject of INSERT statements
where there’s a need to acquire server-generated values, most importantly
server-generated primary key values which allow the new row to be referenced in
subsequent operations. In particular, this scenario has long been a significant
performance issue in the ORM, which relies on being able to retrieve
server-generated primary key values in order to correctly populate the
identity map.
With recent support for RETURNING added to SQLite and MariaDB, SQLAlchemy no
longer needs to rely upon the single-row-only
cursor.lastrowid attribute
provided by the DBAPI for most backends; RETURNING may now be used for
all SQLAlchemy-included backends with the exception
of MySQL. The remaining performance
limitation, that the
cursor.executemany() DBAPI
method does not allow for rows to be fetched, is resolved for most backends by
foregoing the use of executemany() and instead restructuring individual
INSERT statements to each accommodate a large number of rows in a single
statement that is invoked using cursor.execute(). This approach originates
from the
psycopg2 fast execution helpers
feature of the psycopg2 DBAPI, which SQLAlchemy incrementally added more
and more support towards in recent release series.
The feature is enabled for all backend included in SQLAlchemy that support
RETURNING, with the exception of Oracle for which both the cx_Oracle and
OracleDB drivers offer their own equivalent feature. The feature normally takes
place when making use of the Insert.returning() method of an
Insert construct in conjunction with executemany
execution, which occurs when passing a list of dictionaries to the
Connection.execute.parameters parameter of the
Connection.execute() or Session.execute() methods (as
well as equivalent methods under asyncio and
shorthand methods like Session.scalars()). It also takes place
within the ORM unit of work process when using methods such as
Session.add() and Session.add_all() to add rows.
For SQLAlchemy’s included dialects, support or equivalent support is currently
as follows:
SQLite - supported for SQLite versions 3.35 and above
PostgreSQL - all supported Postgresql versions (9 and above)
SQL Server - all supported SQL Server versions [1]
MariaDB - supported for MariaDB versions 10.5 and above
MySQL - no support, no RETURNING feature is present
Oracle - supports RETURNING with executemany using native cx_Oracle / OracleDB
APIs, for all supported Oracle versions 9 and above, using multi-row OUT
parameters. This is not the same implementation as “executemanyvalues”, however has
the same usage patterns and equivalent performance benefits.
The feature has two modes of operation, which are selected transparently on a
per-dialect, per-Table basis. One is batched mode,
which reduces the number of database round trips by rewriting an
INSERT statement of the form:
where above, the statement is organized against a subset (a “batch”) of the
input data, the size of which is determined by the database backend as well as
the number of parameters in each batch to correspond to known limits for
statement size / number of parameters. The feature then executes the INSERT
statement once for each batch of input data until all records are consumed,
concatenating the RETURNING results for each batch into a single large
rowset that’s available from a single Result object.
This “batched” form allows INSERT of many rows using much fewer database round
trips, and has been shown to allow dramatic performance improvements for most
backends where it’s supported.
The “batch” mode query illustrated in the previous section does not guarantee
the order of records returned would correspond with that of the input data.
When used by the SQLAlchemy ORM unit of work process, as well as for
applications which correlate returned server-generated values with input data,
the Insert.returning() and UpdateBase.return_defaults()
methods include an option
Insert.returning.sort_by_parameter_order which indicates that
“insertmanyvalues” mode should guarantee this correspondence. This is not
related to the order in which records are actually INSERTed by the database
backend, which is not assumed under any circumstances; only that the
returned records should be organized when received back to correspond to the
order in which the original input data was passed.
When the Insert.returning.sort_by_parameter_order parameter is
present, for tables that use server-generated integer primary key values such
as IDENTITY, PostgreSQL SERIAL, MariaDB AUTO_INCREMENT, or SQLite’s
ROWID scheme, “batch” mode may instead opt to use a more complex
INSERT..RETURNING form, in conjunction with post-execution sorting of rows
based on the returned values, or if
such a form is not available, the “insertmanyvalues” feature may gracefully
degrade to “non-batched” mode which runs individual INSERT statements for each
parameter set.
For example, on SQL Server when an auto incrementing IDENTITY column is
used as the primary key, the following SQL form is used:
A similar form is used for PostgreSQL as well, when primary key columns use
SERIAL or IDENTITY. The above form does not guarantee the order in which
rows are inserted. However, it does guarantee that the IDENTITY or SERIAL
values will be created in order with each parameter set [2]. The
“insertmanyvalues” feature then sorts the returned rows for the above INSERT
statement by incrementing integer identity.
For the SQLite database, there is no appropriate INSERT form that can
correlate the production of new ROWID values with the order in which
the parameter sets are passed. As a result, when using server-generated
primary key values, the SQLite backend will degrade to “non-batched”
mode when ordered RETURNING is requested.
For MariaDB, the default INSERT form used by insertmanyvalues is sufficient,
as this database backend will line up the
order of AUTO_INCREMENT with the order of input data when using InnoDB [3].
For a client-side generated primary key, such as when using the Python
uuid.uuid4() function to generate new values for a Uuid column,
the “insertmanyvalues” feature transparently includes this column in the
RETURNING records and correlates its value to that of the given input records,
thus maintaining correspondence between input records and result rows. From
this, it follows that all backends allow for batched, parameter-correlated
RETURNING order when client-side-generated primary key values are used.
The subject of how “insertmanyvalues” “batch” mode determines a column or
columns to use as a point of correspondence between input parameters and
RETURNING rows is known as an insert sentinel, which is a specific
column or columns that are used to track such values. The “insert sentinel” is
normally selected automatically, however can also be user-configuration for
extremely special cases; the section
Configuring Sentinel Columns describes this.
For backends that do not offer an appropriate INSERT form that can deliver
server-generated values deterministically aligned with input values, or
for Table configurations that feature other kinds of
server generated primary key values, “insertmanyvalues” mode will make use
of non-batched mode when guaranteed RETURNING ordering is requested.
For Table configurations that do not have client side primary
key values, and offer server-generated primary key values (or no primary key)
that the database in question is not able to invoke in a deterministic or
sortable way relative to multiple parameter sets, the “insertmanyvalues”
feature when tasked with satisfying the
Insert.returning.sort_by_parameter_order requirement for an
Insert statement may instead opt to use non-batched mode.
In this mode, the original SQL form of INSERT is maintained, and the
“insertmanyvalues” feature will instead run the statement as given for each
parameter set individually, organizing the returned rows into a full result
set. Unlike previous SQLAlchemy versions, it does so in a tight loop that
minimizes Python overhead. In some cases, such as on SQLite, “non-batched” mode
performs exactly as well as “batched” mode.
For both “batched” and “non-batched” modes, the feature will necessarily
invoke multiple INSERT statements using the DBAPI cursor.execute() method,
within the scope of single call to the Core-level
Connection.execute() method,
with each statement containing up to a fixed limit of parameter sets.
This limit is configurable as described below at Controlling the Batch Size.
The separate calls to cursor.execute() are logged individually and
also individually passed along to event listeners such as
ConnectionEvents.before_cursor_execute() (see Logging and Events
below).
In typical cases, the “insertmanyvalues” feature in order to provide
INSERT..RETURNING with deterministic row order will automatically determine a
sentinel column from a given table’s primary key, gracefully degrading to “row
at a time” mode if one cannot be identified. As a completely optional
feature, to get full “insertmanyvalues” bulk performance for tables that have
server generated primary keys whose default generator functions aren’t
compatible with the “sentinel” use case, other non-primary key columns may be
marked as “sentinel” columns assuming they meet certain requirements. A typical
example is a non-primary key Uuid column with a client side
default such as the Python uuid.uuid4() function. There is also a construct to create
simple integer columns with a a client side integer counter oriented towards
the “insertmanyvalues” use case.
Sentinel columns may be indicated by adding Column.insert_sentinel
to qualifying columns. The most basic “qualifying” column is a not-nullable,
unique column with a client side default, such as a UUID column as follows:
importuuidfromsqlalchemyimportColumnfromsqlalchemyimportFetchedValuefromsqlalchemyimportIntegerfromsqlalchemyimportStringfromsqlalchemyimportTablefromsqlalchemyimportUuidmy_table=Table("some_table",metadata,# assume some arbitrary server-side function generates# primary key values, so cannot be tracked by a bulk insertColumn("id",String(50),server_default=FetchedValue(),primary_key=True),Column("data",String(50)),Column("uniqueid",Uuid(),default=uuid.uuid4,nullable=False,unique=True,insert_sentinel=True,),)
When using ORM Declarative models, the same forms are available using
the mapped_column construct:
While the values generated by the default generator must be unique, the
actual UNIQUE constraint on the above “sentinel” column, indicated by the
unique=True parameter, itself is optional and may be omitted if not
desired.
There is also a special form of “insert sentinel” that’s a dedicated nullable
integer column which makes use of a special default integer counter that’s only
used during “insertmanyvalues” operations; as an additional behavior, the
column will omit itself from SQL statements and result sets and behave in a
mostly transparent manner. It does need to be physically present within
the actual database table, however. This style of Column
may be constructed using the function insert_sentinel():
When using ORM Declarative, a Declarative-friendly version of
insert_sentinel() is available called
orm_insert_sentinel(), which has the ability to be used on the Base
class or a mixin; if packaged using declared_attr(), the column will
apply itself to all table-bound subclasses including within joined inheritance
hierarchies:
In the example above, both “my_table” and “sub_table” will have an additional
integer column named “_sentinel” that can be used by the “insertmanyvalues”
feature to help optimize bulk inserts used by the ORM.
A key characteristic of “insertmanyvalues” is that the size of the INSERT
statement is limited on a fixed max number of “values” clauses as well as a
dialect-specific fixed total number of bound parameters that may be represented
in one INSERT statement at a time. When the number of parameter dictionaries
given exceeds a fixed limit, or when the total number of bound parameters to be
rendered in a single INSERT statement exceeds a fixed limit (the two fixed
limits are separate), multiple INSERT statements will be invoked within the
scope of a single Connection.execute() call, each of which
accommodate for a portion of the parameter dictionaries, referred towards as a
“batch”. The number of parameter dictionaries represented within each
“batch” is then known as the “batch size”. For example, a batch size of
500 means that each INSERT statement emitted will INSERT at most 500 rows.
It’s potentially important to be able to adjust the batch size,
as a larger batch size may be more performant for an INSERT where the value
sets themselves are relatively small, and a smaller batch size may be more
appropriate for an INSERT that uses very large value sets, where both the size
of the rendered SQL as well as the total data size being passed in one
statement may benefit from being limited to a certain size based on backend
behavior and memory constraints. For this reason the batch size
can be configured on a per-Engine as well as a per-statement
basis. The parameter limit on the other hand is fixed based on the known
characteristics of the database in use.
The batch size defaults to 1000 for most backends, with an additional
per-dialect “max number of parameters” limiting factor that may reduce the
batch size further on a per-statement basis. The max number of parameters
varies by dialect and server version; the largest size is 32700 (chosen as a
healthy distance away from PostgreSQL’s limit of 32767 and SQLite’s modern
limit of 32766, while leaving room for additional parameters in the statement
as well as for DBAPI quirkiness). Older versions of SQLite (prior to 3.32.0)
will set this value to 999. MariaDB has no established limit however 32700
remains as a limiting factor for SQL message size.
The value of the “batch size” can be affected Engine
wide via the create_engine.insertmanyvalues_page_size parameter.
Such as, to affect INSERT statements to include up to 100 parameter sets
in each statement:
The “insertmanyvalues” feature integrates fully with SQLAlchemy’s statement
logging as well as cursor events such as ConnectionEvents.before_cursor_execute().
When the list of parameters is broken into separate batches, each INSERT
statement is logged and passed to event handlers individually. This is a major change
compared to how the psycopg2-only feature worked in previous 1.x series of
SQLAlchemy, where the production of multiple INSERT statements was hidden from
logging and events. Logging display will truncate the long lists of parameters for readability,
and will also indicate the specific batch of each statement. The example below illustrates
an excerpt of this logging:
INSERT INTO a (data, x, y) VALUES (?, ?, ?), ... 795 characters truncated ... (?, ?, ?), (?, ?, ?) RETURNING id
[generated in 0.00177s (insertmanyvalues) 1/10 (unordered)] ('d0', 0, 0, 'd1', ...
INSERT INTO a (data, x, y) VALUES (?, ?, ?), ... 795 characters truncated ... (?, ?, ?), (?, ?, ?) RETURNING id
[insertmanyvalues 2/10 (unordered)] ('d100', 100, 1000, 'd101', ...
...
INSERT INTO a (data, x, y) VALUES (?, ?, ?), ... 795 characters truncated ... (?, ?, ?), (?, ?, ?) RETURNING id
[insertmanyvalues 10/10 (unordered)] ('d900', 900, 9000, 'd901', ...
When non-batch mode takes place, logging
will indicate this along with the insertmanyvalues message:
...
INSERT INTO a (data, x, y) VALUES (?, ?, ?) RETURNING id
[insertmanyvalues 67/78 (ordered; batch not supported)] ('d66', 66, 66)
INSERT INTO a (data, x, y) VALUES (?, ?, ?) RETURNING id
[insertmanyvalues 68/78 (ordered; batch not supported)] ('d67', 67, 67)
INSERT INTO a (data, x, y) VALUES (?, ?, ?) RETURNING id
[insertmanyvalues 69/78 (ordered; batch not supported)] ('d68', 68, 68)
INSERT INTO a (data, x, y) VALUES (?, ?, ?) RETURNING id
[insertmanyvalues 70/78 (ordered; batch not supported)] ('d69', 69, 69)
...
The PostgreSQL, SQLite, and MariaDB dialects offer backend-specific
“upsert” constructs insert(), insert()
and insert(), which are each Insert constructs that
have an additional method such as on_conflict_do_update()`or``on_duplicate_key(). These constructs also support “insertmanyvalues”
behaviors when they are used with RETURNING, allowing efficient upserts
with RETURNING to take place.
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.
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. Use the
Engine.dispose.close parameter set to False in this case.
See the section Using Connection Pools with Multiprocessing or os.fork() for more background on this
use case.
Within test suites or multitenancy scenarios where many
ad-hoc, short-lived Engine objects may be created and disposed.
Connections that are checked out are not discarded when the
engine is disposed or garbage collected, as these connections are still
strongly referenced elsewhere by the application.
However, after Engine.dispose() is called, those
connections are no longer associated with that Engine; when they
are closed, they will be returned to their now-orphaned connection pool
which will ultimately be garbage collected, once all connections which refer
to it are also no longer referenced anywhere.
Since this process is not easy to control, it is strongly recommended that
Engine.dispose() is called only after all checked out connections
are checked in or otherwise de-associated from their pool.
An alternative for applications that are negatively impacted by the
Engine object’s use of connection pooling is to disable pooling
entirely. This typically incurs only a modest performance impact upon the
use of new connections, and means that when a connection is checked in,
it is entirely closed out and is not held in memory. See Switching Pool Implementations
for guidelines on how to disable pooling.
Working with Driver SQL and Raw DBAPI Connections¶
The introduction on using Connection.execute() made use of the
text() construct in order to illustrate how textual SQL statements
may be invoked. When working with SQLAlchemy, textual SQL is actually more
of the exception rather than the norm, as the Core expression language
and the ORM both abstract away the textual representation of SQL. However, the
text() construct itself also provides some abstraction of textual
SQL in that it normalizes how bound parameters are passed, as well as that
it supports datatyping behavior for parameters and result set rows.
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:
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:
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.
Calling Stored Procedures and User Defined Functions¶
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:
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.cfg as follows:
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:
SQLAlchemy also allows a dialect to be registered within the current process, bypassing
the need for separate installation. Use the register() function as follows:
Provides high-level functionality for a wrapped DB-API 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.interfaces.ConnectionEventsTarget, sqlalchemy.inspection.Inspectable)
The returned object is an instance of RootTransaction.
This object represents the “scope” of the transaction,
which completes when either the Transaction.rollback()
or Transaction.commit() method is called; the object
also works as a context manager as illustrated above.
The Connection.begin() method begins a
transaction that normally will be begun in any case when the connection
is first used to execute a statement. The reason this method might be
used would be to invoke the ConnectionEvents.begin()
event at a specific time, or to organize code within the scope of a
connection checkout in terms of context managed blocks, such as:
The above code is not fundamentally any different in its behavior than
the following code which does not use
Connection.begin(); the below style is referred towards
as “commit as you go” style:
From a database point of view, the Connection.begin()
method does not emit any SQL or change the state of the underlying
DBAPI connection in any way; the Python DBAPI does not have any
concept of explicit transaction begin.
The returned object is an instance of
NestedTransaction, which includes transactional
methods NestedTransaction.commit() and
NestedTransaction.rollback(); for a nested transaction,
these methods correspond to the operations “RELEASE SAVEPOINT <name>”
and “ROLLBACK TO SAVEPOINT <name>”. The name of the savepoint is local
to the NestedTransaction object and is generated
automatically. Like any other Transaction, the
NestedTransaction may be used as a context manager as
illustrated above which will “release” or “rollback” corresponding to
if the operation within the block were successful or raised an
exception.
Nested transactions require SAVEPOINT support in the underlying
database, else the behavior is undefined. SAVEPOINT is commonly used to
run operations within a transaction that may fail, while continuing the
outer transaction. E.g.:
fromsqlalchemyimportexcwithengine.begin()asconnection:trans=connection.begin_nested()try:connection.execute(table.insert(),{"username":"sandy"})trans.commit()exceptexc.IntegrityError:# catch for duplicate usernametrans.rollback()# rollback to savepoint# outer transaction continuesconnection.execute(...)
withengine.connect()asconnection:# begin() wasn't calledwithconnection.begin_nested():willauto-"begin()"firstconnection.execute(...)# savepoint is releasedconnection.execute(...)# explicitly commit outer transactionconnection.commit()# can continue working with connection here
Changed in version 2.0: Connection.begin_nested() will now participate
in the connection “autobegin” behavior that is new as of
2.0 / “future” style connections in 1.4.
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.
This has the effect of also calling Connection.rollback()
if any transaction is in place.
After Connection.close() is called, the
Connection is permanently in a closed state,
and will allow no further operations.
Commit the transaction that is currently in progress.
This method commits the current transaction if one has been started.
If no transaction was started, the method has no effect, assuming
the connection is in a non-invalidated state.
A transaction is begun on a Connection automatically
whenever a statement is first executed, or when the
Connection.begin() method is called.
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.
Detach the underlying DB-API connection from its connection pool.
E.g.:
withengine.connect()asconn: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).
Executes a string SQL statement on the DBAPI cursor directly,
without any SQL compilation steps.
This can be used to pass any string directly to the
cursor.execute() method of the DBAPI in use.
Parameters:
statement – The statement str to be executed. Bound parameters
must use the underlying DBAPI’s paramstyle, such as “qmark”,
“pyformat”, “format”, etc.
parameters – represent bound parameter values to be used in the
execution. The format is one of: a dictionary of named parameters,
a tuple of positional parameters, or a list containing either
dictionaries or tuples for multiple-execute support.
parameters – parameters which will be bound into the statement.
This may be either a dictionary of parameter names to values,
or a mutable sequence (e.g. a list) of dictionaries. When a
list of dictionaries is passed, the underlying statement execution
will make use of the DBAPI cursor.executemany() method.
When a single dictionary is passed, the DBAPI cursor.execute()
method will be used.
execution_options – optional dictionary of execution options,
which will be associated with the statement execution. This
dictionary can provide a subset of the options that are accepted
by Connection.execution_options().
Set non-SQL options for the connection which take effect
during execution.
This method modifies this Connectionin-place;
the return value is the same Connection object
upon which the method is called. Note that this is in contrast
to the behavior of the execution_options methods on other
objects such as Engine.execution_options() and
Executable.execution_options(). The rationale is that many
such execution options necessarily modify the state of the base
DBAPI connection in any case so there is no feasible means of
keeping the effect of such an option localized to a “sub” connection.
Changed in version 2.0: The Connection.execution_options()
method, in contrast to other objects with this method, modifies
the connection in-place without creating copy of it.
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.
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.
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.
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. The isolation level will remain
at the given setting until explicitly changed, or when the DBAPI
connection itself is released to the connection pool, i.e. the
Connection.close() method is called, at which time an
event handler will emit additional statements on the DBAPI connection
in order to revert the isolation level change.
Note
The isolation_level execution option may only be
established before the Connection.begin() method is
called, as well as before any SQL statements are emitted which
would otherwise trigger “autobegin”, or directly after a call to
Connection.commit() or
Connection.rollback(). A database cannot change the
isolation level on a transaction in progress.
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 selected isolation level
re-applied to it automatically.
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.
Indicate to the dialect that results should be
“streamed” and not pre-buffered, if possible. For backends
such as PostgreSQL, MySQL and MariaDB, this indicates the use of
a “server side cursor” as opposed to a client side cursor.
Other backends such as that of Oracle may already use server
side cursors by default.
The usage of
Connection.execution_options.stream_results is
usually combined with setting a fixed number of rows to to be fetched
in batches, to allow for efficient iteration of database rows while
at the same time not loading all result rows into memory at once;
this can be configured on a Result object using the
Result.yield_per() method, after execution has
returned a new Result. If
Result.yield_per() is not used,
the Connection.execution_options.stream_results
mode of operation will instead use a dynamically sized buffer
which buffers sets of rows at a time, growing on each batch
based on a fixed growth size up until a limit which may
be configured using the
Connection.execution_options.max_row_buffer
parameter.
Available on: Connection,
Engine. Number of rows to format into an
INSERT statement when the statement uses “insertmanyvalues” mode,
which is a paged form of bulk insert that is used for many backends
when using executemany execution typically in conjunction
with RETURNING. Defaults to 1000. May also be modified on a
per-engine basis using the
create_engine.insertmanyvalues_page_size parameter.
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.
Return the current actual isolation level that’s present on
the database within the scope of this connection.
This attribute will perform a live SQL operation against the database
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. This will be one of the four actual isolation
modes READUNCOMMITTED, READCOMMITTED, REPEATABLEREAD,
SERIALIZABLE. It will not include the AUTOCOMMIT isolation
level setting. Third party dialects may also feature additional
isolation level settings.
Note
This method will not report on the AUTOCOMMIT
isolation level, which is a separate dbapi setting that’s
independent of actual isolation level. When AUTOCOMMIT is
in use, the database connection still has a “traditional” isolation
mode in effect, that is typically one of the four values
READUNCOMMITTED, READCOMMITTED, REPEATABLEREAD,
SERIALIZABLE.
Compare to the Connection.default_isolation_level
accessor which returns the isolation level that is present on the
database at initial connection time.
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.
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.
Roll back the transaction that is currently in progress.
This method rolls back the current transaction if one has been started.
If no transaction was started, the method has no effect. If a
transaction was started and the connection is in an invalidated state,
the transaction is cleared using this method.
A transaction is begun on a Connection automatically
whenever a statement is first executed, or when the
Connection.begin() method is called.
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
importloggingfromsqlalchemy.engineimportCreateEnginePluginfromsqlalchemyimporteventclassLogCursorEventsPlugin(CreateEnginePlugin):def__init__(self,url,kwargs):# consume the parameter "log_cursor_logging_name" from the# URL querylogging_name=url.query.get("log_cursor_logging_name","log_cursor")self.log=logging.getLogger(logging_name)defupdate_url(self,url):"update the URL to one that no longer includes our parameters"returnurl.difference_update_query(["log_cursor_logging_name"])defengine_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:
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:
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:
classMyPlugin(CreateEnginePlugin):def__init__(self,url,kwargs):ifhasattr(CreateEnginePlugin,"update_url"):# detect the 1.4 APIself.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 directlyself.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)defupdate_url(self,url):# this method is only called in the 1.4 versionreturnurl.difference_update_query(["my_argument_one","my_argument_two"])
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).
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.
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.
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.
The Connection acts as a Python context manager, so
the typical use of this method looks like:
withengine.connect()asconnection:connection.execute(text("insert into table values ('foo')"))connection.commit()
Where above, after the block is completed, the connection is “closed”
and its underlying DBAPI resources are returned to the connection pool.
This also has the effect of rolling back any transaction that
was explicitly begun or was begun via autobegin, and will
emit the ConnectionEvents.rollback() event if one was
started and is still in progress.
Dispose of the connection pool used by this
Engine.
A new connection pool is created immediately after the old one has been
disposed. The previous connection pool is disposed either actively, by
closing out all currently checked-in connections in that pool, or
passively, by losing references to it but otherwise not closing any
connections. The latter strategy is more appropriate for an initializer
in a forked Python process.
Parameters:
close –
if left at its default of True, 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.
If set to False, the previous connection pool is de-referenced,
and otherwise not touched in any way.
New in version 1.4.33: Added the Engine.dispose.close
parameter to allow the replacement of a connection pool in a child
process without interfering with the connections used by the parent
process.
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 schemes where multiple Engine
objects refer to the same connection pool, but are differentiated
by options that affect some execution-level behavior for each
engine. One such example is breaking into separate “reader” and
“writer” Engine instances, where one
Engine
has a lower isolation level setting configured or is even
transaction-disabled using “autocommit”. An example of this
configuration is at Maintaining Multiple Isolation Levels for a Single Engine.
Another example is one that
uses a custom option shard_id which is consumed by an event
to change the current schema on a database connection:
The above recipe illustrates two Engine objects that
will each serve as factories for Connection objects
that have pre-established “shard_id” execution options present. A
ConnectionEvents.before_cursor_execute() event handler
then interprets this execution option to emit a MySQL use statement
to switch databases before a statement execution, while at the same
time keeping track of which database we’ve established using the
Connection.info dictionary.
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.
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().
This object exists solely to be passed to the
DialectEvents.handle_error() event,
supporting an interface that
can be extended without backwards-incompatibility.
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.
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
DialectEvents.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.
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.
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.
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 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.
fromsqlalchemyimportcreate_engineengine=create_engine("postgresql+psycopg2://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:
withconnection.begin():connection.execute(text("insert into x (a, b) values (1, 2)"))
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.
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 a 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 a specific “server
side cursor” mode separate from its default mode of operation.
A Result that is representing state from a DBAPI cursor.
Changed in version 1.4: The CursorResult`
class replaces the previous ResultProxy interface.
This 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.
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.
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.
Changed in version 2.0: Due to a bug in 1.4, the
Result.columns() method had an incorrect behavior
where calling upon the method with just one index would cause the
Result object to yield scalar values rather than
Row objects. In version 2.0, this behavior
has been corrected such that calling upon
Result.columns() with a single index will
produce a Result object that continues
to yield Row objects, which include
only a single column.
*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.
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.
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.
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.
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.
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.
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.
inherited from thesqlalchemy.engine._WithKeys.keysmethod ofsqlalchemy.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.
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.
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.
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().
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.
When using the ORM, the Result.partitions() method
is typically more effective from a memory perspective when it is
combined with use of the
yield_per execution option,
which instructs both the DBAPI driver to use server side cursors,
if available, as well as instructs the ORM loading internals to only
build a certain amount of ORM objects from a result at a time before
yielding them out.
New in version 1.4.
Parameters:
size – indicate the maximum number of rows to be present
in each list yielded. If None, makes use of the value set by
the Result.yield_per(), method, if it were called,
or the Connection.execution_options.yield_per
execution option, which is equivalent in this regard. If
yield_per weren’t set, it makes use of the
Result.fetchmany() default, which may be backend
specific and not well defined.
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.
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.
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.
Parameters:
index – integer or row key indicating the column to be fetched
from each row, defaults to 0 indicating the first column.
This method is for the benefit of the SQLAlchemy ORM and is
not intended for general use.
“horizontally splices” means that for each row in the first and second
result sets, a new row that concatenates the two rows together is
produced, which then becomes the new row. The incoming
CursorResult must have the identical number of rows. It is
typically expected that the two result sets come from the same sort
order as well, as the result rows are spliced together based on their
position in the result.
The expected use case here is so that multiple INSERT..RETURNING
statements (which definitely need to be sorted) against different
tables can produce a single result that looks like a JOIN of those two
tables.
This method is for the benefit of the SQLAlchemy ORM and is
not intended for general use.
“vertically splices” means the rows of the given result are appended to
the rows of this cursor result. The incoming CursorResult
must have rows that represent the identical list of columns in the
identical order as they are in this CursorResult.
Apply a “typed tuple” typing filter to returned rows.
This method returns the same Result object
at runtime,
however annotates as returning a TupleResult object
that will indicate to PEP 484 typing tools that plain typed
Tuple instances are returned rather than rows. This allows
tuple unpacking and __getitem__ access of Row
objects to by typed, for those cases where the statement invoked
itself included typing information.
Apply unique filtering to the objects returned by this
Result.
When this filter is applied with no arguments, the rows or objects
returned will filtered such that each row is returned uniquely. The
algorithm used to determine this uniqueness is by default the Python
hashing identity of the whole tuple. In some cases a specialized
per-entity hashing scheme may be used, such as when using the ORM, a
scheme is applied which works against the primary key identity of
returned objects.
The unique filter is applied after all other filters, which means
if the columns returned have been refined using a method such as the
Result.columns() or Result.scalars()
method, the uniquing is applied to only the column or columns
returned. This occurs regardless of the order in which these
methods have been called upon the Result object.
The unique filter also changes the calculus used for methods like
Result.fetchmany() and Result.partitions().
When using Result.unique(), these methods will continue
to yield the number of rows or objects requested, after uniquing
has been applied. However, this necessarily impacts the buffering
behavior of the underlying cursor or datasource, such that multiple
underlying calls to cursor.fetchmany() may be necessary in order
to accumulate enough objects in order to provide a unique collection
of the requested size.
Parameters:
strategy – a callable that will be applied to rows or objects
being iterated, which should return an object that represents the
unique value of the row. A Python set() is used to store
these identities. If not passed, a default uniqueness strategy
is used which may have been assembled by the source of this
Result object.
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 a 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 a specific “server
side cursor” mode separate from its default mode of operation.
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.
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.
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.
Changed in version 2.0: Due to a bug in 1.4, the
Result.columns() method had an incorrect behavior
where calling upon the method with just one index would cause the
Result object to yield scalar values rather than
Row objects. In version 2.0, this behavior
has been corrected such that calling upon
Result.columns() with a single index will
produce a Result object that continues
to yield Row objects, which include
only a single column.
*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.
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.
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.
inherited from thesqlalchemy.engine._WithKeys.keysmethod ofsqlalchemy.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.
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.
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().
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.
When using the ORM, the Result.partitions() method
is typically more effective from a memory perspective when it is
combined with use of the
yield_per execution option,
which instructs both the DBAPI driver to use server side cursors,
if available, as well as instructs the ORM loading internals to only
build a certain amount of ORM objects from a result at a time before
yielding them out.
New in version 1.4.
Parameters:
size – indicate the maximum number of rows to be present
in each list yielded. If None, makes use of the value set by
the Result.yield_per(), method, if it were called,
or the Connection.execution_options.yield_per
execution option, which is equivalent in this regard. If
yield_per weren’t set, it makes use of the
Result.fetchmany() default, which may be backend
specific and not well defined.
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.
Parameters:
index – integer or row key indicating the column to be fetched
from each row, defaults to 0 indicating the first column.
Apply a “typed tuple” typing filter to returned rows.
This method returns the same Result object
at runtime,
however annotates as returning a TupleResult object
that will indicate to PEP 484 typing tools that plain typed
Tuple instances are returned rather than rows. This allows
tuple unpacking and __getitem__ access of Row
objects to by typed, for those cases where the statement invoked
itself included typing information.
Apply unique filtering to the objects returned by this
Result.
When this filter is applied with no arguments, the rows or objects
returned will filtered such that each row is returned uniquely. The
algorithm used to determine this uniqueness is by default the Python
hashing identity of the whole tuple. In some cases a specialized
per-entity hashing scheme may be used, such as when using the ORM, a
scheme is applied which works against the primary key identity of
returned objects.
The unique filter is applied after all other filters, which means
if the columns returned have been refined using a method such as the
Result.columns() or Result.scalars()
method, the uniquing is applied to only the column or columns
returned. This occurs regardless of the order in which these
methods have been called upon the Result object.
The unique filter also changes the calculus used for methods like
Result.fetchmany() and Result.partitions().
When using Result.unique(), these methods will continue
to yield the number of rows or objects requested, after uniquing
has been applied. However, this necessarily impacts the buffering
behavior of the underlying cursor or datasource, such that multiple
underlying calls to cursor.fetchmany() may be necessary in order
to accumulate enough objects in order to provide a unique collection
of the requested size.
Parameters:
strategy – a callable that will be applied to rows or objects
being iterated, which should return an object that represents the
unique value of the row. A Python set() is used to store
these identities. If not passed, a default uniqueness strategy
is used which may have been assembled by the source of this
Result object.
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 a 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 a specific “server
side cursor” mode separate from its default mode of operation.
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.
inherited from thesqlalchemy.engine._WithKeys.keysmethod ofsqlalchemy.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.
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.
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.
At runtime, this method returns “self”; the Row object is
already a named tuple. However, at the typing level, if this
Row is typed, the “tuple” return type will be a PEP 484Tuple datatype that contains typing information about individual
elements, supporting typed unpacking and attribute access.
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:
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.