SQLAlchemy 2.0 Future (Core)

This package includes a relatively small number of transitional elements to allow “2.0 mode” to take place within SQLAlchemy 1.4. The primary objects provided here are Engine and Connection, which are both subclasses of the existing Engine and Connection objects with essentially a smaller set of methods and the removal of “autocommit”.

Within the 1.4 series, the “2.0” style of engines and connections is enabled by passing the create_engine.future flag to create_engine():

from sqlalchemy import create_engine
engine = create_engine("postgresql://user:pass@host/dbname", future=True)

Similarly, with the ORM, to enable “future” behavior in the ORM Session, pass the Session.future parameter either to the Session constructor directly, or via the sessionmaker class:

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(engine, future=True)

See also

Migrating to SQLAlchemy 2.0 - Introduction to the 2.0 series of SQLAlchemy

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

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.

This is the SQLAlchemy 2.0 version of the Connection class. The API and behavior of this object is largely the same, with the following differences in behavior:

• The result object returned for results is the CursorResult object, which is a subclass of the Result. This object has a slightly different API and behavior than the LegacyCursorResult returned for 1.x style usage.

• The object has Connection.commit() and Connection.rollback() methods which commit or roll back the current transaction in progress, if any.

• The object features “autobegin” behavior, such that any call to Connection.execute() will unconditionally start a transaction which can be controlled using the above mentioned Connection.commit() and Connection.rollback() methods.

• The object does not have any “autocommit” functionality. Any SQL statement or DDL statement will not be followed by any COMMIT until the transaction is explicitly committed, either via the Connection.commit() method, or if the connection is being used in a context manager that commits such as the one returned by Engine.begin().

• The SAVEPOINT method Connection.begin_nested() returns a NestedTransaction as was always the case, and the savepoint can be controlled by invoking NestedTransaction.commit() or NestedTransaction.rollback() as was the case before. However, this savepoint “transaction” is not associated with the transaction that is controlled by the connection itself; the overall transaction can be committed or rolled back directly which will not emit any special instructions for the SAVEPOINT (this will typically have the effect that one desires).

• The Connection object does not support “branching”, which was a pattern by which a sub “connection” would be used that refers to this connection as a parent.

Class signature

class sqlalchemy.future.Connection (sqlalchemy.engine.Connection)

• method sqlalchemy.future.Connection.begin()

  Begin a transaction prior to autobegin occurring.

  The Connection.begin() method in SQLAlchemy 2.0 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:

  with engine.connect() as conn:
      with conn.begin():
          conn.execute(...)
          conn.execute(...)
      with conn.begin():
          conn.execute(...)
          conn.execute(...)

  The above code is not fundamentally any different in its behavior than the following code which does not use Connection.begin():

  with engine.connect() as conn:
      conn.execute(...)
      conn.execute(...)
      conn.commit()
      conn.execute(...)
      conn.execute(...)
      conn.commit()

  In both examples, if an exception is raised, the transaction will not be committed. An explicit rollback of the transaction will occur, including that the ConnectionEvents.rollback() event will be emitted, as connection’s context manager will call Connection.close(), which will call Connection.rollback() for any transaction in place (excluding that of a SAVEPOINT).

  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.

  • Returns

    a Transaction object. This object supports context-manager operation which will commit a transaction or emit a rollback in case of error.

  . If this event is not being used, then there is no real effect from invoking Connection.begin() ahead of time as the Python DBAPI does not implement any explicit BEGIN

  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 Connection.begin() on the same Connection will return new Transaction objects that represent an emulated transaction within the scope of the enclosing transaction, that is:

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

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

  See also

  Connection.begin_nested() - use a SAVEPOINT

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

  Engine.begin() - context manager available from Engine

• method sqlalchemy.future.Connection.begin_nested()

  Begin a nested transaction and return a transaction handle.

  The returned object is an instance of NestedTransaction.

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

  In SQLAlchemy 2.0, the NestedTransaction remains independent of the Connection object itself. Calling the Connection.commit() or Connection.rollback() will always affect the actual containing database transaction itself, and not the SAVEPOINT itself. When a database transaction is committed, any SAVEPOINTs that have been established are cleared and the data changes within their scope is also committed.

  See also

  Connection.begin()

• method sqlalchemy.future.Connection.close()

  Close this Connection.

  This has the effect of also calling Connection.rollback() if any transaction is in place.

• method sqlalchemy.future.Connection.commit()

  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.

  Note

  The Connection.commit() method only acts upon the primary database transaction that is linked to the Connection object. It does not operate upon a SAVEPOINT that would have been invoked from the Connection.begin_nested() method; for control of a SAVEPOINT, call NestedTransaction.commit() on the NestedTransaction that is returned by the Connection.begin_nested() method itself.

• method sqlalchemy.future.Connection.execute(statement, parameters=None, execution_options=None)

  Executes a SQL statement construct and returns a Result.

  • Parameters

    • statement –

      The statement to be executed. This is always an object that is in both the ClauseElement and Executable hierarchies, including:

      • Select

      • Insert, Update, Delete

      • TextClause and TextualSelect

      • DDL and objects which inherit from DDLElement

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

    Returns

    a Result object.

• method sqlalchemy.future.Connection.rollback()

  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.

  Note

  The Connection.rollback() method only acts upon the primary database transaction that is linked to the Connection object. It does not operate upon a SAVEPOINT that would have been invoked from the Connection.begin_nested() method; for control of a SAVEPOINT, call NestedTransaction.rollback() on the NestedTransaction that is returned by the Connection.begin_nested() method itself.

• method sqlalchemy.future.Connection.scalar(statement, parameters=None, execution_options=None)

  Executes a SQL statement construct and returns a scalar object.

  This method is shorthand for invoking the Result.scalar() method after invoking the Connection.execute() method. Parameters are equivalent.

  • Returns

    a scalar Python value representing the first column of the first row returned.

function sqlalchemy.future.``create_engine(\arg, **kw*)

Create a new Engine instance.

Arguments passed to create_engine() are mostly identical to those passed to the 1.x create_engine() function. The difference is that the object returned is the Engine which has the 2.0 version of the API.

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

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

This is the SQLAlchemy 2.0 version of the Engine.

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

See also

Engine Configuration

Working with Engines and Connections

Class signature

class sqlalchemy.future.Engine (sqlalchemy.engine.Engine)

• method sqlalchemy.future.Engine.begin()

  Return a Connection object with a transaction begun.

  Use of this method is similar to that of Engine.connect(), typically as a context manager, which will automatically maintain the state of the transaction when the block ends, either by calling Connection.commit() when the block succeeds normally, or Connection.rollback() when an exception is raised, before propagating the exception outwards:

  with engine.begin() as connection:
      connection.execute(text("insert into table values ('foo')"))

  See also

  Engine.connect()

  Connection.begin()

• method sqlalchemy.future.Engine.connect()

  Return a new Connection object.

  The Connection acts as a Python context manager, so the typical use of this method looks like:

  with engine.connect() as connection:
      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.

  See also

  Engine.begin()

function sqlalchemy.future.``select(\entities*)

Construct a new Select using the 2. x style API.

New in version 1.4: - The select() function now accepts column arguments positionally. The top-level select() function will automatically use the 1.x or 2.x style API based on the incoming arguments; using select() from the sqlalchemy.future module will enforce that only the 2.x style constructor is used.

Similar functionality is also available via the FromClause.select() method on any FromClause.

See also

Selecting - Core Tutorial description of select().

• Parameters

  *entities –

  Entities to SELECT from. For Core usage, this is typically a series of ColumnElement and / or FromClause objects which will form the columns clause of the resulting statement. For those objects that are instances of FromClause (typically Table or Alias objects), the FromClause.c collection is extracted to form a collection of ColumnElement objects.

  This parameter will also accept TextClause constructs as given, as well as ORM-mapped classes.