SQLAlchemy 1.4 Documentation
SQLAlchemy Core
- SQL Expression Language Tutorial (1.x API)
- SQL Statements and Expressions API
- Schema Definition Language
- SQL Datatype Objects
- Engine and Connection Use
- Core API Basics
- SQLAlchemy 2.0 Future (Core)¶
Project Versions
- Previous: Core Internals
- Next: Dialects
- Up: Home
- On this page:
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
Object Name | Description |
---|---|
Provides high-level functionality for a wrapped DB-API connection. |
|
create_engine(*arg, **kw) |
Create a new |
Connects a |
|
select(*entities) |
Construct a new |
- class sqlalchemy.future.Connection(engine, connection=None, close_with_result=False, _branch_from=None, _execution_options=None, _dispatch=None, _has_events=None, _allow_revalidate=True)¶
Provides high-level functionality for a wrapped DB-API connection.
The
Connection
object is procured by calling theEngine.connect()
method of theEngine
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 theResult
. This object has a slightly different API and behavior than theLegacyCursorResult
returned for 1.x style usage.The object has
Connection.commit()
andConnection.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 mentionedConnection.commit()
andConnection.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 byEngine.begin()
.The SAVEPOINT method
Connection.begin_nested()
returns aNestedTransaction
as was always the case, and the savepoint can be controlled by invokingNestedTransaction.commit()
orNestedTransaction.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.
Members
begin(), begin_nested(), close(), commit(), execute(), rollback(), scalar()
Class signature
class
sqlalchemy.future.Connection
(sqlalchemy.engine.Connection
)-
method
sqlalchemy.future.Connection.
begin()¶ Begin a transaction prior to autobegin occurring.
The returned object is an instance of
RootTransaction
. This object represents the “scope” of the transaction, which completes when either theTransaction.rollback()
orTransaction.commit()
method is called.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 theConnectionEvents.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()
; the below style is referred towards as “commit as you go” style:with engine.connect() as conn: conn.execute(...) conn.execute(...) conn.commit() conn.execute(...) conn.execute(...) conn.commit()
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.See also
Working with Transactions and the DBAPI - in the SQLAlchemy 1.4 / 2.0 Tutorial
Connection.begin_nested()
- use a SAVEPOINTConnection.begin_twophase()
- use a two phase /XID transactionEngine.begin()
- context manager available fromEngine
-
method
sqlalchemy.future.Connection.
begin_nested()¶ Begin a nested transaction (i.e. SAVEPOINT) 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
androllback
, however the outermost transaction still controls the overallcommit
orrollback
of the transaction of a whole.If an outer
RootTransaction
is not present on thisConnection
, a new one is created using “autobegin”. This outer transaction may be completed using “commit-as-you-go” style usage, by calling uponConnection.commit()
orConnection.rollback()
.Tip
The “autobegin” behavior of
Connection.begin_nested()
is specific to 2.0 style use; for legacy behaviors, seeConnection.begin_nested()
.The
NestedTransaction
remains independent of theConnection
object itself. Calling theConnection.commit()
orConnection.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
-
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 theConnection.begin()
method is called.Note
The
Connection.commit()
method only acts upon the primary database transaction that is linked to theConnection
object. It does not operate upon a SAVEPOINT that would have been invoked from theConnection.begin_nested()
method; for control of a SAVEPOINT, callNestedTransaction.commit()
on theNestedTransaction
that is returned by theConnection.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
andExecutable
hierarchies, including:DDL
and objects which inherit fromDDLElement
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 DBAPIcursor.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 theConnection.begin()
method is called.Note
The
Connection.rollback()
method only acts upon the primary database transaction that is linked to theConnection
object. It does not operate upon a SAVEPOINT that would have been invoked from theConnection.begin_nested()
method; for control of a SAVEPOINT, callNestedTransaction.rollback()
on theNestedTransaction
that is returned by theConnection.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 theConnection.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.xcreate_engine()
function. The difference is that the object returned is theEngine
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
andDialect
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 thecreate_engine()
function.Members
begin(), connect(), execute(), has_table(), run_callable(), scalar(), table_names(), transaction()
Class signature
-
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 callingConnection.commit()
when the block succeeds normally, orConnection.rollback()
when an exception is raised, before propagating the exception outwards:with engine.begin() as connection: connection.execute(text("insert into table values ('foo')"))
-
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
-
method
sqlalchemy.future.Engine.
execute(*arg, **kw)¶ Executes the given construct and returns a
CursorResult
.Deprecated since version 1.4: The
Engine.execute()
method is considered legacy as of the 1.x series of SQLAlchemy and will be removed in 2.0. All statement execution in SQLAlchemy 2.0 is performed by theConnection.execute()
method ofConnection
, or in the ORM by theSession.execute()
method ofSession
. (Background on SQLAlchemy 2.0 at: Migrating to SQLAlchemy 2.0)The arguments are the same as those used by
Connection.execute()
.Here, a
Connection
is acquired using theEngine.connect()
method, and the statement executed with that connection. The returnedCursorResult
is flagged such that when theCursorResult
is exhausted and its underlying cursor is closed, theConnection
created here will also be closed, which allows its associated DBAPI connection resource to be returned to the connection pool.
-
method
sqlalchemy.future.Engine.
has_table(*arg, **kw)¶ Return True if the given backend has a table of the given name.
Deprecated since version 1.4: The
Engine.has_table()
method is deprecated and will be removed in a future release. Please refer toInspector.has_table()
.See also
Fine Grained Reflection with Inspector - detailed schema inspection using the
Inspector
interface.quoted_name
- used to pass quoting information along with a schema identifier.
-
method
sqlalchemy.future.Engine.
run_callable(*arg, **kw)¶ Given a callable object or function, execute it, passing a
Connection
as the first argument.Deprecated since version 1.4: The
Engine.run_callable()
method is deprecated and will be removed in a future release. Use theEngine.begin()
context manager instead.The given *args and **kwargs are passed subsequent to the
Connection
argument.This function, along with
Connection.run_callable()
, allows a function to be run with aConnection
orEngine
object without the need to know which one is being dealt with.
-
method
sqlalchemy.future.Engine.
scalar(*arg, **kw)¶ Executes and returns the first column of the first row.
Deprecated since version 1.4: The
Engine.scalar()
method is considered legacy as of the 1.x series of SQLAlchemy and will be removed in 2.0. All statement execution in SQLAlchemy 2.0 is performed by theConnection.execute()
method ofConnection
, or in the ORM by theSession.execute()
method ofSession
; theResult.scalar()
method can then be used to return a scalar result. (Background on SQLAlchemy 2.0 at: Migrating to SQLAlchemy 2.0)The underlying result/cursor is closed after execution.
-
method
sqlalchemy.future.Engine.
table_names(*arg, **kw)¶ Return a list of all table names available in the database.
Deprecated since version 1.4: The
Engine.table_names()
method is deprecated and will be removed in a future release. Please refer toInspector.get_table_names()
.
-
method
sqlalchemy.future.Engine.
transaction(*arg, **kw)¶ Execute the given function within a transaction boundary.
Deprecated since version 1.4: The
Engine.transaction()
method is deprecated and will be removed in a future release. Use theEngine.begin()
context manager instead.The function is passed a
Connection
newly procured fromEngine.connect()
as the first argument, followed by the given *args and **kwargs.e.g.:
def do_something(conn, x, y): conn.execute(text("some statement"), {'x':x, 'y':y}) engine.transaction(do_something, 5, 10)
The operations inside the function are all invoked within the context of a single
Transaction
. Upon success, the transaction is committed. If an exception is raised, the transaction is rolled back before propagating the exception.Note
The
transaction()
method is superseded by the usage of the Pythonwith:
statement, which can be used withEngine.begin()
:with engine.begin() as conn: conn.execute(text("some statement"), {'x':5, 'y':10})
See also
Engine.begin()
- engine-level transactional contextConnection.transaction()
- connection-level version ofEngine.transaction()
-
method
- 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-levelselect()
function will automatically use the 1.x or 2.x style API based on the incoming arguments; usingselect()
from thesqlalchemy.future
module will enforce that only the 2.x style constructor is used.Similar functionality is also available via the
FromClause.select()
method on anyFromClause
.- Parameters:
*entities¶ –
Entities to SELECT from. For Core usage, this is typically a series of
ColumnElement
and / orFromClause
objects which will form the columns clause of the resulting statement. For those objects that are instances ofFromClause
(typicallyTable
orAlias
objects), theFromClause.c
collection is extracted to form a collection ofColumnElement
objects.This parameter will also accept
TextClause
constructs as given, as well as ORM-mapped classes.
flambé! the dragon and The Alchemist image designs created and generously donated by Rotem Yaari.
Created using Sphinx 7.2.6. Documentation last generated: Wed 30 Oct 2024 02:18:58 PM EDT