diff options
Diffstat (limited to 'lib/sqlalchemy')
31 files changed, 656 insertions, 1961 deletions
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py index 684f35c5f..63a11fcc7 100644 --- a/lib/sqlalchemy/dialects/mysql/base.py +++ b/lib/sqlalchemy/dialects/mysql/base.py @@ -1039,10 +1039,6 @@ from ...types import DATE from ...types import VARBINARY from ...util import topological -AUTOCOMMIT_RE = re.compile( - r"\s*(?:UPDATE|INSERT|CREATE|DELETE|DROP|ALTER|LOAD +DATA|REPLACE)", - re.I | re.UNICODE, -) SET_RE = re.compile( r"\s*SET\s+(?:(?:GLOBAL|SESSION)\s+)?\w", re.I | re.UNICODE ) @@ -1135,9 +1131,6 @@ ischema_names = { class MySQLExecutionContext(default.DefaultExecutionContext): - def should_autocommit_text(self, statement): - return AUTOCOMMIT_RE.match(statement) - def create_server_side_cursor(self): if self.dialect.supports_server_side_cursors: return self._dbapi_connection.cursor(self.dialect._sscursor) diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index c1a2cf81d..87afa0293 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1412,12 +1412,6 @@ from ...types import VARCHAR IDX_USING = re.compile(r"^(?:btree|hash|gist|gin|[\w_]+)$", re.I) -AUTOCOMMIT_REGEXP = re.compile( - r"\s*(?:UPDATE|INSERT|CREATE|DELETE|DROP|ALTER|GRANT|REVOKE|" - "IMPORT FOREIGN SCHEMA|REFRESH MATERIALIZED VIEW|TRUNCATE)", - re.I | re.UNICODE, -) - RESERVED_WORDS = set( [ "all", @@ -3088,9 +3082,6 @@ class PGExecutionContext(default.DefaultExecutionContext): return super(PGExecutionContext, self).get_insert_default(column) - def should_autocommit_text(self, statement): - return AUTOCOMMIT_REGEXP.match(statement) - class PGReadOnlyConnectionCharacteristic( characteristics.ConnectionCharacteristic diff --git a/lib/sqlalchemy/dialects/postgresql/provision.py b/lib/sqlalchemy/dialects/postgresql/provision.py index 68a01e483..289dda4b6 100644 --- a/lib/sqlalchemy/dialects/postgresql/provision.py +++ b/lib/sqlalchemy/dialects/postgresql/provision.py @@ -88,7 +88,9 @@ def _postgresql_set_default_schema_on_connection( @drop_all_schema_objects_pre_tables.for_db("postgresql") def drop_all_schema_objects_pre_tables(cfg, eng): with eng.connect().execution_options(isolation_level="AUTOCOMMIT") as conn: - for xid in conn.execute("select gid from pg_prepared_xacts").scalars(): + for xid in conn.exec_driver_sql( + "select gid from pg_prepared_xacts" + ).scalars(): conn.execute("ROLLBACK PREPARED '%s'" % xid) diff --git a/lib/sqlalchemy/engine/base.py b/lib/sqlalchemy/engine/base.py index 5c1a159a6..41c5f4753 100644 --- a/lib/sqlalchemy/engine/base.py +++ b/lib/sqlalchemy/engine/base.py @@ -12,11 +12,10 @@ import sys from .interfaces import Connectable from .interfaces import ConnectionEventsTarget from .interfaces import ExceptionContext -from .util import _distill_params from .util import _distill_params_20 +from .util import _distill_raw_params from .util import TransactionalContext from .. import exc -from .. import inspection from .. import log from .. import util from ..sql import compiler @@ -28,15 +27,12 @@ from ..sql import util as sql_util """ _EMPTY_EXECUTION_OPTS = util.immutabledict() +NO_OPTIONS = util.immutabledict() class Connection(Connectable): """Provides high-level functionality for a wrapped DB-API connection. - **This is the SQLAlchemy 1.x.x version** of the :class:`_engine.Connection` - class. For the :term:`2.0 style` version, which features some API - differences, see :class:`_future.Connection`. - The :class:`_engine.Connection` object is procured by calling the :meth:`_engine.Engine.connect` method of the :class:`_engine.Engine` object, and provides services for execution of SQL statements as well @@ -59,7 +55,6 @@ class Connection(Connectable): """ - _is_future = False _sqla_logger_namespace = "sqlalchemy.engine.Connection" # used by sqlalchemy.engine.util.TransactionalContext @@ -74,53 +69,46 @@ class Connection(Connectable): self, engine, connection=None, - _branch_from=None, - _execution_options=None, - _dispatch=None, _has_events=None, _allow_revalidate=True, + _allow_autobegin=True, ): """Construct a new Connection.""" self.engine = engine - self.dialect = engine.dialect - self.__branch_from = _branch_from + self.dialect = dialect = engine.dialect - if _branch_from: - # branching is always "from" the root connection - assert _branch_from.__branch_from is None - self._dbapi_connection = connection - self._execution_options = _execution_options - self._echo = _branch_from._echo - self.dispatch = _dispatch - self._has_events = _branch_from._has_events + if connection is None: + try: + self._dbapi_connection = engine.raw_connection() + except dialect.dbapi.Error as err: + Connection._handle_dbapi_exception_noconnection( + err, dialect, engine + ) + raise else: - self._dbapi_connection = ( - connection - if connection is not None - else engine.raw_connection() - ) - - self._transaction = self._nested_transaction = None - self.__savepoint_seq = 0 - self.__in_begin = False - - self.__can_reconnect = _allow_revalidate - self._echo = self.engine._should_log_info() + self._dbapi_connection = connection - if _has_events is None: - # if _has_events is sent explicitly as False, - # then don't join the dispatch of the engine; we don't - # want to handle any of the engine's events in that case. - self.dispatch = self.dispatch._join(engine.dispatch) - self._has_events = _has_events or ( - _has_events is None and engine._has_events - ) + self._transaction = self._nested_transaction = None + self.__savepoint_seq = 0 + self.__in_begin = False + + self.__can_reconnect = _allow_revalidate + self._allow_autobegin = _allow_autobegin + self._echo = self.engine._should_log_info() + + if _has_events is None: + # if _has_events is sent explicitly as False, + # then don't join the dispatch of the engine; we don't + # want to handle any of the engine's events in that case. + self.dispatch = self.dispatch._join(engine.dispatch) + self._has_events = _has_events or ( + _has_events is None and engine._has_events + ) - assert not _execution_options - self._execution_options = engine._execution_options + self._execution_options = engine._execution_options if self._has_events or self.engine._has_events: - self.dispatch.engine_connect(self, _branch_from is not None) + self.dispatch.engine_connect(self) @util.memoized_property def _message_formatter(self): @@ -170,44 +158,6 @@ class Connection(Connectable): else: return name - def _branch(self): - """Return a new Connection which references this Connection's - engine and connection; whose close() method does nothing. - - .. deprecated:: 1.4 the "branching" concept will be removed in - SQLAlchemy 2.0 as well as the "Connection.connect()" method which - is the only consumer for this. - - The Core uses this very sparingly, only in the case of - custom SQL default functions that are to be INSERTed as the - primary key of a row where we need to get the value back, so we have - to invoke it distinctly - this is a very uncommon case. - - Userland code accesses _branch() when the connect() - method is called. The branched connection - acts as much as possible like the parent, except that it stays - connected when a close() event occurs. - - """ - return self.engine._connection_cls( - self.engine, - self._dbapi_connection, - _branch_from=self.__branch_from if self.__branch_from else self, - _execution_options=self._execution_options, - _has_events=self._has_events, - _dispatch=self.dispatch, - ) - - def _generate_for_options(self): - """define connection method chaining behavior for execution_options""" - - if self._is_future: - return self - else: - c = self.__class__.__new__(self.__class__) - c.__dict__ = self.__dict__.copy() - return c - def __enter__(self): return self @@ -215,49 +165,37 @@ class Connection(Connectable): self.close() def execution_options(self, **opt): - r""" Set non-SQL options for the connection which take effect + r"""Set non-SQL options for the connection which take effect during execution. - For a "future" style connection, this method returns this same - :class:`_future.Connection` object with the new options added. - - For a legacy connection, this method returns a copy of this - :class:`_engine.Connection` which references the same underlying DBAPI - connection, but also defines the given execution options which will - take effect for a call to - :meth:`execute`. As the new :class:`_engine.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:: - - result = connection.execution_options(stream_results=True).\ - execute(stmt) - - Note that any key/value can be passed to - :meth:`_engine.Connection.execution_options`, - and it will be stored in the - ``_execution_options`` dictionary of the :class:`_engine.Connection`. - It - is suitable for usage by end-user schemes to communicate with - event listeners, for example. + This method modifies this :class:`_engine.Connection` **in-place**; + the return value is the same :class:`_engine.Connection` object + upon which the method is called. Note that this is in contrast + to the behavior of the ``execution_options`` methods on other + objects such as :meth:`_engine.Engine.execution_options` and + :meth:`_sql.Executable.execution_options`. The rationale is that many + such execution options necessarily modify the state of the base + DBAPI connection in any case so there is no feasible means of + keeping the effect of such an option localized to a "sub" connection. + + .. versionchanged:: 2.0 The :meth:`_engine.Connection.execution_options` + method, in constrast to other objects with this method, modifies + the connection in-place without creating copy of it. + + As discussed elsewhere, the :meth:`_engine.Connection.execution_options` + method accepts any arbitrary parameters including user defined names. + All parameters given are consumable in a number of ways including + by using the :meth:`_engine.Connection.get_execution_options` method. + See the examples at :meth:`_sql.Executable.execution_options` + and :meth:`_engine.Engine.execution_options`. The keywords that are currently recognized by SQLAlchemy itself include all those listed under :meth:`.Executable.execution_options`, as well as others that are specific to :class:`_engine.Connection`. - :param 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 this - is **library level, not DBAPI level autocommit**. The DBAPI - connection will remain in a real transaction unless the - "AUTOCOMMIT" isolation level is used. - - .. deprecated:: 1.4 The "autocommit" execution option is deprecated - and will be removed in SQLAlchemy 2.0. See - :ref:`migration_20_autocommit` for discussion. + :param compiled_cache: Available on: :class:`_engine.Connection`, + :class:`_engine.Engine`. - :param compiled_cache: Available on: Connection. A dictionary where :class:`.Compiled` objects will be cached when the :class:`_engine.Connection` compiles a clause @@ -272,7 +210,7 @@ class Connection(Connectable): specified here. :param logging_token: Available on: :class:`_engine.Connection`, - :class:`_engine.Engine`. + :class:`_engine.Engine`, :class:`_sql.Executable`. Adds the specified string token surrounded by brackets in log messages logged by the connection, i.e. the logging that's enabled @@ -290,7 +228,8 @@ class Connection(Connectable): :paramref:`_sa.create_engine.logging_name` - adds a name to the name used by the Python logger object itself. - :param isolation_level: Available on: :class:`_engine.Connection`. + :param isolation_level: Available on: :class:`_engine.Connection`, + :class:`_engine.Engine`. Set the transaction isolation level for the lifespan of this :class:`_engine.Connection` object. @@ -301,52 +240,40 @@ class Connection(Connectable): valid levels. The isolation level option applies the isolation level by emitting - statements on the DBAPI connection, and **necessarily affects the - original Connection object overall**, not just the copy that is - returned by the call to :meth:`_engine.Connection.execution_options` - method. The isolation level will remain at the given setting until - the DBAPI connection itself is returned to the connection pool, i.e. - the :meth:`_engine.Connection.close` method on the original - :class:`_engine.Connection` is called, - where an event handler will emit - additional statements on the DBAPI connection in order to revert the - isolation level change. - - .. warning:: The ``isolation_level`` execution option should - **not** be used when a transaction is already established, that - is, the :meth:`_engine.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. + statements on the DBAPI connection, and **necessarily affects the + original Connection object overall**. The isolation level will remain + at the given setting until explicitly changed, or when the DBAPI + connection itself is :term:`released` to the connection pool, i.e. the + :meth:`_engine.Connection.close` method is called, at which time an + event handler will emit additional statements on the DBAPI connection + in order to revert the isolation level change. + + .. note:: The ``isolation_level`` execution option may only be + established before the :meth:`_engine.Connection.begin` method is + called, as well as before any SQL statements are emitted which + would otherwise trigger "autobegin", or directly after a call to + :meth:`_engine.Connection.commit` or + :meth:`_engine.Connection.rollback`. A database cannot change the + isolation level on a transaction in progress. .. note:: The ``isolation_level`` execution option is implicitly reset if the :class:`_engine.Connection` is invalidated, e.g. via the :meth:`_engine.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. + disconnection error occurs. The new connection produced after the + invalidation will **not** have the selected isolation level + re-applied to it automatically. .. seealso:: - :paramref:`_sa.create_engine.isolation_level` - - set per :class:`_engine.Engine` isolation level + :ref:`dbapi_autocommit` :meth:`_engine.Connection.get_isolation_level` - view current level - :ref:`SQLite Transaction Isolation <sqlite_isolation_level>` - - :ref:`PostgreSQL Transaction Isolation <postgresql_isolation_level>` - - :ref:`MySQL Transaction Isolation <mysql_isolation_level>` - - :ref:`SQL Server Transaction Isolation <mssql_isolation_level>` - - :ref:`session_transaction_isolation` - for the ORM + :param no_parameters: Available on: :class:`_engine.Connection`, + :class:`_sql.Executable`. - :param no_parameters: When ``True``, if the final parameter + 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. @@ -358,7 +285,9 @@ class Connection(Connectable): or piped into a script that's later invoked by command line tools. - :param stream_results: Available on: Connection, statement. + :param stream_results: Available on: :class:`_engine.Connection`, + :class:`_sql.Executable`. + 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 within a subset @@ -369,7 +298,9 @@ class Connection(Connectable): :ref:`engine_stream_results` - :param schema_translate_map: Available on: Connection, Engine. + :param schema_translate_map: Available on: :class:`_engine.Connection`, + :class:`_engine.Engine`, :class:`_sql.Executable`. + A dictionary mapping schema names to schema names, that will be applied to the :paramref:`_schema.Table.schema` element of each :class:`_schema.Table` @@ -391,14 +322,15 @@ class Connection(Connectable): :meth:`_engine.Connection.get_execution_options` + :ref:`orm_queryguide_execution_options` - documentation on all + ORM-specific execution options """ # noqa - c = self._generate_for_options() - c._execution_options = c._execution_options.union(opt) + self._execution_options = self._execution_options.union(opt) if self._has_events or self.engine._has_events: - self.dispatch.set_connection_execution_options(c, opt) - self.dialect.set_connection_execution_options(c, opt) - return c + self.dispatch.set_connection_execution_options(self, opt) + self.dialect.set_connection_execution_options(self, opt) + return self def get_execution_options(self): """Get the non-SQL options which will take effect during execution. @@ -415,9 +347,6 @@ class Connection(Connectable): def closed(self): """Return True if this connection is closed.""" - # note this is independent for a "branched" connection vs. - # the base - return self._dbapi_connection is None and not self.__can_reconnect @property @@ -433,9 +362,6 @@ class Connection(Connectable): # "closed" does not need to be "invalid". So the state is now # represented by the two facts alone. - if self.__branch_from: - return self.__branch_from.invalidated - return self._dbapi_connection is None and not self.closed @property @@ -535,39 +461,18 @@ class Connection(Connectable): return self.dialect.default_isolation_level def _invalid_transaction(self): - if self.invalidated: - raise exc.PendingRollbackError( - "Can't reconnect until invalid %stransaction is rolled " - "back." - % ( - "savepoint " - if self._nested_transaction is not None - else "" - ), - code="8s2b", - ) - else: - assert not self._is_future - raise exc.PendingRollbackError( - "This connection is on an inactive %stransaction. " - "Please rollback() fully before proceeding." - % ( - "savepoint " - if self._nested_transaction is not None - else "" - ), - code="8s2a", - ) + raise exc.PendingRollbackError( + "Can't reconnect until invalid %stransaction is rolled " + "back. Please rollback() fully before proceeding" + % ("savepoint " if self._nested_transaction is not None else ""), + code="8s2b", + ) def _revalidate_connection(self): - if self.__branch_from: - return self.__branch_from._revalidate_connection() if self.__can_reconnect and self.invalidated: if self._transaction is not None: self._invalid_transaction() - self._dbapi_connection = self.engine.raw_connection( - _connection=self - ) + self._dbapi_connection = self.engine.raw_connection() return self._dbapi_connection raise exc.ResourceClosedError("This Connection is closed") @@ -591,24 +496,6 @@ class Connection(Connectable): return self.connection.info - @util.deprecated_20(":meth:`.Connection.connect`") - def connect( - self, - ): - """Returns a branched version of this :class:`_engine.Connection`. - - The :meth:`_engine.Connection.close` method on the returned - :class:`_engine.Connection` can be called and this - :class:`_engine.Connection` will remain open. - - This method provides usage symmetry with - :meth:`_engine.Engine.connect`, including for usage - with context managers. - - """ - - return self._branch() - def invalidate(self, exception=None): """Invalidate the underlying DBAPI connection associated with this :class:`_engine.Connection`. @@ -655,9 +542,6 @@ class Connection(Connectable): """ - if self.__branch_from: - return self.__branch_from.invalidate(exception=exception) - if self.invalidated: return @@ -697,50 +581,65 @@ class Connection(Connectable): self._dbapi_connection.detach() def _autobegin(self): - self.begin() + if self._allow_autobegin: + self.begin() def begin(self): - """Begin a transaction and return a transaction handle. + """Begin a transaction prior to autobegin occurring. - The returned object is an instance of :class:`.Transaction`. - This object represents the "scope" of the transaction, - which completes when either the :meth:`.Transaction.rollback` - or :meth:`.Transaction.commit` method is called. + E.g.:: - .. tip:: + with engine.connect() as conn: + with conn.begin() as trans: + conn.execute(table.insert(), {"username": "sandy"}) - The :meth:`_engine.Connection.begin` method is invoked when using - the :meth:`_engine.Engine.begin` context manager method as well. - All documentation that refers to behaviors specific to the - :meth:`_engine.Connection.begin` method also apply to use of the - :meth:`_engine.Engine.begin` method. - Legacy use: nested calls to :meth:`.begin` on the same - :class:`_engine.Connection` will return new :class:`.Transaction` - objects that represent an emulated transaction within the scope of the - enclosing transaction, that is:: + The returned object is an instance of :class:`_engine.RootTransaction`. + This object represents the "scope" of the transaction, + which completes when either the :meth:`_engine.Transaction.rollback` + or :meth:`_engine.Transaction.commit` method is called; the object + also works as a context manager as illustrated above. - trans = conn.begin() # outermost transaction - trans2 = conn.begin() # "nested" - trans2.commit() # does nothing - trans.commit() # actually commits + The :meth:`_engine.Connection.begin` method begins a + transaction that normally will be begun in any case when the connection + is first used to execute a statement. The reason this method might be + used would be to invoke the :meth:`_events.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:: - Calls to :meth:`.Transaction.commit` only have an effect - when invoked via the outermost :class:`.Transaction` object, though the - :meth:`.Transaction.rollback` method of any of the - :class:`.Transaction` objects will roll back the - transaction. + with engine.connect() as conn: + with conn.begin(): + conn.execute(...) + conn.execute(...) - .. tip:: + with conn.begin(): + conn.execute(...) + conn.execute(...) - The above "nesting" behavior is a legacy behavior specific to - :term:`1.x style` use and will be removed in SQLAlchemy 2.0. For - notes on :term:`2.0 style` use, see - :meth:`_future.Connection.begin`. + The above code is not fundamentally any different in its behavior than + the following code which does not use + :meth:`_engine.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 :meth:`_engine.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. .. seealso:: + :ref:`tutorial_working_with_transactions` - in the + :ref:`unified_tutorial` + :meth:`_engine.Connection.begin_nested` - use a SAVEPOINT :meth:`_engine.Connection.begin_twophase` - @@ -750,11 +649,6 @@ class Connection(Connectable): :class:`_engine.Engine` """ - if self._is_future: - assert not self.__branch_from - elif self.__branch_from: - return self.__branch_from.begin() - if self.__in_begin: # for dialects that emit SQL within the process of # dialect.do_begin() or dialect.do_begin_twophase(), this @@ -766,82 +660,86 @@ class Connection(Connectable): self._transaction = RootTransaction(self) return self._transaction else: - if self._is_future: - raise exc.InvalidRequestError( - "This connection has already initialized a SQLAlchemy " - "Transaction() object via begin() or autobegin; can't " - "call begin() here unless rollback() or commit() " - "is called first." - ) - else: - return MarkerTransaction(self) + raise exc.InvalidRequestError( + "This connection has already initialized a SQLAlchemy " + "Transaction() object via begin() or autobegin; can't " + "call begin() here unless rollback() or commit() " + "is called first." + ) def begin_nested(self): - """Begin a nested transaction (i.e. SAVEPOINT) and return a - transaction handle, assuming an outer transaction is already - established. - - 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. - - The legacy form of :meth:`_engine.Connection.begin_nested` method has - alternate behaviors based on whether or not the - :meth:`_engine.Connection.begin` method was called previously. If - :meth:`_engine.Connection.begin` was not called, then this method will - behave the same as the :meth:`_engine.Connection.begin` method and - return a :class:`.RootTransaction` object that begins and commits a - real transaction - **no savepoint is invoked**. If - :meth:`_engine.Connection.begin` **has** been called, and a - :class:`.RootTransaction` is already established, then this method - returns an instance of :class:`.NestedTransaction` which will invoke - and manage the scope of a SAVEPOINT. - - .. tip:: - - The above mentioned behavior of - :meth:`_engine.Connection.begin_nested` is a legacy behavior - specific to :term:`1.x style` use. In :term:`2.0 style` use, the - :meth:`_future.Connection.begin_nested` method instead autobegins - the outer transaction that can be committed using - "commit-as-you-go" style; see - :meth:`_future.Connection.begin_nested` for migration details. - - .. versionchanged:: 1.4.13 The behavior of - :meth:`_engine.Connection.begin_nested` - as returning a :class:`.RootTransaction` if - :meth:`_engine.Connection.begin` were not called has been restored - as was the case in 1.3.x versions; in previous 1.4.x versions, an - outer transaction would be "autobegun" but would not be committed. + """Begin a nested transaction (i.e. SAVEPOINT) and return a transaction + handle that controls the scope of the SAVEPOINT. + + E.g.:: + + with engine.begin() as connection: + with connection.begin_nested(): + connection.execute(table.insert(), {"username": "sandy"}) + + The returned object is an instance of + :class:`_engine.NestedTransaction`, which includes transactional + methods :meth:`_engine.NestedTransaction.commit` and + :meth:`_engine.NestedTransaction.rollback`; for a nested transaction, + these methods correspond to the operations "RELEASE SAVEPOINT <name>" + and "ROLLBACK TO SAVEPOINT <name>". The name of the savepoint is local + to the :class:`_engine.NestedTransaction` object and is generated + automatically. Like any other :class:`_engine.Transaction`, the + :class:`_engine.NestedTransaction` may be used as a context manager as + illustrated above which will "release" or "rollback" corresponding to + if the operation within the block were successful or raised an + exception. + + Nested transactions require SAVEPOINT support in the underlying + database, else the behavior is undefined. SAVEPOINT is commonly used to + run operations within a transaction that may fail, while continuing the + outer transaction. E.g.:: + + from sqlalchemy import exc + + with engine.begin() as connection: + trans = connection.begin_nested() + try: + connection.execute(table.insert(), {"username": "sandy"}) + trans.commit() + except exc.IntegrityError: # catch for duplicate username + trans.rollback() # rollback to savepoint + + # outer transaction continues + connection.execute( ... ) + + If :meth:`_engine.Connection.begin_nested` is called without first + calling :meth:`_engine.Connection.begin` or + :meth:`_engine.Engine.begin`, the :class:`_engine.Connection` object + will "autobegin" the outer transaction first. This outer transaction + may be committed using "commit-as-you-go" style, e.g.:: + with engine.connect() as connection: # begin() wasn't called + + with connection.begin_nested(): will auto-"begin()" first + connection.execute( ... ) + # savepoint is released + + connection.execute( ... ) + + # explicitly commit outer transaction + connection.commit() + + # can continue working with connection here + + .. versionchanged:: 2.0 + + :meth:`_engine.Connection.begin_nested` will now participate + in the connection "autobegin" behavior that is new as of + 2.0 / "future" style connections in 1.4. .. seealso:: :meth:`_engine.Connection.begin` - :meth:`_engine.Connection.begin_twophase` - """ - if self._is_future: - assert not self.__branch_from - elif self.__branch_from: - return self.__branch_from.begin_nested() - if self._transaction is None: - if not self._is_future: - util.warn_deprecated_20( - "Calling Connection.begin_nested() in 2.0 style use will " - "return a NestedTransaction (SAVEPOINT) in all cases, " - "that will not commit the outer transaction. For code " - "that is cross-compatible between 1.x and 2.0 style use, " - "ensure Connection.begin() is called before calling " - "Connection.begin_nested()." - ) - return self.begin() - else: - self._autobegin() + self._autobegin() return NestedTransaction(self) @@ -865,9 +763,6 @@ class Connection(Connectable): """ - if self.__branch_from: - return self.__branch_from.begin_twophase(xid=xid) - if self._transaction is not None: raise exc.InvalidRequestError( "Cannot start a two phase transaction when a transaction " @@ -877,6 +772,57 @@ class Connection(Connectable): xid = self.engine.dialect.create_xid() return TwoPhaseTransaction(self, xid) + def commit(self): + """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 :class:`_engine.Connection` automatically + whenever a statement is first executed, or when the + :meth:`_engine.Connection.begin` method is called. + + .. note:: The :meth:`_engine.Connection.commit` method only acts upon + the primary database transaction that is linked to the + :class:`_engine.Connection` object. It does not operate upon a + SAVEPOINT that would have been invoked from the + :meth:`_engine.Connection.begin_nested` method; for control of a + SAVEPOINT, call :meth:`_engine.NestedTransaction.commit` on the + :class:`_engine.NestedTransaction` that is returned by the + :meth:`_engine.Connection.begin_nested` method itself. + + + """ + if self._transaction: + self._transaction.commit() + + def rollback(self): + """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 :class:`_engine.Connection` automatically + whenever a statement is first executed, or when the + :meth:`_engine.Connection.begin` method is called. + + .. note:: The :meth:`_engine.Connection.rollback` method only acts + upon the primary database transaction that is linked to the + :class:`_engine.Connection` object. It does not operate upon a + SAVEPOINT that would have been invoked from the + :meth:`_engine.Connection.begin_nested` method; for control of a + SAVEPOINT, call :meth:`_engine.NestedTransaction.rollback` on the + :class:`_engine.NestedTransaction` that is returned by the + :meth:`_engine.Connection.begin_nested` method itself. + + + """ + if self._transaction: + self._transaction.rollback() + def recover_twophase(self): return self.engine.dialect.do_recover_twophase(self) @@ -888,16 +834,10 @@ class Connection(Connectable): def in_transaction(self): """Return True if a transaction is in progress.""" - if self.__branch_from is not None: - return self.__branch_from.in_transaction() - return self._transaction is not None and self._transaction.is_active def in_nested_transaction(self): """Return True if a transaction is in progress.""" - if self.__branch_from is not None: - return self.__branch_from.in_nested_transaction() - return ( self._nested_transaction is not None and self._nested_transaction.is_active @@ -916,9 +856,6 @@ class Connection(Connectable): """ - if self.__branch_from is not None: - return self.__branch_from.get_transaction() - return self._transaction def get_nested_transaction(self): @@ -927,15 +864,9 @@ class Connection(Connectable): .. versionadded:: 1.4 """ - if self.__branch_from is not None: - - return self.__branch_from.get_nested_transaction() - return self._nested_transaction def _begin_impl(self, transaction): - assert not self.__branch_from - if self._echo: self._log_info("BEGIN (implicit)") @@ -952,8 +883,6 @@ class Connection(Connectable): self.__in_begin = False def _rollback_impl(self): - assert not self.__branch_from - if self._has_events or self.engine._has_events: self.dispatch.rollback(self) @@ -971,21 +900,7 @@ class Connection(Connectable): except BaseException as e: self._handle_dbapi_exception(e, None, None, None, None) - def _commit_impl(self, autocommit=False): - assert not self.__branch_from - - # AUTOCOMMIT isolation-level is a dialect-specific concept, however - # if a connection has this set as the isolation level, we can skip - # the "autocommit" warning as the operation will do "autocommit" - # in any case - if autocommit and not self._is_autocommit(): - util.warn_deprecated_20( - "The current statement is being autocommitted using " - "implicit autocommit, which will be removed in " - "SQLAlchemy 2.0. " - "Use the .begin() method of Engine or Connection in order to " - "use an explicit transaction for DML and DDL statements." - ) + def _commit_impl(self): if self._has_events or self.engine._has_events: self.dispatch.commit(self) @@ -1004,8 +919,6 @@ class Connection(Connectable): self._handle_dbapi_exception(e, None, None, None, None) def _savepoint_impl(self, name=None): - assert not self.__branch_from - if self._has_events or self.engine._has_events: self.dispatch.savepoint(self, name) @@ -1017,8 +930,6 @@ class Connection(Connectable): return name def _rollback_to_savepoint_impl(self, name): - assert not self.__branch_from - if self._has_events or self.engine._has_events: self.dispatch.rollback_savepoint(self, name, None) @@ -1026,8 +937,6 @@ class Connection(Connectable): self.engine.dialect.do_rollback_to_savepoint(self, name) def _release_savepoint_impl(self, name): - assert not self.__branch_from - if self._has_events or self.engine._has_events: self.dispatch.release_savepoint(self, name, None) @@ -1035,8 +944,6 @@ class Connection(Connectable): self.engine.dialect.do_release_savepoint(self, name) def _begin_twophase_impl(self, transaction): - assert not self.__branch_from - if self._echo: self._log_info("BEGIN TWOPHASE (implicit)") if self._has_events or self.engine._has_events: @@ -1052,8 +959,6 @@ class Connection(Connectable): self.__in_begin = False def _prepare_twophase_impl(self, xid): - assert not self.__branch_from - if self._has_events or self.engine._has_events: self.dispatch.prepare_twophase(self, xid) @@ -1065,8 +970,6 @@ class Connection(Connectable): self._handle_dbapi_exception(e, None, None, None, None) def _rollback_twophase_impl(self, xid, is_prepared): - assert not self.__branch_from - if self._has_events or self.engine._has_events: self.dispatch.rollback_twophase(self, xid, is_prepared) @@ -1080,8 +983,6 @@ class Connection(Connectable): self._handle_dbapi_exception(e, None, None, None, None) def _commit_twophase_impl(self, xid, is_prepared): - assert not self.__branch_from - if self._has_events or self.engine._has_events: self.dispatch.commit_twophase(self, xid, is_prepared) @@ -1092,24 +993,6 @@ class Connection(Connectable): except BaseException as e: self._handle_dbapi_exception(e, None, None, None, None) - def _autorollback(self): - if self.__branch_from: - self.__branch_from._autorollback() - - if not self.in_transaction(): - self._rollback_impl() - - def _warn_for_legacy_exec_format(self): - util.warn_deprecated_20( - "The connection.execute() method in " - "SQLAlchemy 2.0 will accept parameters as a single " - "dictionary or a " - "single sequence of dictionaries only. " - "Parameters passed as keyword arguments, tuples or positionally " - "oriented dictionaries and/or tuples " - "will no longer be accepted." - ) - def close(self): """Close this :class:`_engine.Connection`. @@ -1124,24 +1007,15 @@ class Connection(Connectable): of any :class:`.Transaction` object that may be outstanding with regards to this :class:`_engine.Connection`. + This has the effect of also calling :meth:`_engine.Connection.rollback` + if any transaction is in place. + After :meth:`_engine.Connection.close` is called, the :class:`_engine.Connection` is permanently in a closed state, and will allow no further operations. """ - if self.__branch_from: - assert not self._is_future - util.warn_deprecated_20( - "The .close() method on a so-called 'branched' connection is " - "deprecated as of 1.4, as are 'branched' connections overall, " - "and will be removed in a future release. If this is a " - "default-handling function, don't close the connection." - ) - self._dbapi_connection = None - self.__can_reconnect = False - return - if self._transaction: self._transaction.close() skip_reset = True @@ -1165,16 +1039,20 @@ class Connection(Connectable): self._dbapi_connection = None self.__can_reconnect = False - def scalar(self, object_, *multiparams, **params): - """Executes and returns the first column of the first row. + def scalar(self, statement, parameters=None, execution_options=None): + r"""Executes a SQL statement construct and returns a scalar object. - The underlying result/cursor is closed after execution. + This method is shorthand for invoking the + :meth:`_engine.Result.scalar` method after invoking the + :meth:`_engine.Connection.execute` method. Parameters are equivalent. - """ + :return: a scalar Python value representing the first column of the + first row returned. - return self.execute(object_, *multiparams, **params).scalar() + """ + return self.execute(statement, parameters, execution_options).scalar() - def scalars(self, object_, *multiparams, **params): + def scalars(self, statement, parameters=None, execution_options=None): """Executes and returns a scalar result set, which yields scalar values from the first column of each row. @@ -1189,100 +1067,41 @@ class Connection(Connectable): """ - return self.execute(object_, *multiparams, **params).scalars() + return self.execute(statement, parameters, execution_options).scalars() - def execute(self, statement, *multiparams, **params): + def execute(self, statement, parameters=None, execution_options=None): r"""Executes a SQL statement construct and returns a - :class:`_engine.CursorResult`. - - :param statement: The statement to be executed. May be - one of: - - * a plain string (deprecated) - * any :class:`_expression.ClauseElement` construct that is also - a subclass of :class:`.Executable`, such as a - :func:`_expression.select` construct - * a :class:`.FunctionElement`, such as that generated - by :data:`.func`, will be automatically wrapped in - a SELECT statement, which is then executed. - * a :class:`.DDLElement` object - * a :class:`.DefaultGenerator` object - * a :class:`.Compiled` object - - .. deprecated:: 2.0 passing a string to - :meth:`_engine.Connection.execute` is - deprecated and will be removed in version 2.0. Use the - :func:`_expression.text` construct with - :meth:`_engine.Connection.execute`, or the - :meth:`_engine.Connection.exec_driver_sql` - method to invoke a driver-level - SQL string. - - :param \*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 - <https://www.python.org/dev/peps/pep-0249/>`_ for details on - paramstyle. - - To execute a textual SQL statement which uses bound parameters in a - DBAPI-agnostic way, use the :func:`_expression.text` construct. - - .. deprecated:: 2.0 use of tuple or scalar positional parameters - is deprecated. All params should be dicts or sequences of dicts. - Use :meth:`.exec_driver_sql` to execute a plain string with - tuple or scalar positional parameters. + :class:`_engine.Result`. + + :param statement: The statement to be executed. This is always + an object that is in both the :class:`_expression.ClauseElement` and + :class:`_expression.Executable` hierarchies, including: + + * :class:`_expression.Select` + * :class:`_expression.Insert`, :class:`_expression.Update`, + :class:`_expression.Delete` + * :class:`_expression.TextClause` and + :class:`_expression.TextualSelect` + * :class:`_schema.DDL` and objects which inherit from + :class:`_schema.DDLElement` + + :param 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. + + :param 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 :meth:`_engine.Connection.execution_options`. + + :return: a :class:`_engine.Result` object. """ - - if isinstance(statement, util.string_types): - util.warn_deprecated_20( - "Passing a string to Connection.execute() is " - "deprecated and will be removed in version 2.0. Use the " - "text() construct, " - "or the Connection.exec_driver_sql() method to invoke a " - "driver-level SQL string." - ) - - return self._exec_driver_sql( - statement, - multiparams, - params, - _EMPTY_EXECUTION_OPTS, - future=False, - ) - + distilled_parameters = _distill_params_20(parameters) try: meth = statement._execute_on_connection except AttributeError as err: @@ -1290,22 +1109,21 @@ class Connection(Connectable): exc.ObjectNotExecutableError(statement), replace_context=err ) else: - return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS) + return meth( + self, + distilled_parameters, + execution_options or NO_OPTIONS, + ) - def _execute_function(self, func, multiparams, params, execution_options): + def _execute_function(self, func, distilled_parameters, execution_options): """Execute a sql.FunctionElement object.""" return self._execute_clauseelement( - func.select(), multiparams, params, execution_options + func.select(), distilled_parameters, execution_options ) def _execute_default( - self, - default, - multiparams, - params, - # migrate is calling this directly :( - execution_options=_EMPTY_EXECUTION_OPTS, + self, default, distilled_parameters, execution_options ): """Execute a schema.ColumnDefault object.""" @@ -1313,12 +1131,14 @@ class Connection(Connectable): execution_options ) - distilled_parameters = _distill_params(self, multiparams, params) - + # note for event handlers, the "distilled parameters" which is always + # a list of dicts is broken out into separate "multiparams" and + # "params" collections, which allows the handler to distinguish + # between an executemany and execute style set of parameters. if self._has_events or self.engine._has_events: ( default, - distilled_params, + distilled_parameters, event_multiparams, event_params, ) = self._invoke_before_exec_event( @@ -1353,19 +1173,17 @@ class Connection(Connectable): return ret - def _execute_ddl(self, ddl, multiparams, params, execution_options): + def _execute_ddl(self, ddl, distilled_parameters, execution_options): """Execute a schema.DDL object.""" execution_options = ddl._execution_options.merge_with( self._execution_options, execution_options ) - distilled_parameters = _distill_params(self, multiparams, params) - if self._has_events or self.engine._has_events: ( ddl, - distilled_params, + distilled_parameters, event_multiparams, event_params, ) = self._invoke_before_exec_event( @@ -1432,7 +1250,7 @@ class Connection(Connectable): return elem, distilled_params, event_multiparams, event_params def _execute_clauseelement( - self, elem, multiparams, params, execution_options + self, elem, distilled_parameters, execution_options ): """Execute a sql.ClauseElement object.""" @@ -1440,24 +1258,22 @@ class Connection(Connectable): self._execution_options, execution_options ) - distilled_params = _distill_params(self, multiparams, params) - has_events = self._has_events or self.engine._has_events if has_events: ( elem, - distilled_params, + distilled_parameters, event_multiparams, event_params, ) = self._invoke_before_exec_event( - elem, distilled_params, execution_options + elem, distilled_parameters, execution_options ) - if distilled_params: + if distilled_parameters: # ensure we don't retain a link to the view object for keys() # which links to the values, which we don't want to cache - keys = sorted(distilled_params[0]) - for_executemany = len(distilled_params) > 1 + keys = sorted(distilled_parameters[0]) + for_executemany = len(distilled_parameters) > 1 else: keys = [] for_executemany = False @@ -1484,10 +1300,10 @@ class Connection(Connectable): dialect, dialect.execution_ctx_cls._init_compiled, compiled_sql, - distilled_params, + distilled_parameters, execution_options, compiled_sql, - distilled_params, + distilled_parameters, elem, extracted_params, cache_hit=cache_hit, @@ -1506,8 +1322,7 @@ class Connection(Connectable): def _execute_compiled( self, compiled, - multiparams, - params, + distilled_parameters, execution_options=_EMPTY_EXECUTION_OPTS, ): """Execute a sql.Compiled object. @@ -1519,12 +1334,11 @@ class Connection(Connectable): execution_options = compiled.execution_options.merge_with( self._execution_options, execution_options ) - distilled_parameters = _distill_params(self, multiparams, params) if self._has_events or self.engine._has_events: ( compiled, - distilled_params, + distilled_parameters, event_multiparams, event_params, ) = self._invoke_before_exec_event( @@ -1555,66 +1369,6 @@ class Connection(Connectable): ) return ret - def _exec_driver_sql( - self, statement, multiparams, params, execution_options, future - ): - - execution_options = self._execution_options.merge_with( - execution_options - ) - - distilled_parameters = _distill_params(self, multiparams, params) - - if not future: - if self._has_events or self.engine._has_events: - ( - statement, - distilled_params, - event_multiparams, - event_params, - ) = self._invoke_before_exec_event( - statement, distilled_parameters, execution_options - ) - - dialect = self.dialect - ret = self._execute_context( - dialect, - dialect.execution_ctx_cls._init_statement, - statement, - distilled_parameters, - execution_options, - statement, - distilled_parameters, - ) - - if not future: - if self._has_events or self.engine._has_events: - self.dispatch.after_execute( - self, - statement, - event_multiparams, - event_params, - execution_options, - ret, - ) - return ret - - def _execute_20( - self, - statement, - parameters=None, - execution_options=_EMPTY_EXECUTION_OPTS, - ): - args_10style, kwargs_10style = _distill_params_20(parameters) - try: - meth = statement._execute_on_connection - except AttributeError as err: - util.raise_( - exc.ObjectNotExecutableError(statement), replace_context=err - ) - else: - return meth(self, args_10style, kwargs_10style, execution_options) - def exec_driver_sql( self, statement, parameters=None, execution_options=None ): @@ -1666,16 +1420,25 @@ class Connection(Connectable): """ - args_10style, kwargs_10style = _distill_params_20(parameters) + distilled_parameters = _distill_raw_params(parameters) - return self._exec_driver_sql( + execution_options = self._execution_options.merge_with( + execution_options + ) + + dialect = self.dialect + ret = self._execute_context( + dialect, + dialect.execution_ctx_cls._init_statement, statement, - args_10style, - kwargs_10style, + distilled_parameters, execution_options, - future=True, + statement, + distilled_parameters, ) + return ret + def _execute_context( self, dialect, @@ -1689,12 +1452,6 @@ class Connection(Connectable): """Create an :class:`.ExecutionContext` and execute, returning a :class:`_engine.CursorResult`.""" - if self.__branch_from: - # if this is a "branched" connection, do everything in terms - # of the "root" connection, *except* for .close(), which is - # the only feature that branching provides - self = self.__branch_from - try: conn = self._dbapi_connection if conn is None: @@ -1724,7 +1481,7 @@ class Connection(Connectable): elif self._trans_context_manager: TransactionalContext._trans_ctx_check(self) - if self._is_future and self._transaction is None: + if self._transaction is None: self._autobegin() context.pre_exec() @@ -1819,16 +1576,6 @@ class Connection(Connectable): result = context._setup_result_proxy() - if not self._is_future: - - if ( - # usually we're in a transaction so avoid relatively - # expensive / legacy should_autocommit call - self._transaction is None - and context.should_autocommit - ): - self._commit_impl(autocommit=True) - except BaseException as e: self._handle_dbapi_exception( e, statement, parameters, cursor, context @@ -2006,7 +1753,14 @@ class Connection(Connectable): if cursor: self._safe_close_cursor(cursor) with util.safe_reraise(warn_only=True): - self._autorollback() + # "autorollback" was mostly relevant in 1.x series. + # It's very unlikely to reach here, as the connection + # does autobegin so when we are here, we are usually + # in an explicit / semi-explicit transaction. + # however we have a test which manufactures this + # scenario in any case using an event handler. + if not self.in_transaction(): + self._rollback_impl() if newraise: util.raise_(newraise, with_traceback=exc_info[2], from_=e) @@ -2097,87 +1851,6 @@ class Connection(Connectable): """ visitorcallable(self.dialect, self, **kwargs).traverse_single(element) - @util.deprecated( - "1.4", - "The :meth:`_engine.Connection.transaction` " - "method is deprecated and will be " - "removed in a future release. Use the :meth:`_engine.Engine.begin` " - "context manager instead.", - ) - def transaction(self, callable_, *args, **kwargs): - r"""Execute the given function within a transaction boundary. - - The function is passed this :class:`_engine.Connection` - 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}) - - conn.transaction(do_something, 5, 10) - - The operations inside the function are all invoked within the - context of a single :class:`.Transaction`. - Upon success, the transaction is committed. If an - exception is raised, the transaction is rolled back - before propagating the exception. - - .. note:: - - The :meth:`.transaction` method is superseded by - the usage of the Python ``with:`` statement, which can - be used with :meth:`_engine.Connection.begin`:: - - with conn.begin(): - conn.execute(text("some statement"), {'x':5, 'y':10}) - - As well as with :meth:`_engine.Engine.begin`:: - - with engine.begin() as conn: - conn.execute(text("some statement"), {'x':5, 'y':10}) - - .. seealso:: - - :meth:`_engine.Engine.begin` - engine-level transactional - context - - :meth:`_engine.Engine.transaction` - engine-level version of - :meth:`_engine.Connection.transaction` - - """ - - kwargs["_sa_skip_warning"] = True - trans = self.begin() - try: - ret = self.run_callable(callable_, *args, **kwargs) - trans.commit() - return ret - except: - with util.safe_reraise(): - trans.rollback() - - @util.deprecated( - "1.4", - "The :meth:`_engine.Connection.run_callable` " - "method is deprecated and will " - "be removed in a future release. Invoke the callable function " - "directly, passing the Connection.", - ) - def run_callable(self, callable_, *args, **kwargs): - r"""Given a callable object or function, execute it, passing - a :class:`_engine.Connection` as the first argument. - - The given \*args and \**kwargs are passed subsequent - to the :class:`_engine.Connection` argument. - - This function, along with :meth:`_engine.Engine.run_callable`, - allows a function to be run with a :class:`_engine.Connection` - or :class:`_engine.Engine` object without the need to know - which one is being dealt with. - - """ - return callable_(self, *args, **kwargs) - class ExceptionContextImpl(ExceptionContext): """Implement the :class:`.ExceptionContext` interface.""" @@ -2250,23 +1923,6 @@ class Transaction(TransactionalContext): def __init__(self, connection): raise NotImplementedError() - def _do_deactivate(self): - """do whatever steps are necessary to set this transaction as - "deactive", however leave this transaction object in place as far - as the connection's state. - - for a "real" transaction this should roll back the transaction - and ensure this transaction is no longer a reset agent. - - this is used for nesting of marker transactions where the marker - can set the "real" transaction as rolled back, however it stays - in place. - - for 2.0 we hope to remove this nesting feature. - - """ - raise NotImplementedError() - @property def _deactivated_from_connection(self): """True if this transaction is totally deactivated from the connection @@ -2357,70 +2013,6 @@ class Transaction(TransactionalContext): return not self._deactivated_from_connection -class MarkerTransaction(Transaction): - """A 'marker' transaction that is used for nested begin() calls. - - .. deprecated:: 1.4 future connection for 2.0 won't support this pattern. - - """ - - __slots__ = ("connection", "_is_active", "_transaction") - - def __init__(self, connection): - assert connection._transaction is not None - if not connection._transaction.is_active: - raise exc.InvalidRequestError( - "the current transaction on this connection is inactive. " - "Please issue a rollback first." - ) - - assert not connection._is_future - util.warn_deprecated_20( - "Calling .begin() when a transaction is already begun, creating " - "a 'sub' transaction, is deprecated " - "and will be removed in 2.0. See the documentation section " - "'Migrating from the nesting pattern' for background on how " - "to migrate from this pattern." - ) - - self.connection = connection - - if connection._trans_context_manager: - TransactionalContext._trans_ctx_check(connection) - - if connection._nested_transaction is not None: - self._transaction = connection._nested_transaction - else: - self._transaction = connection._transaction - self._is_active = True - - @property - def _deactivated_from_connection(self): - return not self.is_active - - @property - def is_active(self): - return self._is_active and self._transaction.is_active - - def _deactivate(self): - self._is_active = False - - def _do_close(self): - # does not actually roll back the root - self._deactivate() - - def _do_rollback(self): - # does roll back the root - if self._is_active: - try: - self._transaction._do_deactivate() - finally: - self._deactivate() - - def _do_commit(self): - self._deactivate() - - class RootTransaction(Transaction): """Represent the "root" transaction on a :class:`_engine.Connection`. @@ -2469,27 +2061,6 @@ class RootTransaction(Transaction): def _deactivated_from_connection(self): return self.connection._transaction is not self - def _do_deactivate(self): - # called from a MarkerTransaction to cancel this root transaction. - # the transaction stays in place as connection._transaction, but - # is no longer active and is no longer the reset agent for the - # pooled connection. the connection won't support a new begin() - # until this transaction is explicitly closed, rolled back, - # or committed. - - assert self.connection._transaction is self - - if self.is_active: - self._connection_rollback_impl() - - # handle case where a savepoint was created inside of a marker - # transaction that refers to a root. nested has to be cancelled - # also. - if self.connection._nested_transaction: - self.connection._nested_transaction._cancel() - - self._deactivate_from_connection() - def _connection_begin_impl(self): self.connection._begin_impl(self) @@ -2629,9 +2200,6 @@ class NestedTransaction(Transaction): if deactivate_from_connection: assert self.connection._nested_transaction is not self - def _do_deactivate(self): - self._close_impl(False, False) - def _do_close(self): self._close_impl(True, False) @@ -2824,45 +2392,52 @@ class Engine(ConnectionEventsTarget, log.Identified): * The logging configuration and logging_name is copied from the parent :class:`_engine.Engine`. + .. TODO: the below autocommit link will have a more specific ref + for the example in an upcoming commit + The intent of the :meth:`_engine.Engine.execution_options` method is - to implement "sharding" schemes where multiple :class:`_engine.Engine` + to implement schemes where multiple :class:`_engine.Engine` objects refer to the same connection pool, but are differentiated - by options that would be consumed by a custom event:: + by options that affect some execution-level behavior for each + engine. One such example is breaking into separate "reader" and + "writer" :class:`_engine.Engine` instances, where one + :class:`_engine.Engine` + has a lower :term:`isolation level` setting configured or is even + transaction-disabled using "autocommit". An example of this + configuration is at :ref:`dbapi_autocommit`. + + Another example is one that + uses a custom option ``shard_id`` which is consumed by an event + to change the current schema on a database connection:: + + from sqlalchemy import event + from sqlalchemy.engine import Engine 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 - :class:`_engine.Connection` - objects that will contain the execution option - ``shard_id=shard1``, and ``shard2`` will produce - :class:`_engine.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 - :attr:`_engine.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"} + 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") + shard_id = conn.get_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 + The above recipe illustrates two :class:`_engine.Engine` objects that + will each serve as factories for :class:`_engine.Connection` objects + that have pre-established "shard_id" execution options present. A + :meth:`_events.ConnectionEvents.before_cursor_execute` event handler + then interprets this execution option to emit a MySQL ``use`` statement + to switch databases before a statement execution, while at the same + time keeping track of which database we've established using the + :attr:`_engine.Connection.info` dictionary. + .. seealso:: :meth:`_engine.Connection.execution_options` @@ -2876,7 +2451,7 @@ class Engine(ConnectionEventsTarget, log.Identified): :meth:`_engine.Engine.get_execution_options` - """ + """ # noqa E501 return self._option_cls(self, opt) def get_execution_options(self): @@ -2938,12 +2513,6 @@ class Engine(ConnectionEventsTarget, log.Identified): self.pool = self.pool.recreate() self.dispatch.engine_disposed(self) - def _execute_default( - self, default, multiparams=(), params=util.EMPTY_DICT - ): - with self.connect() as conn: - return conn._execute_default(default, multiparams, params) - @contextlib.contextmanager def _optional_conn_ctx_manager(self, connection=None): if connection is None: @@ -2952,21 +2521,7 @@ class Engine(ConnectionEventsTarget, log.Identified): else: yield connection - class _trans_ctx(object): - def __init__(self, conn, transaction): - self.conn = conn - self.transaction = transaction - - def __enter__(self): - self.transaction.__enter__() - return self.conn - - def __exit__(self, type_, value, traceback): - try: - self.transaction.__exit__(type_, value, traceback) - finally: - self.conn.close() - + @util.contextmanager def begin(self): """Return a context manager delivering a :class:`_engine.Connection` with a :class:`.Transaction` established. @@ -2993,88 +2548,9 @@ class Engine(ConnectionEventsTarget, log.Identified): for a particular :class:`_engine.Connection`. """ - conn = self.connect() - try: - trans = conn.begin() - except: - with util.safe_reraise(): - conn.close() - return Engine._trans_ctx(conn, trans) - - @util.deprecated( - "1.4", - "The :meth:`_engine.Engine.transaction` " - "method is deprecated and will be " - "removed in a future release. Use the :meth:`_engine.Engine.begin` " - "context " - "manager instead.", - ) - def transaction(self, callable_, *args, **kwargs): - r"""Execute the given function within a transaction boundary. - - The function is passed a :class:`_engine.Connection` newly procured - from :meth:`_engine.Engine.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 :class:`.Transaction`. - Upon success, the transaction is committed. If an - exception is raised, the transaction is rolled back - before propagating the exception. - - .. note:: - - The :meth:`.transaction` method is superseded by - the usage of the Python ``with:`` statement, which can - be used with :meth:`_engine.Engine.begin`:: - - with engine.begin() as conn: - conn.execute(text("some statement"), {'x':5, 'y':10}) - - .. seealso:: - - :meth:`_engine.Engine.begin` - engine-level transactional - context - - :meth:`_engine.Connection.transaction` - - connection-level version of - :meth:`_engine.Engine.transaction` - - """ - kwargs["_sa_skip_warning"] = True - with self.connect() as conn: - return conn.transaction(callable_, *args, **kwargs) - - @util.deprecated( - "1.4", - "The :meth:`_engine.Engine.run_callable` " - "method is deprecated and will be " - "removed in a future release. Use the :meth:`_engine.Engine.begin` " - "context manager instead.", - ) - def run_callable(self, callable_, *args, **kwargs): - r"""Given a callable object or function, execute it, passing - a :class:`_engine.Connection` as the first argument. - - The given \*args and \**kwargs are passed subsequent - to the :class:`_engine.Connection` argument. - - This function, along with :meth:`_engine.Connection.run_callable`, - allows a function to be run with a :class:`_engine.Connection` - or :class:`_engine.Engine` object without the need to know - which one is being dealt with. - - """ - kwargs["_sa_skip_warning"] = True with self.connect() as conn: - return conn.run_callable(callable_, *args, **kwargs) + with conn.begin(): + yield conn def _run_ddl_visitor(self, visitorcallable, element, **kwargs): with self.begin() as conn: @@ -3083,76 +2559,29 @@ class Engine(ConnectionEventsTarget, log.Identified): def connect(self): """Return a new :class:`_engine.Connection` object. - The :class:`_engine.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 :class:`_pool.Pool` - referenced by this :class:`_engine.Engine`. When the - :meth:`_engine.Connection.close` method of the - :class:`_engine.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 - :meth:`_engine.Engine.connect`. + The :class:`_engine.Connection` acts as a Python context manager, so + the typical use of this method looks like:: - """ - - return self._connection_cls(self) + with engine.connect() as connection: + connection.execute(text("insert into table values ('foo')")) + connection.commit() - @util.deprecated( - "1.4", - "The :meth:`_engine.Engine.table_names` " - "method is deprecated and will be " - "removed in a future release. Please refer to " - ":meth:`_reflection.Inspector.get_table_names`.", - ) - def table_names(self, schema=None, connection=None): - """Return a list of all table names available in the database. - - :param schema: Optional, retrieve names from a non-default schema. - - :param connection: Optional, use a specified connection. - """ - with self._optional_conn_ctx_manager(connection) as conn: - insp = inspection.inspect(conn) - return insp.get_table_names(schema) - - @util.deprecated( - "1.4", - "The :meth:`_engine.Engine.has_table` " - "method is deprecated and will be " - "removed in a future release. Please refer to " - ":meth:`_reflection.Inspector.has_table`.", - ) - def has_table(self, table_name, schema=None): - """Return True if the given backend has a table of the given name. + 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 :meth:`_events.ConnectionEvents.rollback` event if one was + started and is still in progress. .. seealso:: - :ref:`metadata_reflection_inspector` - detailed schema inspection - using the :class:`_reflection.Inspector` interface. - - :class:`.quoted_name` - used to pass quoting information along - with a schema identifier. + :meth:`_engine.Engine.begin` """ - with self._optional_conn_ctx_manager(None) as conn: - insp = inspection.inspect(conn) - return insp.has_table(table_name, schema=schema) - def _wrap_pool_connect(self, fn, connection): - dialect = self.dialect - try: - return fn() - except dialect.dbapi.Error as e: - if connection is None: - Connection._handle_dbapi_exception_noconnection( - e, dialect, self - ) - else: - util.raise_( - sys.exc_info()[1], with_traceback=sys.exc_info()[2] - ) + return self._connection_cls(self) - def raw_connection(self, _connection=None): + def raw_connection(self): """Return a "raw" DBAPI connection from the connection pool. The returned object is a proxied version of the DBAPI @@ -3174,7 +2603,7 @@ class Engine(ConnectionEventsTarget, log.Identified): :ref:`dbapi_connections` """ - return self._wrap_pool_connect(self.pool.connect, _connection) + return self.pool.connect() class OptionEngineMixin(object): diff --git a/lib/sqlalchemy/engine/create.py b/lib/sqlalchemy/engine/create.py index b9e111647..6e5a07098 100644 --- a/lib/sqlalchemy/engine/create.py +++ b/lib/sqlalchemy/engine/create.py @@ -230,11 +230,21 @@ def create_engine(url, **kwargs): be applied to all connections. See :meth:`~sqlalchemy.engine.Connection.execution_options` - :param future: Use the 2.0 style :class:`_future.Engine` and - :class:`_future.Connection` API. + :param future: Use the 2.0 style :class:`_engine.Engine` and + :class:`_engine.Connection` API. + + As of SQLAlchemy 2.0, this parameter is present for backwards + compatibility only and must remain at its default value of ``True``. + + The :paramref:`_sa.create_engine.future` parameter will be + deprecated in a subsequent 2.x release and eventually removed. .. versionadded:: 1.4 + .. versionchanged:: 2.0 All :class:`_engine.Engine` objects are + "future" style engines and there is no longer a ``future=False`` + mode of operation. + .. seealso:: :ref:`migration_20_toplevel` @@ -613,14 +623,13 @@ def create_engine(url, **kwargs): pool._dialect = dialect # create engine. - if pop_kwarg("future", False): - from sqlalchemy import future - - default_engine_class = future.Engine - else: - default_engine_class = base.Engine + if not pop_kwarg("future", True): + raise exc.ArgumentError( + "The 'future' parameter passed to " + "create_engine() may only be set to True." + ) - engineclass = kwargs.pop("_future_engine_class", default_engine_class) + engineclass = base.Engine engine_args = {} for k in util.get_cls_kwargs(engineclass): @@ -630,7 +639,6 @@ def create_engine(url, **kwargs): # internal flags used by the test suite for instrumenting / proxying # engines with mocks etc. _initialize = kwargs.pop("_initialize", True) - _wrap_do_on_connect = kwargs.pop("_wrap_do_on_connect", None) # all kwargs should be consumed if kwargs: @@ -652,8 +660,6 @@ def create_engine(url, **kwargs): if _initialize: do_on_connect = dialect.on_connect_url(u) if do_on_connect: - if _wrap_do_on_connect: - do_on_connect = _wrap_do_on_connect(do_on_connect) def on_connect(dbapi_connection, connection_record): do_on_connect(dbapi_connection) @@ -668,6 +674,9 @@ def create_engine(url, **kwargs): # reconnecting will be a reentrant condition, so if the # connection goes away, Connection is then closed _allow_revalidate=False, + # dont trigger the autobegin sequence + # within the up front dialect checks + _allow_autobegin=False, ) c._execution_options = util.EMPTY_DICT diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py index b6dae6abc..9a59250e9 100644 --- a/lib/sqlalchemy/engine/default.py +++ b/lib/sqlalchemy/engine/default.py @@ -33,10 +33,6 @@ from ..sql import compiler from ..sql import expression from ..sql.elements import quoted_name -AUTOCOMMIT_REGEXP = re.compile( - r"\s*(?:UPDATE|INSERT|CREATE|DELETE|DROP|ALTER)", re.I | re.UNICODE -) - # When we're handed literal SQL, ensure it's a SELECT query SERVER_SIDE_CURSOR_RE = re.compile(r"\s*SELECT", re.I | re.UNICODE) @@ -112,16 +108,10 @@ class DefaultDialect(interfaces.Dialect): # *not* the FLOAT type however. supports_native_decimal = False - if util.py3k: - supports_unicode_statements = True - supports_unicode_binds = True - returns_unicode_strings = sqltypes.String.RETURNS_UNICODE - description_encoding = None - else: - supports_unicode_statements = False - supports_unicode_binds = False - returns_unicode_strings = sqltypes.String.RETURNS_UNKNOWN - description_encoding = "use_encoding" + supports_unicode_statements = True + supports_unicode_binds = True + returns_unicode_strings = sqltypes.String.RETURNS_UNICODE + description_encoding = None name = "default" @@ -401,15 +391,6 @@ class DefaultDialect(interfaces.Dialect): except NotImplementedError: self.default_isolation_level = None - if self.returns_unicode_strings is sqltypes.String.RETURNS_UNKNOWN: - if util.py3k: - raise exc.InvalidRequestError( - "RETURNS_UNKNOWN is unsupported in Python 3" - ) - self.returns_unicode_strings = self._check_unicode_returns( - connection - ) - if ( self.description_encoding is not None and self._check_unicode_description(connection) @@ -463,60 +444,6 @@ class DefaultDialect(interfaces.Dialect): """ return self.get_isolation_level(dbapi_conn) - def _check_unicode_returns(self, connection, additional_tests=None): - cast_to = util.text_type - - if self.positional: - parameters = self.execute_sequence_format() - else: - parameters = {} - - def check_unicode(test): - statement = cast_to(expression.select(test).compile(dialect=self)) - try: - cursor = connection.connection.cursor() - connection._cursor_execute(cursor, statement, parameters) - row = cursor.fetchone() - cursor.close() - except exc.DBAPIError as de: - # note that _cursor_execute() will have closed the cursor - # if an exception is thrown. - util.warn( - "Exception attempting to " - "detect unicode returns: %r" % de - ) - return False - else: - return isinstance(row[0], util.text_type) - - tests = [ - # detect plain VARCHAR - expression.cast( - expression.literal_column("'test plain returns'"), - sqltypes.VARCHAR(60), - ), - # detect if there's an NVARCHAR type with different behavior - # available - expression.cast( - expression.literal_column("'test unicode returns'"), - sqltypes.Unicode(60), - ), - ] - - if additional_tests: - tests += additional_tests - - results = {check_unicode(test) for test in tests} - - if results.issuperset([True, False]): - return sqltypes.String.RETURNS_CONDITIONAL - else: - return ( - sqltypes.String.RETURNS_UNICODE - if results == {True} - else sqltypes.String.RETURNS_BYTES - ) - def _check_unicode_description(self, connection): cast_to = util.text_type @@ -580,11 +507,10 @@ class DefaultDialect(interfaces.Dialect): ) @event.listens_for(engine, "engine_connect") - def set_connection_characteristics(connection, branch): - if not branch: - self._set_connection_characteristics( - connection, characteristics - ) + def set_connection_characteristics(connection): + self._set_connection_characteristics( + connection, characteristics + ) def set_connection_execution_options(self, connection, opts): supported_names = set(self.connection_characteristics).intersection( @@ -610,24 +536,13 @@ class DefaultDialect(interfaces.Dialect): if obj.transactional ] if trans_objs: - if connection._is_future: - raise exc.InvalidRequestError( - "This connection has already initialized a SQLAlchemy " - "Transaction() object via begin() or autobegin; " - "%s may not be altered unless rollback() or commit() " - "is called first." - % (", ".join(name for name, obj in trans_objs)) - ) - else: - util.warn( - "Connection is already established with a " - "Transaction; " - "setting %s may implicitly rollback or " - "commit " - "the existing transaction, or have no effect until " - "next transaction" - % (", ".join(name for name, obj in trans_objs)) - ) + raise exc.InvalidRequestError( + "This connection has already initialized a SQLAlchemy " + "Transaction() object via begin() or autobegin; " + "%s may not be altered unless rollback() or commit() " + "is called first." + % (", ".join(name for name, obj in trans_objs)) + ) dbapi_connection = connection.connection.dbapi_connection for name, characteristic, value in characteristic_values: @@ -1175,21 +1090,6 @@ class DefaultExecutionContext(interfaces.ExecutionContext): def no_parameters(self): return self.execution_options.get("no_parameters", False) - @util.memoized_property - def should_autocommit(self): - autocommit = self.execution_options.get( - "autocommit", - not self.compiled - and self.statement - and expression.PARSE_AUTOCOMMIT - or False, - ) - - if autocommit is expression.PARSE_AUTOCOMMIT: - return self.should_autocommit_text(self.unicode_statement) - else: - return autocommit - def _execute_scalar(self, stmt, type_, parameters=None): """Execute a string statement on the current cursor, returning a scalar result. @@ -1232,16 +1132,9 @@ class DefaultExecutionContext(interfaces.ExecutionContext): return proc(r) return r - @property + @util.memoized_property def connection(self): - conn = self.root_connection - if conn._is_future: - return conn - else: - return conn._branch() - - def should_autocommit_text(self, statement): - return AUTOCOMMIT_REGEXP.match(statement) + return self.root_connection def _use_server_side_cursor(self): if not self.dialect.supports_server_side_cursors: @@ -1522,7 +1415,9 @@ class DefaultExecutionContext(interfaces.ExecutionContext): if self.isddl or self.is_text: return - inputsizes = self.compiled._get_set_input_sizes_lookup( + compiled = self.compiled + + inputsizes = compiled._get_set_input_sizes_lookup( include_types=self.include_set_input_sizes, exclude_types=self.exclude_set_input_sizes, ) @@ -1530,30 +1425,31 @@ class DefaultExecutionContext(interfaces.ExecutionContext): if inputsizes is None: return - if self.dialect._has_events: + dialect = self.dialect + + if dialect._has_events: inputsizes = dict(inputsizes) - self.dialect.dispatch.do_setinputsizes( + dialect.dispatch.do_setinputsizes( inputsizes, self.cursor, self.statement, self.parameters, self ) - has_escaped_names = bool(self.compiled.escaped_bind_names) + has_escaped_names = bool(compiled.escaped_bind_names) if has_escaped_names: - escaped_bind_names = self.compiled.escaped_bind_names + escaped_bind_names = compiled.escaped_bind_names - if self.dialect.positional: + if dialect.positional: items = [ - (key, self.compiled.binds[key]) - for key in self.compiled.positiontup + (key, compiled.binds[key]) for key in compiled.positiontup ] else: items = [ (key, bindparam) - for bindparam, key in self.compiled.bind_names.items() + for bindparam, key in compiled.bind_names.items() ] generic_inputsizes = [] for key, bindparam in items: - if bindparam in self.compiled.literal_execute_params: + if bindparam in compiled.literal_execute_params: continue if key in self._expanded_parameters: @@ -1601,9 +1497,7 @@ class DefaultExecutionContext(interfaces.ExecutionContext): (escaped_name, dbtype, bindparam.type) ) try: - self.dialect.do_set_input_sizes( - self.cursor, generic_inputsizes, self - ) + dialect.do_set_input_sizes(self.cursor, generic_inputsizes, self) except BaseException as e: self.root_connection._handle_dbapi_exception( e, None, None, None, self diff --git a/lib/sqlalchemy/engine/events.py b/lib/sqlalchemy/engine/events.py index effebb4cb..cfb616aff 100644 --- a/lib/sqlalchemy/engine/events.py +++ b/lib/sqlalchemy/engine/events.py @@ -336,10 +336,7 @@ class ConnectionEvents(event.Events): The hook is called while the cursor from the failed operation (if any) is still open and accessible. Special cleanup operations can be called on this cursor; SQLAlchemy will attempt to close - this cursor subsequent to this hook being invoked. If the connection - is in "autocommit" mode, the transaction also remains open within - the scope of this hook; the rollback of the per-statement transaction - also occurs after the hook is called. + this cursor subsequent to this hook being invoked. .. note:: @@ -437,7 +434,10 @@ class ConnectionEvents(event.Events): """ - def engine_connect(self, conn, branch): + @event._legacy_signature( + "2.0", ["conn", "branch"], converter=lambda conn: (conn, False) + ) + def engine_connect(self, conn): """Intercept the creation of a new :class:`_engine.Connection`. This event is called typically as the direct result of calling @@ -461,19 +461,9 @@ class ConnectionEvents(event.Events): events within the lifespan of a single :class:`_engine.Connection` object, if that :class:`_engine.Connection` - is invalidated and re-established. There can also be multiple - :class:`_engine.Connection` - objects generated for the same already-checked-out - DBAPI connection, in the case that a "branch" of a - :class:`_engine.Connection` - is produced. + is invalidated and re-established. :param conn: :class:`_engine.Connection` object. - :param branch: if True, this is a "branch" of an existing - :class:`_engine.Connection`. A branch is generated within the course - of a statement execution to invoke supplemental statements, most - typically to pre-execute a SELECT of a default value for the purposes - of an INSERT statement. .. seealso:: diff --git a/lib/sqlalchemy/engine/interfaces.py b/lib/sqlalchemy/engine/interfaces.py index 3fd245e44..38d2c7a57 100644 --- a/lib/sqlalchemy/engine/interfaces.py +++ b/lib/sqlalchemy/engine/interfaces.py @@ -550,15 +550,6 @@ class Dialect(object): that transactions are implicit. This hook is provided for those DBAPIs that might need additional help in this area. - Note that :meth:`.Dialect.do_begin` is not called unless a - :class:`.Transaction` object is in use. The - :meth:`.Dialect.do_autocommit` - hook is provided for DBAPIs that need some extra commands emitted - after a commit in order to enter the next transaction, when the - SQLAlchemy :class:`_engine.Connection` - is used in its default "autocommit" - mode. - :param dbapi_connection: a DBAPI connection, typically proxied within a :class:`.ConnectionFairy`. @@ -1356,9 +1347,6 @@ class ExecutionContext(object): isupdate True if the statement is an UPDATE. - should_autocommit - True if the statement is a "committable" statement. - prefetch_cols a list of Column objects for which a client-side default was fired off. Applies to inserts and updates. @@ -1507,12 +1495,6 @@ class ExecutionContext(object): raise NotImplementedError() - def should_autocommit_text(self, statement): - """Parse the given textual statement and return True if it refers to - a "committable" statement""" - - raise NotImplementedError() - def lastrow_has_defaults(self): """Return True if the last INSERT or UPDATE row contained inlined or database-side defaults. diff --git a/lib/sqlalchemy/engine/util.py b/lib/sqlalchemy/engine/util.py index 8eb0f1820..36691504c 100644 --- a/lib/sqlalchemy/engine/util.py +++ b/lib/sqlalchemy/engine/util.py @@ -31,110 +31,36 @@ def connection_memoize(key): _no_tuple = () -_no_kw = util.immutabledict() -def _distill_params(connection, multiparams, params): - r"""Given arguments from the calling form \*multiparams, \**params, - return a list of bind parameter structures, usually a list of - dictionaries. - - In the case of 'raw' execution which accepts positional parameters, - it may be a list of tuples or lists. - - """ - - if not multiparams: - if params: - connection._warn_for_legacy_exec_format() - return [params] - else: - return [] - elif len(multiparams) == 1: - zero = multiparams[0] - if isinstance(zero, (list, tuple)): - if ( - not zero - or hasattr(zero[0], "__iter__") - and not hasattr(zero[0], "strip") - ): - # execute(stmt, [{}, {}, {}, ...]) - # execute(stmt, [(), (), (), ...]) - return zero - else: - # this is used by exec_driver_sql only, so a deprecation - # warning would already be coming from passing a plain - # textual statement with positional parameters to - # execute(). - # execute(stmt, ("value", "value")) - return [zero] - elif hasattr(zero, "keys"): - # execute(stmt, {"key":"value"}) - return [zero] - else: - connection._warn_for_legacy_exec_format() - # execute(stmt, "value") - return [[zero]] - else: - connection._warn_for_legacy_exec_format() - if hasattr(multiparams[0], "__iter__") and not hasattr( - multiparams[0], "strip" +def _distill_params_20(params): + if params is None: + return _no_tuple + elif isinstance(params, (list, tuple)): + # collections_abc.MutableSequence): # avoid abc.__instancecheck__ + if params and not isinstance( + params[0], (collections_abc.Mapping, tuple) ): - return multiparams - else: - return [multiparams] - - -def _distill_cursor_params(connection, multiparams, params): - """_distill_params without any warnings. more appropriate for - "cursor" params that can include tuple arguments, lists of tuples, - etc. - - """ + raise exc.ArgumentError( + "List argument must consist only of tuples or dictionaries" + ) - if not multiparams: - if params: - return [params] - else: - return [] - elif len(multiparams) == 1: - zero = multiparams[0] - if isinstance(zero, (list, tuple)): - if ( - not zero - or hasattr(zero[0], "__iter__") - and not hasattr(zero[0], "strip") - ): - # execute(stmt, [{}, {}, {}, ...]) - # execute(stmt, [(), (), (), ...]) - return zero - else: - # this is used by exec_driver_sql only, so a deprecation - # warning would already be coming from passing a plain - # textual statement with positional parameters to - # execute(). - # execute(stmt, ("value", "value")) - - return [zero] - elif hasattr(zero, "keys"): - # execute(stmt, {"key":"value"}) - return [zero] - else: - # execute(stmt, "value") - return [[zero]] + return params + elif isinstance( + params, + (dict, immutabledict), + # only do abc.__instancecheck__ for Mapping after we've checked + # for plain dictionaries and would otherwise raise + ) or isinstance(params, collections_abc.Mapping): + return [params] else: - if hasattr(multiparams[0], "__iter__") and not hasattr( - multiparams[0], "strip" - ): - return multiparams - else: - return [multiparams] + raise exc.ArgumentError("mapping or sequence expected for parameters") -def _distill_params_20(params): +def _distill_raw_params(params): if params is None: - return _no_tuple, _no_kw - elif isinstance(params, list): + return _no_tuple + elif isinstance(params, (list,)): # collections_abc.MutableSequence): # avoid abc.__instancecheck__ if params and not isinstance( params[0], (collections_abc.Mapping, tuple) @@ -143,14 +69,14 @@ def _distill_params_20(params): "List argument must consist only of tuples or dictionaries" ) - return (params,), _no_kw + return params elif isinstance( params, (tuple, dict, immutabledict), # only do abc.__instancecheck__ for Mapping after we've checked # for plain dictionaries and would otherwise raise ) or isinstance(params, collections_abc.Mapping): - return (params,), _no_kw + return [params] else: raise exc.ArgumentError("mapping or sequence expected for parameters") diff --git a/lib/sqlalchemy/event/legacy.py b/lib/sqlalchemy/event/legacy.py index 0dbf69504..f75ed121f 100644 --- a/lib/sqlalchemy/event/legacy.py +++ b/lib/sqlalchemy/event/legacy.py @@ -14,6 +14,17 @@ from .. import util def _legacy_signature(since, argnames, converter=None): + """legacy sig decorator + + + :param since: string version for deprecation warning + :param argnames: list of strings, which is *all* arguments that the legacy + version accepted, including arguments that are still there + :param converter: lambda that will accept tuple of this full arg signature + and return tuple of new arg signature. + + """ + def leg(fn): if not hasattr(fn, "_legacy_signatures"): fn._legacy_signatures = [] diff --git a/lib/sqlalchemy/ext/asyncio/engine.py b/lib/sqlalchemy/ext/asyncio/engine.py index bfaaea4d9..221d82f08 100644 --- a/lib/sqlalchemy/ext/asyncio/engine.py +++ b/lib/sqlalchemy/ext/asyncio/engine.py @@ -379,7 +379,7 @@ class AsyncConnection(ProxyComparable, StartableContext, AsyncConnectable): conn = self._sync_connection() result = await greenlet_spawn( - conn._execute_20, + conn.execute, statement, parameters, util.EMPTY_DICT.merge_with( @@ -432,7 +432,7 @@ class AsyncConnection(ProxyComparable, StartableContext, AsyncConnectable): conn = self._sync_connection() result = await greenlet_spawn( - conn._execute_20, + conn.execute, statement, parameters, execution_options, diff --git a/lib/sqlalchemy/ext/compiler.py b/lib/sqlalchemy/ext/compiler.py index c7eb7cc32..354b0ca4a 100644 --- a/lib/sqlalchemy/ext/compiler.py +++ b/lib/sqlalchemy/ext/compiler.py @@ -103,10 +103,6 @@ Produces:: functionality is already available using the :meth:`_expression.Insert.from_select` method. -.. note:: - - The above ``InsertFromSelect`` construct probably wants to have "autocommit" - enabled. See :ref:`enabling_compiled_autocommit` for this step. Cross Compiling between SQL and DDL compilers --------------------------------------------- @@ -135,52 +131,6 @@ a bound parameter; when emitting DDL, bound parameters are typically not supported. -.. _enabling_compiled_autocommit: - -Enabling Autocommit on a Construct -================================== - -Recall from the section :ref:`autocommit` that the :class:`_engine.Engine`, -when -asked to execute a construct in the absence of a user-defined transaction, -detects if the given construct represents DML or DDL, that is, a data -modification or data definition statement, which requires (or may require, -in the case of DDL) that the transaction generated by the DBAPI be committed -(recall that DBAPI always has a transaction going on regardless of what -SQLAlchemy does). Checking for this is actually accomplished by checking for -the "autocommit" execution option on the construct. When building a -construct like an INSERT derivation, a new DDL type, or perhaps a stored -procedure that alters data, the "autocommit" option needs to be set in order -for the statement to function with "connectionless" execution -(as described in :ref:`dbengine_implicit`). - -Currently a quick way to do this is to subclass :class:`.Executable`, then -add the "autocommit" flag to the ``_execution_options`` dictionary (note this -is a "frozen" dictionary which supplies a generative ``union()`` method):: - - from sqlalchemy.sql.expression import Executable, ClauseElement - - class MyInsertThing(Executable, ClauseElement): - _execution_options = \ - Executable._execution_options.union({'autocommit': True}) - -More succinctly, if the construct is truly similar to an INSERT, UPDATE, or -DELETE, :class:`.UpdateBase` can be used, which already is a subclass -of :class:`.Executable`, :class:`_expression.ClauseElement` and includes the -``autocommit`` flag:: - - from sqlalchemy.sql.expression import UpdateBase - - class MyInsertThing(UpdateBase): - def __init__(self, ...): - ... - - - - -DDL elements that subclass :class:`.DDLElement` already have the -"autocommit" flag turned on. - diff --git a/lib/sqlalchemy/future/engine.py b/lib/sqlalchemy/future/engine.py index 3235529f7..788c94193 100644 --- a/lib/sqlalchemy/future/engine.py +++ b/lib/sqlalchemy/future/engine.py @@ -1,413 +1,3 @@ -from .. import util -from ..engine import Connection as _LegacyConnection -from ..engine import create_engine as _create_engine -from ..engine import Engine as _LegacyEngine -from ..engine.base import OptionEngineMixin - -NO_OPTIONS = util.immutabledict() - - -def create_engine(*arg, **kw): - """Create a new :class:`_future.Engine` instance. - - Arguments passed to :func:`_future.create_engine` are mostly identical - to those passed to the 1.x :func:`_sa.create_engine` function. - The difference is that the object returned is the :class:`._future.Engine` - which has the 2.0 version of the API. - - """ - - kw["_future_engine_class"] = Engine - return _create_engine(*arg, **kw) - - -class Connection(_LegacyConnection): - """Provides high-level functionality for a wrapped DB-API connection. - - The :class:`_future.Connection` object is procured by calling - the :meth:`_future.Engine.connect` method of the :class:`_future.Engine` - object, and provides services for execution of SQL statements as well - as transaction control. - - **This is the SQLAlchemy 2.0 version** of the :class:`_engine.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 - :class:`_engine.CursorResult` - object, which is a subclass of the :class:`_engine.Result`. - This object has a slightly different API and behavior than the - :class:`_engine.LegacyCursorResult` returned for 1.x style usage. - - * The object has :meth:`_future.Connection.commit` and - :meth:`_future.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 - :meth:`_future.Connection.execute` will - unconditionally start a - transaction which can be controlled using the above mentioned - :meth:`_future.Connection.commit` and - :meth:`_future.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 - :meth:`_future.Connection.commit` method, or if the connection is - being used in a context manager that commits such as the one - returned by :meth:`_future.Engine.begin`. - - * The SAVEPOINT method :meth:`_future.Connection.begin_nested` returns - a :class:`_engine.NestedTransaction` as was always the case, and the - savepoint can be controlled by invoking - :meth:`_engine.NestedTransaction.commit` or - :meth:`_engine.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 :class:`_future.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. - - - - """ - - _is_future = True - - def _branch(self): - raise NotImplementedError( - "sqlalchemy.future.Connection does not support " - "'branching' of new connections." - ) - - def begin(self): - """Begin a transaction prior to autobegin occurring. - - The returned object is an instance of :class:`_engine.RootTransaction`. - This object represents the "scope" of the transaction, - which completes when either the :meth:`_engine.Transaction.rollback` - or :meth:`_engine.Transaction.commit` method is called. - - The :meth:`_future.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 :meth:`_events.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 - :meth:`_future.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 :meth:`_future.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. - - .. seealso:: - - :ref:`tutorial_working_with_transactions` - in the - :ref:`unified_tutorial` - - :meth:`_future.Connection.begin_nested` - use a SAVEPOINT - - :meth:`_engine.Connection.begin_twophase` - - use a two phase /XID transaction - - :meth:`_future.Engine.begin` - context manager available from - :class:`_future.Engine` - - """ - return super(Connection, self).begin() - - def begin_nested(self): - """Begin a nested transaction (i.e. SAVEPOINT) and return a transaction - handle. - - The returned object is an instance of - :class:`_engine.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. - - If an outer :class:`.RootTransaction` is not present on this - :class:`_future.Connection`, a new one is created using "autobegin". - This outer transaction may be completed using "commit-as-you-go" style - usage, by calling upon :meth:`_future.Connection.commit` or - :meth:`_future.Connection.rollback`. - - .. tip:: - - The "autobegin" behavior of :meth:`_future.Connection.begin_nested` - is specific to :term:`2.0 style` use; for legacy behaviors, see - :meth:`_engine.Connection.begin_nested`. - - The :class:`_engine.NestedTransaction` remains independent of the - :class:`_future.Connection` object itself. Calling the - :meth:`_future.Connection.commit` or - :meth:`_future.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. - - .. seealso:: - - :meth:`_future.Connection.begin` - - - """ - return super(Connection, self).begin_nested() - - def commit(self): - """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 :class:`_future.Connection` automatically - whenever a statement is first executed, or when the - :meth:`_future.Connection.begin` method is called. - - .. note:: The :meth:`_future.Connection.commit` method only acts upon - the primary database transaction that is linked to the - :class:`_future.Connection` object. It does not operate upon a - SAVEPOINT that would have been invoked from the - :meth:`_future.Connection.begin_nested` method; for control of a - SAVEPOINT, call :meth:`_engine.NestedTransaction.commit` on the - :class:`_engine.NestedTransaction` that is returned by the - :meth:`_future.Connection.begin_nested` method itself. - - - """ - if self._transaction: - self._transaction.commit() - - def rollback(self): - """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 :class:`_future.Connection` automatically - whenever a statement is first executed, or when the - :meth:`_future.Connection.begin` method is called. - - .. note:: The :meth:`_future.Connection.rollback` method only acts - upon the primary database transaction that is linked to the - :class:`_future.Connection` object. It does not operate upon a - SAVEPOINT that would have been invoked from the - :meth:`_future.Connection.begin_nested` method; for control of a - SAVEPOINT, call :meth:`_engine.NestedTransaction.rollback` on the - :class:`_engine.NestedTransaction` that is returned by the - :meth:`_future.Connection.begin_nested` method itself. - - - """ - if self._transaction: - self._transaction.rollback() - - def close(self): - """Close this :class:`_future.Connection`. - - This has the effect of also calling :meth:`_future.Connection.rollback` - if any transaction is in place. - - """ - super(Connection, self).close() - - def execute(self, statement, parameters=None, execution_options=None): - r"""Executes a SQL statement construct and returns a - :class:`_engine.Result`. - - :param statement: The statement to be executed. This is always - an object that is in both the :class:`_expression.ClauseElement` and - :class:`_expression.Executable` hierarchies, including: - - * :class:`_expression.Select` - * :class:`_expression.Insert`, :class:`_expression.Update`, - :class:`_expression.Delete` - * :class:`_expression.TextClause` and - :class:`_expression.TextualSelect` - * :class:`_schema.DDL` and objects which inherit from - :class:`_schema.DDLElement` - - :param 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. - - :param 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 :meth:`_future.Connection.execution_options`. - - :return: a :class:`_engine.Result` object. - - """ - return self._execute_20( - statement, parameters, execution_options or NO_OPTIONS - ) - - def scalar(self, statement, parameters=None, execution_options=None): - r"""Executes a SQL statement construct and returns a scalar object. - - This method is shorthand for invoking the - :meth:`_engine.Result.scalar` method after invoking the - :meth:`_future.Connection.execute` method. Parameters are equivalent. - - :return: a scalar Python value representing the first column of the - first row returned. - - """ - return self.execute(statement, parameters, execution_options).scalar() - - -class Engine(_LegacyEngine): - """Connects a :class:`_pool.Pool` and - :class:`_engine.Dialect` together to provide a - source of database connectivity and behavior. - - **This is the SQLAlchemy 2.0 version** of the :class:`~.engine.Engine`. - - An :class:`.future.Engine` object is instantiated publicly using the - :func:`~sqlalchemy.future.create_engine` function. - - .. seealso:: - - :doc:`/core/engines` - - :ref:`connections_toplevel` - - """ - - _connection_cls = Connection - _is_future = True - - def _not_implemented(self, *arg, **kw): - raise NotImplementedError( - "This method is not implemented for SQLAlchemy 2.0." - ) - - transaction = ( - run_callable - ) = ( - execute - ) = ( - scalar - ) = ( - _execute_clauseelement - ) = _execute_compiled = table_names = has_table = _not_implemented - - def _run_ddl_visitor(self, visitorcallable, element, **kwargs): - # TODO: this is for create_all support etc. not clear if we - # want to provide this in 2.0, that is, a way to execute SQL where - # they aren't calling "engine.begin()" explicitly, however, DDL - # may be a special case for which we want to continue doing it this - # way. A big win here is that the full DDL sequence is inside of a - # single transaction rather than COMMIT for each statement. - with self.begin() as conn: - conn._run_ddl_visitor(visitorcallable, element, **kwargs) - - @classmethod - def _future_facade(self, legacy_engine): - return Engine( - legacy_engine.pool, - legacy_engine.dialect, - legacy_engine.url, - logging_name=legacy_engine.logging_name, - echo=legacy_engine.echo, - hide_parameters=legacy_engine.hide_parameters, - execution_options=legacy_engine._execution_options, - ) - - @util.contextmanager - def begin(self): - """Return a :class:`_future.Connection` object with a transaction - begun. - - Use of this method is similar to that of - :meth:`_future.Engine.connect`, typically as a context manager, which - will automatically maintain the state of the transaction when the block - ends, either by calling :meth:`_future.Connection.commit` when the - block succeeds normally, or :meth:`_future.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')")) - - - .. seealso:: - - :meth:`_future.Engine.connect` - - :meth:`_future.Connection.begin` - - """ - with self.connect() as conn: - with conn.begin(): - yield conn - - def connect(self): - """Return a new :class:`_future.Connection` object. - - The :class:`_future.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 :meth:`_events.ConnectionEvents.rollback` event if one was - started and is still in progress. - - .. seealso:: - - :meth:`_future.Engine.begin` - - - """ - return super(Engine, self).connect() - - -class OptionEngine(OptionEngineMixin, Engine): - pass - - -Engine._option_cls = OptionEngine +from ..engine import Connection # noqa +from ..engine import create_engine # noqa +from ..engine import Engine # noqa diff --git a/lib/sqlalchemy/orm/persistence.py b/lib/sqlalchemy/orm/persistence.py index 4ba1917f6..3c0c637a2 100644 --- a/lib/sqlalchemy/orm/persistence.py +++ b/lib/sqlalchemy/orm/persistence.py @@ -938,7 +938,7 @@ def _emit_update_statements( has_all_defaults, has_all_pks, ) in records: - c = connection._execute_20( + c = connection.execute( statement.values(value_params), params, execution_options=execution_options, @@ -971,7 +971,7 @@ def _emit_update_statements( has_all_defaults, has_all_pks, ) in records: - c = connection._execute_20( + c = connection.execute( statement, params, execution_options=execution_options ) @@ -997,7 +997,7 @@ def _emit_update_statements( assert_singlerow and len(multiparams) == 1 ) - c = connection._execute_20( + c = connection.execute( statement, multiparams, execution_options=execution_options ) @@ -1093,7 +1093,7 @@ def _emit_insert_statements( records = list(records) multiparams = [rec[2] for rec in records] - c = connection._execute_20( + c = connection.execute( statement, multiparams, execution_options=execution_options ) @@ -1152,7 +1152,7 @@ def _emit_insert_statements( if do_executemany: multiparams = [rec[2] for rec in records] - c = connection._execute_20( + c = connection.execute( statement, multiparams, execution_options=execution_options ) @@ -1212,13 +1212,13 @@ def _emit_insert_statements( has_all_defaults, ) in records: if value_params: - result = connection._execute_20( + result = connection.execute( statement.values(value_params), params, execution_options=execution_options, ) else: - result = connection._execute_20( + result = connection.execute( statement, params, execution_options=execution_options, @@ -1321,7 +1321,7 @@ def _emit_post_update_statements( check_rowcount = assert_singlerow for state, state_dict, mapper_rec, connection, params in records: - c = connection._execute_20( + c = connection.execute( statement, params, execution_options=execution_options ) @@ -1345,7 +1345,7 @@ def _emit_post_update_statements( assert_singlerow and len(multiparams) == 1 ) - c = connection._execute_20( + c = connection.execute( statement, multiparams, execution_options=execution_options ) @@ -1425,7 +1425,7 @@ def _emit_delete_statements( # rows can be verified for params in del_objects: - c = connection._execute_20( + c = connection.execute( statement, params, execution_options=execution_options ) rows_matched += c.rowcount @@ -1435,11 +1435,11 @@ def _emit_delete_statements( "- versioning cannot be verified." % connection.dialect.dialect_description ) - connection._execute_20( + connection.execute( statement, del_objects, execution_options=execution_options ) else: - c = connection._execute_20( + c = connection.execute( statement, del_objects, execution_options=execution_options ) diff --git a/lib/sqlalchemy/orm/session.py b/lib/sqlalchemy/orm/session.py index 415826e5f..c76ece5de 100644 --- a/lib/sqlalchemy/orm/session.py +++ b/lib/sqlalchemy/orm/session.py @@ -1499,6 +1499,11 @@ class Session(_SessionClassMethods): by :meth:`_engine.Connection.execution_options`, and may also provide additional options understood only in an ORM context. + .. seealso:: + + :ref:`orm_queryguide_execution_options` - ORM-specific execution + options + :param bind_arguments: dictionary of additional arguments to determine the bind. May include "mapper", "bind", or other custom arguments. Contents of this dictionary are passed to the @@ -1592,7 +1597,7 @@ class Session(_SessionClassMethods): bind = self.get_bind(**bind_arguments) conn = self._connection_for_bind(bind) - result = conn._execute_20(statement, params or {}, execution_options) + result = conn.execute(statement, params or {}, execution_options) if compile_state_cls: result = compile_state_cls.orm_setup_cursor_result( diff --git a/lib/sqlalchemy/pool/base.py b/lib/sqlalchemy/pool/base.py index 38b0f67cb..ec55da730 100644 --- a/lib/sqlalchemy/pool/base.py +++ b/lib/sqlalchemy/pool/base.py @@ -131,7 +131,7 @@ class Pool(log.Identified): any data changes present on the transaction are committed unconditionally. * ``None`` - don't do anything on the connection. - This setting is only appropriate if the database / DBAPI + This setting may be appropriate if the database / DBAPI works in pure "autocommit" mode at all times, or if the application uses the :class:`_engine.Engine` with consistent connectivity patterns. See the section diff --git a/lib/sqlalchemy/sql/base.py b/lib/sqlalchemy/sql/base.py index 4bcb655d6..0c375b6d7 100644 --- a/lib/sqlalchemy/sql/base.py +++ b/lib/sqlalchemy/sql/base.py @@ -32,7 +32,6 @@ coercions = None elements = None type_api = None -PARSE_AUTOCOMMIT = util.symbol("PARSE_AUTOCOMMIT") NO_ARG = util.symbol("NO_ARG") @@ -884,34 +883,104 @@ class Executable(roles.StatementRole, Generative): """Set non-SQL options for the statement which take effect during execution. - Execution options can be set on a per-statement or - per :class:`_engine.Connection` basis. Additionally, the - :class:`_engine.Engine` and ORM :class:`~.orm.query.Query` - objects provide - access to execution options which they in turn configure upon - connections. - - The :meth:`execution_options` method is generative. A new - instance of this statement is returned that contains the options:: + Execution options can be set at many scopes, including per-statement, + per-connection, or per execution, using methods such as + :meth:`_engine.Connection.execution_options` and parameters which + accept a dictionary of options such as + :paramref:`_engine.Connection.execute.execution_options` and + :paramref:`_orm.Session.execute.execution_options`. + + The primary characteristic of an execution option, as opposed to + other kinds of options such as ORM loader options, is that + **execution options never affect the compiled SQL of a query, only + things that affect how the SQL statement itself is invoked or how + results are fetched**. That is, execution options are not part of + what's accommodated by SQL compilation nor are they considered part of + the cached state of a statement. + + The :meth:`_sql.Executable.execution_options` method is + :term:`generative`, as + is the case for the method as applied to the :class:`_engine.Engine` + and :class:`_orm.Query` objects, which means when the method is called, + a copy of the object is returned, which applies the given parameters to + that new copy, but leaves the original unchanged:: statement = select(table.c.x, table.c.y) - statement = statement.execution_options(autocommit=True) - - Note that only a subset of possible execution options can be applied - to a statement - these include "autocommit" and "stream_results", - but not "isolation_level" or "compiled_cache". - See :meth:`_engine.Connection.execution_options` for a full list of - possible options. + new_statement = statement.execution_options(my_option=True) + + An exception to this behavior is the :class:`_engine.Connection` + object, where the :meth:`_engine.Connection.execution_options` method + is explicitly **not** generative. + + The kinds of options that may be passed to + :meth:`_sql.Executable.execution_options` and other related methods and + parameter dictionaries include parameters that are explicitly consumed + by SQLAlchemy Core or ORM, as well as arbitrary keyword arguments not + defined by SQLAlchemy, which means the methods and/or parameter + dictionaries may be used for user-defined parameters that interact with + custom code, which may access the parameters using methods such as + :meth:`_sql.Executable.get_execution_options` and + :meth:`_engine.Connection.get_execution_options`, or within selected + event hooks using a dedicated ``execution_options`` event parameter + such as + :paramref:`_events.ConnectionEvents.before_execute.execution_options` + or :attr:`_orm.ORMExecuteState.execution_options`, e.g.:: + + from sqlalchemy import event + + @event.listens_for(some_engine, "before_execute") + def _process_opt(conn, statement, multiparams, params, execution_options): + "run a SQL function before invoking a statement" + + if execution_options.get("do_special_thing", False): + conn.exec_driver_sql("run_special_function()") + + Within the scope of options that are explicitly recognized by + SQLAlchemy, most apply to specific classes of objects and not others. + The most common execution options include: + + * :paramref:`_engine.Connection.execution_options.isolation_level` - + sets the isolation level for a connection or a class of connections + via an :class:`_engine.Engine`. This option is accepted only + by :class:`_engine.Connection` or :class:`_engine.Engine`. + + * :paramref:`_engine.Connection.execution_options.stream_results` - + indicates results should be fetched using a server side cursor; + this option is accepted by :class:`_engine.Connection`, by the + :paramref:`_engine.Connection.execute.execution_options` parameter + on :meth:`_engine.Connection.execute`, and additionally by + :meth:`_sql.Executable.execution_options` on a SQL statement object, + as well as by ORM constructs like :meth:`_orm.Session.execute`. + + * :paramref:`_engine.Connection.execution_options.compiled_cache` - + indicates a dictionary that will serve as the + :ref:`SQL compilation cache <sql_caching>` + for a :class:`_engine.Connection` or :class:`_engine.Engine`, as + well as for ORM methods like :meth:`_orm.Session.execute`. + Can be passed as ``None`` to disable caching for statements. + This option is not accepted by + :meth:`_sql.Executable.execution_options` as it is inadvisable to + carry along a compilation cache within a statement object. + + * :paramref:`_engine.Connection.execution_options.schema_translate_map` + - a mapping of schema names used by the + :ref:`Schema Translate Map <schema_translating>` feature, accepted + by :class:`_engine.Connection`, :class:`_engine.Engine`, + :class:`_sql.Executable`, as well as by ORM constructs + like :meth:`_orm.Session.execute`. .. seealso:: :meth:`_engine.Connection.execution_options` - :meth:`_query.Query.execution_options` + :paramref:`_engine.Connection.execute.execution_options` - :meth:`.Executable.get_execution_options` + :paramref:`_orm.Session.execute.execution_options` - """ + :ref:`orm_queryguide_execution_options` - documentation on all + ORM-specific execution options + + """ # noqa E501 if "isolation_level" in kw: raise exc.ArgumentError( "'isolation_level' execution option may only be specified " diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index 96349578c..4611c5e13 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -456,11 +456,11 @@ class Compiled(object): self._gen_time = util.perf_counter() def _execute_on_connection( - self, connection, multiparams, params, execution_options + self, connection, distilled_params, execution_options ): if self.can_execute: return connection._execute_compiled( - self, multiparams, params, execution_options + self, distilled_params, execution_options ) else: raise exc.ObjectNotExecutableError(self.statement) diff --git a/lib/sqlalchemy/sql/ddl.py b/lib/sqlalchemy/sql/ddl.py index f8985548e..f732ff2b0 100644 --- a/lib/sqlalchemy/sql/ddl.py +++ b/lib/sqlalchemy/sql/ddl.py @@ -11,7 +11,6 @@ to invoke them for a create/drop call. """ from . import roles -from .base import _bind_or_error from .base import _generative from .base import Executable from .base import SchemaVisitor @@ -62,62 +61,18 @@ class DDLElement(roles.DDLRole, Executable, _DDLCompiles): """ - _execution_options = Executable._execution_options.union( - {"autocommit": True} - ) - target = None on = None dialect = None callable_ = None def _execute_on_connection( - self, connection, multiparams, params, execution_options + self, connection, distilled_params, execution_options ): return connection._execute_ddl( - self, multiparams, params, execution_options + self, distilled_params, execution_options ) - @util.deprecated_20( - ":meth:`.DDLElement.execute`", - alternative="All statement execution in SQLAlchemy 2.0 is performed " - "by the :meth:`_engine.Connection.execute` method of " - ":class:`_engine.Connection`, " - "or in the ORM by the :meth:`.Session.execute` method of " - ":class:`.Session`.", - ) - def execute(self, bind=None, target=None): - """Execute this DDL immediately. - - Executes the DDL statement in isolation using the supplied - :class:`.Connectable` or - :class:`.Connectable` assigned to the ``.bind`` - property, if not supplied. If the DDL has a conditional ``on`` - criteria, it will be invoked with None as the event. - - :param bind: - Optional, an ``Engine`` or ``Connection``. If not supplied, a valid - :class:`.Connectable` must be present in the - ``.bind`` property. - - :param target: - Optional, defaults to None. The target :class:`_schema.SchemaItem` - for the execute call. This is equivalent to passing the - :class:`_schema.SchemaItem` to the :meth:`.DDLElement.against` - method and then invoking :meth:`_schema.DDLElement.execute` - upon the resulting :class:`_schema.DDLElement` object. See - :meth:`.DDLElement.against` for further detail. - - """ - - if bind is None: - bind = _bind_or_error(self) - - if self._should_execute(target, bind): - return bind.execute(self.against(target)) - else: - bind.engine.logger.info("DDL execution skipped, criteria not met.") - @_generative def against(self, target): """Return a copy of this :class:`_schema.DDLElement` which will include diff --git a/lib/sqlalchemy/sql/dml.py b/lib/sqlalchemy/sql/dml.py index ebff0df88..03968d284 100644 --- a/lib/sqlalchemy/sql/dml.py +++ b/lib/sqlalchemy/sql/dml.py @@ -223,9 +223,6 @@ class UpdateBase( __visit_name__ = "update_base" - _execution_options = Executable._execution_options.union( - {"autocommit": True} - ) _hints = util.immutabledict() named_with_column = False diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py index c8faebbd9..a279f634d 100644 --- a/lib/sqlalchemy/sql/elements.py +++ b/lib/sqlalchemy/sql/elements.py @@ -29,7 +29,6 @@ from .base import Executable from .base import HasMemoized from .base import Immutable from .base import NO_ARG -from .base import PARSE_AUTOCOMMIT from .base import SingletonConstant from .coercions import _document_text_coercion from .traversals import HasCopyInternals @@ -319,11 +318,11 @@ class ClauseElement( return d def _execute_on_connection( - self, connection, multiparams, params, execution_options, _force=False + self, connection, distilled_params, execution_options, _force=False ): if _force or self.supports_execution: return connection._execute_clauseelement( - self, multiparams, params, execution_options + self, distilled_params, execution_options ) else: raise exc.ObjectNotExecutableError(self) @@ -1755,9 +1754,6 @@ class TextClause( _is_textual = True _bind_params_regex = re.compile(r"(?<![:\w\x5c]):(\w+)(?!:)", re.UNICODE) - _execution_options = Executable._execution_options.union( - {"autocommit": PARSE_AUTOCOMMIT} - ) _is_implicitly_boolean = False _render_label_in_columns_clause = False @@ -1860,19 +1856,8 @@ class TextClause( :func:`_expression.text` is also used for the construction of a full, standalone statement using plain text. As such, SQLAlchemy refers - to it as an :class:`.Executable` object, and it supports - the :meth:`Executable.execution_options` method. For example, - a :func:`_expression.text` - construct that should be subject to "autocommit" - can be set explicitly so using the - :paramref:`.Connection.execution_options.autocommit` option:: - - t = text("EXEC my_procedural_thing()").\ - execution_options(autocommit=True) - - .. deprecated:: 1.4 The "autocommit" execution option is deprecated - and will be removed in SQLAlchemy 2.0. See - :ref:`migration_20_autocommit` for discussion. + to it as an :class:`.Executable` object and may be used + like any other statement passed to an ``.execute()`` method. :param text: the text of the SQL statement to be created. Use ``:<param>`` @@ -5044,9 +5029,6 @@ class CollationClause(ColumnElement): class _IdentifiedClause(Executable, ClauseElement): __visit_name__ = "identified" - _execution_options = Executable._execution_options.union( - {"autocommit": False} - ) def __init__(self, ident): self.ident = ident diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py index 129e628ab..03fe9b567 100644 --- a/lib/sqlalchemy/sql/expression.py +++ b/lib/sqlalchemy/sql/expression.py @@ -96,7 +96,6 @@ from .base import _from_objects from .base import _select_iterables from .base import ColumnCollection from .base import Executable -from .base import PARSE_AUTOCOMMIT from .dml import Delete from .dml import Insert from .dml import Update diff --git a/lib/sqlalchemy/sql/functions.py b/lib/sqlalchemy/sql/functions.py index 5729f81f5..35b0cb7d7 100644 --- a/lib/sqlalchemy/sql/functions.py +++ b/lib/sqlalchemy/sql/functions.py @@ -134,10 +134,10 @@ class FunctionElement(Executable, ColumnElement, FromClause, Generative): ) def _execute_on_connection( - self, connection, multiparams, params, execution_options + self, connection, distilled_params, execution_options ): return connection._execute_function( - self, multiparams, params, execution_options + self, distilled_params, execution_options ) def scalar_table_valued(self, name, type_=None): @@ -571,50 +571,6 @@ class FunctionElement(Executable, ColumnElement, FromClause, Generative): s = s.execution_options(**self._execution_options) return s - @util.deprecated_20( - ":meth:`.FunctionElement.scalar`", - alternative="Scalar execution in SQLAlchemy 2.0 is performed " - "by the :meth:`_engine.Connection.scalar` method of " - ":class:`_engine.Connection`, " - "or in the ORM by the :meth:`.Session.scalar` method of " - ":class:`.Session`.", - ) - def scalar(self): - """Execute this :class:`.FunctionElement` against an embedded - 'bind' and return a scalar value. - - This first calls :meth:`~.FunctionElement.select` to - produce a SELECT construct. - - Note that :class:`.FunctionElement` can be passed to - the :meth:`.Connectable.scalar` method of :class:`_engine.Connection` - or :class:`_engine.Engine`. - - """ - return self.select().execute().scalar() - - @util.deprecated_20( - ":meth:`.FunctionElement.execute`", - alternative="All statement execution in SQLAlchemy 2.0 is performed " - "by the :meth:`_engine.Connection.execute` method of " - ":class:`_engine.Connection`, " - "or in the ORM by the :meth:`.Session.execute` method of " - ":class:`.Session`.", - ) - def execute(self): - """Execute this :class:`.FunctionElement` against an embedded - 'bind'. - - This first calls :meth:`~.FunctionElement.select` to - produce a SELECT construct. - - Note that :class:`.FunctionElement` can be passed to - the :meth:`.Connectable.execute` method of :class:`_engine.Connection` - or :class:`_engine.Engine`. - - """ - return self.select().execute() - def _bind_param(self, operator, obj, type_=None, **kw): return BindParameter( None, diff --git a/lib/sqlalchemy/sql/lambdas.py b/lib/sqlalchemy/sql/lambdas.py index 03cd05f02..e7c19b9a0 100644 --- a/lib/sqlalchemy/sql/lambdas.py +++ b/lib/sqlalchemy/sql/lambdas.py @@ -502,11 +502,11 @@ class StatementLambdaElement(roles.AllowsLambdaRole, LambdaElement): return LinkedLambdaElement(other, parent_lambda=self, opts=opts) def _execute_on_connection( - self, connection, multiparams, params, execution_options + self, connection, distilled_params, execution_options ): if self._rec.expected_expr.supports_execution: return connection._execute_clauseelement( - self, multiparams, params, execution_options + self, distilled_params, execution_options ) else: raise exc.ObjectNotExecutableError(self) @@ -568,11 +568,11 @@ class NullLambdaStatement(roles.AllowsLambdaRole, elements.ClauseElement): return NullLambdaStatement(statement) def _execute_on_connection( - self, connection, multiparams, params, execution_options + self, connection, distilled_params, execution_options ): if self._resolved.supports_execution: return connection._execute_clauseelement( - self, multiparams, params, execution_options + self, distilled_params, execution_options ) else: raise exc.ObjectNotExecutableError(self) diff --git a/lib/sqlalchemy/sql/schema.py b/lib/sqlalchemy/sql/schema.py index e45e22564..dbd50d5d8 100644 --- a/lib/sqlalchemy/sql/schema.py +++ b/lib/sqlalchemy/sql/schema.py @@ -2518,10 +2518,10 @@ class DefaultGenerator(Executable, SchemaItem): self.column.default = self def _execute_on_connection( - self, connection, multiparams, params, execution_options + self, connection, distilled_params, execution_options ): return connection._execute_default( - self, multiparams, params, execution_options + self, distilled_params, execution_options ) @property diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py index 8013477fa..0e5ae89e4 100644 --- a/lib/sqlalchemy/sql/selectable.py +++ b/lib/sqlalchemy/sql/selectable.py @@ -2495,8 +2495,7 @@ class Subquery(AliasedReturnsRows): def _execute_on_connection( self, connection, - multiparams, - params, + distilled_params, execution_options, ): util.warn_deprecated( @@ -2506,7 +2505,7 @@ class Subquery(AliasedReturnsRows): "1.4", ) return self.element._execute_on_connection( - connection, multiparams, params, execution_options, _force=True + connection, distilled_params, execution_options, _force=True ) diff --git a/lib/sqlalchemy/testing/assertsql.py b/lib/sqlalchemy/testing/assertsql.py index 4ee4c5844..ca0bc6726 100644 --- a/lib/sqlalchemy/testing/assertsql.py +++ b/lib/sqlalchemy/testing/assertsql.py @@ -13,7 +13,6 @@ from .. import event from .. import util from ..engine import url from ..engine.default import DefaultDialect -from ..engine.util import _distill_cursor_params from ..schema import _DDLCompiles @@ -370,9 +369,13 @@ class SQLExecuteObserved(object): def __init__(self, context, clauseelement, multiparams, params): self.context = context self.clauseelement = clauseelement - self.parameters = _distill_cursor_params( - context.connection, tuple(multiparams), params - ) + + if multiparams: + self.parameters = multiparams + elif params: + self.parameters = [params] + else: + self.parameters = [] self.statements = [] def __repr__(self): diff --git a/lib/sqlalchemy/testing/engines.py b/lib/sqlalchemy/testing/engines.py index a54f70c5e..815009e78 100644 --- a/lib/sqlalchemy/testing/engines.py +++ b/lib/sqlalchemy/testing/engines.py @@ -269,7 +269,6 @@ def reconnecting_engine(url=None, options=None): def testing_engine( url=None, options=None, - future=None, asyncio=False, transfer_staticpool=False, ): @@ -277,10 +276,6 @@ def testing_engine( if asyncio: from sqlalchemy.ext.asyncio import create_async_engine as create_engine - elif future or ( - config.db and config.db._is_future and future is not False - ): - from sqlalchemy.future import create_engine else: from sqlalchemy import create_engine from sqlalchemy.engine.url import make_url @@ -417,28 +412,3 @@ class DBAPIProxyConnection(object): def __getattr__(self, key): return getattr(self.conn, key) - - -def proxying_engine( - conn_cls=DBAPIProxyConnection, cursor_cls=DBAPIProxyCursor -): - """Produce an engine that provides proxy hooks for - common methods. - - """ - - def mock_conn(): - return conn_cls(config.db, cursor_cls) - - def _wrap_do_on_connect(do_on_connect): - def go(dbapi_conn): - return do_on_connect(dbapi_conn.conn) - - return go - - return testing_engine( - options={ - "creator": mock_conn, - "_wrap_do_on_connect": _wrap_do_on_connect, - } - ) diff --git a/lib/sqlalchemy/testing/fixtures.py b/lib/sqlalchemy/testing/fixtures.py index f04056c5e..1899e5b7c 100644 --- a/lib/sqlalchemy/testing/fixtures.py +++ b/lib/sqlalchemy/testing/fixtures.py @@ -5,7 +5,6 @@ # This module is part of SQLAlchemy and is released under # the MIT License: https://www.opensource.org/licenses/mit-license.php -import contextlib import re import sys @@ -93,9 +92,7 @@ class TestBase(object): @config.fixture() def future_engine(self): - eng = getattr(self, "bind", None) or config.db - with _push_future_engine(eng): - yield + yield @config.fixture() def testing_engine(self): @@ -114,7 +111,6 @@ class TestBase(object): return engines.testing_engine( url=url, options=options, - future=future, asyncio=asyncio, transfer_staticpool=transfer_staticpool, ) @@ -303,26 +299,8 @@ class TestBase(object): _connection_fixture_connection = None -@contextlib.contextmanager -def _push_future_engine(engine): - - from ..future.engine import Engine - from sqlalchemy import testing - - facade = Engine._future_facade(engine) - config._current.push_engine(facade, testing) - - yield facade - - config._current.pop(testing) - - class FutureEngineMixin(object): - @config.fixture(autouse=True, scope="class") - def _push_future_engine(self): - eng = getattr(self, "bind", None) or config.db - with _push_future_engine(eng): - yield + """alembic's suite still using this""" class TablesTest(TestBase): diff --git a/lib/sqlalchemy/testing/suite/test_dialect.py b/lib/sqlalchemy/testing/suite/test_dialect.py index 32dfdedad..b3e43aad0 100644 --- a/lib/sqlalchemy/testing/suite/test_dialect.py +++ b/lib/sqlalchemy/testing/suite/test_dialect.py @@ -170,6 +170,7 @@ class AutocommitIsolationTest(fixtures.TablesTest): conn.scalar(select(self.tables.some_table.c.id)), 1 if autocommit else None, ) + conn.rollback() with conn.begin(): conn.execute(self.tables.some_table.delete()) diff --git a/lib/sqlalchemy/testing/suite/test_results.py b/lib/sqlalchemy/testing/suite/test_results.py index c41a55025..f470432d2 100644 --- a/lib/sqlalchemy/testing/suite/test_results.py +++ b/lib/sqlalchemy/testing/suite/test_results.py @@ -323,6 +323,20 @@ class ServerSideCursorsTest( ).exec_driver_sql("select 1") assert self._is_server_side(result.cursor) + # the connection has autobegun, which means at the end of the + # block, we will roll back, which on MySQL at least will fail + # with "Commands out of sync" if the result set + # is not closed, so we close it first. + # + # fun fact! why did we not have this result.close() in this test + # before 2.0? don't we roll back in the connection pool + # unconditionally? yes! and in fact if you run this test in 1.4 + # with stdout shown, there is in fact "Exception during reset or + # similar" with "Commands out sync" emitted a warning! 2.0's + # architecture finds and fixes what was previously an expensive + # silent error condition. + result.close() + def test_stmt_enabled_conn_option_disabled(self): engine = self._fixture(False) |