Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py: 24%
1027 statements
« prev ^ index » next coverage.py v7.0.1, created at 2022-12-25 06:11 +0000
1# engine/base.py
2# Copyright (C) 2005-2022 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
7from __future__ import with_statement
9import contextlib
10import sys
12from .interfaces import Connectable
13from .interfaces import ExceptionContext
14from .util import _distill_params
15from .util import _distill_params_20
16from .util import TransactionalContext
17from .. import exc
18from .. import inspection
19from .. import log
20from .. import util
21from ..sql import compiler
22from ..sql import util as sql_util
25"""Defines :class:`_engine.Connection` and :class:`_engine.Engine`.
27"""
29_EMPTY_EXECUTION_OPTS = util.immutabledict()
32class Connection(Connectable):
33 """Provides high-level functionality for a wrapped DB-API connection.
35 **This is the SQLAlchemy 1.x.x version** of the :class:`_engine.Connection`
36 class. For the :term:`2.0 style` version, which features some API
37 differences, see :class:`_future.Connection`.
39 The :class:`_engine.Connection` object is procured by calling
40 the :meth:`_engine.Engine.connect` method of the :class:`_engine.Engine`
41 object, and provides services for execution of SQL statements as well
42 as transaction control.
44 The Connection object is **not** thread-safe. While a Connection can be
45 shared among threads using properly synchronized access, it is still
46 possible that the underlying DBAPI connection may not support shared
47 access between threads. Check the DBAPI documentation for details.
49 The Connection object represents a single DBAPI connection checked out
50 from the connection pool. In this state, the connection pool has no affect
51 upon the connection, including its expiration or timeout state. For the
52 connection pool to properly manage connections, connections should be
53 returned to the connection pool (i.e. ``connection.close()``) whenever the
54 connection is not in use.
56 .. index::
57 single: thread safety; Connection
59 """
61 _is_future = False
62 _sqla_logger_namespace = "sqlalchemy.engine.Connection"
64 # used by sqlalchemy.engine.util.TransactionalContext
65 _trans_context_manager = None
67 def __init__(
68 self,
69 engine,
70 connection=None,
71 close_with_result=False,
72 _branch_from=None,
73 _execution_options=None,
74 _dispatch=None,
75 _has_events=None,
76 _allow_revalidate=True,
77 ):
78 """Construct a new Connection."""
79 self.engine = engine
80 self.dialect = engine.dialect
81 self.__branch_from = _branch_from
83 if _branch_from:
84 # branching is always "from" the root connection
85 assert _branch_from.__branch_from is None
86 self._dbapi_connection = connection
87 self._execution_options = _execution_options
88 self._echo = _branch_from._echo
89 self.should_close_with_result = False
90 self.dispatch = _dispatch
91 self._has_events = _branch_from._has_events
92 else:
93 self._dbapi_connection = (
94 connection
95 if connection is not None
96 else engine.raw_connection()
97 )
99 self._transaction = self._nested_transaction = None
100 self.__savepoint_seq = 0
101 self.__in_begin = False
102 self.should_close_with_result = close_with_result
104 self.__can_reconnect = _allow_revalidate
105 self._echo = self.engine._should_log_info()
107 if _has_events is None:
108 # if _has_events is sent explicitly as False,
109 # then don't join the dispatch of the engine; we don't
110 # want to handle any of the engine's events in that case.
111 self.dispatch = self.dispatch._join(engine.dispatch)
112 self._has_events = _has_events or (
113 _has_events is None and engine._has_events
114 )
116 assert not _execution_options
117 self._execution_options = engine._execution_options
119 if self._has_events or self.engine._has_events:
120 self.dispatch.engine_connect(self, _branch_from is not None)
122 @util.memoized_property
123 def _message_formatter(self):
124 if "logging_token" in self._execution_options:
125 token = self._execution_options["logging_token"]
126 return lambda msg: "[%s] %s" % (token, msg)
127 else:
128 return None
130 def _log_info(self, message, *arg, **kw):
131 fmt = self._message_formatter
133 if fmt:
134 message = fmt(message)
136 if log.STACKLEVEL:
137 kw["stacklevel"] = 1 + log.STACKLEVEL_OFFSET
139 self.engine.logger.info(message, *arg, **kw)
141 def _log_debug(self, message, *arg, **kw):
142 fmt = self._message_formatter
144 if fmt:
145 message = fmt(message)
147 if log.STACKLEVEL:
148 kw["stacklevel"] = 1 + log.STACKLEVEL_OFFSET
150 self.engine.logger.debug(message, *arg, **kw)
152 @property
153 def _schema_translate_map(self):
154 return self._execution_options.get("schema_translate_map", None)
156 def schema_for_object(self, obj):
157 """Return the schema name for the given schema item taking into
158 account current schema translate map.
160 """
162 name = obj.schema
163 schema_translate_map = self._execution_options.get(
164 "schema_translate_map", None
165 )
167 if (
168 schema_translate_map
169 and name in schema_translate_map
170 and obj._use_schema_map
171 ):
172 return schema_translate_map[name]
173 else:
174 return name
176 def _branch(self):
177 """Return a new Connection which references this Connection's
178 engine and connection; but does not have close_with_result enabled,
179 and also whose close() method does nothing.
181 .. deprecated:: 1.4 the "branching" concept will be removed in
182 SQLAlchemy 2.0 as well as the "Connection.connect()" method which
183 is the only consumer for this.
185 The Core uses this very sparingly, only in the case of
186 custom SQL default functions that are to be INSERTed as the
187 primary key of a row where we need to get the value back, so we have
188 to invoke it distinctly - this is a very uncommon case.
190 Userland code accesses _branch() when the connect()
191 method is called. The branched connection
192 acts as much as possible like the parent, except that it stays
193 connected when a close() event occurs.
195 """
196 return self.engine._connection_cls(
197 self.engine,
198 self._dbapi_connection,
199 _branch_from=self.__branch_from if self.__branch_from else self,
200 _execution_options=self._execution_options,
201 _has_events=self._has_events,
202 _dispatch=self.dispatch,
203 )
205 def _generate_for_options(self):
206 """define connection method chaining behavior for execution_options"""
208 if self._is_future:
209 return self
210 else:
211 c = self.__class__.__new__(self.__class__)
212 c.__dict__ = self.__dict__.copy()
213 return c
215 def __enter__(self):
216 return self
218 def __exit__(self, type_, value, traceback):
219 self.close()
221 def execution_options(self, **opt):
222 r""" Set non-SQL options for the connection which take effect
223 during execution.
225 For a "future" style connection, this method returns this same
226 :class:`_future.Connection` object with the new options added.
228 For a legacy connection, this method returns a copy of this
229 :class:`_engine.Connection` which references the same underlying DBAPI
230 connection, but also defines the given execution options which will
231 take effect for a call to
232 :meth:`execute`. As the new :class:`_engine.Connection` references the
233 same underlying resource, it's usually a good idea to ensure that
234 the copies will be discarded immediately, which is implicit if used
235 as in::
237 result = connection.execution_options(stream_results=True).\
238 execute(stmt)
240 Note that any key/value can be passed to
241 :meth:`_engine.Connection.execution_options`,
242 and it will be stored in the
243 ``_execution_options`` dictionary of the :class:`_engine.Connection`.
244 It
245 is suitable for usage by end-user schemes to communicate with
246 event listeners, for example.
248 The keywords that are currently recognized by SQLAlchemy itself
249 include all those listed under :meth:`.Executable.execution_options`,
250 as well as others that are specific to :class:`_engine.Connection`.
252 :param autocommit: Available on: Connection, statement.
253 When True, a COMMIT will be invoked after execution
254 when executed in 'autocommit' mode, i.e. when an explicit
255 transaction is not begun on the connection. Note that this
256 is **library level, not DBAPI level autocommit**. The DBAPI
257 connection will remain in a real transaction unless the
258 "AUTOCOMMIT" isolation level is used.
260 .. deprecated:: 1.4 The "autocommit" execution option is deprecated
261 and will be removed in SQLAlchemy 2.0. See
262 :ref:`migration_20_autocommit` for discussion.
264 :param compiled_cache: Available on: Connection.
265 A dictionary where :class:`.Compiled` objects
266 will be cached when the :class:`_engine.Connection`
267 compiles a clause
268 expression into a :class:`.Compiled` object. This dictionary will
269 supersede the statement cache that may be configured on the
270 :class:`_engine.Engine` itself. If set to None, caching
271 is disabled, even if the engine has a configured cache size.
273 Note that the ORM makes use of its own "compiled" caches for
274 some operations, including flush operations. The caching
275 used by the ORM internally supersedes a cache dictionary
276 specified here.
278 :param logging_token: Available on: :class:`_engine.Connection`,
279 :class:`_engine.Engine`.
281 Adds the specified string token surrounded by brackets in log
282 messages logged by the connection, i.e. the logging that's enabled
283 either via the :paramref:`_sa.create_engine.echo` flag or via the
284 ``logging.getLogger("sqlalchemy.engine")`` logger. This allows a
285 per-connection or per-sub-engine token to be available which is
286 useful for debugging concurrent connection scenarios.
288 .. versionadded:: 1.4.0b2
290 .. seealso::
292 :ref:`dbengine_logging_tokens` - usage example
294 :paramref:`_sa.create_engine.logging_name` - adds a name to the
295 name used by the Python logger object itself.
297 :param isolation_level: Available on: :class:`_engine.Connection`.
299 Set the transaction isolation level for the lifespan of this
300 :class:`_engine.Connection` object.
301 Valid values include those string
302 values accepted by the :paramref:`_sa.create_engine.isolation_level`
303 parameter passed to :func:`_sa.create_engine`. These levels are
304 semi-database specific; see individual dialect documentation for
305 valid levels.
307 The isolation level option applies the isolation level by emitting
308 statements on the DBAPI connection, and **necessarily affects the
309 original Connection object overall**, not just the copy that is
310 returned by the call to :meth:`_engine.Connection.execution_options`
311 method. The isolation level will remain at the given setting until
312 the DBAPI connection itself is returned to the connection pool, i.e.
313 the :meth:`_engine.Connection.close` method on the original
314 :class:`_engine.Connection` is called,
315 where an event handler will emit
316 additional statements on the DBAPI connection in order to revert the
317 isolation level change.
319 .. warning:: The ``isolation_level`` execution option should
320 **not** be used when a transaction is already established, that
321 is, the :meth:`_engine.Connection.begin`
322 method or similar has been
323 called. A database cannot change the isolation level on a
324 transaction in progress, and different DBAPIs and/or
325 SQLAlchemy dialects may implicitly roll back or commit
326 the transaction, or not affect the connection at all.
328 .. note:: The ``isolation_level`` execution option is implicitly
329 reset if the :class:`_engine.Connection` is invalidated, e.g. via
330 the :meth:`_engine.Connection.invalidate` method, or if a
331 disconnection error occurs. The new connection produced after
332 the invalidation will not have the isolation level re-applied
333 to it automatically.
335 .. seealso::
337 :paramref:`_sa.create_engine.isolation_level`
338 - set per :class:`_engine.Engine` isolation level
340 :meth:`_engine.Connection.get_isolation_level`
341 - view current level
343 :ref:`SQLite Transaction Isolation <sqlite_isolation_level>`
345 :ref:`PostgreSQL Transaction Isolation <postgresql_isolation_level>`
347 :ref:`MySQL Transaction Isolation <mysql_isolation_level>`
349 :ref:`SQL Server Transaction Isolation <mssql_isolation_level>`
351 :ref:`session_transaction_isolation` - for the ORM
353 :param no_parameters: When ``True``, if the final parameter
354 list or dictionary is totally empty, will invoke the
355 statement on the cursor as ``cursor.execute(statement)``,
356 not passing the parameter collection at all.
357 Some DBAPIs such as psycopg2 and mysql-python consider
358 percent signs as significant only when parameters are
359 present; this option allows code to generate SQL
360 containing percent signs (and possibly other characters)
361 that is neutral regarding whether it's executed by the DBAPI
362 or piped into a script that's later invoked by
363 command line tools.
365 :param stream_results: Available on: Connection, statement.
366 Indicate to the dialect that results should be
367 "streamed" and not pre-buffered, if possible. For backends
368 such as PostgreSQL, MySQL and MariaDB, this indicates the use of
369 a "server side cursor" as opposed to a client side cursor.
370 Other backends such as that of Oracle may already use server
371 side cursors by default.
373 The usage of
374 :paramref:`_engine.Connection.execution_options.stream_results` is
375 usually combined with setting a fixed number of rows to to be fetched
376 in batches, to allow for efficient iteration of database rows while
377 at the same time not loading all result rows into memory at once;
378 this can be configured on a :class:`_engine.Result` object using the
379 :meth:`_engine.Result.yield_per` method, after execution has
380 returned a new :class:`_engine.Result`. If
381 :meth:`_engine.Result.yield_per` is not used,
382 the :paramref:`_engine.Connection.execution_options.stream_results`
383 mode of operation will instead use a dynamically sized buffer
384 which buffers sets of rows at a time, growing on each batch
385 based on a fixed growth size up until a limit which may
386 be configured using the
387 :paramref:`_engine.Connection.execution_options.max_row_buffer`
388 parameter.
390 When using the ORM to fetch ORM mapped objects from a result,
391 :meth:`_engine.Result.yield_per` should always be used with
392 :paramref:`_engine.Connection.execution_options.stream_results`,
393 so that the ORM does not fetch all rows into new ORM objects at once.
395 For typical use, the
396 :paramref:`_engine.Connection.execution_options.yield_per` execution
397 option should be preferred, which sets up both
398 :paramref:`_engine.Connection.execution_options.stream_results` and
399 :meth:`_engine.Result.yield_per` at once. This option is supported
400 both at a core level by :class:`_engine.Connection` as well as by the
401 ORM :class:`_engine.Session`; the latter is described at
402 :ref:`orm_queryguide_yield_per`.
404 .. seealso::
406 :ref:`engine_stream_results` - background on
407 :paramref:`_engine.Connection.execution_options.stream_results`
409 :paramref:`_engine.Connection.execution_options.max_row_buffer`
411 :paramref:`_engine.Connection.execution_options.yield_per`
413 :ref:`orm_queryguide_yield_per` - in the :ref:`queryguide_toplevel`
414 describing the ORM version of ``yield_per``
416 :param max_row_buffer: Available on: :class:`_engine.Connection`,
417 :class:`_sql.Executable`. Sets a maximum
418 buffer size to use when the
419 :paramref:`_engine.Connection.execution_options.stream_results`
420 execution option is used on a backend that supports server side
421 cursors. The default value if not specified is 1000.
423 .. seealso::
425 :paramref:`_engine.Connection.execution_options.stream_results`
427 :ref:`engine_stream_results`
430 :param yield_per: Available on: :class:`_engine.Connection`,
431 :class:`_sql.Executable`. Integer value applied which will
432 set the :paramref:`_engine.Connection.execution_options.stream_results`
433 execution option and invoke :meth:`_engine.Result.yield_per`
434 automatically at once. Allows equivalent functionality as
435 is present when using this parameter with the ORM.
437 .. versionadded:: 1.4.40
439 .. seealso::
441 :ref:`engine_stream_results` - background and examples
442 on using server side cursors with Core.
444 :ref:`orm_queryguide_yield_per` - in the :ref:`queryguide_toplevel`
445 describing the ORM version of ``yield_per``
447 :param schema_translate_map: Available on: :class:`_engine.Connection`,
448 :class:`_engine.Engine`, :class:`_sql.Executable`.
450 :param schema_translate_map: Available on: Connection, Engine.
451 A dictionary mapping schema names to schema names, that will be
452 applied to the :paramref:`_schema.Table.schema` element of each
453 :class:`_schema.Table`
454 encountered when SQL or DDL expression elements
455 are compiled into strings; the resulting schema name will be
456 converted based on presence in the map of the original name.
458 .. versionadded:: 1.1
460 .. seealso::
462 :ref:`schema_translating`
464 .. seealso::
466 :meth:`_engine.Engine.execution_options`
468 :meth:`.Executable.execution_options`
470 :meth:`_engine.Connection.get_execution_options`
473 """ # noqa
474 c = self._generate_for_options()
475 c._execution_options = c._execution_options.union(opt)
476 if self._has_events or self.engine._has_events:
477 self.dispatch.set_connection_execution_options(c, opt)
478 self.dialect.set_connection_execution_options(c, opt)
479 return c
481 def get_execution_options(self):
482 """Get the non-SQL options which will take effect during execution.
484 .. versionadded:: 1.3
486 .. seealso::
488 :meth:`_engine.Connection.execution_options`
489 """
490 return self._execution_options
492 @property
493 def closed(self):
494 """Return True if this connection is closed."""
496 # note this is independent for a "branched" connection vs.
497 # the base
499 return self._dbapi_connection is None and not self.__can_reconnect
501 @property
502 def invalidated(self):
503 """Return True if this connection was invalidated."""
505 # prior to 1.4, "invalid" was stored as a state independent of
506 # "closed", meaning an invalidated connection could be "closed",
507 # the _dbapi_connection would be None and closed=True, yet the
508 # "invalid" flag would stay True. This meant that there were
509 # three separate states (open/valid, closed/valid, closed/invalid)
510 # when there is really no reason for that; a connection that's
511 # "closed" does not need to be "invalid". So the state is now
512 # represented by the two facts alone.
514 if self.__branch_from:
515 return self.__branch_from.invalidated
517 return self._dbapi_connection is None and not self.closed
519 @property
520 def connection(self):
521 """The underlying DB-API connection managed by this Connection.
523 This is a SQLAlchemy connection-pool proxied connection
524 which then has the attribute
525 :attr:`_pool._ConnectionFairy.dbapi_connection` that refers to the
526 actual driver connection.
528 .. seealso::
531 :ref:`dbapi_connections`
533 """
535 if self._dbapi_connection is None:
536 try:
537 return self._revalidate_connection()
538 except (exc.PendingRollbackError, exc.ResourceClosedError):
539 raise
540 except BaseException as e:
541 self._handle_dbapi_exception(e, None, None, None, None)
542 else:
543 return self._dbapi_connection
545 def get_isolation_level(self):
546 """Return the current isolation level assigned to this
547 :class:`_engine.Connection`.
549 This will typically be the default isolation level as determined
550 by the dialect, unless if the
551 :paramref:`.Connection.execution_options.isolation_level`
552 feature has been used to alter the isolation level on a
553 per-:class:`_engine.Connection` basis.
555 This attribute will typically perform a live SQL operation in order
556 to procure the current isolation level, so the value returned is the
557 actual level on the underlying DBAPI connection regardless of how
558 this state was set. Compare to the
559 :attr:`_engine.Connection.default_isolation_level` accessor
560 which returns the dialect-level setting without performing a SQL
561 query.
563 .. versionadded:: 0.9.9
565 .. seealso::
567 :attr:`_engine.Connection.default_isolation_level`
568 - view default level
570 :paramref:`_sa.create_engine.isolation_level`
571 - set per :class:`_engine.Engine` isolation level
573 :paramref:`.Connection.execution_options.isolation_level`
574 - set per :class:`_engine.Connection` isolation level
576 """
577 try:
578 return self.dialect.get_isolation_level(self.connection)
579 except BaseException as e:
580 self._handle_dbapi_exception(e, None, None, None, None)
582 @property
583 def default_isolation_level(self):
584 """The default isolation level assigned to this
585 :class:`_engine.Connection`.
587 This is the isolation level setting that the
588 :class:`_engine.Connection`
589 has when first procured via the :meth:`_engine.Engine.connect` method.
590 This level stays in place until the
591 :paramref:`.Connection.execution_options.isolation_level` is used
592 to change the setting on a per-:class:`_engine.Connection` basis.
594 Unlike :meth:`_engine.Connection.get_isolation_level`,
595 this attribute is set
596 ahead of time from the first connection procured by the dialect,
597 so SQL query is not invoked when this accessor is called.
599 .. versionadded:: 0.9.9
601 .. seealso::
603 :meth:`_engine.Connection.get_isolation_level`
604 - view current level
606 :paramref:`_sa.create_engine.isolation_level`
607 - set per :class:`_engine.Engine` isolation level
609 :paramref:`.Connection.execution_options.isolation_level`
610 - set per :class:`_engine.Connection` isolation level
612 """
613 return self.dialect.default_isolation_level
615 def _invalid_transaction(self):
616 if self.invalidated:
617 raise exc.PendingRollbackError(
618 "Can't reconnect until invalid %stransaction is rolled "
619 "back."
620 % (
621 "savepoint "
622 if self._nested_transaction is not None
623 else ""
624 ),
625 code="8s2b",
626 )
627 else:
628 assert not self._is_future
629 raise exc.PendingRollbackError(
630 "This connection is on an inactive %stransaction. "
631 "Please rollback() fully before proceeding."
632 % (
633 "savepoint "
634 if self._nested_transaction is not None
635 else ""
636 ),
637 code="8s2a",
638 )
640 def _revalidate_connection(self):
641 if self.__branch_from:
642 return self.__branch_from._revalidate_connection()
643 if self.__can_reconnect and self.invalidated:
644 if self._transaction is not None:
645 self._invalid_transaction()
646 self._dbapi_connection = self.engine.raw_connection(
647 _connection=self
648 )
649 return self._dbapi_connection
650 raise exc.ResourceClosedError("This Connection is closed")
652 @property
653 def _still_open_and_dbapi_connection_is_valid(self):
654 return self._dbapi_connection is not None and getattr(
655 self._dbapi_connection, "is_valid", False
656 )
658 @property
659 def info(self):
660 """Info dictionary associated with the underlying DBAPI connection
661 referred to by this :class:`_engine.Connection`, allowing user-defined
662 data to be associated with the connection.
664 The data here will follow along with the DBAPI connection including
665 after it is returned to the connection pool and used again
666 in subsequent instances of :class:`_engine.Connection`.
668 """
670 return self.connection.info
672 @util.deprecated_20(":meth:`.Connection.connect`")
673 def connect(self, close_with_result=False):
674 """Returns a branched version of this :class:`_engine.Connection`.
676 The :meth:`_engine.Connection.close` method on the returned
677 :class:`_engine.Connection` can be called and this
678 :class:`_engine.Connection` will remain open.
680 This method provides usage symmetry with
681 :meth:`_engine.Engine.connect`, including for usage
682 with context managers.
684 """
686 return self._branch()
688 def invalidate(self, exception=None):
689 """Invalidate the underlying DBAPI connection associated with
690 this :class:`_engine.Connection`.
692 An attempt will be made to close the underlying DBAPI connection
693 immediately; however if this operation fails, the error is logged
694 but not raised. The connection is then discarded whether or not
695 close() succeeded.
697 Upon the next use (where "use" typically means using the
698 :meth:`_engine.Connection.execute` method or similar),
699 this :class:`_engine.Connection` will attempt to
700 procure a new DBAPI connection using the services of the
701 :class:`_pool.Pool` as a source of connectivity (e.g.
702 a "reconnection").
704 If a transaction was in progress (e.g. the
705 :meth:`_engine.Connection.begin` method has been called) when
706 :meth:`_engine.Connection.invalidate` method is called, at the DBAPI
707 level all state associated with this transaction is lost, as
708 the DBAPI connection is closed. The :class:`_engine.Connection`
709 will not allow a reconnection to proceed until the
710 :class:`.Transaction` object is ended, by calling the
711 :meth:`.Transaction.rollback` method; until that point, any attempt at
712 continuing to use the :class:`_engine.Connection` will raise an
713 :class:`~sqlalchemy.exc.InvalidRequestError`.
714 This is to prevent applications from accidentally
715 continuing an ongoing transactional operations despite the
716 fact that the transaction has been lost due to an
717 invalidation.
719 The :meth:`_engine.Connection.invalidate` method,
720 just like auto-invalidation,
721 will at the connection pool level invoke the
722 :meth:`_events.PoolEvents.invalidate` event.
724 :param exception: an optional ``Exception`` instance that's the
725 reason for the invalidation. is passed along to event handlers
726 and logging functions.
728 .. seealso::
730 :ref:`pool_connection_invalidation`
732 """
734 if self.__branch_from:
735 return self.__branch_from.invalidate(exception=exception)
737 if self.invalidated:
738 return
740 if self.closed:
741 raise exc.ResourceClosedError("This Connection is closed")
743 if self._still_open_and_dbapi_connection_is_valid:
744 self._dbapi_connection.invalidate(exception)
745 self._dbapi_connection = None
747 def detach(self):
748 """Detach the underlying DB-API connection from its connection pool.
750 E.g.::
752 with engine.connect() as conn:
753 conn.detach()
754 conn.execute(text("SET search_path TO schema1, schema2"))
756 # work with connection
758 # connection is fully closed (since we used "with:", can
759 # also call .close())
761 This :class:`_engine.Connection` instance will remain usable.
762 When closed
763 (or exited from a context manager context as above),
764 the DB-API connection will be literally closed and not
765 returned to its originating pool.
767 This method can be used to insulate the rest of an application
768 from a modified state on a connection (such as a transaction
769 isolation level or similar).
771 """
773 self._dbapi_connection.detach()
775 def _autobegin(self):
776 self.begin()
778 def begin(self):
779 """Begin a transaction and return a transaction handle.
781 The returned object is an instance of :class:`.Transaction`.
782 This object represents the "scope" of the transaction,
783 which completes when either the :meth:`.Transaction.rollback`
784 or :meth:`.Transaction.commit` method is called.
786 .. tip::
788 The :meth:`_engine.Connection.begin` method is invoked when using
789 the :meth:`_engine.Engine.begin` context manager method as well.
790 All documentation that refers to behaviors specific to the
791 :meth:`_engine.Connection.begin` method also apply to use of the
792 :meth:`_engine.Engine.begin` method.
794 Legacy use: nested calls to :meth:`.begin` on the same
795 :class:`_engine.Connection` will return new :class:`.Transaction`
796 objects that represent an emulated transaction within the scope of the
797 enclosing transaction, that is::
799 trans = conn.begin() # outermost transaction
800 trans2 = conn.begin() # "nested"
801 trans2.commit() # does nothing
802 trans.commit() # actually commits
804 Calls to :meth:`.Transaction.commit` only have an effect
805 when invoked via the outermost :class:`.Transaction` object, though the
806 :meth:`.Transaction.rollback` method of any of the
807 :class:`.Transaction` objects will roll back the
808 transaction.
810 .. tip::
812 The above "nesting" behavior is a legacy behavior specific to
813 :term:`1.x style` use and will be removed in SQLAlchemy 2.0. For
814 notes on :term:`2.0 style` use, see
815 :meth:`_future.Connection.begin`.
818 .. seealso::
820 :meth:`_engine.Connection.begin_nested` - use a SAVEPOINT
822 :meth:`_engine.Connection.begin_twophase` -
823 use a two phase /XID transaction
825 :meth:`_engine.Engine.begin` - context manager available from
826 :class:`_engine.Engine`
828 """
829 if self._is_future:
830 assert not self.__branch_from
831 elif self.__branch_from:
832 return self.__branch_from.begin()
834 if self.__in_begin:
835 # for dialects that emit SQL within the process of
836 # dialect.do_begin() or dialect.do_begin_twophase(), this
837 # flag prevents "autobegin" from being emitted within that
838 # process, while allowing self._transaction to remain at None
839 # until it's complete.
840 return
841 elif self._transaction is None:
842 self._transaction = RootTransaction(self)
843 return self._transaction
844 else:
845 if self._is_future:
846 raise exc.InvalidRequestError(
847 "This connection has already initialized a SQLAlchemy "
848 "Transaction() object via begin() or autobegin; can't "
849 "call begin() here unless rollback() or commit() "
850 "is called first."
851 )
852 else:
853 return MarkerTransaction(self)
855 def begin_nested(self):
856 """Begin a nested transaction (i.e. SAVEPOINT) and return a
857 transaction handle, assuming an outer transaction is already
858 established.
860 Nested transactions require SAVEPOINT support in the
861 underlying database. Any transaction in the hierarchy may
862 ``commit`` and ``rollback``, however the outermost transaction
863 still controls the overall ``commit`` or ``rollback`` of the
864 transaction of a whole.
866 The legacy form of :meth:`_engine.Connection.begin_nested` method has
867 alternate behaviors based on whether or not the
868 :meth:`_engine.Connection.begin` method was called previously. If
869 :meth:`_engine.Connection.begin` was not called, then this method will
870 behave the same as the :meth:`_engine.Connection.begin` method and
871 return a :class:`.RootTransaction` object that begins and commits a
872 real transaction - **no savepoint is invoked**. If
873 :meth:`_engine.Connection.begin` **has** been called, and a
874 :class:`.RootTransaction` is already established, then this method
875 returns an instance of :class:`.NestedTransaction` which will invoke
876 and manage the scope of a SAVEPOINT.
878 .. tip::
880 The above mentioned behavior of
881 :meth:`_engine.Connection.begin_nested` is a legacy behavior
882 specific to :term:`1.x style` use. In :term:`2.0 style` use, the
883 :meth:`_future.Connection.begin_nested` method instead autobegins
884 the outer transaction that can be committed using
885 "commit-as-you-go" style; see
886 :meth:`_future.Connection.begin_nested` for migration details.
888 .. versionchanged:: 1.4.13 The behavior of
889 :meth:`_engine.Connection.begin_nested`
890 as returning a :class:`.RootTransaction` if
891 :meth:`_engine.Connection.begin` were not called has been restored
892 as was the case in 1.3.x versions; in previous 1.4.x versions, an
893 outer transaction would be "autobegun" but would not be committed.
896 .. seealso::
898 :meth:`_engine.Connection.begin`
900 :ref:`session_begin_nested` - ORM support for SAVEPOINT
902 """
903 if self._is_future:
904 assert not self.__branch_from
905 elif self.__branch_from:
906 return self.__branch_from.begin_nested()
908 if self._transaction is None:
909 if not self._is_future:
910 util.warn_deprecated_20(
911 "Calling Connection.begin_nested() in 2.0 style use will "
912 "return a NestedTransaction (SAVEPOINT) in all cases, "
913 "that will not commit the outer transaction. For code "
914 "that is cross-compatible between 1.x and 2.0 style use, "
915 "ensure Connection.begin() is called before calling "
916 "Connection.begin_nested()."
917 )
918 return self.begin()
919 else:
920 self._autobegin()
922 return NestedTransaction(self)
924 def begin_twophase(self, xid=None):
925 """Begin a two-phase or XA transaction and return a transaction
926 handle.
928 The returned object is an instance of :class:`.TwoPhaseTransaction`,
929 which in addition to the methods provided by
930 :class:`.Transaction`, also provides a
931 :meth:`~.TwoPhaseTransaction.prepare` method.
933 :param xid: the two phase transaction id. If not supplied, a
934 random id will be generated.
936 .. seealso::
938 :meth:`_engine.Connection.begin`
940 :meth:`_engine.Connection.begin_twophase`
942 """
944 if self.__branch_from:
945 return self.__branch_from.begin_twophase(xid=xid)
947 if self._transaction is not None:
948 raise exc.InvalidRequestError(
949 "Cannot start a two phase transaction when a transaction "
950 "is already in progress."
951 )
952 if xid is None:
953 xid = self.engine.dialect.create_xid()
954 return TwoPhaseTransaction(self, xid)
956 def recover_twophase(self):
957 return self.engine.dialect.do_recover_twophase(self)
959 def rollback_prepared(self, xid, recover=False):
960 self.engine.dialect.do_rollback_twophase(self, xid, recover=recover)
962 def commit_prepared(self, xid, recover=False):
963 self.engine.dialect.do_commit_twophase(self, xid, recover=recover)
965 def in_transaction(self):
966 """Return True if a transaction is in progress."""
967 if self.__branch_from is not None:
968 return self.__branch_from.in_transaction()
970 return self._transaction is not None and self._transaction.is_active
972 def in_nested_transaction(self):
973 """Return True if a transaction is in progress."""
974 if self.__branch_from is not None:
975 return self.__branch_from.in_nested_transaction()
977 return (
978 self._nested_transaction is not None
979 and self._nested_transaction.is_active
980 )
982 def _is_autocommit_isolation(self):
983 opt_iso = self._execution_options.get("isolation_level", None)
984 return bool(
985 opt_iso == "AUTOCOMMIT"
986 or (
987 opt_iso is None
988 and getattr(self.engine.dialect, "isolation_level", None)
989 == "AUTOCOMMIT"
990 )
991 )
993 def get_transaction(self):
994 """Return the current root transaction in progress, if any.
996 .. versionadded:: 1.4
998 """
1000 if self.__branch_from is not None:
1001 return self.__branch_from.get_transaction()
1003 return self._transaction
1005 def get_nested_transaction(self):
1006 """Return the current nested transaction in progress, if any.
1008 .. versionadded:: 1.4
1010 """
1011 if self.__branch_from is not None:
1013 return self.__branch_from.get_nested_transaction()
1015 return self._nested_transaction
1017 def _begin_impl(self, transaction):
1018 assert not self.__branch_from
1020 if self._echo:
1021 if self._is_autocommit_isolation():
1022 self._log_info(
1023 "BEGIN (implicit; DBAPI should not BEGIN due to "
1024 "autocommit mode)"
1025 )
1026 else:
1027 self._log_info("BEGIN (implicit)")
1029 self.__in_begin = True
1031 if self._has_events or self.engine._has_events:
1032 self.dispatch.begin(self)
1034 try:
1035 self.engine.dialect.do_begin(self.connection)
1036 except BaseException as e:
1037 self._handle_dbapi_exception(e, None, None, None, None)
1038 finally:
1039 self.__in_begin = False
1041 def _rollback_impl(self):
1042 assert not self.__branch_from
1044 if self._has_events or self.engine._has_events:
1045 self.dispatch.rollback(self)
1047 if self._still_open_and_dbapi_connection_is_valid:
1048 if self._echo:
1049 if self._is_autocommit_isolation():
1050 self._log_info(
1051 "ROLLBACK using DBAPI connection.rollback(), "
1052 "DBAPI should ignore due to autocommit mode"
1053 )
1054 else:
1055 self._log_info("ROLLBACK")
1056 try:
1057 self.engine.dialect.do_rollback(self.connection)
1058 except BaseException as e:
1059 self._handle_dbapi_exception(e, None, None, None, None)
1061 def _commit_impl(self, autocommit=False):
1062 assert not self.__branch_from
1064 # AUTOCOMMIT isolation-level is a dialect-specific concept, however
1065 # if a connection has this set as the isolation level, we can skip
1066 # the "autocommit" warning as the operation will do "autocommit"
1067 # in any case
1068 if autocommit and not self._is_autocommit_isolation():
1069 util.warn_deprecated_20(
1070 "The current statement is being autocommitted using "
1071 "implicit autocommit, which will be removed in "
1072 "SQLAlchemy 2.0. "
1073 "Use the .begin() method of Engine or Connection in order to "
1074 "use an explicit transaction for DML and DDL statements."
1075 )
1077 if self._has_events or self.engine._has_events:
1078 self.dispatch.commit(self)
1080 if self._echo:
1081 if self._is_autocommit_isolation():
1082 self._log_info(
1083 "COMMIT using DBAPI connection.commit(), "
1084 "DBAPI should ignore due to autocommit mode"
1085 )
1086 else:
1087 self._log_info("COMMIT")
1088 try:
1089 self.engine.dialect.do_commit(self.connection)
1090 except BaseException as e:
1091 self._handle_dbapi_exception(e, None, None, None, None)
1093 def _savepoint_impl(self, name=None):
1094 assert not self.__branch_from
1096 if self._has_events or self.engine._has_events:
1097 self.dispatch.savepoint(self, name)
1099 if name is None:
1100 self.__savepoint_seq += 1
1101 name = "sa_savepoint_%s" % self.__savepoint_seq
1102 if self._still_open_and_dbapi_connection_is_valid:
1103 self.engine.dialect.do_savepoint(self, name)
1104 return name
1106 def _rollback_to_savepoint_impl(self, name):
1107 assert not self.__branch_from
1109 if self._has_events or self.engine._has_events:
1110 self.dispatch.rollback_savepoint(self, name, None)
1112 if self._still_open_and_dbapi_connection_is_valid:
1113 self.engine.dialect.do_rollback_to_savepoint(self, name)
1115 def _release_savepoint_impl(self, name):
1116 assert not self.__branch_from
1118 if self._has_events or self.engine._has_events:
1119 self.dispatch.release_savepoint(self, name, None)
1121 if self._still_open_and_dbapi_connection_is_valid:
1122 self.engine.dialect.do_release_savepoint(self, name)
1124 def _begin_twophase_impl(self, transaction):
1125 assert not self.__branch_from
1127 if self._echo:
1128 self._log_info("BEGIN TWOPHASE (implicit)")
1129 if self._has_events or self.engine._has_events:
1130 self.dispatch.begin_twophase(self, transaction.xid)
1132 if self._still_open_and_dbapi_connection_is_valid:
1133 self.__in_begin = True
1134 try:
1135 self.engine.dialect.do_begin_twophase(self, transaction.xid)
1136 except BaseException as e:
1137 self._handle_dbapi_exception(e, None, None, None, None)
1138 finally:
1139 self.__in_begin = False
1141 def _prepare_twophase_impl(self, xid):
1142 assert not self.__branch_from
1144 if self._has_events or self.engine._has_events:
1145 self.dispatch.prepare_twophase(self, xid)
1147 if self._still_open_and_dbapi_connection_is_valid:
1148 assert isinstance(self._transaction, TwoPhaseTransaction)
1149 try:
1150 self.engine.dialect.do_prepare_twophase(self, xid)
1151 except BaseException as e:
1152 self._handle_dbapi_exception(e, None, None, None, None)
1154 def _rollback_twophase_impl(self, xid, is_prepared):
1155 assert not self.__branch_from
1157 if self._has_events or self.engine._has_events:
1158 self.dispatch.rollback_twophase(self, xid, is_prepared)
1160 if self._still_open_and_dbapi_connection_is_valid:
1161 assert isinstance(self._transaction, TwoPhaseTransaction)
1162 try:
1163 self.engine.dialect.do_rollback_twophase(
1164 self, xid, is_prepared
1165 )
1166 except BaseException as e:
1167 self._handle_dbapi_exception(e, None, None, None, None)
1169 def _commit_twophase_impl(self, xid, is_prepared):
1170 assert not self.__branch_from
1172 if self._has_events or self.engine._has_events:
1173 self.dispatch.commit_twophase(self, xid, is_prepared)
1175 if self._still_open_and_dbapi_connection_is_valid:
1176 assert isinstance(self._transaction, TwoPhaseTransaction)
1177 try:
1178 self.engine.dialect.do_commit_twophase(self, xid, is_prepared)
1179 except BaseException as e:
1180 self._handle_dbapi_exception(e, None, None, None, None)
1182 def _autorollback(self):
1183 if self.__branch_from:
1184 self.__branch_from._autorollback()
1186 if not self.in_transaction():
1187 self._rollback_impl()
1189 def _warn_for_legacy_exec_format(self):
1190 util.warn_deprecated_20(
1191 "The connection.execute() method in "
1192 "SQLAlchemy 2.0 will accept parameters as a single "
1193 "dictionary or a "
1194 "single sequence of dictionaries only. "
1195 "Parameters passed as keyword arguments, tuples or positionally "
1196 "oriented dictionaries and/or tuples "
1197 "will no longer be accepted."
1198 )
1200 def close(self):
1201 """Close this :class:`_engine.Connection`.
1203 This results in a release of the underlying database
1204 resources, that is, the DBAPI connection referenced
1205 internally. The DBAPI connection is typically restored
1206 back to the connection-holding :class:`_pool.Pool` referenced
1207 by the :class:`_engine.Engine` that produced this
1208 :class:`_engine.Connection`. Any transactional state present on
1209 the DBAPI connection is also unconditionally released via
1210 the DBAPI connection's ``rollback()`` method, regardless
1211 of any :class:`.Transaction` object that may be
1212 outstanding with regards to this :class:`_engine.Connection`.
1214 After :meth:`_engine.Connection.close` is called, the
1215 :class:`_engine.Connection` is permanently in a closed state,
1216 and will allow no further operations.
1218 """
1220 if self.__branch_from:
1221 assert not self._is_future
1222 util.warn_deprecated_20(
1223 "The .close() method on a so-called 'branched' connection is "
1224 "deprecated as of 1.4, as are 'branched' connections overall, "
1225 "and will be removed in a future release. If this is a "
1226 "default-handling function, don't close the connection."
1227 )
1228 self._dbapi_connection = None
1229 self.__can_reconnect = False
1230 return
1232 if self._transaction:
1233 self._transaction.close()
1234 skip_reset = True
1235 else:
1236 skip_reset = False
1238 if self._dbapi_connection is not None:
1239 conn = self._dbapi_connection
1241 # as we just closed the transaction, close the connection
1242 # pool connection without doing an additional reset
1243 if skip_reset:
1244 conn._close_special(transaction_reset=True)
1245 else:
1246 conn.close()
1248 # There is a slight chance that conn.close() may have
1249 # triggered an invalidation here in which case
1250 # _dbapi_connection would already be None, however usually
1251 # it will be non-None here and in a "closed" state.
1252 self._dbapi_connection = None
1253 self.__can_reconnect = False
1255 def scalar(self, object_, *multiparams, **params):
1256 """Executes and returns the first column of the first row.
1258 The underlying result/cursor is closed after execution.
1260 """
1262 return self.execute(object_, *multiparams, **params).scalar()
1264 def scalars(self, object_, *multiparams, **params):
1265 """Executes and returns a scalar result set, which yields scalar values
1266 from the first column of each row.
1268 This method is equivalent to calling :meth:`_engine.Connection.execute`
1269 to receive a :class:`_result.Result` object, then invoking the
1270 :meth:`_result.Result.scalars` method to produce a
1271 :class:`_result.ScalarResult` instance.
1273 :return: a :class:`_result.ScalarResult`
1275 .. versionadded:: 1.4.24
1277 """
1279 return self.execute(object_, *multiparams, **params).scalars()
1281 def execute(self, statement, *multiparams, **params):
1282 r"""Executes a SQL statement construct and returns a
1283 :class:`_engine.CursorResult`.
1285 :param statement: The statement to be executed. May be
1286 one of:
1288 * a plain string (deprecated)
1289 * any :class:`_expression.ClauseElement` construct that is also
1290 a subclass of :class:`.Executable`, such as a
1291 :func:`_expression.select` construct
1292 * a :class:`.FunctionElement`, such as that generated
1293 by :data:`.func`, will be automatically wrapped in
1294 a SELECT statement, which is then executed.
1295 * a :class:`.DDLElement` object
1296 * a :class:`.DefaultGenerator` object
1297 * a :class:`.Compiled` object
1299 .. deprecated:: 2.0 passing a string to
1300 :meth:`_engine.Connection.execute` is
1301 deprecated and will be removed in version 2.0. Use the
1302 :func:`_expression.text` construct with
1303 :meth:`_engine.Connection.execute`, or the
1304 :meth:`_engine.Connection.exec_driver_sql`
1305 method to invoke a driver-level
1306 SQL string.
1308 :param \*multiparams/\**params: represent bound parameter
1309 values to be used in the execution. Typically,
1310 the format is either a collection of one or more
1311 dictionaries passed to \*multiparams::
1313 conn.execute(
1314 table.insert(),
1315 {"id":1, "value":"v1"},
1316 {"id":2, "value":"v2"}
1317 )
1319 ...or individual key/values interpreted by \**params::
1321 conn.execute(
1322 table.insert(), id=1, value="v1"
1323 )
1325 In the case that a plain SQL string is passed, and the underlying
1326 DBAPI accepts positional bind parameters, a collection of tuples
1327 or individual values in \*multiparams may be passed::
1329 conn.execute(
1330 "INSERT INTO table (id, value) VALUES (?, ?)",
1331 (1, "v1"), (2, "v2")
1332 )
1334 conn.execute(
1335 "INSERT INTO table (id, value) VALUES (?, ?)",
1336 1, "v1"
1337 )
1339 Note above, the usage of a question mark "?" or other
1340 symbol is contingent upon the "paramstyle" accepted by the DBAPI
1341 in use, which may be any of "qmark", "named", "pyformat", "format",
1342 "numeric". See `pep-249
1343 <https://www.python.org/dev/peps/pep-0249/>`_ for details on
1344 paramstyle.
1346 To execute a textual SQL statement which uses bound parameters in a
1347 DBAPI-agnostic way, use the :func:`_expression.text` construct.
1349 .. deprecated:: 2.0 use of tuple or scalar positional parameters
1350 is deprecated. All params should be dicts or sequences of dicts.
1351 Use :meth:`.exec_driver_sql` to execute a plain string with
1352 tuple or scalar positional parameters.
1354 """
1356 if isinstance(statement, util.string_types):
1357 util.warn_deprecated_20(
1358 "Passing a string to Connection.execute() is "
1359 "deprecated and will be removed in version 2.0. Use the "
1360 "text() construct, "
1361 "or the Connection.exec_driver_sql() method to invoke a "
1362 "driver-level SQL string."
1363 )
1365 return self._exec_driver_sql(
1366 statement,
1367 multiparams,
1368 params,
1369 _EMPTY_EXECUTION_OPTS,
1370 future=False,
1371 )
1373 try:
1374 meth = statement._execute_on_connection
1375 except AttributeError as err:
1376 util.raise_(
1377 exc.ObjectNotExecutableError(statement), replace_context=err
1378 )
1379 else:
1380 return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
1382 def _execute_function(self, func, multiparams, params, execution_options):
1383 """Execute a sql.FunctionElement object."""
1385 return self._execute_clauseelement(
1386 func.select(), multiparams, params, execution_options
1387 )
1389 def _execute_default(
1390 self,
1391 default,
1392 multiparams,
1393 params,
1394 # migrate is calling this directly :(
1395 execution_options=_EMPTY_EXECUTION_OPTS,
1396 ):
1397 """Execute a schema.ColumnDefault object."""
1399 execution_options = self._execution_options.merge_with(
1400 execution_options
1401 )
1403 distilled_parameters = _distill_params(self, multiparams, params)
1405 if self._has_events or self.engine._has_events:
1406 (
1407 default,
1408 distilled_params,
1409 event_multiparams,
1410 event_params,
1411 ) = self._invoke_before_exec_event(
1412 default, distilled_parameters, execution_options
1413 )
1415 try:
1416 conn = self._dbapi_connection
1417 if conn is None:
1418 conn = self._revalidate_connection()
1420 dialect = self.dialect
1421 ctx = dialect.execution_ctx_cls._init_default(
1422 dialect, self, conn, execution_options
1423 )
1424 except (exc.PendingRollbackError, exc.ResourceClosedError):
1425 raise
1426 except BaseException as e:
1427 self._handle_dbapi_exception(e, None, None, None, None)
1429 ret = ctx._exec_default(None, default, None)
1430 if self.should_close_with_result:
1431 self.close()
1433 if self._has_events or self.engine._has_events:
1434 self.dispatch.after_execute(
1435 self,
1436 default,
1437 event_multiparams,
1438 event_params,
1439 execution_options,
1440 ret,
1441 )
1443 return ret
1445 def _execute_ddl(self, ddl, multiparams, params, execution_options):
1446 """Execute a schema.DDL object."""
1448 execution_options = ddl._execution_options.merge_with(
1449 self._execution_options, execution_options
1450 )
1452 distilled_parameters = _distill_params(self, multiparams, params)
1454 if self._has_events or self.engine._has_events:
1455 (
1456 ddl,
1457 distilled_params,
1458 event_multiparams,
1459 event_params,
1460 ) = self._invoke_before_exec_event(
1461 ddl, distilled_parameters, execution_options
1462 )
1464 exec_opts = self._execution_options.merge_with(execution_options)
1465 schema_translate_map = exec_opts.get("schema_translate_map", None)
1467 dialect = self.dialect
1469 compiled = ddl.compile(
1470 dialect=dialect, schema_translate_map=schema_translate_map
1471 )
1472 ret = self._execute_context(
1473 dialect,
1474 dialect.execution_ctx_cls._init_ddl,
1475 compiled,
1476 None,
1477 execution_options,
1478 compiled,
1479 )
1480 if self._has_events or self.engine._has_events:
1481 self.dispatch.after_execute(
1482 self,
1483 ddl,
1484 event_multiparams,
1485 event_params,
1486 execution_options,
1487 ret,
1488 )
1489 return ret
1491 def _invoke_before_exec_event(
1492 self, elem, distilled_params, execution_options
1493 ):
1495 if len(distilled_params) == 1:
1496 event_multiparams, event_params = [], distilled_params[0]
1497 else:
1498 event_multiparams, event_params = distilled_params, {}
1500 for fn in self.dispatch.before_execute:
1501 elem, event_multiparams, event_params = fn(
1502 self,
1503 elem,
1504 event_multiparams,
1505 event_params,
1506 execution_options,
1507 )
1509 if event_multiparams:
1510 distilled_params = list(event_multiparams)
1511 if event_params:
1512 raise exc.InvalidRequestError(
1513 "Event handler can't return non-empty multiparams "
1514 "and params at the same time"
1515 )
1516 elif event_params:
1517 distilled_params = [event_params]
1518 else:
1519 distilled_params = []
1521 return elem, distilled_params, event_multiparams, event_params
1523 def _execute_clauseelement(
1524 self, elem, multiparams, params, execution_options
1525 ):
1526 """Execute a sql.ClauseElement object."""
1528 execution_options = elem._execution_options.merge_with(
1529 self._execution_options, execution_options
1530 )
1532 distilled_params = _distill_params(self, multiparams, params)
1534 has_events = self._has_events or self.engine._has_events
1535 if has_events:
1536 (
1537 elem,
1538 distilled_params,
1539 event_multiparams,
1540 event_params,
1541 ) = self._invoke_before_exec_event(
1542 elem, distilled_params, execution_options
1543 )
1545 if distilled_params:
1546 # ensure we don't retain a link to the view object for keys()
1547 # which links to the values, which we don't want to cache
1548 keys = sorted(distilled_params[0])
1549 for_executemany = len(distilled_params) > 1
1550 else:
1551 keys = []
1552 for_executemany = False
1554 dialect = self.dialect
1556 schema_translate_map = execution_options.get(
1557 "schema_translate_map", None
1558 )
1560 compiled_cache = execution_options.get(
1561 "compiled_cache", self.engine._compiled_cache
1562 )
1564 compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
1565 dialect=dialect,
1566 compiled_cache=compiled_cache,
1567 column_keys=keys,
1568 for_executemany=for_executemany,
1569 schema_translate_map=schema_translate_map,
1570 linting=self.dialect.compiler_linting | compiler.WARN_LINTING,
1571 )
1572 ret = self._execute_context(
1573 dialect,
1574 dialect.execution_ctx_cls._init_compiled,
1575 compiled_sql,
1576 distilled_params,
1577 execution_options,
1578 compiled_sql,
1579 distilled_params,
1580 elem,
1581 extracted_params,
1582 cache_hit=cache_hit,
1583 )
1584 if has_events:
1585 self.dispatch.after_execute(
1586 self,
1587 elem,
1588 event_multiparams,
1589 event_params,
1590 execution_options,
1591 ret,
1592 )
1593 return ret
1595 def _execute_compiled(
1596 self,
1597 compiled,
1598 multiparams,
1599 params,
1600 execution_options=_EMPTY_EXECUTION_OPTS,
1601 ):
1602 """Execute a sql.Compiled object.
1604 TODO: why do we have this? likely deprecate or remove
1606 """
1608 execution_options = compiled.execution_options.merge_with(
1609 self._execution_options, execution_options
1610 )
1611 distilled_parameters = _distill_params(self, multiparams, params)
1613 if self._has_events or self.engine._has_events:
1614 (
1615 compiled,
1616 distilled_params,
1617 event_multiparams,
1618 event_params,
1619 ) = self._invoke_before_exec_event(
1620 compiled, distilled_parameters, execution_options
1621 )
1623 dialect = self.dialect
1625 ret = self._execute_context(
1626 dialect,
1627 dialect.execution_ctx_cls._init_compiled,
1628 compiled,
1629 distilled_parameters,
1630 execution_options,
1631 compiled,
1632 distilled_parameters,
1633 None,
1634 None,
1635 )
1636 if self._has_events or self.engine._has_events:
1637 self.dispatch.after_execute(
1638 self,
1639 compiled,
1640 event_multiparams,
1641 event_params,
1642 execution_options,
1643 ret,
1644 )
1645 return ret
1647 def _exec_driver_sql(
1648 self, statement, multiparams, params, execution_options, future
1649 ):
1651 execution_options = self._execution_options.merge_with(
1652 execution_options
1653 )
1655 distilled_parameters = _distill_params(self, multiparams, params)
1657 if not future:
1658 if self._has_events or self.engine._has_events:
1659 (
1660 statement,
1661 distilled_params,
1662 event_multiparams,
1663 event_params,
1664 ) = self._invoke_before_exec_event(
1665 statement, distilled_parameters, execution_options
1666 )
1668 dialect = self.dialect
1669 ret = self._execute_context(
1670 dialect,
1671 dialect.execution_ctx_cls._init_statement,
1672 statement,
1673 distilled_parameters,
1674 execution_options,
1675 statement,
1676 distilled_parameters,
1677 )
1679 if not future:
1680 if self._has_events or self.engine._has_events:
1681 self.dispatch.after_execute(
1682 self,
1683 statement,
1684 event_multiparams,
1685 event_params,
1686 execution_options,
1687 ret,
1688 )
1689 return ret
1691 def _execute_20(
1692 self,
1693 statement,
1694 parameters=None,
1695 execution_options=_EMPTY_EXECUTION_OPTS,
1696 ):
1697 args_10style, kwargs_10style = _distill_params_20(parameters)
1698 try:
1699 meth = statement._execute_on_connection
1700 except AttributeError as err:
1701 util.raise_(
1702 exc.ObjectNotExecutableError(statement), replace_context=err
1703 )
1704 else:
1705 return meth(self, args_10style, kwargs_10style, execution_options)
1707 def exec_driver_sql(
1708 self, statement, parameters=None, execution_options=None
1709 ):
1710 r"""Executes a SQL statement construct and returns a
1711 :class:`_engine.CursorResult`.
1713 :param statement: The statement str to be executed. Bound parameters
1714 must use the underlying DBAPI's paramstyle, such as "qmark",
1715 "pyformat", "format", etc.
1717 :param parameters: represent bound parameter values to be used in the
1718 execution. The format is one of: a dictionary of named parameters,
1719 a tuple of positional parameters, or a list containing either
1720 dictionaries or tuples for multiple-execute support.
1722 E.g. multiple dictionaries::
1725 conn.exec_driver_sql(
1726 "INSERT INTO table (id, value) VALUES (%(id)s, %(value)s)",
1727 [{"id":1, "value":"v1"}, {"id":2, "value":"v2"}]
1728 )
1730 Single dictionary::
1732 conn.exec_driver_sql(
1733 "INSERT INTO table (id, value) VALUES (%(id)s, %(value)s)",
1734 dict(id=1, value="v1")
1735 )
1737 Single tuple::
1739 conn.exec_driver_sql(
1740 "INSERT INTO table (id, value) VALUES (?, ?)",
1741 (1, 'v1')
1742 )
1744 .. note:: The :meth:`_engine.Connection.exec_driver_sql` method does
1745 not participate in the
1746 :meth:`_events.ConnectionEvents.before_execute` and
1747 :meth:`_events.ConnectionEvents.after_execute` events. To
1748 intercept calls to :meth:`_engine.Connection.exec_driver_sql`, use
1749 :meth:`_events.ConnectionEvents.before_cursor_execute` and
1750 :meth:`_events.ConnectionEvents.after_cursor_execute`.
1752 .. seealso::
1754 :pep:`249`
1756 """
1758 args_10style, kwargs_10style = _distill_params_20(parameters)
1760 return self._exec_driver_sql(
1761 statement,
1762 args_10style,
1763 kwargs_10style,
1764 execution_options,
1765 future=True,
1766 )
1768 def _execute_context(
1769 self,
1770 dialect,
1771 constructor,
1772 statement,
1773 parameters,
1774 execution_options,
1775 *args,
1776 **kw
1777 ):
1778 """Create an :class:`.ExecutionContext` and execute, returning
1779 a :class:`_engine.CursorResult`."""
1781 branched = self
1782 if self.__branch_from:
1783 # if this is a "branched" connection, do everything in terms
1784 # of the "root" connection, *except* for .close(), which is
1785 # the only feature that branching provides
1786 self = self.__branch_from
1788 if execution_options:
1789 yp = execution_options.get("yield_per", None)
1790 if yp:
1791 execution_options = execution_options.union(
1792 {"stream_results": True, "max_row_buffer": yp}
1793 )
1795 try:
1796 conn = self._dbapi_connection
1797 if conn is None:
1798 conn = self._revalidate_connection()
1800 context = constructor(
1801 dialect, self, conn, execution_options, *args, **kw
1802 )
1803 except (exc.PendingRollbackError, exc.ResourceClosedError):
1804 raise
1805 except BaseException as e:
1806 self._handle_dbapi_exception(
1807 e, util.text_type(statement), parameters, None, None
1808 )
1810 if (
1811 self._transaction
1812 and not self._transaction.is_active
1813 or (
1814 self._nested_transaction
1815 and not self._nested_transaction.is_active
1816 )
1817 ):
1818 self._invalid_transaction()
1820 elif self._trans_context_manager:
1821 TransactionalContext._trans_ctx_check(self)
1823 if self._is_future and self._transaction is None:
1824 self._autobegin()
1826 context.pre_exec()
1828 if dialect.use_setinputsizes:
1829 context._set_input_sizes()
1831 cursor, statement, parameters = (
1832 context.cursor,
1833 context.statement,
1834 context.parameters,
1835 )
1837 if not context.executemany:
1838 parameters = parameters[0]
1840 if self._has_events or self.engine._has_events:
1841 for fn in self.dispatch.before_cursor_execute:
1842 statement, parameters = fn(
1843 self,
1844 cursor,
1845 statement,
1846 parameters,
1847 context,
1848 context.executemany,
1849 )
1851 if self._echo:
1853 self._log_info(statement)
1855 stats = context._get_cache_stats()
1857 if not self.engine.hide_parameters:
1858 self._log_info(
1859 "[%s] %r",
1860 stats,
1861 sql_util._repr_params(
1862 parameters, batches=10, ismulti=context.executemany
1863 ),
1864 )
1865 else:
1866 self._log_info(
1867 "[%s] [SQL parameters hidden due to hide_parameters=True]"
1868 % (stats,)
1869 )
1871 evt_handled = False
1872 try:
1873 if context.executemany:
1874 if self.dialect._has_events:
1875 for fn in self.dialect.dispatch.do_executemany:
1876 if fn(cursor, statement, parameters, context):
1877 evt_handled = True
1878 break
1879 if not evt_handled:
1880 self.dialect.do_executemany(
1881 cursor, statement, parameters, context
1882 )
1883 elif not parameters and context.no_parameters:
1884 if self.dialect._has_events:
1885 for fn in self.dialect.dispatch.do_execute_no_params:
1886 if fn(cursor, statement, context):
1887 evt_handled = True
1888 break
1889 if not evt_handled:
1890 self.dialect.do_execute_no_params(
1891 cursor, statement, context
1892 )
1893 else:
1894 if self.dialect._has_events:
1895 for fn in self.dialect.dispatch.do_execute:
1896 if fn(cursor, statement, parameters, context):
1897 evt_handled = True
1898 break
1899 if not evt_handled:
1900 self.dialect.do_execute(
1901 cursor, statement, parameters, context
1902 )
1904 if self._has_events or self.engine._has_events:
1905 self.dispatch.after_cursor_execute(
1906 self,
1907 cursor,
1908 statement,
1909 parameters,
1910 context,
1911 context.executemany,
1912 )
1914 context.post_exec()
1916 result = context._setup_result_proxy()
1918 if not self._is_future:
1919 should_close_with_result = branched.should_close_with_result
1921 if not result._soft_closed and should_close_with_result:
1922 result._autoclose_connection = True
1924 if (
1925 # usually we're in a transaction so avoid relatively
1926 # expensive / legacy should_autocommit call
1927 self._transaction is None
1928 and context.should_autocommit
1929 ):
1930 self._commit_impl(autocommit=True)
1932 # for "connectionless" execution, we have to close this
1933 # Connection after the statement is complete.
1934 # legacy stuff.
1935 if should_close_with_result and context._soft_closed:
1936 assert not self._is_future
1938 # CursorResult already exhausted rows / has no rows.
1939 # close us now
1940 branched.close()
1942 except BaseException as e:
1943 self._handle_dbapi_exception(
1944 e, statement, parameters, cursor, context
1945 )
1947 return result
1949 def _cursor_execute(self, cursor, statement, parameters, context=None):
1950 """Execute a statement + params on the given cursor.
1952 Adds appropriate logging and exception handling.
1954 This method is used by DefaultDialect for special-case
1955 executions, such as for sequences and column defaults.
1956 The path of statement execution in the majority of cases
1957 terminates at _execute_context().
1959 """
1960 if self._has_events or self.engine._has_events:
1961 for fn in self.dispatch.before_cursor_execute:
1962 statement, parameters = fn(
1963 self, cursor, statement, parameters, context, False
1964 )
1966 if self._echo:
1967 self._log_info(statement)
1968 self._log_info("[raw sql] %r", parameters)
1969 try:
1970 for fn in (
1971 ()
1972 if not self.dialect._has_events
1973 else self.dialect.dispatch.do_execute
1974 ):
1975 if fn(cursor, statement, parameters, context):
1976 break
1977 else:
1978 self.dialect.do_execute(cursor, statement, parameters, context)
1979 except BaseException as e:
1980 self._handle_dbapi_exception(
1981 e, statement, parameters, cursor, context
1982 )
1984 if self._has_events or self.engine._has_events:
1985 self.dispatch.after_cursor_execute(
1986 self, cursor, statement, parameters, context, False
1987 )
1989 def _safe_close_cursor(self, cursor):
1990 """Close the given cursor, catching exceptions
1991 and turning into log warnings.
1993 """
1994 try:
1995 cursor.close()
1996 except Exception:
1997 # log the error through the connection pool's logger.
1998 self.engine.pool.logger.error(
1999 "Error closing cursor", exc_info=True
2000 )
2002 _reentrant_error = False
2003 _is_disconnect = False
2005 def _handle_dbapi_exception(
2006 self, e, statement, parameters, cursor, context
2007 ):
2008 exc_info = sys.exc_info()
2010 is_exit_exception = util.is_exit_exception(e)
2012 if not self._is_disconnect:
2013 self._is_disconnect = (
2014 isinstance(e, self.dialect.dbapi.Error)
2015 and not self.closed
2016 and self.dialect.is_disconnect(
2017 e,
2018 self._dbapi_connection if not self.invalidated else None,
2019 cursor,
2020 )
2021 ) or (is_exit_exception and not self.closed)
2023 invalidate_pool_on_disconnect = not is_exit_exception
2025 if self._reentrant_error:
2026 util.raise_(
2027 exc.DBAPIError.instance(
2028 statement,
2029 parameters,
2030 e,
2031 self.dialect.dbapi.Error,
2032 hide_parameters=self.engine.hide_parameters,
2033 dialect=self.dialect,
2034 ismulti=context.executemany
2035 if context is not None
2036 else None,
2037 ),
2038 with_traceback=exc_info[2],
2039 from_=e,
2040 )
2041 self._reentrant_error = True
2042 try:
2043 # non-DBAPI error - if we already got a context,
2044 # or there's no string statement, don't wrap it
2045 should_wrap = isinstance(e, self.dialect.dbapi.Error) or (
2046 statement is not None
2047 and context is None
2048 and not is_exit_exception
2049 )
2051 if should_wrap:
2052 sqlalchemy_exception = exc.DBAPIError.instance(
2053 statement,
2054 parameters,
2055 e,
2056 self.dialect.dbapi.Error,
2057 hide_parameters=self.engine.hide_parameters,
2058 connection_invalidated=self._is_disconnect,
2059 dialect=self.dialect,
2060 ismulti=context.executemany
2061 if context is not None
2062 else None,
2063 )
2064 else:
2065 sqlalchemy_exception = None
2067 newraise = None
2069 if (
2070 self._has_events or self.engine._has_events
2071 ) and not self._execution_options.get(
2072 "skip_user_error_events", False
2073 ):
2074 ctx = ExceptionContextImpl(
2075 e,
2076 sqlalchemy_exception,
2077 self.engine,
2078 self,
2079 cursor,
2080 statement,
2081 parameters,
2082 context,
2083 self._is_disconnect,
2084 invalidate_pool_on_disconnect,
2085 )
2087 for fn in self.dispatch.handle_error:
2088 try:
2089 # handler returns an exception;
2090 # call next handler in a chain
2091 per_fn = fn(ctx)
2092 if per_fn is not None:
2093 ctx.chained_exception = newraise = per_fn
2094 except Exception as _raised:
2095 # handler raises an exception - stop processing
2096 newraise = _raised
2097 break
2099 if self._is_disconnect != ctx.is_disconnect:
2100 self._is_disconnect = ctx.is_disconnect
2101 if sqlalchemy_exception:
2102 sqlalchemy_exception.connection_invalidated = (
2103 ctx.is_disconnect
2104 )
2106 # set up potentially user-defined value for
2107 # invalidate pool.
2108 invalidate_pool_on_disconnect = (
2109 ctx.invalidate_pool_on_disconnect
2110 )
2112 if should_wrap and context:
2113 context.handle_dbapi_exception(e)
2115 if not self._is_disconnect:
2116 if cursor:
2117 self._safe_close_cursor(cursor)
2118 with util.safe_reraise(warn_only=True):
2119 self._autorollback()
2121 if newraise:
2122 util.raise_(newraise, with_traceback=exc_info[2], from_=e)
2123 elif should_wrap:
2124 util.raise_(
2125 sqlalchemy_exception, with_traceback=exc_info[2], from_=e
2126 )
2127 else:
2128 util.raise_(exc_info[1], with_traceback=exc_info[2])
2130 finally:
2131 del self._reentrant_error
2132 if self._is_disconnect:
2133 del self._is_disconnect
2134 if not self.invalidated:
2135 dbapi_conn_wrapper = self._dbapi_connection
2136 if invalidate_pool_on_disconnect:
2137 self.engine.pool._invalidate(dbapi_conn_wrapper, e)
2138 self.invalidate(e)
2139 if self.should_close_with_result:
2140 assert not self._is_future
2141 self.close()
2143 @classmethod
2144 def _handle_dbapi_exception_noconnection(cls, e, dialect, engine):
2145 exc_info = sys.exc_info()
2147 is_disconnect = dialect.is_disconnect(e, None, None)
2149 should_wrap = isinstance(e, dialect.dbapi.Error)
2151 if should_wrap:
2152 sqlalchemy_exception = exc.DBAPIError.instance(
2153 None,
2154 None,
2155 e,
2156 dialect.dbapi.Error,
2157 hide_parameters=engine.hide_parameters,
2158 connection_invalidated=is_disconnect,
2159 )
2160 else:
2161 sqlalchemy_exception = None
2163 newraise = None
2165 if engine._has_events:
2166 ctx = ExceptionContextImpl(
2167 e,
2168 sqlalchemy_exception,
2169 engine,
2170 None,
2171 None,
2172 None,
2173 None,
2174 None,
2175 is_disconnect,
2176 True,
2177 )
2178 for fn in engine.dispatch.handle_error:
2179 try:
2180 # handler returns an exception;
2181 # call next handler in a chain
2182 per_fn = fn(ctx)
2183 if per_fn is not None:
2184 ctx.chained_exception = newraise = per_fn
2185 except Exception as _raised:
2186 # handler raises an exception - stop processing
2187 newraise = _raised
2188 break
2190 if sqlalchemy_exception and is_disconnect != ctx.is_disconnect:
2191 sqlalchemy_exception.connection_invalidated = (
2192 is_disconnect
2193 ) = ctx.is_disconnect
2195 if newraise:
2196 util.raise_(newraise, with_traceback=exc_info[2], from_=e)
2197 elif should_wrap:
2198 util.raise_(
2199 sqlalchemy_exception, with_traceback=exc_info[2], from_=e
2200 )
2201 else:
2202 util.raise_(exc_info[1], with_traceback=exc_info[2])
2204 def _run_ddl_visitor(self, visitorcallable, element, **kwargs):
2205 """run a DDL visitor.
2207 This method is only here so that the MockConnection can change the
2208 options given to the visitor so that "checkfirst" is skipped.
2210 """
2211 visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
2213 @util.deprecated(
2214 "1.4",
2215 "The :meth:`_engine.Connection.transaction` "
2216 "method is deprecated and will be "
2217 "removed in a future release. Use the :meth:`_engine.Engine.begin` "
2218 "context manager instead.",
2219 )
2220 def transaction(self, callable_, *args, **kwargs):
2221 r"""Execute the given function within a transaction boundary.
2223 The function is passed this :class:`_engine.Connection`
2224 as the first argument, followed by the given \*args and \**kwargs,
2225 e.g.::
2227 def do_something(conn, x, y):
2228 conn.execute(text("some statement"), {'x':x, 'y':y})
2230 conn.transaction(do_something, 5, 10)
2232 The operations inside the function are all invoked within the
2233 context of a single :class:`.Transaction`.
2234 Upon success, the transaction is committed. If an
2235 exception is raised, the transaction is rolled back
2236 before propagating the exception.
2238 .. note::
2240 The :meth:`.transaction` method is superseded by
2241 the usage of the Python ``with:`` statement, which can
2242 be used with :meth:`_engine.Connection.begin`::
2244 with conn.begin():
2245 conn.execute(text("some statement"), {'x':5, 'y':10})
2247 As well as with :meth:`_engine.Engine.begin`::
2249 with engine.begin() as conn:
2250 conn.execute(text("some statement"), {'x':5, 'y':10})
2252 .. seealso::
2254 :meth:`_engine.Engine.begin` - engine-level transactional
2255 context
2257 :meth:`_engine.Engine.transaction` - engine-level version of
2258 :meth:`_engine.Connection.transaction`
2260 """
2262 kwargs["_sa_skip_warning"] = True
2263 trans = self.begin()
2264 try:
2265 ret = self.run_callable(callable_, *args, **kwargs)
2266 trans.commit()
2267 return ret
2268 except:
2269 with util.safe_reraise():
2270 trans.rollback()
2272 @util.deprecated(
2273 "1.4",
2274 "The :meth:`_engine.Connection.run_callable` "
2275 "method is deprecated and will "
2276 "be removed in a future release. Invoke the callable function "
2277 "directly, passing the Connection.",
2278 )
2279 def run_callable(self, callable_, *args, **kwargs):
2280 r"""Given a callable object or function, execute it, passing
2281 a :class:`_engine.Connection` as the first argument.
2283 The given \*args and \**kwargs are passed subsequent
2284 to the :class:`_engine.Connection` argument.
2286 This function, along with :meth:`_engine.Engine.run_callable`,
2287 allows a function to be run with a :class:`_engine.Connection`
2288 or :class:`_engine.Engine` object without the need to know
2289 which one is being dealt with.
2291 """
2292 return callable_(self, *args, **kwargs)
2295class ExceptionContextImpl(ExceptionContext):
2296 """Implement the :class:`.ExceptionContext` interface."""
2298 def __init__(
2299 self,
2300 exception,
2301 sqlalchemy_exception,
2302 engine,
2303 connection,
2304 cursor,
2305 statement,
2306 parameters,
2307 context,
2308 is_disconnect,
2309 invalidate_pool_on_disconnect,
2310 ):
2311 self.engine = engine
2312 self.connection = connection
2313 self.sqlalchemy_exception = sqlalchemy_exception
2314 self.original_exception = exception
2315 self.execution_context = context
2316 self.statement = statement
2317 self.parameters = parameters
2318 self.is_disconnect = is_disconnect
2319 self.invalidate_pool_on_disconnect = invalidate_pool_on_disconnect
2322class Transaction(TransactionalContext):
2323 """Represent a database transaction in progress.
2325 The :class:`.Transaction` object is procured by
2326 calling the :meth:`_engine.Connection.begin` method of
2327 :class:`_engine.Connection`::
2329 from sqlalchemy import create_engine
2330 engine = create_engine("postgresql://scott:tiger@localhost/test")
2331 connection = engine.connect()
2332 trans = connection.begin()
2333 connection.execute(text("insert into x (a, b) values (1, 2)"))
2334 trans.commit()
2336 The object provides :meth:`.rollback` and :meth:`.commit`
2337 methods in order to control transaction boundaries. It
2338 also implements a context manager interface so that
2339 the Python ``with`` statement can be used with the
2340 :meth:`_engine.Connection.begin` method::
2342 with connection.begin():
2343 connection.execute(text("insert into x (a, b) values (1, 2)"))
2345 The Transaction object is **not** threadsafe.
2347 .. seealso::
2349 :meth:`_engine.Connection.begin`
2351 :meth:`_engine.Connection.begin_twophase`
2353 :meth:`_engine.Connection.begin_nested`
2355 .. index::
2356 single: thread safety; Transaction
2357 """
2359 __slots__ = ()
2361 _is_root = False
2363 def __init__(self, connection):
2364 raise NotImplementedError()
2366 def _do_deactivate(self):
2367 """do whatever steps are necessary to set this transaction as
2368 "deactive", however leave this transaction object in place as far
2369 as the connection's state.
2371 for a "real" transaction this should roll back the transaction
2372 and ensure this transaction is no longer a reset agent.
2374 this is used for nesting of marker transactions where the marker
2375 can set the "real" transaction as rolled back, however it stays
2376 in place.
2378 for 2.0 we hope to remove this nesting feature.
2380 """
2381 raise NotImplementedError()
2383 @property
2384 def _deactivated_from_connection(self):
2385 """True if this transaction is totally deactivated from the connection
2386 and therefore can no longer affect its state.
2388 """
2389 raise NotImplementedError()
2391 def _do_close(self):
2392 raise NotImplementedError()
2394 def _do_rollback(self):
2395 raise NotImplementedError()
2397 def _do_commit(self):
2398 raise NotImplementedError()
2400 @property
2401 def is_valid(self):
2402 return self.is_active and not self.connection.invalidated
2404 def close(self):
2405 """Close this :class:`.Transaction`.
2407 If this transaction is the base transaction in a begin/commit
2408 nesting, the transaction will rollback(). Otherwise, the
2409 method returns.
2411 This is used to cancel a Transaction without affecting the scope of
2412 an enclosing transaction.
2414 """
2415 try:
2416 self._do_close()
2417 finally:
2418 assert not self.is_active
2420 def rollback(self):
2421 """Roll back this :class:`.Transaction`.
2423 The implementation of this may vary based on the type of transaction in
2424 use:
2426 * For a simple database transaction (e.g. :class:`.RootTransaction`),
2427 it corresponds to a ROLLBACK.
2429 * For a :class:`.NestedTransaction`, it corresponds to a
2430 "ROLLBACK TO SAVEPOINT" operation.
2432 * For a :class:`.TwoPhaseTransaction`, DBAPI-specific methods for two
2433 phase transactions may be used.
2436 """
2437 try:
2438 self._do_rollback()
2439 finally:
2440 assert not self.is_active
2442 def commit(self):
2443 """Commit this :class:`.Transaction`.
2445 The implementation of this may vary based on the type of transaction in
2446 use:
2448 * For a simple database transaction (e.g. :class:`.RootTransaction`),
2449 it corresponds to a COMMIT.
2451 * For a :class:`.NestedTransaction`, it corresponds to a
2452 "RELEASE SAVEPOINT" operation.
2454 * For a :class:`.TwoPhaseTransaction`, DBAPI-specific methods for two
2455 phase transactions may be used.
2457 """
2458 try:
2459 self._do_commit()
2460 finally:
2461 assert not self.is_active
2463 def _get_subject(self):
2464 return self.connection
2466 def _transaction_is_active(self):
2467 return self.is_active
2469 def _transaction_is_closed(self):
2470 return not self._deactivated_from_connection
2472 def _rollback_can_be_called(self):
2473 # for RootTransaction / NestedTransaction, it's safe to call
2474 # rollback() even if the transaction is deactive and no warnings
2475 # will be emitted. tested in
2476 # test_transaction.py -> test_no_rollback_in_deactive(?:_savepoint)?
2477 return True
2480class MarkerTransaction(Transaction):
2481 """A 'marker' transaction that is used for nested begin() calls.
2483 .. deprecated:: 1.4 future connection for 2.0 won't support this pattern.
2485 """
2487 __slots__ = ("connection", "_is_active", "_transaction")
2489 def __init__(self, connection):
2490 assert connection._transaction is not None
2491 if not connection._transaction.is_active:
2492 raise exc.InvalidRequestError(
2493 "the current transaction on this connection is inactive. "
2494 "Please issue a rollback first."
2495 )
2497 assert not connection._is_future
2498 util.warn_deprecated_20(
2499 "Calling .begin() when a transaction is already begun, creating "
2500 "a 'sub' transaction, is deprecated "
2501 "and will be removed in 2.0. See the documentation section "
2502 "'Migrating from the nesting pattern' for background on how "
2503 "to migrate from this pattern."
2504 )
2506 self.connection = connection
2508 if connection._trans_context_manager:
2509 TransactionalContext._trans_ctx_check(connection)
2511 if connection._nested_transaction is not None:
2512 self._transaction = connection._nested_transaction
2513 else:
2514 self._transaction = connection._transaction
2515 self._is_active = True
2517 @property
2518 def _deactivated_from_connection(self):
2519 return not self.is_active
2521 @property
2522 def is_active(self):
2523 return self._is_active and self._transaction.is_active
2525 def _deactivate(self):
2526 self._is_active = False
2528 def _do_close(self):
2529 # does not actually roll back the root
2530 self._deactivate()
2532 def _do_rollback(self):
2533 # does roll back the root
2534 if self._is_active:
2535 try:
2536 self._transaction._do_deactivate()
2537 finally:
2538 self._deactivate()
2540 def _do_commit(self):
2541 self._deactivate()
2544class RootTransaction(Transaction):
2545 """Represent the "root" transaction on a :class:`_engine.Connection`.
2547 This corresponds to the current "BEGIN/COMMIT/ROLLBACK" that's occurring
2548 for the :class:`_engine.Connection`. The :class:`_engine.RootTransaction`
2549 is created by calling upon the :meth:`_engine.Connection.begin` method, and
2550 remains associated with the :class:`_engine.Connection` throughout its
2551 active span. The current :class:`_engine.RootTransaction` in use is
2552 accessible via the :attr:`_engine.Connection.get_transaction` method of
2553 :class:`_engine.Connection`.
2555 In :term:`2.0 style` use, the :class:`_future.Connection` also employs
2556 "autobegin" behavior that will create a new
2557 :class:`_engine.RootTransaction` whenever a connection in a
2558 non-transactional state is used to emit commands on the DBAPI connection.
2559 The scope of the :class:`_engine.RootTransaction` in 2.0 style
2560 use can be controlled using the :meth:`_future.Connection.commit` and
2561 :meth:`_future.Connection.rollback` methods.
2564 """
2566 _is_root = True
2568 __slots__ = ("connection", "is_active")
2570 def __init__(self, connection):
2571 assert connection._transaction is None
2572 if connection._trans_context_manager:
2573 TransactionalContext._trans_ctx_check(connection)
2574 self.connection = connection
2575 self._connection_begin_impl()
2576 connection._transaction = self
2578 self.is_active = True
2580 def _deactivate_from_connection(self):
2581 if self.is_active:
2582 assert self.connection._transaction is self
2583 self.is_active = False
2585 elif self.connection._transaction is not self:
2586 util.warn("transaction already deassociated from connection")
2588 @property
2589 def _deactivated_from_connection(self):
2590 return self.connection._transaction is not self
2592 def _do_deactivate(self):
2593 # called from a MarkerTransaction to cancel this root transaction.
2594 # the transaction stays in place as connection._transaction, but
2595 # is no longer active and is no longer the reset agent for the
2596 # pooled connection. the connection won't support a new begin()
2597 # until this transaction is explicitly closed, rolled back,
2598 # or committed.
2600 assert self.connection._transaction is self
2602 if self.is_active:
2603 self._connection_rollback_impl()
2605 # handle case where a savepoint was created inside of a marker
2606 # transaction that refers to a root. nested has to be cancelled
2607 # also.
2608 if self.connection._nested_transaction:
2609 self.connection._nested_transaction._cancel()
2611 self._deactivate_from_connection()
2613 def _connection_begin_impl(self):
2614 self.connection._begin_impl(self)
2616 def _connection_rollback_impl(self):
2617 self.connection._rollback_impl()
2619 def _connection_commit_impl(self):
2620 self.connection._commit_impl()
2622 def _close_impl(self, try_deactivate=False):
2623 try:
2624 if self.is_active:
2625 self._connection_rollback_impl()
2627 if self.connection._nested_transaction:
2628 self.connection._nested_transaction._cancel()
2629 finally:
2630 if self.is_active or try_deactivate:
2631 self._deactivate_from_connection()
2632 if self.connection._transaction is self:
2633 self.connection._transaction = None
2635 assert not self.is_active
2636 assert self.connection._transaction is not self
2638 def _do_close(self):
2639 self._close_impl()
2641 def _do_rollback(self):
2642 self._close_impl(try_deactivate=True)
2644 def _do_commit(self):
2645 if self.is_active:
2646 assert self.connection._transaction is self
2648 try:
2649 self._connection_commit_impl()
2650 finally:
2651 # whether or not commit succeeds, cancel any
2652 # nested transactions, make this transaction "inactive"
2653 # and remove it as a reset agent
2654 if self.connection._nested_transaction:
2655 self.connection._nested_transaction._cancel()
2657 self._deactivate_from_connection()
2659 # ...however only remove as the connection's current transaction
2660 # if commit succeeded. otherwise it stays on so that a rollback
2661 # needs to occur.
2662 self.connection._transaction = None
2663 else:
2664 if self.connection._transaction is self:
2665 self.connection._invalid_transaction()
2666 else:
2667 raise exc.InvalidRequestError("This transaction is inactive")
2669 assert not self.is_active
2670 assert self.connection._transaction is not self
2673class NestedTransaction(Transaction):
2674 """Represent a 'nested', or SAVEPOINT transaction.
2676 The :class:`.NestedTransaction` object is created by calling the
2677 :meth:`_engine.Connection.begin_nested` method of
2678 :class:`_engine.Connection`.
2680 When using :class:`.NestedTransaction`, the semantics of "begin" /
2681 "commit" / "rollback" are as follows:
2683 * the "begin" operation corresponds to the "BEGIN SAVEPOINT" command, where
2684 the savepoint is given an explicit name that is part of the state
2685 of this object.
2687 * The :meth:`.NestedTransaction.commit` method corresponds to a
2688 "RELEASE SAVEPOINT" operation, using the savepoint identifier associated
2689 with this :class:`.NestedTransaction`.
2691 * The :meth:`.NestedTransaction.rollback` method corresponds to a
2692 "ROLLBACK TO SAVEPOINT" operation, using the savepoint identifier
2693 associated with this :class:`.NestedTransaction`.
2695 The rationale for mimicking the semantics of an outer transaction in
2696 terms of savepoints so that code may deal with a "savepoint" transaction
2697 and an "outer" transaction in an agnostic way.
2699 .. seealso::
2701 :ref:`session_begin_nested` - ORM version of the SAVEPOINT API.
2703 """
2705 __slots__ = ("connection", "is_active", "_savepoint", "_previous_nested")
2707 def __init__(self, connection):
2708 assert connection._transaction is not None
2709 if connection._trans_context_manager:
2710 TransactionalContext._trans_ctx_check(connection)
2711 self.connection = connection
2712 self._savepoint = self.connection._savepoint_impl()
2713 self.is_active = True
2714 self._previous_nested = connection._nested_transaction
2715 connection._nested_transaction = self
2717 def _deactivate_from_connection(self, warn=True):
2718 if self.connection._nested_transaction is self:
2719 self.connection._nested_transaction = self._previous_nested
2720 elif warn:
2721 util.warn(
2722 "nested transaction already deassociated from connection"
2723 )
2725 @property
2726 def _deactivated_from_connection(self):
2727 return self.connection._nested_transaction is not self
2729 def _cancel(self):
2730 # called by RootTransaction when the outer transaction is
2731 # committed, rolled back, or closed to cancel all savepoints
2732 # without any action being taken
2733 self.is_active = False
2734 self._deactivate_from_connection()
2735 if self._previous_nested:
2736 self._previous_nested._cancel()
2738 def _close_impl(self, deactivate_from_connection, warn_already_deactive):
2739 try:
2740 if self.is_active and self.connection._transaction.is_active:
2741 self.connection._rollback_to_savepoint_impl(self._savepoint)
2742 finally:
2743 self.is_active = False
2745 if deactivate_from_connection:
2746 self._deactivate_from_connection(warn=warn_already_deactive)
2748 assert not self.is_active
2749 if deactivate_from_connection:
2750 assert self.connection._nested_transaction is not self
2752 def _do_deactivate(self):
2753 self._close_impl(False, False)
2755 def _do_close(self):
2756 self._close_impl(True, False)
2758 def _do_rollback(self):
2759 self._close_impl(True, True)
2761 def _do_commit(self):
2762 if self.is_active:
2763 try:
2764 self.connection._release_savepoint_impl(self._savepoint)
2765 finally:
2766 # nested trans becomes inactive on failed release
2767 # unconditionally. this prevents it from trying to
2768 # emit SQL when it rolls back.
2769 self.is_active = False
2771 # but only de-associate from connection if it succeeded
2772 self._deactivate_from_connection()
2773 else:
2774 if self.connection._nested_transaction is self:
2775 self.connection._invalid_transaction()
2776 else:
2777 raise exc.InvalidRequestError(
2778 "This nested transaction is inactive"
2779 )
2782class TwoPhaseTransaction(RootTransaction):
2783 """Represent a two-phase transaction.
2785 A new :class:`.TwoPhaseTransaction` object may be procured
2786 using the :meth:`_engine.Connection.begin_twophase` method.
2788 The interface is the same as that of :class:`.Transaction`
2789 with the addition of the :meth:`prepare` method.
2791 """
2793 __slots__ = ("connection", "is_active", "xid", "_is_prepared")
2795 def __init__(self, connection, xid):
2796 self._is_prepared = False
2797 self.xid = xid
2798 super(TwoPhaseTransaction, self).__init__(connection)
2800 def prepare(self):
2801 """Prepare this :class:`.TwoPhaseTransaction`.
2803 After a PREPARE, the transaction can be committed.
2805 """
2806 if not self.is_active:
2807 raise exc.InvalidRequestError("This transaction is inactive")
2808 self.connection._prepare_twophase_impl(self.xid)
2809 self._is_prepared = True
2811 def _connection_begin_impl(self):
2812 self.connection._begin_twophase_impl(self)
2814 def _connection_rollback_impl(self):
2815 self.connection._rollback_twophase_impl(self.xid, self._is_prepared)
2817 def _connection_commit_impl(self):
2818 self.connection._commit_twophase_impl(self.xid, self._is_prepared)
2821class Engine(Connectable, log.Identified):
2822 """
2823 Connects a :class:`~sqlalchemy.pool.Pool` and
2824 :class:`~sqlalchemy.engine.interfaces.Dialect` together to provide a
2825 source of database connectivity and behavior.
2827 This is the **SQLAlchemy 1.x version** of :class:`_engine.Engine`. For
2828 the :term:`2.0 style` version, which includes some API differences,
2829 see :class:`_future.Engine`.
2831 An :class:`_engine.Engine` object is instantiated publicly using the
2832 :func:`~sqlalchemy.create_engine` function.
2834 .. seealso::
2836 :doc:`/core/engines`
2838 :ref:`connections_toplevel`
2840 """
2842 _execution_options = _EMPTY_EXECUTION_OPTS
2843 _has_events = False
2844 _connection_cls = Connection
2845 _sqla_logger_namespace = "sqlalchemy.engine.Engine"
2846 _is_future = False
2848 _schema_translate_map = None
2850 def __init__(
2851 self,
2852 pool,
2853 dialect,
2854 url,
2855 logging_name=None,
2856 echo=None,
2857 query_cache_size=500,
2858 execution_options=None,
2859 hide_parameters=False,
2860 ):
2861 self.pool = pool
2862 self.url = url
2863 self.dialect = dialect
2864 if logging_name:
2865 self.logging_name = logging_name
2866 self.echo = echo
2867 self.hide_parameters = hide_parameters
2868 if query_cache_size != 0:
2869 self._compiled_cache = util.LRUCache(
2870 query_cache_size, size_alert=self._lru_size_alert
2871 )
2872 else:
2873 self._compiled_cache = None
2874 log.instance_logger(self, echoflag=echo)
2875 if execution_options:
2876 self.update_execution_options(**execution_options)
2878 def _lru_size_alert(self, cache):
2879 if self._should_log_info:
2880 self.logger.info(
2881 "Compiled cache size pruning from %d items to %d. "
2882 "Increase cache size to reduce the frequency of pruning.",
2883 len(cache),
2884 cache.capacity,
2885 )
2887 @property
2888 def engine(self):
2889 return self
2891 def clear_compiled_cache(self):
2892 """Clear the compiled cache associated with the dialect.
2894 This applies **only** to the built-in cache that is established
2895 via the :paramref:`_engine.create_engine.query_cache_size` parameter.
2896 It will not impact any dictionary caches that were passed via the
2897 :paramref:`.Connection.execution_options.query_cache` parameter.
2899 .. versionadded:: 1.4
2901 """
2902 if self._compiled_cache:
2903 self._compiled_cache.clear()
2905 def update_execution_options(self, **opt):
2906 r"""Update the default execution_options dictionary
2907 of this :class:`_engine.Engine`.
2909 The given keys/values in \**opt are added to the
2910 default execution options that will be used for
2911 all connections. The initial contents of this dictionary
2912 can be sent via the ``execution_options`` parameter
2913 to :func:`_sa.create_engine`.
2915 .. seealso::
2917 :meth:`_engine.Connection.execution_options`
2919 :meth:`_engine.Engine.execution_options`
2921 """
2922 self._execution_options = self._execution_options.union(opt)
2923 self.dispatch.set_engine_execution_options(self, opt)
2924 self.dialect.set_engine_execution_options(self, opt)
2926 def execution_options(self, **opt):
2927 """Return a new :class:`_engine.Engine` that will provide
2928 :class:`_engine.Connection` objects with the given execution options.
2930 The returned :class:`_engine.Engine` remains related to the original
2931 :class:`_engine.Engine` in that it shares the same connection pool and
2932 other state:
2934 * The :class:`_pool.Pool` used by the new :class:`_engine.Engine`
2935 is the
2936 same instance. The :meth:`_engine.Engine.dispose`
2937 method will replace
2938 the connection pool instance for the parent engine as well
2939 as this one.
2940 * Event listeners are "cascaded" - meaning, the new
2941 :class:`_engine.Engine`
2942 inherits the events of the parent, and new events can be associated
2943 with the new :class:`_engine.Engine` individually.
2944 * The logging configuration and logging_name is copied from the parent
2945 :class:`_engine.Engine`.
2947 The intent of the :meth:`_engine.Engine.execution_options` method is
2948 to implement "sharding" schemes where multiple :class:`_engine.Engine`
2949 objects refer to the same connection pool, but are differentiated
2950 by options that would be consumed by a custom event::
2952 primary_engine = create_engine("mysql://")
2953 shard1 = primary_engine.execution_options(shard_id="shard1")
2954 shard2 = primary_engine.execution_options(shard_id="shard2")
2956 Above, the ``shard1`` engine serves as a factory for
2957 :class:`_engine.Connection`
2958 objects that will contain the execution option
2959 ``shard_id=shard1``, and ``shard2`` will produce
2960 :class:`_engine.Connection`
2961 objects that contain the execution option ``shard_id=shard2``.
2963 An event handler can consume the above execution option to perform
2964 a schema switch or other operation, given a connection. Below
2965 we emit a MySQL ``use`` statement to switch databases, at the same
2966 time keeping track of which database we've established using the
2967 :attr:`_engine.Connection.info` dictionary,
2968 which gives us a persistent
2969 storage space that follows the DBAPI connection::
2971 from sqlalchemy import event
2972 from sqlalchemy.engine import Engine
2974 shards = {"default": "base", shard_1: "db1", "shard_2": "db2"}
2976 @event.listens_for(Engine, "before_cursor_execute")
2977 def _switch_shard(conn, cursor, stmt,
2978 params, context, executemany):
2979 shard_id = conn._execution_options.get('shard_id', "default")
2980 current_shard = conn.info.get("current_shard", None)
2982 if current_shard != shard_id:
2983 cursor.execute("use %s" % shards[shard_id])
2984 conn.info["current_shard"] = shard_id
2986 .. seealso::
2988 :meth:`_engine.Connection.execution_options`
2989 - update execution options
2990 on a :class:`_engine.Connection` object.
2992 :meth:`_engine.Engine.update_execution_options`
2993 - update the execution
2994 options for a given :class:`_engine.Engine` in place.
2996 :meth:`_engine.Engine.get_execution_options`
2999 """
3000 return self._option_cls(self, opt)
3002 def get_execution_options(self):
3003 """Get the non-SQL options which will take effect during execution.
3005 .. versionadded: 1.3
3007 .. seealso::
3009 :meth:`_engine.Engine.execution_options`
3010 """
3011 return self._execution_options
3013 @property
3014 def name(self):
3015 """String name of the :class:`~sqlalchemy.engine.interfaces.Dialect`
3016 in use by this :class:`Engine`."""
3018 return self.dialect.name
3020 @property
3021 def driver(self):
3022 """Driver name of the :class:`~sqlalchemy.engine.interfaces.Dialect`
3023 in use by this :class:`Engine`."""
3025 return self.dialect.driver
3027 echo = log.echo_property()
3029 def __repr__(self):
3030 return "Engine(%r)" % (self.url,)
3032 def dispose(self, close=True):
3033 """Dispose of the connection pool used by this
3034 :class:`_engine.Engine`.
3036 A new connection pool is created immediately after the old one has been
3037 disposed. The previous connection pool is disposed either actively, by
3038 closing out all currently checked-in connections in that pool, or
3039 passively, by losing references to it but otherwise not closing any
3040 connections. The latter strategy is more appropriate for an initializer
3041 in a forked Python process.
3043 :param close: if left at its default of ``True``, has the
3044 effect of fully closing all **currently checked in**
3045 database connections. Connections that are still checked out
3046 will **not** be closed, however they will no longer be associated
3047 with this :class:`_engine.Engine`,
3048 so when they are closed individually, eventually the
3049 :class:`_pool.Pool` which they are associated with will
3050 be garbage collected and they will be closed out fully, if
3051 not already closed on checkin.
3053 If set to ``False``, the previous connection pool is de-referenced,
3054 and otherwise not touched in any way.
3056 .. versionadded:: 1.4.33 Added the :paramref:`.Engine.dispose.close`
3057 parameter to allow the replacement of a connection pool in a child
3058 process without interfering with the connections used by the parent
3059 process.
3062 .. seealso::
3064 :ref:`engine_disposal`
3066 :ref:`pooling_multiprocessing`
3068 """
3069 if close:
3070 self.pool.dispose()
3071 self.pool = self.pool.recreate()
3072 self.dispatch.engine_disposed(self)
3074 def _execute_default(
3075 self, default, multiparams=(), params=util.EMPTY_DICT
3076 ):
3077 with self.connect() as conn:
3078 return conn._execute_default(default, multiparams, params)
3080 @contextlib.contextmanager
3081 def _optional_conn_ctx_manager(self, connection=None):
3082 if connection is None:
3083 with self.connect() as conn:
3084 yield conn
3085 else:
3086 yield connection
3088 class _trans_ctx(object):
3089 def __init__(self, conn, transaction, close_with_result):
3090 self.conn = conn
3091 self.transaction = transaction
3092 self.close_with_result = close_with_result
3094 def __enter__(self):
3095 self.transaction.__enter__()
3096 return self.conn
3098 def __exit__(self, type_, value, traceback):
3099 try:
3100 self.transaction.__exit__(type_, value, traceback)
3101 finally:
3102 if not self.close_with_result:
3103 self.conn.close()
3105 def begin(self, close_with_result=False):
3106 """Return a context manager delivering a :class:`_engine.Connection`
3107 with a :class:`.Transaction` established.
3109 E.g.::
3111 with engine.begin() as conn:
3112 conn.execute(
3113 text("insert into table (x, y, z) values (1, 2, 3)")
3114 )
3115 conn.execute(text("my_special_procedure(5)"))
3117 Upon successful operation, the :class:`.Transaction`
3118 is committed. If an error is raised, the :class:`.Transaction`
3119 is rolled back.
3121 Legacy use only: the ``close_with_result`` flag is normally ``False``,
3122 and indicates that the :class:`_engine.Connection` will be closed when
3123 the operation is complete. When set to ``True``, it indicates the
3124 :class:`_engine.Connection` is in "single use" mode, where the
3125 :class:`_engine.CursorResult` returned by the first call to
3126 :meth:`_engine.Connection.execute` will close the
3127 :class:`_engine.Connection` when that :class:`_engine.CursorResult` has
3128 exhausted all result rows.
3130 .. seealso::
3132 :meth:`_engine.Engine.connect` - procure a
3133 :class:`_engine.Connection` from
3134 an :class:`_engine.Engine`.
3136 :meth:`_engine.Connection.begin` - start a :class:`.Transaction`
3137 for a particular :class:`_engine.Connection`.
3139 """
3140 if self._connection_cls._is_future:
3141 conn = self.connect()
3142 else:
3143 conn = self.connect(close_with_result=close_with_result)
3144 try:
3145 trans = conn.begin()
3146 except:
3147 with util.safe_reraise():
3148 conn.close()
3149 return Engine._trans_ctx(conn, trans, close_with_result)
3151 @util.deprecated(
3152 "1.4",
3153 "The :meth:`_engine.Engine.transaction` "
3154 "method is deprecated and will be "
3155 "removed in a future release. Use the :meth:`_engine.Engine.begin` "
3156 "context "
3157 "manager instead.",
3158 )
3159 def transaction(self, callable_, *args, **kwargs):
3160 r"""Execute the given function within a transaction boundary.
3162 The function is passed a :class:`_engine.Connection` newly procured
3163 from :meth:`_engine.Engine.connect` as the first argument,
3164 followed by the given \*args and \**kwargs.
3166 e.g.::
3168 def do_something(conn, x, y):
3169 conn.execute(text("some statement"), {'x':x, 'y':y})
3171 engine.transaction(do_something, 5, 10)
3173 The operations inside the function are all invoked within the
3174 context of a single :class:`.Transaction`.
3175 Upon success, the transaction is committed. If an
3176 exception is raised, the transaction is rolled back
3177 before propagating the exception.
3179 .. note::
3181 The :meth:`.transaction` method is superseded by
3182 the usage of the Python ``with:`` statement, which can
3183 be used with :meth:`_engine.Engine.begin`::
3185 with engine.begin() as conn:
3186 conn.execute(text("some statement"), {'x':5, 'y':10})
3188 .. seealso::
3190 :meth:`_engine.Engine.begin` - engine-level transactional
3191 context
3193 :meth:`_engine.Connection.transaction`
3194 - connection-level version of
3195 :meth:`_engine.Engine.transaction`
3197 """
3198 kwargs["_sa_skip_warning"] = True
3199 with self.connect() as conn:
3200 return conn.transaction(callable_, *args, **kwargs)
3202 @util.deprecated(
3203 "1.4",
3204 "The :meth:`_engine.Engine.run_callable` "
3205 "method is deprecated and will be "
3206 "removed in a future release. Use the :meth:`_engine.Engine.begin` "
3207 "context manager instead.",
3208 )
3209 def run_callable(self, callable_, *args, **kwargs):
3210 r"""Given a callable object or function, execute it, passing
3211 a :class:`_engine.Connection` as the first argument.
3213 The given \*args and \**kwargs are passed subsequent
3214 to the :class:`_engine.Connection` argument.
3216 This function, along with :meth:`_engine.Connection.run_callable`,
3217 allows a function to be run with a :class:`_engine.Connection`
3218 or :class:`_engine.Engine` object without the need to know
3219 which one is being dealt with.
3221 """
3222 kwargs["_sa_skip_warning"] = True
3223 with self.connect() as conn:
3224 return conn.run_callable(callable_, *args, **kwargs)
3226 def _run_ddl_visitor(self, visitorcallable, element, **kwargs):
3227 with self.begin() as conn:
3228 conn._run_ddl_visitor(visitorcallable, element, **kwargs)
3230 @util.deprecated_20(
3231 ":meth:`_engine.Engine.execute`",
3232 alternative="All statement execution in SQLAlchemy 2.0 is performed "
3233 "by the :meth:`_engine.Connection.execute` method of "
3234 ":class:`_engine.Connection`, "
3235 "or in the ORM by the :meth:`.Session.execute` method of "
3236 ":class:`.Session`.",
3237 )
3238 def execute(self, statement, *multiparams, **params):
3239 """Executes the given construct and returns a
3240 :class:`_engine.CursorResult`.
3242 The arguments are the same as those used by
3243 :meth:`_engine.Connection.execute`.
3245 Here, a :class:`_engine.Connection` is acquired using the
3246 :meth:`_engine.Engine.connect` method, and the statement executed
3247 with that connection. The returned :class:`_engine.CursorResult`
3248 is flagged
3249 such that when the :class:`_engine.CursorResult` is exhausted and its
3250 underlying cursor is closed, the :class:`_engine.Connection`
3251 created here
3252 will also be closed, which allows its associated DBAPI connection
3253 resource to be returned to the connection pool.
3255 """
3256 connection = self.connect(close_with_result=True)
3257 return connection.execute(statement, *multiparams, **params)
3259 @util.deprecated_20(
3260 ":meth:`_engine.Engine.scalar`",
3261 alternative="All statement execution in SQLAlchemy 2.0 is performed "
3262 "by the :meth:`_engine.Connection.execute` method of "
3263 ":class:`_engine.Connection`, "
3264 "or in the ORM by the :meth:`.Session.execute` method of "
3265 ":class:`.Session`; the :meth:`_future.Result.scalar` "
3266 "method can then be "
3267 "used to return a scalar result.",
3268 )
3269 def scalar(self, statement, *multiparams, **params):
3270 """Executes and returns the first column of the first row.
3272 The underlying result/cursor is closed after execution.
3273 """
3274 return self.execute(statement, *multiparams, **params).scalar()
3276 def _execute_clauseelement(
3277 self,
3278 elem,
3279 multiparams=None,
3280 params=None,
3281 execution_options=_EMPTY_EXECUTION_OPTS,
3282 ):
3283 connection = self.connect(close_with_result=True)
3284 return connection._execute_clauseelement(
3285 elem, multiparams, params, execution_options
3286 )
3288 def _execute_compiled(
3289 self,
3290 compiled,
3291 multiparams,
3292 params,
3293 execution_options=_EMPTY_EXECUTION_OPTS,
3294 ):
3295 connection = self.connect(close_with_result=True)
3296 return connection._execute_compiled(
3297 compiled, multiparams, params, execution_options
3298 )
3300 def connect(self, close_with_result=False):
3301 """Return a new :class:`_engine.Connection` object.
3303 The :class:`_engine.Connection` object is a facade that uses a DBAPI
3304 connection internally in order to communicate with the database. This
3305 connection is procured from the connection-holding :class:`_pool.Pool`
3306 referenced by this :class:`_engine.Engine`. When the
3307 :meth:`_engine.Connection.close` method of the
3308 :class:`_engine.Connection` object
3309 is called, the underlying DBAPI connection is then returned to the
3310 connection pool, where it may be used again in a subsequent call to
3311 :meth:`_engine.Engine.connect`.
3313 """
3315 return self._connection_cls(self, close_with_result=close_with_result)
3317 @util.deprecated(
3318 "1.4",
3319 "The :meth:`_engine.Engine.table_names` "
3320 "method is deprecated and will be "
3321 "removed in a future release. Please refer to "
3322 ":meth:`_reflection.Inspector.get_table_names`.",
3323 )
3324 def table_names(self, schema=None, connection=None):
3325 """Return a list of all table names available in the database.
3327 :param schema: Optional, retrieve names from a non-default schema.
3329 :param connection: Optional, use a specified connection.
3330 """
3331 with self._optional_conn_ctx_manager(connection) as conn:
3332 insp = inspection.inspect(conn)
3333 return insp.get_table_names(schema)
3335 @util.deprecated(
3336 "1.4",
3337 "The :meth:`_engine.Engine.has_table` "
3338 "method is deprecated and will be "
3339 "removed in a future release. Please refer to "
3340 ":meth:`_reflection.Inspector.has_table`.",
3341 )
3342 def has_table(self, table_name, schema=None):
3343 """Return True if the given backend has a table of the given name.
3345 .. seealso::
3347 :ref:`metadata_reflection_inspector` - detailed schema inspection
3348 using the :class:`_reflection.Inspector` interface.
3350 :class:`.quoted_name` - used to pass quoting information along
3351 with a schema identifier.
3353 """
3354 with self._optional_conn_ctx_manager(None) as conn:
3355 insp = inspection.inspect(conn)
3356 return insp.has_table(table_name, schema=schema)
3358 def _wrap_pool_connect(self, fn, connection):
3359 dialect = self.dialect
3360 try:
3361 return fn()
3362 except dialect.dbapi.Error as e:
3363 if connection is None:
3364 Connection._handle_dbapi_exception_noconnection(
3365 e, dialect, self
3366 )
3367 else:
3368 util.raise_(
3369 sys.exc_info()[1], with_traceback=sys.exc_info()[2]
3370 )
3372 def raw_connection(self, _connection=None):
3373 """Return a "raw" DBAPI connection from the connection pool.
3375 The returned object is a proxied version of the DBAPI
3376 connection object used by the underlying driver in use.
3377 The object will have all the same behavior as the real DBAPI
3378 connection, except that its ``close()`` method will result in the
3379 connection being returned to the pool, rather than being closed
3380 for real.
3382 This method provides direct DBAPI connection access for
3383 special situations when the API provided by
3384 :class:`_engine.Connection`
3385 is not needed. When a :class:`_engine.Connection` object is already
3386 present, the DBAPI connection is available using
3387 the :attr:`_engine.Connection.connection` accessor.
3389 .. seealso::
3391 :ref:`dbapi_connections`
3393 """
3394 return self._wrap_pool_connect(self.pool.connect, _connection)
3397class OptionEngineMixin(object):
3398 _sa_propagate_class_events = False
3400 def __init__(self, proxied, execution_options):
3401 self._proxied = proxied
3402 self.url = proxied.url
3403 self.dialect = proxied.dialect
3404 self.logging_name = proxied.logging_name
3405 self.echo = proxied.echo
3406 self._compiled_cache = proxied._compiled_cache
3407 self.hide_parameters = proxied.hide_parameters
3408 log.instance_logger(self, echoflag=self.echo)
3410 # note: this will propagate events that are assigned to the parent
3411 # engine after this OptionEngine is created. Since we share
3412 # the events of the parent we also disallow class-level events
3413 # to apply to the OptionEngine class directly.
3414 #
3415 # the other way this can work would be to transfer existing
3416 # events only, using:
3417 # self.dispatch._update(proxied.dispatch)
3418 #
3419 # that might be more appropriate however it would be a behavioral
3420 # change for logic that assigns events to the parent engine and
3421 # would like it to take effect for the already-created sub-engine.
3422 self.dispatch = self.dispatch._join(proxied.dispatch)
3424 self._execution_options = proxied._execution_options
3425 self.update_execution_options(**execution_options)
3427 def _get_pool(self):
3428 return self._proxied.pool
3430 def _set_pool(self, pool):
3431 self._proxied.pool = pool
3433 pool = property(_get_pool, _set_pool)
3435 def _get_has_events(self):
3436 return self._proxied._has_events or self.__dict__.get(
3437 "_has_events", False
3438 )
3440 def _set_has_events(self, value):
3441 self.__dict__["_has_events"] = value
3443 _has_events = property(_get_has_events, _set_has_events)
3446class OptionEngine(OptionEngineMixin, Engine):
3447 pass
3450Engine._option_cls = OptionEngine