Coverage for /home/martinb/.local/share/virtualenvs/camcops/lib/python3.6/site-packages/sqlalchemy/engine/__init__.py : 86%

Hot-keys on this page
r m x p 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-2020 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 URL as the
89 first positional argument, usually a string
90 that indicates database dialect and connection arguments::
93 engine = create_engine("postgresql://scott:tiger@localhost/test")
95 Additional keyword arguments may then follow it which
96 establish various options on the resulting :class:`_engine.Engine`
97 and its underlying :class:`.Dialect` and :class:`_pool.Pool`
98 constructs::
100 engine = create_engine("mysql://scott:tiger@hostname/dbname",
101 encoding='latin1', echo=True)
103 The string form of the URL is
104 ``dialect[+driver]://user:password@host/dbname[?key=value..]``, where
105 ``dialect`` is a database name such as ``mysql``, ``oracle``,
106 ``postgresql``, etc., and ``driver`` the name of a DBAPI, such as
107 ``psycopg2``, ``pyodbc``, ``cx_oracle``, etc. Alternatively,
108 the URL can be an instance of :class:`~sqlalchemy.engine.url.URL`.
110 ``**kwargs`` takes a wide variety of options which are routed
111 towards their appropriate components. Arguments may be specific to
112 the :class:`_engine.Engine`, the underlying :class:`.Dialect`,
113 as well as the
114 :class:`_pool.Pool`. Specific dialects also accept keyword arguments that
115 are unique to that dialect. Here, we describe the parameters
116 that are common to most :func:`_sa.create_engine()` usage.
118 Once established, the newly resulting :class:`_engine.Engine` will
119 request a connection from the underlying :class:`_pool.Pool` once
120 :meth:`_engine.Engine.connect` is called, or a method which depends on it
121 such as :meth:`_engine.Engine.execute` is invoked. The
122 :class:`_pool.Pool` in turn
123 will establish the first actual DBAPI connection when this request
124 is received. The :func:`_sa.create_engine` call itself does **not**
125 establish any actual DBAPI connections directly.
127 .. seealso::
129 :doc:`/core/engines`
131 :doc:`/dialects/index`
133 :ref:`connections_toplevel`
135 :param case_sensitive=True: if False, result column names
136 will match in a case-insensitive fashion, that is,
137 ``row['SomeColumn']``.
139 :param connect_args: a dictionary of options which will be
140 passed directly to the DBAPI's ``connect()`` method as
141 additional keyword arguments. See the example
142 at :ref:`custom_dbapi_args`.
144 :param convert_unicode=False: if set to True, causes
145 all :class:`.String` datatypes to act as though the
146 :paramref:`.String.convert_unicode` flag has been set to ``True``,
147 regardless of a setting of ``False`` on an individual :class:`.String`
148 type. This has the effect of causing all :class:`.String` -based
149 columns to accommodate Python Unicode objects directly as though the
150 datatype were the :class:`.Unicode` type.
152 .. deprecated:: 1.3
154 The :paramref:`_sa.create_engine.convert_unicode` parameter
155 is deprecated and will be removed in a future release.
156 All modern DBAPIs now support Python Unicode directly and this
157 parameter is unnecessary.
159 :param creator: a callable which returns a DBAPI connection.
160 This creation function will be passed to the underlying
161 connection pool and will be used to create all new database
162 connections. Usage of this function causes connection
163 parameters specified in the URL argument to be bypassed.
165 This hook is not as flexible as the newer
166 :class:`_events.DialectEvents.do_connect` hook which allows complete
167 control over how a connection is made to the database, given the full
168 set of URL arguments and state beforehand.
170 .. seealso::
172 :class:`_events.DialectEvents.do_connect` - event hook that allows
173 full control over DBAPI connection mechanics.
175 :ref:`custom_dbapi_args`
177 :param echo=False: if True, the Engine will log all statements
178 as well as a ``repr()`` of their parameter lists to the default log
179 handler, which defaults to ``sys.stdout`` for output. If set to the
180 string ``"debug"``, result rows will be printed to the standard output
181 as well. The ``echo`` attribute of ``Engine`` can be modified at any
182 time to turn logging on and off; direct control of logging is also
183 available using the standard Python ``logging`` module.
185 .. seealso::
187 :ref:`dbengine_logging` - further detail on how to configure
188 logging.
190 :param echo_pool=False: if True, the connection pool will log
191 informational output such as when connections are invalidated
192 as well as when connections are recycled to the default log handler,
193 which defaults to ``sys.stdout`` for output. If set to the string
194 ``"debug"``, the logging will include pool checkouts and checkins.
195 Direct control of logging is also available using the standard Python
196 ``logging`` module.
198 .. seealso::
200 :ref:`dbengine_logging` - further detail on how to configure
201 logging.
204 :param empty_in_strategy: The SQL compilation strategy to use when
205 rendering an IN or NOT IN expression for :meth:`.ColumnOperators.in_`
206 where the right-hand side
207 is an empty set. This is a string value that may be one of
208 ``static``, ``dynamic``, or ``dynamic_warn``. The ``static``
209 strategy is the default, and an IN comparison to an empty set
210 will generate a simple false expression "1 != 1". The ``dynamic``
211 strategy behaves like that of SQLAlchemy 1.1 and earlier, emitting
212 a false expression of the form "expr != expr", which has the effect
213 of evaluting to NULL in the case of a null expression.
214 ``dynamic_warn`` is the same as ``dynamic``, however also emits a
215 warning when an empty set is encountered; this because the "dynamic"
216 comparison is typically poorly performing on most databases.
218 .. versionadded:: 1.2 Added the ``empty_in_strategy`` setting and
219 additionally defaulted the behavior for empty-set IN comparisons
220 to a static boolean expression.
222 :param encoding: Defaults to ``utf-8``. This is the string
223 encoding used by SQLAlchemy for string encode/decode
224 operations which occur within SQLAlchemy, **outside of
225 the DBAPIs own encoding facilities.**
227 .. note:: The ``encoding`` parameter deals only with in-Python
228 encoding issues that were prevalent with many DBAPIs under Python
229 2. Under Python 3 it is mostly unused. For DBAPIs that require
230 client encoding configurations, such as those of MySQL and Oracle,
231 please consult specific :ref:`dialect documentation
232 <dialect_toplevel>` for details.
234 All modern DBAPIs that work in Python 3 necessarily feature direct
235 support for Python unicode strings. Under Python 2, this was not
236 always the case. For those scenarios where the DBAPI is detected as
237 not supporting a Python ``unicode`` object under Python 2, this
238 encoding is used to determine the source/destination encoding. It is
239 **not used** for those cases where the DBAPI handles unicode directly.
241 To properly configure a system to accommodate Python ``unicode``
242 objects, the DBAPI should be configured to handle unicode to the
243 greatest degree as is appropriate - see the notes on unicode pertaining
244 to the specific target database in use at :ref:`dialect_toplevel`.
246 Areas where string encoding may need to be accommodated
247 outside of the DBAPI, nearly always under **Python 2 only**,
248 include zero or more of:
250 * the values passed to bound parameters, corresponding to
251 the :class:`.Unicode` type or the :class:`.String` type
252 when ``convert_unicode`` is ``True``;
253 * the values returned in result set columns corresponding
254 to the :class:`.Unicode` type or the :class:`.String`
255 type when ``convert_unicode`` is ``True``;
256 * the string SQL statement passed to the DBAPI's
257 ``cursor.execute()`` method;
258 * the string names of the keys in the bound parameter
259 dictionary passed to the DBAPI's ``cursor.execute()``
260 as well as ``cursor.setinputsizes()`` methods;
261 * the string column names retrieved from the DBAPI's
262 ``cursor.description`` attribute.
264 When using Python 3, the DBAPI is required to support all of the above
265 values as Python ``unicode`` objects, which in Python 3 are just known
266 as ``str``. In Python 2, the DBAPI does not specify unicode behavior
267 at all, so SQLAlchemy must make decisions for each of the above values
268 on a per-DBAPI basis - implementations are completely inconsistent in
269 their behavior.
271 :param execution_options: Dictionary execution options which will
272 be applied to all connections. See
273 :meth:`~sqlalchemy.engine.Connection.execution_options`
275 :param hide_parameters: Boolean, when set to True, SQL statement parameters
276 will not be displayed in INFO logging nor will they be formatted into
277 the string representation of :class:`.StatementError` objects.
279 .. versionadded:: 1.3.8
281 :param implicit_returning=True: When ``True``, a RETURNING-
282 compatible construct, if available, will be used to
283 fetch newly generated primary key values when a single row
284 INSERT statement is emitted with no existing returning()
285 clause. This applies to those backends which support RETURNING
286 or a compatible construct, including PostgreSQL, Firebird, Oracle,
287 Microsoft SQL Server. Set this to ``False`` to disable
288 the automatic usage of RETURNING.
290 :param isolation_level: this string parameter is interpreted by various
291 dialects in order to affect the transaction isolation level of the
292 database connection. The parameter essentially accepts some subset of
293 these string arguments: ``"SERIALIZABLE"``, ``"REPEATABLE_READ"``,
294 ``"READ_COMMITTED"``, ``"READ_UNCOMMITTED"`` and ``"AUTOCOMMIT"``.
295 Behavior here varies per backend, and
296 individual dialects should be consulted directly.
298 Note that the isolation level can also be set on a
299 per-:class:`_engine.Connection` basis as well, using the
300 :paramref:`.Connection.execution_options.isolation_level`
301 feature.
303 .. seealso::
305 :attr:`_engine.Connection.default_isolation_level`
306 - view default level
308 :paramref:`.Connection.execution_options.isolation_level`
309 - set per :class:`_engine.Connection` isolation level
311 :ref:`SQLite Transaction Isolation <sqlite_isolation_level>`
313 :ref:`PostgreSQL Transaction Isolation <postgresql_isolation_level>`
315 :ref:`MySQL Transaction Isolation <mysql_isolation_level>`
317 :ref:`session_transaction_isolation` - for the ORM
319 :param json_deserializer: for dialects that support the
320 :class:`_types.JSON`
321 datatype, this is a Python callable that will convert a JSON string
322 to a Python object. By default, the Python ``json.loads`` function is
323 used.
325 .. versionchanged:: 1.3.7 The SQLite dialect renamed this from
326 ``_json_deserializer``.
328 :param json_serializer: for dialects that support the :class:`_types.JSON`
329 datatype, this is a Python callable that will render a given object
330 as JSON. By default, the Python ``json.dumps`` function is used.
332 .. versionchanged:: 1.3.7 The SQLite dialect renamed this from
333 ``_json_serializer``.
335 :param label_length=None: optional integer value which limits
336 the size of dynamically generated column labels to that many
337 characters. If less than 6, labels are generated as
338 "_(counter)". If ``None``, the value of
339 ``dialect.max_identifier_length``, which may be affected via the
340 :paramref:`_sa.create_engine.max_identifier_length` parameter,
341 is used instead. The value of
342 :paramref:`_sa.create_engine.label_length`
343 may not be larger than that of
344 :paramref:`_sa.create_engine.max_identfier_length`.
346 .. seealso::
348 :paramref:`_sa.create_engine.max_identifier_length`
350 :param listeners: A list of one or more
351 :class:`~sqlalchemy.interfaces.PoolListener` objects which will
352 receive connection pool events.
354 :param logging_name: String identifier which will be used within
355 the "name" field of logging records generated within the
356 "sqlalchemy.engine" logger. Defaults to a hexstring of the
357 object's id.
359 :param max_identifier_length: integer; override the max_identifier_length
360 determined by the dialect. if ``None`` or zero, has no effect. This
361 is the database's configured maximum number of characters that may be
362 used in a SQL identifier such as a table name, column name, or label
363 name. All dialects determine this value automatically, however in the
364 case of a new database version for which this value has changed but
365 SQLAlchemy's dialect has not been adjusted, the value may be passed
366 here.
368 .. versionadded:: 1.3.9
370 .. seealso::
372 :paramref:`_sa.create_engine.label_length`
374 :param max_overflow=10: the number of connections to allow in
375 connection pool "overflow", that is connections that can be
376 opened above and beyond the pool_size setting, which defaults
377 to five. this is only used with :class:`~sqlalchemy.pool.QueuePool`.
379 :param module=None: reference to a Python module object (the module
380 itself, not its string name). Specifies an alternate DBAPI module to
381 be used by the engine's dialect. Each sub-dialect references a
382 specific DBAPI which will be imported before first connect. This
383 parameter causes the import to be bypassed, and the given module to
384 be used instead. Can be used for testing of DBAPIs as well as to
385 inject "mock" DBAPI implementations into the :class:`_engine.Engine`.
387 :param paramstyle=None: The `paramstyle <http://legacy.python.org/dev/peps/pep-0249/#paramstyle>`_
388 to use when rendering bound parameters. This style defaults to the
389 one recommended by the DBAPI itself, which is retrieved from the
390 ``.paramstyle`` attribute of the DBAPI. However, most DBAPIs accept
391 more than one paramstyle, and in particular it may be desirable
392 to change a "named" paramstyle into a "positional" one, or vice versa.
393 When this attribute is passed, it should be one of the values
394 ``"qmark"``, ``"numeric"``, ``"named"``, ``"format"`` or
395 ``"pyformat"``, and should correspond to a parameter style known
396 to be supported by the DBAPI in use.
398 :param pool=None: an already-constructed instance of
399 :class:`~sqlalchemy.pool.Pool`, such as a
400 :class:`~sqlalchemy.pool.QueuePool` instance. If non-None, this
401 pool will be used directly as the underlying connection pool
402 for the engine, bypassing whatever connection parameters are
403 present in the URL argument. For information on constructing
404 connection pools manually, see :ref:`pooling_toplevel`.
406 :param poolclass=None: a :class:`~sqlalchemy.pool.Pool`
407 subclass, which will be used to create a connection pool
408 instance using the connection parameters given in the URL. Note
409 this differs from ``pool`` in that you don't actually
410 instantiate the pool in this case, you just indicate what type
411 of pool to be used.
413 :param pool_logging_name: String identifier which will be used within
414 the "name" field of logging records generated within the
415 "sqlalchemy.pool" logger. Defaults to a hexstring of the object's
416 id.
418 :param pool_pre_ping: boolean, if True will enable the connection pool
419 "pre-ping" feature that tests connections for liveness upon
420 each checkout.
422 .. versionadded:: 1.2
424 .. seealso::
426 :ref:`pool_disconnects_pessimistic`
428 :param pool_size=5: the number of connections to keep open
429 inside the connection pool. This used with
430 :class:`~sqlalchemy.pool.QueuePool` as
431 well as :class:`~sqlalchemy.pool.SingletonThreadPool`. With
432 :class:`~sqlalchemy.pool.QueuePool`, a ``pool_size`` setting
433 of 0 indicates no limit; to disable pooling, set ``poolclass`` to
434 :class:`~sqlalchemy.pool.NullPool` instead.
436 :param pool_recycle=-1: this setting causes the pool to recycle
437 connections after the given number of seconds has passed. It
438 defaults to -1, or no timeout. For example, setting to 3600
439 means connections will be recycled after one hour. Note that
440 MySQL in particular will disconnect automatically if no
441 activity is detected on a connection for eight hours (although
442 this is configurable with the MySQLDB connection itself and the
443 server configuration as well).
445 .. seealso::
447 :ref:`pool_setting_recycle`
449 :param pool_reset_on_return='rollback': set the
450 :paramref:`_pool.Pool.reset_on_return` parameter of the underlying
451 :class:`_pool.Pool` object, which can be set to the values
452 ``"rollback"``, ``"commit"``, or ``None``.
454 .. seealso::
456 :paramref:`_pool.Pool.reset_on_return`
458 :param pool_timeout=30: number of seconds to wait before giving
459 up on getting a connection from the pool. This is only used
460 with :class:`~sqlalchemy.pool.QueuePool`.
462 :param pool_use_lifo=False: use LIFO (last-in-first-out) when retrieving
463 connections from :class:`.QueuePool` instead of FIFO
464 (first-in-first-out). Using LIFO, a server-side timeout scheme can
465 reduce the number of connections used during non- peak periods of
466 use. When planning for server-side timeouts, ensure that a recycle or
467 pre-ping strategy is in use to gracefully handle stale connections.
469 .. versionadded:: 1.3
471 .. seealso::
473 :ref:`pool_use_lifo`
475 :ref:`pool_disconnects`
477 :param plugins: string list of plugin names to load. See
478 :class:`.CreateEnginePlugin` for background.
480 .. versionadded:: 1.2.3
482 :param strategy='plain': selects alternate engine implementations.
483 Currently available are:
485 * the ``threadlocal`` strategy, which is described in
486 :ref:`threadlocal_strategy`;
487 * the ``mock`` strategy, which dispatches all statement
488 execution to a function passed as the argument ``executor``.
489 See `example in the FAQ
490 <http://docs.sqlalchemy.org/en/latest/faq/metadata_schema.html#how-can-i-get-the-create-table-drop-table-output-as-a-string>`_.
492 :param executor=None: a function taking arguments
493 ``(sql, *multiparams, **params)``, to which the ``mock`` strategy will
494 dispatch all statement execution. Used only by ``strategy='mock'``.
496 """ # noqa
498 strategy = kwargs.pop("strategy", default_strategy)
499 strategy = strategies.strategies[strategy]
500 return strategy.create(*args, **kwargs)
503def engine_from_config(configuration, prefix="sqlalchemy.", **kwargs):
504 """Create a new Engine instance using a configuration dictionary.
506 The dictionary is typically produced from a config file.
508 The keys of interest to ``engine_from_config()`` should be prefixed, e.g.
509 ``sqlalchemy.url``, ``sqlalchemy.echo``, etc. The 'prefix' argument
510 indicates the prefix to be searched for. Each matching key (after the
511 prefix is stripped) is treated as though it were the corresponding keyword
512 argument to a :func:`_sa.create_engine` call.
514 The only required key is (assuming the default prefix) ``sqlalchemy.url``,
515 which provides the :ref:`database URL <database_urls>`.
517 A select set of keyword arguments will be "coerced" to their
518 expected type based on string values. The set of arguments
519 is extensible per-dialect using the ``engine_config_types`` accessor.
521 :param configuration: A dictionary (typically produced from a config file,
522 but this is not a requirement). Items whose keys start with the value
523 of 'prefix' will have that prefix stripped, and will then be passed to
524 :ref:`create_engine`.
526 :param prefix: Prefix to match and then strip from keys
527 in 'configuration'.
529 :param kwargs: Each keyword argument to ``engine_from_config()`` itself
530 overrides the corresponding item taken from the 'configuration'
531 dictionary. Keyword arguments should *not* be prefixed.
533 """
535 options = dict(
536 (key[len(prefix) :], configuration[key])
537 for key in configuration
538 if key.startswith(prefix)
539 )
540 options["_coerce_config"] = True
541 options.update(kwargs)
542 url = options.pop("url")
543 return create_engine(url, **options)
546__all__ = ("create_engine", "engine_from_config")