Project Versions

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.

For a multiple-process application that uses the os.fork system call, or
for example the Python multiprocessing module, it’s usually required that a
separate Engine be used for each child process. This is because the
Engine maintains a reference to a connection pool that ultimately
references DBAPI connections - these tend to not be portable across process
boundaries. An Engine that is configured not to use pooling (which
is achieved via the usage of NullPool) does not have this
requirement.

The engine can be used directly to issue SQL to the database. The most generic
way is first procure a connection resource, which you get via the
Engine.connect() method:

The connection is an instance of Connection,
which 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 returned result is an instance of ResultProxy, which
references a DBAPI cursor and provides a largely compatible interface
with that of the DBAPI cursor. The DBAPI cursor will be closed
by the ResultProxy when all of its result rows (if any) are
exhausted. A ResultProxy that returns no rows, such as that of
an UPDATE statement (without any returned rows),
releases cursor resources immediately upon construction.

When the close() method is called, the referenced DBAPI
connection is released to the connection pool. From the perspective
of the database itself, nothing is actually “closed”, assuming pooling is
in use. The pooling mechanism issues a rollback() call on the DBAPI
connection so that any transactional state or locks are removed, and
the connection is ready for its next usage.

The above procedure can be performed in a shorthand way by using the
execute() method of Engine itself:

Where above, the execute() method acquires a new
Connection on its own, executes the statement with that object,
and returns the ResultProxy. In this case, the ResultProxy
contains a special flag known as close_with_result, which indicates
that when its underlying DBAPI cursor is closed, the Connection
object itself is also closed, which again returns the DBAPI connection
to the connection pool, releasing transactional resources.

If the ResultProxy potentially has rows remaining, it can be
instructed to close out its resources explicitly:

result.close()

If the ResultProxy has pending rows remaining and is dereferenced by
the application without being closed, Python garbage collection will
ultimately close out the cursor as well as trigger a return of the pooled
DBAPI connection resource to the pool (SQLAlchemy achieves this by the usage
of weakref callbacks - never the __del__ method) - however it’s never a
good idea to rely upon Python garbage collection to manage resources.

Our example above illustrated the execution of a textual SQL string.
The execute() method can of course accommodate more than
that, including the variety of SQL expression constructs described
in SQL Expression Language 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.

connection=engine.connect()trans=connection.begin()try:r1=connection.execute(table1.select())connection.execute(table1.insert(),col1=7,col2='this is some data')trans.commit()except:trans.rollback()raise

The above block can be created more succinctly using context
managers, either given an Engine:

# runs a transactionwithengine.begin()asconnection:r1=connection.execute(table1.select())connection.execute(table1.insert(),col1=7,col2='this is some data')

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

# method_a starts a transaction and calls method_bdefmethod_a(connection):trans=connection.begin()# open a transactiontry:method_b(connection)trans.commit()# transaction is committed hereexcept:trans.rollback()# this rolls back the transaction unconditionallyraise# method_b also starts a transactiondefmethod_b(connection):trans=connection.begin()# open a transaction - this runs in the context of method_a's transactiontry:connection.execute("insert into mytable values ('bat', 'lala')")connection.execute(mytable.insert(),col1='bat',col2='lala')trans.commit()# transaction is not committed yetexcept:trans.rollback()# this rolls back the transaction unconditionallyraise# open a Connection and call method_aconn=engine.connect()method_a(conn)conn.close()

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

The “threadlocal” engine strategy is an optional feature which
can be used by non-ORM applications to associate transactions
with the current thread, such that all parts of the
application can participate in that transaction implicitly without the need to
explicitly reference a Connection.

Note

The “threadlocal” feature is generally discouraged. It’s
designed for a particular pattern of usage which is generally
considered as a legacy pattern. It has no impact on the “thread safety”
of SQLAlchemy components
or one’s application. It also should not be used when using an ORM
Session object, as the
Session itself represents an ongoing
transaction and itself handles the job of maintaining connection and
transactional resources.

Enabling threadlocal is achieved as follows:

db=create_engine('mysql://localhost/test',strategy='threadlocal')

The above Engine will now acquire a Connection using
connection resources derived from a thread-local variable whenever
Engine.execute() or Engine.contextual_connect() is called. This
connection resource is maintained as long as it is referenced, which allows
multiple points of an application to share a transaction while using
connectionless execution:

Calling close() on the “contextual” connection does not release
its resources until all other usages of that resource are closed as well, including
that any ongoing transactions are rolled back or committed.

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

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

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

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

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.

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.

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:

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.

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

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

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

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

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

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

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

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

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

Available on: Connection.
A dictionary where Compiled objects
will be cached when the Connection compiles a clause
expression into a Compiled object.
It is the user’s responsibility to
manage the size of this dictionary, which will have keys
corresponding to the dialect, clause element, the column
names within the VALUES or SET clause of an INSERT or UPDATE,
as well as the “batch” mode for an INSERT or UPDATE statement.
The format of this dictionary is not guaranteed to stay the
same in future releases.

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.

Available on: Connection.
Set the transaction isolation level for
the lifespan of this Connection object (not the
underyling DBAPI connection, for which the level is reset
to its original setting upon termination of this
Connection object).

Note that this option necessarily affects the underlying
DBAPI connection for the lifespan of the originating
Connection, and is not per-execution. This
setting is not removed until the underlying DBAPI connection
is returned to the connection pool, i.e.
the Connection.close() method is called.

Warning

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

Changed in version 0.9.9: A warning is emitted when the
isolation_level execution option is used after a
transaction has been started with Connection.begin()
or similar.

Note

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

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.

New in version 0.7.6.

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

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

Invalidate the underlying DBAPI connection associated with
this Connection.

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

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

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

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

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

By default, this method does the same thing as Engine.connect().
Subclasses of Engine may override this method
to provide contextual behavior.

Parameters:

close_with_result¶ – When True, the first ResultProxy
created by the Connection will call the
Connection.close() method of that connection as soon as any
pending result rows are exhausted. This is used to supply the
“connectionless execution” behavior provided by the
Engine.execute() method.

A new connection pool is created immediately after the old one has
been disposed. This new pool, like all SQLAlchemy connection pools,
does not make any actual connections to the database until one is
first requested.

This method has two general use cases:

When a dropped connection is detected, it is assumed that all
connections held by the pool are potentially dropped, and
the entire pool is replaced.

An application may want to use dispose() within a test
suite that is creating multiple engines.

It is critical to note that dispose() does not guarantee
that the application will release all open database connections - only
those connections that are checked into the pool are closed.
Connections which remain checked out or have been detached from
the engine are not affected.

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

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

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

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

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

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

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

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

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

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.

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

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

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

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
inserted_primary_key attribute provides a
tuple of primary key values for a newly inserted row,
regardless of database backend.

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.

ResultProxy.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.

Mostly follows “ordered dictionary” behavior, mapping result
values to the string-based column name, the integer position of
the result in the row, as well as Column instances which can be
mapped to the original Columns that produced this result set (for
results that correspond to constructed SQL expressions).

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: