Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# engine/__init__.py
2# Copyright (C) 2005-2021 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: http://www.opensource.org/licenses/mit-license.php
8"""SQL connections, SQL execution and high-level DB-API interface.
10The engine package defines the basic components used to interface
11DB-API modules with higher-level statement construction,
12connection-management, execution and result contexts. The primary
13"entry point" class into this package is the Engine and its public
14constructor ``create_engine()``.
16This package includes:
18base.py
19 Defines interface classes and some implementation classes which
20 comprise the basic components used to interface between a DB-API,
21 constructed and plain-text statements, connections, transactions,
22 and results.
24default.py
25 Contains default implementations of some of the components defined
26 in base.py. All current database dialects use the classes in
27 default.py as base classes for their own database-specific
28 implementations.
30strategies.py
31 The mechanics of constructing ``Engine`` objects are represented
32 here. Defines the ``EngineStrategy`` class which represents how
33 to go from arguments specified to the ``create_engine()``
34 function, to a fully constructed ``Engine``, including
35 initialization of connection pooling, dialects, and specific
36 subclasses of ``Engine``.
38threadlocal.py
39 The ``TLEngine`` class is defined here, which is a subclass of
40 the generic ``Engine`` and tracks ``Connection`` and
41 ``Transaction`` objects against the identity of the current
42 thread. This allows certain programming patterns based around
43 the concept of a "thread-local connection" to be possible.
44 The ``TLEngine`` is created by using the "threadlocal" engine
45 strategy in conjunction with the ``create_engine()`` function.
47url.py
48 Defines the ``URL`` class which represents the individual
49 components of a string URL passed to ``create_engine()``. Also
50 defines a basic module-loading strategy for the dialect specifier
51 within a URL.
52"""
54from . import strategies
55from . import util # noqa
56from .base import Connection # noqa
57from .base import Engine # noqa
58from .base import NestedTransaction # noqa
59from .base import RootTransaction # noqa
60from .base import Transaction # noqa
61from .base import TwoPhaseTransaction # noqa
62from .interfaces import Compiled # noqa
63from .interfaces import Connectable # noqa
64from .interfaces import CreateEnginePlugin # noqa
65from .interfaces import Dialect # noqa
66from .interfaces import ExceptionContext # noqa
67from .interfaces import ExecutionContext # noqa
68from .interfaces import TypeCompiler # noqa
69from .result import BaseRowProxy # noqa
70from .result import BufferedColumnResultProxy # noqa
71from .result import BufferedColumnRow # noqa
72from .result import BufferedRowResultProxy # noqa
73from .result import FullyBufferedResultProxy # noqa
74from .result import ResultProxy # noqa
75from .result import RowProxy # noqa
76from .util import connection_memoize # noqa
77from ..sql import ddl # noqa
80# backwards compat
82default_strategy = "plain"
85def create_engine(*args, **kwargs):
86 """Create a new :class:`_engine.Engine` instance.
88 The standard calling form is to send the :ref:`URL <database_urls>` as the
89 first positional argument, usually a string
90 that indicates database dialect and connection arguments::
92 engine = create_engine("postgresql://scott:tiger@localhost/test")
94 .. note::
96 Please review :ref:`database_urls` for general guidelines in composing
97 URL strings. In particular, special characters, such as those often
98 part of passwords, must be URL encoded to be properly parsed.
100 Additional keyword arguments may then follow it which
101 establish various options on the resulting :class:`_engine.Engine`
102 and its underlying :class:`.Dialect` and :class:`_pool.Pool`
103 constructs::
105 engine = create_engine("mysql://scott:tiger@hostname/dbname",
106 encoding='latin1', echo=True)
108 The string form of the URL is
109 ``dialect[+driver]://user:password@host/dbname[?key=value..]``, where
110 ``dialect`` is a database name such as ``mysql``, ``oracle``,
111 ``postgresql``, etc., and ``driver`` the name of a DBAPI, such as
112 ``psycopg2``, ``pyodbc``, ``cx_oracle``, etc. Alternatively,
113 the URL can be an instance of :class:`~sqlalchemy.engine.url.URL`.
115 ``**kwargs`` takes a wide variety of options which are routed
116 towards their appropriate components. Arguments may be specific to
117 the :class:`_engine.Engine`, the underlying :class:`.Dialect`,
118 as well as the
119 :class:`_pool.Pool`. Specific dialects also accept keyword arguments that
120 are unique to that dialect. Here, we describe the parameters
121 that are common to most :func:`_sa.create_engine()` usage.
123 Once established, the newly resulting :class:`_engine.Engine` will
124 request a connection from the underlying :class:`_pool.Pool` once
125 :meth:`_engine.Engine.connect` is called, or a method which depends on it
126 such as :meth:`_engine.Engine.execute` is invoked. The
127 :class:`_pool.Pool` in turn
128 will establish the first actual DBAPI connection when this request
129 is received. The :func:`_sa.create_engine` call itself does **not**
130 establish any actual DBAPI connections directly.
132 .. seealso::
134 :doc:`/core/engines`
136 :doc:`/dialects/index`
138 :ref:`connections_toplevel`
140 :param case_sensitive=True: if False, result column names
141 will match in a case-insensitive fashion, that is,
142 ``row['SomeColumn']``.
144 :param connect_args: a dictionary of options which will be
145 passed directly to the DBAPI's ``connect()`` method as
146 additional keyword arguments. See the example
147 at :ref:`custom_dbapi_args`.
149 :param convert_unicode=False: if set to True, causes
150 all :class:`.String` datatypes to act as though the
151 :paramref:`.String.convert_unicode` flag has been set to ``True``,
152 regardless of a setting of ``False`` on an individual :class:`.String`
153 type. This has the effect of causing all :class:`.String` -based
154 columns to accommodate Python Unicode objects directly as though the
155 datatype were the :class:`.Unicode` type.
157 .. deprecated:: 1.3
159 The :paramref:`_sa.create_engine.convert_unicode` parameter
160 is deprecated and will be removed in a future release.
161 All modern DBAPIs now support Python Unicode directly and this
162 parameter is unnecessary.
164 :param creator: a callable which returns a DBAPI connection.
165 This creation function will be passed to the underlying
166 connection pool and will be used to create all new database
167 connections. Usage of this function causes connection
168 parameters specified in the URL argument to be bypassed.
170 This hook is not as flexible as the newer
171 :class:`_events.DialectEvents.do_connect` hook which allows complete
172 control over how a connection is made to the database, given the full
173 set of URL arguments and state beforehand.
175 .. seealso::
177 :class:`_events.DialectEvents.do_connect` - event hook that allows
178 full control over DBAPI connection mechanics.
180 :ref:`custom_dbapi_args`
182 :param echo=False: if True, the Engine will log all statements
183 as well as a ``repr()`` of their parameter lists to the default log
184 handler, which defaults to ``sys.stdout`` for output. If set to the
185 string ``"debug"``, result rows will be printed to the standard output
186 as well. The ``echo`` attribute of ``Engine`` can be modified at any
187 time to turn logging on and off; direct control of logging is also
188 available using the standard Python ``logging`` module.
190 .. seealso::
192 :ref:`dbengine_logging` - further detail on how to configure
193 logging.
196 :param echo_pool=False: if True, the connection pool will log
197 informational output such as when connections are invalidated
198 as well as when connections are recycled to the default log handler,
199 which defaults to ``sys.stdout`` for output. If set to the string
200 ``"debug"``, the logging will include pool checkouts and checkins.
201 Direct control of logging is also available using the standard Python
202 ``logging`` module.
204 .. seealso::
206 :ref:`dbengine_logging` - further detail on how to configure
207 logging.
210 :param empty_in_strategy: The SQL compilation strategy to use when
211 rendering an IN or NOT IN expression for :meth:`.ColumnOperators.in_`
212 where the right-hand side
213 is an empty set. This is a string value that may be one of
214 ``static``, ``dynamic``, or ``dynamic_warn``. The ``static``
215 strategy is the default, and an IN comparison to an empty set
216 will generate a simple false expression "1 != 1". The ``dynamic``
217 strategy behaves like that of SQLAlchemy 1.1 and earlier, emitting
218 a false expression of the form "expr != expr", which has the effect
219 of evaluting to NULL in the case of a null expression.
220 ``dynamic_warn`` is the same as ``dynamic``, however also emits a
221 warning when an empty set is encountered; this because the "dynamic"
222 comparison is typically poorly performing on most databases.
224 .. versionadded:: 1.2 Added the ``empty_in_strategy`` setting and
225 additionally defaulted the behavior for empty-set IN comparisons
226 to a static boolean expression.
228 :param encoding: Defaults to ``utf-8``. This is the string
229 encoding used by SQLAlchemy for string encode/decode
230 operations which occur within SQLAlchemy, **outside of
231 the DBAPIs own encoding facilities.**
233 .. note:: The ``encoding`` parameter deals only with in-Python
234 encoding issues that were prevalent with many DBAPIs under Python
235 2. Under Python 3 it is mostly unused. For DBAPIs that require
236 client encoding configurations, such as those of MySQL and Oracle,
237 please consult specific :ref:`dialect documentation
238 <dialect_toplevel>` for details.
240 All modern DBAPIs that work in Python 3 necessarily feature direct
241 support for Python unicode strings. Under Python 2, this was not
242 always the case. For those scenarios where the DBAPI is detected as
243 not supporting a Python ``unicode`` object under Python 2, this
244 encoding is used to determine the source/destination encoding. It is
245 **not used** for those cases where the DBAPI handles unicode directly.
247 To properly configure a system to accommodate Python ``unicode``
248 objects, the DBAPI should be configured to handle unicode to the
249 greatest degree as is appropriate - see the notes on unicode pertaining
250 to the specific target database in use at :ref:`dialect_toplevel`.
252 Areas where string encoding may need to be accommodated
253 outside of the DBAPI, nearly always under **Python 2 only**,
254 include zero or more of:
256 * the values passed to bound parameters, corresponding to
257 the :class:`.Unicode` type or the :class:`.String` type
258 when ``convert_unicode`` is ``True``;
259 * the values returned in result set columns corresponding
260 to the :class:`.Unicode` type or the :class:`.String`
261 type when ``convert_unicode`` is ``True``;
262 * the string SQL statement passed to the DBAPI's
263 ``cursor.execute()`` method;
264 * the string names of the keys in the bound parameter
265 dictionary passed to the DBAPI's ``cursor.execute()``
266 as well as ``cursor.setinputsizes()`` methods;
267 * the string column names retrieved from the DBAPI's
268 ``cursor.description`` attribute.
270 When using Python 3, the DBAPI is required to support all of the above
271 values as Python ``unicode`` objects, which in Python 3 are just known
272 as ``str``. In Python 2, the DBAPI does not specify unicode behavior
273 at all, so SQLAlchemy must make decisions for each of the above values
274 on a per-DBAPI basis - implementations are completely inconsistent in
275 their behavior.
277 :param execution_options: Dictionary execution options which will
278 be applied to all connections. See
279 :meth:`~sqlalchemy.engine.Connection.execution_options`
281 :param hide_parameters: Boolean, when set to True, SQL statement parameters
282 will not be displayed in INFO logging nor will they be formatted into
283 the string representation of :class:`.StatementError` objects.
285 .. versionadded:: 1.3.8
287 .. seealso::
289 :ref:`dbengine_logging` - further detail on how to configure
290 logging.
292 :param implicit_returning=True: When ``True``, a RETURNING-
293 compatible construct, if available, will be used to
294 fetch newly generated primary key values when a single row
295 INSERT statement is emitted with no existing returning()
296 clause. This applies to those backends which support RETURNING
297 or a compatible construct, including PostgreSQL, Firebird, Oracle,
298 Microsoft SQL Server. Set this to ``False`` to disable
299 the automatic usage of RETURNING.
301 :param isolation_level: this string parameter is interpreted by various
302 dialects in order to affect the transaction isolation level of the
303 database connection. The parameter essentially accepts some subset of
304 these string arguments: ``"SERIALIZABLE"``, ``"REPEATABLE READ"``,
305 ``"READ COMMITTED"``, ``"READ UNCOMMITTED"`` and ``"AUTOCOMMIT"``.
306 Behavior here varies per backend, and
307 individual dialects should be consulted directly.
309 Note that the isolation level can also be set on a
310 per-:class:`_engine.Connection` basis as well, using the
311 :paramref:`.Connection.execution_options.isolation_level`
312 feature.
314 .. seealso::
316 :attr:`_engine.Connection.default_isolation_level`
317 - view default level
319 :paramref:`.Connection.execution_options.isolation_level`
320 - set per :class:`_engine.Connection` isolation level
322 :ref:`SQLite Transaction Isolation <sqlite_isolation_level>`
324 :ref:`PostgreSQL Transaction Isolation <postgresql_isolation_level>`
326 :ref:`MySQL Transaction Isolation <mysql_isolation_level>`
328 :ref:`session_transaction_isolation` - for the ORM
330 :param json_deserializer: for dialects that support the
331 :class:`_types.JSON`
332 datatype, this is a Python callable that will convert a JSON string
333 to a Python object. By default, the Python ``json.loads`` function is
334 used.
336 .. versionchanged:: 1.3.7 The SQLite dialect renamed this from
337 ``_json_deserializer``.
339 :param json_serializer: for dialects that support the :class:`_types.JSON`
340 datatype, this is a Python callable that will render a given object
341 as JSON. By default, the Python ``json.dumps`` function is used.
343 .. versionchanged:: 1.3.7 The SQLite dialect renamed this from
344 ``_json_serializer``.
346 :param label_length=None: optional integer value which limits
347 the size of dynamically generated column labels to that many
348 characters. If less than 6, labels are generated as
349 "_(counter)". If ``None``, the value of
350 ``dialect.max_identifier_length``, which may be affected via the
351 :paramref:`_sa.create_engine.max_identifier_length` parameter,
352 is used instead. The value of
353 :paramref:`_sa.create_engine.label_length`
354 may not be larger than that of
355 :paramref:`_sa.create_engine.max_identfier_length`.
357 .. seealso::
359 :paramref:`_sa.create_engine.max_identifier_length`
361 :param listeners: A list of one or more
362 :class:`~sqlalchemy.interfaces.PoolListener` objects which will
363 receive connection pool events.
365 :param logging_name: String identifier which will be used within
366 the "name" field of logging records generated within the
367 "sqlalchemy.engine" logger. Defaults to a hexstring of the
368 object's id.
370 .. seealso::
372 :ref:`dbengine_logging` - further detail on how to configure
373 logging.
377 :param max_identifier_length: integer; override the max_identifier_length
378 determined by the dialect. if ``None`` or zero, has no effect. This
379 is the database's configured maximum number of characters that may be
380 used in a SQL identifier such as a table name, column name, or label
381 name. All dialects determine this value automatically, however in the
382 case of a new database version for which this value has changed but
383 SQLAlchemy's dialect has not been adjusted, the value may be passed
384 here.
386 .. versionadded:: 1.3.9
388 .. seealso::
390 :paramref:`_sa.create_engine.label_length`
392 :param max_overflow=10: the number of connections to allow in
393 connection pool "overflow", that is connections that can be
394 opened above and beyond the pool_size setting, which defaults
395 to five. this is only used with :class:`~sqlalchemy.pool.QueuePool`.
397 :param module=None: reference to a Python module object (the module
398 itself, not its string name). Specifies an alternate DBAPI module to
399 be used by the engine's dialect. Each sub-dialect references a
400 specific DBAPI which will be imported before first connect. This
401 parameter causes the import to be bypassed, and the given module to
402 be used instead. Can be used for testing of DBAPIs as well as to
403 inject "mock" DBAPI implementations into the :class:`_engine.Engine`.
405 :param paramstyle=None: The `paramstyle <http://legacy.python.org/dev/peps/pep-0249/#paramstyle>`_
406 to use when rendering bound parameters. This style defaults to the
407 one recommended by the DBAPI itself, which is retrieved from the
408 ``.paramstyle`` attribute of the DBAPI. However, most DBAPIs accept
409 more than one paramstyle, and in particular it may be desirable
410 to change a "named" paramstyle into a "positional" one, or vice versa.
411 When this attribute is passed, it should be one of the values
412 ``"qmark"``, ``"numeric"``, ``"named"``, ``"format"`` or
413 ``"pyformat"``, and should correspond to a parameter style known
414 to be supported by the DBAPI in use.
416 :param pool=None: an already-constructed instance of
417 :class:`~sqlalchemy.pool.Pool`, such as a
418 :class:`~sqlalchemy.pool.QueuePool` instance. If non-None, this
419 pool will be used directly as the underlying connection pool
420 for the engine, bypassing whatever connection parameters are
421 present in the URL argument. For information on constructing
422 connection pools manually, see :ref:`pooling_toplevel`.
424 :param poolclass=None: a :class:`~sqlalchemy.pool.Pool`
425 subclass, which will be used to create a connection pool
426 instance using the connection parameters given in the URL. Note
427 this differs from ``pool`` in that you don't actually
428 instantiate the pool in this case, you just indicate what type
429 of pool to be used.
431 :param pool_logging_name: String identifier which will be used within
432 the "name" field of logging records generated within the
433 "sqlalchemy.pool" logger. Defaults to a hexstring of the object's
434 id.
437 .. seealso::
439 :ref:`dbengine_logging` - further detail on how to configure
440 logging.
443 :param pool_pre_ping: boolean, if True will enable the connection pool
444 "pre-ping" feature that tests connections for liveness upon
445 each checkout.
447 .. versionadded:: 1.2
449 .. seealso::
451 :ref:`pool_disconnects_pessimistic`
453 :param pool_size=5: the number of connections to keep open
454 inside the connection pool. This used with
455 :class:`~sqlalchemy.pool.QueuePool` as
456 well as :class:`~sqlalchemy.pool.SingletonThreadPool`. With
457 :class:`~sqlalchemy.pool.QueuePool`, a ``pool_size`` setting
458 of 0 indicates no limit; to disable pooling, set ``poolclass`` to
459 :class:`~sqlalchemy.pool.NullPool` instead.
461 :param pool_recycle=-1: this setting causes the pool to recycle
462 connections after the given number of seconds has passed. It
463 defaults to -1, or no timeout. For example, setting to 3600
464 means connections will be recycled after one hour. Note that
465 MySQL in particular will disconnect automatically if no
466 activity is detected on a connection for eight hours (although
467 this is configurable with the MySQLDB connection itself and the
468 server configuration as well).
470 .. seealso::
472 :ref:`pool_setting_recycle`
474 :param pool_reset_on_return='rollback': set the
475 :paramref:`_pool.Pool.reset_on_return` parameter of the underlying
476 :class:`_pool.Pool` object, which can be set to the values
477 ``"rollback"``, ``"commit"``, or ``None``.
479 .. seealso::
481 :paramref:`_pool.Pool.reset_on_return`
483 :param pool_timeout=30: number of seconds to wait before giving
484 up on getting a connection from the pool. This is only used
485 with :class:`~sqlalchemy.pool.QueuePool`.
487 :param pool_use_lifo=False: use LIFO (last-in-first-out) when retrieving
488 connections from :class:`.QueuePool` instead of FIFO
489 (first-in-first-out). Using LIFO, a server-side timeout scheme can
490 reduce the number of connections used during non- peak periods of
491 use. When planning for server-side timeouts, ensure that a recycle or
492 pre-ping strategy is in use to gracefully handle stale connections.
494 .. versionadded:: 1.3
496 .. seealso::
498 :ref:`pool_use_lifo`
500 :ref:`pool_disconnects`
502 :param plugins: string list of plugin names to load. See
503 :class:`.CreateEnginePlugin` for background.
505 .. versionadded:: 1.2.3
507 :param strategy='plain': selects alternate engine implementations.
508 Currently available are:
510 * the ``threadlocal`` strategy, which is described in
511 :ref:`threadlocal_strategy`;
512 * the ``mock`` strategy, which dispatches all statement
513 execution to a function passed as the argument ``executor``.
514 See `example in the FAQ
515 <http://docs.sqlalchemy.org/en/latest/faq/metadata_schema.html#how-can-i-get-the-create-table-drop-table-output-as-a-string>`_.
517 :param executor=None: a function taking arguments
518 ``(sql, *multiparams, **params)``, to which the ``mock`` strategy will
519 dispatch all statement execution. Used only by ``strategy='mock'``.
521 """ # noqa
523 strategy = kwargs.pop("strategy", default_strategy)
524 strategy = strategies.strategies[strategy]
525 return strategy.create(*args, **kwargs)
528def engine_from_config(configuration, prefix="sqlalchemy.", **kwargs):
529 """Create a new Engine instance using a configuration dictionary.
531 The dictionary is typically produced from a config file.
533 The keys of interest to ``engine_from_config()`` should be prefixed, e.g.
534 ``sqlalchemy.url``, ``sqlalchemy.echo``, etc. The 'prefix' argument
535 indicates the prefix to be searched for. Each matching key (after the
536 prefix is stripped) is treated as though it were the corresponding keyword
537 argument to a :func:`_sa.create_engine` call.
539 The only required key is (assuming the default prefix) ``sqlalchemy.url``,
540 which provides the :ref:`database URL <database_urls>`.
542 A select set of keyword arguments will be "coerced" to their
543 expected type based on string values. The set of arguments
544 is extensible per-dialect using the ``engine_config_types`` accessor.
546 :param configuration: A dictionary (typically produced from a config file,
547 but this is not a requirement). Items whose keys start with the value
548 of 'prefix' will have that prefix stripped, and will then be passed to
549 :ref:`create_engine`.
551 :param prefix: Prefix to match and then strip from keys
552 in 'configuration'.
554 :param kwargs: Each keyword argument to ``engine_from_config()`` itself
555 overrides the corresponding item taken from the 'configuration'
556 dictionary. Keyword arguments should *not* be prefixed.
558 """
560 options = dict(
561 (key[len(prefix) :], configuration[key])
562 for key in configuration
563 if key.startswith(prefix)
564 )
565 options["_coerce_config"] = True
566 options.update(kwargs)
567 url = options.pop("url")
568 return create_engine(url, **options)
571__all__ = ("create_engine", "engine_from_config")