Project Versions

The Engine is the starting point for any SQLAlchemy application. It’s
“home base” for the actual database and its DBAPI, delivered to the SQLAlchemy
application through a connection pool and a Dialect, which describes how
to talk to a specific kind of database/DBAPI combination.

The general structure can be illustrated as follows:

Where above, an Engine references both a
Dialect and a Pool,
which together interpret the DBAPI’s module functions as well as the behavior
of the database.

Creating an engine is just a matter of issuing a single call,
create_engine():

The above engine creates a Dialect object tailored towards
PostgreSQL, as well as a Pool object which will establish a DBAPI
connection at localhost:5432 when a connection request is first received.
Note that the Engine and its underlying Pool do not
establish the first actual DBAPI connection until the Engine.connect()
method is called, or an operation which is dependent on this method such as
Engine.execute() is invoked. In this way, Engine and
Pool can be said to have a lazy initialization behavior.

The Engine, once created, can either be used directly to interact with the database,
or can be passed to a Session object to work with the ORM. This section
covers the details of configuring an Engine. The next section, Working with Engines and Connections,
will detail the usage API of the Engine and similar, typically for non-ORM
applications.

SQLAlchemy includes many Dialect implementations for various
backends. Dialects for the most common databases are included with SQLAlchemy; a handful
of others require an additional install of a separate dialect.

See the section Dialects for information on the various backends available.

The create_engine() function produces an Engine object based
on a URL. These URLs follow RFC-1738, and usually can include username, password,
hostname, database name as well as optional keyword arguments for additional configuration.
In some cases a file path is accepted, and in others a “data source name” replaces
the “host” and “database” portions. The typical form of a database URL is:

dialect+driver://username:password@host:port/database

Dialect names include the identifying name of the SQLAlchemy dialect,
a name such as sqlite, mysql, postgresql, oracle, or mssql.
The drivername is the name of the DBAPI to be used to connect to
the database using all lowercase letters. If not specified, a “default” DBAPI
will be imported if available - this default is typically the most widely
known driver available for that backend.

Examples for common connection styles follow below. For a full index of
detailed information on all included dialects as well as links to third-party dialects, see
Dialects.

The string form of the URL is
dialect[+driver]://user:password@host/dbname[?key=value..], where
dialect is a database name such as mysql, oracle,
postgresql, etc., and driver the name of a DBAPI, such as
psycopg2, pyodbc, cx_oracle, etc. Alternatively,
the URL can be an instance of URL.

**kwargs takes a wide variety of options which are routed
towards their appropriate components. Arguments may be specific to
the Engine, the underlying Dialect, as well as the
Pool. Specific dialects also accept keyword arguments that
are unique to that dialect. Here, we describe the parameters
that are common to most create_engine() usage.

Once established, the newly resulting Engine will
request a connection from the underlying Pool once
Engine.connect() is called, or a method which depends on it
such as Engine.execute() is invoked. The Pool in turn
will establish the first actual DBAPI connection when this request
is received. The create_engine() call itself does not
establish any actual DBAPI connections directly.

if set to True, sets
the default behavior of convert_unicode on the
String type to True, regardless
of a setting of False on an individual
String type, thus causing all String
-based columns
to accommodate Python unicode objects. This flag
is useful as an engine-wide setting when using a
DBAPI that does not natively support Python
unicode objects and raises an error when
one is received (such as pyodbc with FreeTDS).

creator¶ – a callable which returns a DBAPI connection.
This creation function will be passed to the underlying
connection pool and will be used to create all new database
connections. Usage of this function causes connection
parameters specified in the URL argument to be bypassed.

echo=False¶ – if True, the Engine will log all statements
as well as a repr() of their parameter lists to the engines
logger, which defaults to sys.stdout. The echo attribute of
Engine can be modified at any time to turn logging on and
off. If set to the string "debug", result rows will be
printed to the standard output as well. This flag ultimately
controls a Python logger; see Configuring Logging for
information on how to configure logging directly.

echo_pool=False¶ – if True, the connection pool will log
all checkouts/checkins to the logging stream, which defaults to
sys.stdout. This flag ultimately controls a Python logger; see
Configuring Logging for information on how to configure logging
directly.

Defaults to utf-8. This is the string
encoding used by SQLAlchemy for string encode/decode
operations which occur within SQLAlchemy, outside of
the DBAPI. Most modern DBAPIs feature some degree of
direct support for Python unicode objects,
what you see in Python 2 as a string of the form
u'somestring'. For those scenarios where the
DBAPI is detected as not supporting a Python unicode
object, this encoding is used to determine the
source/destination encoding. It is not used
for those cases where the DBAPI handles unicode
directly.

To properly configure a system to accommodate Python
unicode objects, the DBAPI should be
configured to handle unicode to the greatest
degree as is appropriate - see
the notes on unicode pertaining to the specific
target database in use at Dialects.

Areas where string encoding may need to be accommodated
outside of the DBAPI include zero or more of:

the values passed to bound parameters, corresponding to
the Unicode type or the String type
when convert_unicode is True;

the values returned in result set columns corresponding
to the Unicode type or the String
type when convert_unicode is True;

the string SQL statement passed to the DBAPI’s
cursor.execute() method;

the string names of the keys in the bound parameter
dictionary passed to the DBAPI’s cursor.execute()
as well as cursor.setinputsizes() methods;

the string column names retrieved from the DBAPI’s
cursor.description attribute.

When using Python 3, the DBAPI is required to support
all of the above values as Python unicode objects,
which in Python 3 are just known as str. In Python 2,
the DBAPI does not specify unicode behavior at all,
so SQLAlchemy must make decisions for each of the above
values on a per-DBAPI basis - implementations are
completely inconsistent in their behavior.

execution_options¶ – Dictionary execution options which will
be applied to all connections. See
execution_options()

implicit_returning=True¶ – When True, a RETURNING-
compatible construct, if available, will be used to
fetch newly generated primary key values when a single row
INSERT statement is emitted with no existing returning()
clause. This applies to those backends which support RETURNING
or a compatible construct, including Postgresql, Firebird, Oracle,
Microsoft SQL Server. Set this to False to disable
the automatic usage of RETURNING.

this string parameter is interpreted by various
dialects in order to affect the transaction isolation level of the
database connection. The parameter essentially accepts some subset of
these string arguments: "SERIALIZABLE", "REPEATABLE_READ",
"READ_COMMITTED", "READ_UNCOMMITTED" and "AUTOCOMMIT".
Behavior here varies per backend, and
individual dialects should be consulted directly.

label_length=None¶ – optional integer value which limits
the size of dynamically generated column labels to that many
characters. If less than 6, labels are generated as
“_(counter)”. If None, the value of
dialect.max_identifier_length is used instead.

listeners¶ – A list of one or more
PoolListener objects which will
receive connection pool events.

logging_name¶ – String identifier which will be used within
the “name” field of logging records generated within the
“sqlalchemy.engine” logger. Defaults to a hexstring of the
object’s id.

max_overflow=10¶ – the number of connections to allow in
connection pool “overflow”, that is connections that can be
opened above and beyond the pool_size setting, which defaults
to five. this is only used with QueuePool.

module=None¶ – reference to a Python module object (the module
itself, not its string name). Specifies an alternate DBAPI module to
be used by the engine’s dialect. Each sub-dialect references a
specific DBAPI which will be imported before first connect. This
parameter causes the import to be bypassed, and the given module to
be used instead. Can be used for testing of DBAPIs as well as to
inject “mock” DBAPI implementations into the Engine.

paramstyle=None¶ – The paramstyle
to use when rendering bound parameters. This style defaults to the
one recommended by the DBAPI itself, which is retrieved from the
.paramstyle attribute of the DBAPI. However, most DBAPIs accept
more than one paramstyle, and in particular it may be desirable
to change a “named” paramstyle into a “positional” one, or vice versa.
When this attribute is passed, it should be one of the values
"qmark", "numeric", "named", "format" or
"pyformat", and should correspond to a parameter style known
to be supported by the DBAPI in use.

pool=None¶ – an already-constructed instance of
Pool, such as a
QueuePool instance. If non-None, this
pool will be used directly as the underlying connection pool
for the engine, bypassing whatever connection parameters are
present in the URL argument. For information on constructing
connection pools manually, see Connection Pooling.

poolclass=None¶ – a Pool
subclass, which will be used to create a connection pool
instance using the connection parameters given in the URL. Note
this differs from pool in that you don’t actually
instantiate the pool in this case, you just indicate what type
of pool to be used.

pool_logging_name¶ – String identifier which will be used within
the “name” field of logging records generated within the
“sqlalchemy.pool” logger. Defaults to a hexstring of the object’s
id.

pool_size=5¶ – the number of connections to keep open
inside the connection pool. This used with
QueuePool as
well as SingletonThreadPool. With
QueuePool, a pool_size setting
of 0 indicates no limit; to disable pooling, set poolclass to
NullPool instead.

pool_recycle=-1¶ – this setting causes the pool to recycle
connections after the given number of seconds has passed. It
defaults to -1, or no timeout. For example, setting to 3600
means connections will be recycled after one hour. Note that
MySQL in particular will disconnect automatically if no
activity is detected on a connection for eight hours (although
this is configurable with the MySQLDB connection itself and the
server configuration as well).

This object is suitable to be passed directly to a
create_engine() call. The fields of the URL are parsed
from a string by the make_url() function. the string
format of the URL is an RFC-1738-style string.

All initialization parameters are available as public attributes.

Parameters:

drivername¶ – the name of the database backend.
This name will correspond to a module in sqlalchemy/databases
or a third party plug-in.

Returns attributes of this url (host, database, username,
password, port) as a plain dictionary. The attribute names are
used as the keys by default. Unset or false attributes are omitted
from the final dictionary.

The Engine will ask the connection pool for a
connection when the connect() or execute() methods are called. The
default connection pool, QueuePool, will open connections to the
database on an as-needed basis. As concurrent statements are executed,
QueuePool will grow its pool of connections to a
default size of five, and will allow a default “overflow” of ten. Since the
Engine is essentially “home base” for the
connection pool, it follows that you should keep a single
Engine per database established within an
application, rather than creating a new one for each connection.

Note

QueuePool is not used by default for SQLite engines. See
SQLite for details on SQLite connection pool usage.

Custom arguments used when issuing the connect() call to the underlying
DBAPI may be issued in three distinct ways. String-based arguments can be
passed directly from the URL string as query arguments:

If SQLAlchemy’s database connector is aware of a particular query argument, it
may convert its type from string to its proper type.

create_engine() also takes an argument connect_args which is an additional dictionary that will be passed to connect(). This can be used when arguments of a type other than string are required, and SQLAlchemy’s database connector has no type conversion logic present for that parameter:

Python’s standard logging module is used to
implement informational and debug log output with SQLAlchemy. This allows
SQLAlchemy’s logging to integrate in a standard way with other applications
and libraries. The echo and echo_pool flags that are present on
create_engine(), as well as the echo_uow flag used on
Session, all interact with regular loggers.

This section assumes familiarity with the above linked logging module. All
logging performed by SQLAlchemy exists underneath the sqlalchemy
namespace, as used by logging.getLogger('sqlalchemy'). When logging has
been configured (i.e. such as via logging.basicConfig()), the general
namespace of SA loggers that can be turned on is as follows:

sqlalchemy.engine - controls SQL echoing. set to logging.INFO for SQL query output, logging.DEBUG for query + result set output.

sqlalchemy.dialects - controls custom logging for SQL dialects. See the documentation of individual dialects for details.

By default, the log level is set to logging.WARN within the entire
sqlalchemy namespace so that no log operations occur, even within an
application that has logging enabled otherwise.

The echo flags present as keyword arguments to
create_engine() and others as well as the echo property
on Engine, when set to True, will first
attempt to ensure that logging is enabled. Unfortunately, the logging
module provides no way of determining if output has already been configured
(note we are referring to if a logging configuration has been set up, not just
that the logging level is set). For this reason, any echo=True flags will
result in a call to logging.basicConfig() using sys.stdout as the
destination. It also sets up a default format using the level name, timestamp,
and logger name. Note that this configuration has the affect of being
configured in addition to any existing logger configurations. Therefore,
when using Python logging, ensure all echo flags are set to False at all
times, to avoid getting duplicate log lines.

The logger name of instance such as an Engine
or Pool defaults to using a truncated hex identifier
string. To set this to a specific name, use the “logging_name” and
“pool_logging_name” keyword arguments with sqlalchemy.create_engine().

Note

The SQLAlchemy Engine conserves Python function call overhead
by only emitting log statements when the current logging level is detected
as logging.INFO or logging.DEBUG. It only checks this level when
a new connection is procured from the connection pool. Therefore when
changing the logging configuration for an already-running application, any
Connection that’s currently active, or more commonly a
Session object that’s active in a transaction, won’t log any
SQL according to the new configuration until a new Connection
is procured (in the case of Session, this is
after the current transaction ends and a new one begins).