SQLAlchemy 0.9 Documentation

Release: 0.9.4 | Release Date: March 28, 2014 | Download PDF
SQLAlchemy 0.9 Documentation » SQLAlchemy Core » Working with Engines and Connections

Working with Engines and Connections

Working with Engines and Connections

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

Basic Usage

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

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

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

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:

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

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:

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

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.

Using Transactions

Note

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

The Connection object provides a begin() method which returns a Transaction object. This object is usually used within a try/except clause so that it is guaranteed to invoke Transaction.rollback() or Transaction.commit():

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 transaction
with engine.begin() as connection:
    r1 = connection.execute(table1.select())
    connection.execute(table1.insert(), col1=7, col2='this is some data')

Or from the Connection, in which case the Transaction object is available as well:

with connection.begin() as trans:
    r1 = connection.execute(table1.select())
    connection.execute(table1.insert(), col1=7, col2='this is some data')

Nesting of Transaction Blocks

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

# method_a starts a transaction and calls method_b
def method_a(connection):
    trans = connection.begin() # open a transaction
    try:
        method_b(connection)
        trans.commit()  # transaction is committed here
    except:
        trans.rollback() # this rolls back the transaction unconditionally
        raise

# method_b also starts a transaction
def method_b(connection):
    trans = connection.begin() # open a transaction - this runs in the context of method_a's transaction
    try:
        connection.execute("insert into mytable values ('bat', 'lala')")
        connection.execute(mytable.insert(), col1='bat', col2='lala')
        trans.commit()  # transaction is not committed yet
    except:
        trans.rollback() # this rolls back the transaction unconditionally
        raise

# open a Connection and call method_a
conn = 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.

Understanding Autocommit

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

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

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

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

Full control of the “autocommit” behavior is available using the generative Connection.execution_options() method provided on Connection, 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:

engine.execute(text("SELECT my_mutating_procedure()").execution_options(autocommit=True))

Connectionless Execution, Implicit Execution

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:

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

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.

Given a table as below:

from sqlalchemy import MetaData, Table, Column, Integer

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

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

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

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

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

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

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

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

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

  • SQL statement objects gain an Executable.execute() method which automatically locates a “bind” with which to execute themselves.
  • The ORM Session object supports using “bound metadata” in order to establish which Engine should be used to invoke SQL statements on behalf of a particular mapped class, though the Session also features its own explicit system of establishing complex Engine/ mapped class configurations.
  • The MetaData.create_all(), MetaData.drop_all(), Table.create(), Table.drop(), and “autoload” features all make usage of the bound Engine automatically without the need to pass it explicitly.

Note

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

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

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

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

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

In both “connectionless” examples, the Connection is created behind the scenes; the 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.

Using the Threadlocal Execution Strategy

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:

def call_operation1():
    engine.execute("insert into users values (?, ?)", 1, "john")

def call_operation2():
    users.update(users.c.user_id==5).execute(name='ed')

db.begin()
try:
    call_operation1()
    call_operation2()
    db.commit()
except:
    db.rollback()

Explicit execution can be mixed with connectionless execution by using the Engine.connect() method to acquire a Connection that is not part of the threadlocal scope:

db.begin()
conn = db.connect()
try:
    conn.execute(log_table.insert(), message="Operation started")
    call_operation1()
    call_operation2()
    db.commit()
    conn.execute(log_table.insert(), message="Operation succeeded")
except:
    db.rollback()
    conn.execute(log_table.insert(), message="Operation failed")
finally:
    conn.close()

To access the Connection that is bound to the threadlocal scope, call Engine.contextual_connect():

conn = db.contextual_connect()
call_operation3(conn)
conn.close()

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.

Registering New Dialects

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

  1. Create a package called foodialect.

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

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

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

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

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

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

Registering Dialects In-Process

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

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

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

New in version 0.8.

Connection / Engine API

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

Bases: sqlalchemy.engine.Connectable

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

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

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

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

__init__(engine, connection=None, close_with_result=False, _branch=False, _execution_options=None, _dispatch=None, _has_events=None)

Construct a new Connection.

The constructor here is not public and is only called only by an Engine. See Engine.connect() and Engine.contextual_connect() methods.

begin()

Begin a transaction and return a transaction handle.

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

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

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

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

See also:

Connection.begin_nested() - use a SAVEPOINT

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

Engine.begin() - context manager available from Engine.

begin_nested()

Begin a nested transaction and return a transaction handle.

The returned object is an instance of NestedTransaction.

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

See also Connection.begin(), Connection.begin_twophase().

begin_twophase(xid=None)

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

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

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

See also Connection.begin(), Connection.begin_twophase().

close()

Close this Connection.

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

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

closed

Return True if this connection is closed.

connect()

Returns a branched version of this Connection.

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

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

connection

The underlying DB-API connection managed by this Connection.

contextual_connect(**kwargs)

Returns a branched version of this Connection.

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

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

detach()

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

E.g.:

with engine.connect() as conn:
    conn.detach()
    conn.execute("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).

execute(object, *multiparams, **params)

Executes the a SQL statement construct and returns a ResultProxy.

Parameters:
  • object

    The statement to be executed. May be one of:

  • *multiparams/**params

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

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

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

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

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

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

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

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

execution_options(**opt)

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

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

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

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

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

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

    Available on: Connection. A dictionary where Compiled objects will be cached when the Connection compiles a clause expression into a Compiled object. 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.

  • isolation_level

    Available on: Connection. Set the transaction isolation level for the lifespan of this connection. Valid values include those string values accepted by the isolation_level parameter passed to create_engine(), and are database specific, including those for SQLite, PostgreSQL - see those dialect’s documentation for further info.

    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.

  • no_parameters

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

    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.
in_transaction()

Return True if a transaction is in progress.

info

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

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

invalidate(exception=None)

Invalidate the underlying DBAPI connection associated with this Connection.

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

Upon the next use (where “use” typically means using the Connection.execute() method or similar), this Connection will attempt to procure a new DBAPI connection using the services of the Pool as a source of 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 Connection.invalidate() method, just like auto-invalidation, will at the connection pool level invoke the PoolEvents.invalidate() event.

invalidated

Return True if this connection was invalidated.

run_callable(callable_, *args, **kwargs)

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

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

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

scalar(object, *multiparams, **params)

Executes and returns the first column of the first row.

The underlying result/cursor is closed after execution.

transaction(callable_, *args, **kwargs)

Execute the given function within a transaction boundary.

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

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

conn.transaction(do_something, 5, 10)

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

Note

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

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

As well as with Engine.begin():

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

See also:

Engine.begin() - engine-level transactional context

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

class sqlalchemy.engine.Connectable

Interface for an object which supports execution of SQL constructs.

The two implementations of Connectable are Connection and Engine.

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

connect(**kwargs)

Return a Connection object.

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

contextual_connect()

Return a Connection object which may be part of an ongoing context.

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

create(entity, **kwargs)

Emit CREATE statements for the given schema entity.

Deprecated since version 0.7: Use the create() method on the given schema object directly, i.e. Table.create(), Index.create(), MetaData.create_all()

drop(entity, **kwargs)

Emit DROP statements for the given schema entity.

Deprecated since version 0.7: Use the drop() method on the given schema object directly, i.e. Table.drop(), Index.drop(), MetaData.drop_all()

execute(object, *multiparams, **params)

Executes the given construct and returns a ResultProxy.

scalar(object, *multiparams, **params)

Executes and returns the first column of the first row.

The underlying cursor is closed after execution.

class sqlalchemy.engine.Engine(pool, dialect, url, logging_name=None, echo=None, proxy=None, execution_options=None)

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

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

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

See also:

Engine Configuration

Working with Engines and Connections

begin(close_with_result=False)

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

E.g.:

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

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

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

New in version 0.7.6.

See also:

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

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

connect(**kwargs)

Return a new Connection object.

The Connection object is a facade that uses a DBAPI connection internally in order to communicate with the database. This connection is procured from the connection-holding Pool referenced by this Engine. When the 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().

contextual_connect(close_with_result=False, **kwargs)

Return a Connection object which may be part of some ongoing context.

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.
dispose()

Dispose of the connection pool used by this Engine.

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.

driver

Driver name of the Dialect in use by this Engine.

execute(statement, *multiparams, **params)

Executes the given construct and returns a ResultProxy.

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

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.

execution_options(**opt)

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

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

  • The Pool used by the new Engine is the same instance. The Engine.dispose() method will replace the connection pool instance for the parent engine as well as this one.
  • Event listeners are “cascaded” - meaning, the new Engine inherits the events of the parent, and new events can be associated with the new Engine individually.
  • The logging configuration and logging_name is copied from the parent Engine.

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

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

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

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

from sqlalchemy import event
from sqlalchemy.engine import Engine

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

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

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

New in version 0.8.

See also

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

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

has_table(table_name, schema=None)

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

See also

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

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

name

String name of the Dialect in use by this Engine.

raw_connection()

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. In most situations, the Connection object should be used, which is procured using the Engine.connect() method.

run_callable(callable_, *args, **kwargs)

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

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

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

table_names(schema=None, connection=None)

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

Parameters:
  • schema – Optional, retrieve names from a non-default schema.
  • connection – Optional, use a specified connection. Default is the contextual_connect for this Engine.
transaction(callable_, *args, **kwargs)

Execute the given function within a transaction boundary.

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

e.g.:

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

engine.transaction(do_something, 5, 10)

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

Note

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

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

See also:

Engine.begin() - engine-level transactional context

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

update_execution_options(**opt)

Update the default execution_options dictionary of this Engine.

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

class sqlalchemy.engine.NestedTransaction(connection, parent)

Bases: sqlalchemy.engine.base.Transaction

Represent a ‘nested’, or SAVEPOINT transaction.

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

The interface is the same as that of Transaction.

class sqlalchemy.engine.ResultProxy(context)

Wraps a DB-API cursor object to provide easier access to row columns.

Individual columns may be accessed by their integer position, case-insensitive column name, or by schema.Column object. e.g.:

row = fetchone()

col1 = row[0]    # access via integer position

col2 = row['col2']   # access via name

col3 = row[mytable.c.mycol] # access via Column object.

ResultProxy also handles post-processing of result column data using TypeEngine objects, which are referenced from the originating SQL statement that produced this result set.

close(_autoclose_connection=True)

Close this ResultProxy.

Closes the underlying DBAPI cursor corresponding to the execution.

Note that any data cached within this ResultProxy is still available. For some types of results, this may include buffered rows.

If this ResultProxy was generated from an implicit execution, the underlying Connection will also be closed (returns the underlying DBAPI connection to the connection pool.)

This method is called automatically when:

  • all result rows are exhausted using the fetchXXX() methods.
  • cursor.description is None.
fetchall()

Fetch all rows, just like DB-API cursor.fetchall().

fetchmany(size=None)

Fetch many rows, just like DB-API cursor.fetchmany(size=cursor.arraysize).

If rows are present, the cursor remains open after this is called. Else the cursor is automatically closed and an empty list is returned.

fetchone()

Fetch one row, just like DB-API cursor.fetchone().

If a row is present, the cursor remains open after this is called. Else the cursor is automatically closed and None is returned.

first()

Fetch the first row and then close the result set unconditionally.

Returns None if no row is present.

inserted_primary_key

Return the primary key for the row just inserted.

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

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

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

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

is_insert

True if this ResultProxy is the result of a executing an expression language compiled expression.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.

keys()

Return the current set of string keys for rows.

last_inserted_params()

Return the collection of inserted parameters from this execution.

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

last_updated_params()

Return the collection of updated parameters from this execution.

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

lastrow_has_defaults()

Return lastrow_has_defaults() from the underlying ExecutionContext.

See ExecutionContext for details.

lastrowid

return the ‘lastrowid’ accessor on the DBAPI cursor.

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

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

postfetch_cols()

Return postfetch_cols() from the underlying ExecutionContext.

See ExecutionContext for details.

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

prefetch_cols()

Return prefetch_cols() from the underlying ExecutionContext.

See ExecutionContext for details.

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

returned_defaults

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

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

New in version 0.9.0.

returns_rows

True if this ResultProxy returns rows.

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

rowcount

Return the ‘rowcount’ for this result.

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

Note

Notes regarding ResultProxy.rowcount:

  • This attribute returns the number of rows matched, which is not necessarily the same as the number of rows that were actually modified - an UPDATE statement, for example, may have no net change on a given row if the SET values given are the same as those present in the row already. Such a row would be matched but not modified. On backends that feature both styles, such as MySQL, rowcount is configured by default to return the match count in all cases.
  • 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.
  • ResultProxy.rowcount may not be fully implemented by all dialects. In particular, most DBAPIs do not support an aggregate rowcount result from an executemany call. The ResultProxy.supports_sane_rowcount() and ResultProxy.supports_sane_multi_rowcount() methods will report from the dialect if each usage is known to be supported.
  • Statements that use RETURNING may not return a correct rowcount.
scalar()

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

Returns None if no row is present.

supports_sane_multi_rowcount()

Return supports_sane_multi_rowcount from the dialect.

See ResultProxy.rowcount for background.

supports_sane_rowcount()

Return supports_sane_rowcount from the dialect.

See ResultProxy.rowcount for background.

class sqlalchemy.engine.RowProxy(parent, row, processors, keymap)

Bases: sqlalchemy.engine.result.BaseRowProxy

Proxy values from a single cursor row.

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

has_key(key)

Return True if this RowProxy contains the given key.

items()

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

keys()

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

class sqlalchemy.engine.Transaction(connection, parent)

Represent a database transaction in progress.

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

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

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

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

The Transaction object is not threadsafe.

See also: Connection.begin(), Connection.begin_twophase(), Connection.begin_nested().

close()

Close this Transaction.

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

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

commit()

Commit this Transaction.

rollback()

Roll back this Transaction.

class sqlalchemy.engine.TwoPhaseTransaction(connection, xid)

Bases: sqlalchemy.engine.base.Transaction

Represent a two-phase transaction.

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

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

prepare()

Prepare this TwoPhaseTransaction.

After a PREPARE, the transaction can be committed.