Engine Configuration
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()
:
from sqlalchemy import create_engine engine = create_engine('postgresql://scott:tiger@localhost:5432/mydatabase')
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.
Supported Databases
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.
Database Urls
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.
Postgresql
The Postgresql dialect uses psycopg2 as the default DBAPI. pg8000 is also available as a pure-Python substitute:
# default engine = create_engine('postgresql://scott:tiger@localhost/mydatabase') # psycopg2 engine = create_engine('postgresql+psycopg2://scott:tiger@localhost/mydatabase') # pg8000 engine = create_engine('postgresql+pg8000://scott:tiger@localhost/mydatabase')
More notes on connecting to Postgresql at PostgreSQL.
MySQL
The MySQL dialect uses mysql-python as the default DBAPI. There are many MySQL DBAPIs available, including MySQL-connector-python and OurSQL:
# default engine = create_engine('mysql://scott:tiger@localhost/foo') # mysql-python engine = create_engine('mysql+mysqldb://scott:tiger@localhost/foo') # MySQL-connector-python engine = create_engine('mysql+mysqlconnector://scott:tiger@localhost/foo') # OurSQL engine = create_engine('mysql+oursql://scott:tiger@localhost/foo')
More notes on connecting to MySQL at MySQL.
Oracle
The Oracle dialect uses cx_oracle as the default DBAPI:
engine = create_engine('oracle://scott:tiger@127.0.0.1:1521/sidname') engine = create_engine('oracle+cx_oracle://scott:tiger@tnsname')
More notes on connecting to Oracle at Oracle.
Microsoft SQL Server
The SQL Server dialect uses pyodbc as the default DBAPI. pymssql is also available:
# pyodbc engine = create_engine('mssql+pyodbc://scott:tiger@mydsn') # pymssql engine = create_engine('mssql+pymssql://scott:tiger@hostname:port/dbname')
More notes on connecting to SQL Server at Microsoft SQL Server.
SQLite
SQLite connects to file-based databases, using the Python built-in module sqlite3
by default.
As SQLite connects to local files, the URL format is slightly different. The “file” portion of the URL is the filename of the database. For a relative file path, this requires three slashes:
# sqlite://<nohostname>/<path> # where <path> is relative: engine = create_engine('sqlite:///foo.db')
And for an absolute file path, the three slashes are followed by the absolute path:
#Unix/Mac - 4 initial slashes in total engine = create_engine('sqlite:absolute/path/to/foo.db') #Windows engine = create_engine('sqlite:///C:\\path\\to\\foo.db') #Windows alternative using raw string engine = create_engine(r'sqlite:///C:\path\to\foo.db')
To use a SQLite :memory:
database, specify an empty URL:
engine = create_engine('sqlite://')
More notes on connecting to SQLite at SQLite.
Others
See Dialects, the top-level page for all additional dialect documentation.
Engine Creation API
sqlalchemy.
create_engine
(*args, **kwargs)
Create a new Engine
instance.
The standard calling form is to send the URL as the first positional argument, usually a string that indicates database dialect and connection arguments:
engine = create_engine("postgresql://scott:tiger@localhost/test")
Additional keyword arguments may then follow it which establish various options on the resulting Engine
and its underlying Dialect
and Pool
constructs:
engine = create_engine("mysql://scott:tiger@hostname/dbname", encoding='latin1', echo=True)
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.
See also
Working with Engines and Connections
Parameters: |
|
---|
sqlalchemy.
engine_from_config
(configuration, prefix='sqlalchemy.', **kwargs)
Create a new Engine instance using a configuration dictionary.
The dictionary is typically produced from a config file.
The keys of interest to engine_from_config()
should be prefixed, e.g. sqlalchemy.url
, sqlalchemy.echo
, etc. The ‘prefix’ argument indicates the prefix to be searched for. Each matching key (after the prefix is stripped) is treated as though it were the corresponding keyword argument to a create_engine()
call.
The only required key is (assuming the default prefix) sqlalchemy.url
, which provides the database URL.
A select set of keyword arguments will be “coerced” to their expected type based on string values. The set of arguments is extensible per-dialect using the engine_config_types
accessor.
Parameters: |
|
---|
sqlalchemy.engine.url.
make_url
(name_or_url)
Given a string or unicode instance, produce a new URL instance.
The given string is parsed according to the RFC 1738 spec. If an existing URL object is passed, just returns the object.
class sqlalchemy.engine.url.
URL
(drivername, username=None, password=None, host=None, port=None, database=None, query=None)
Represent the components of a URL used to connect to a database.
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: |
|
---|
get_dialect
()
Return the SQLAlchemy database dialect class corresponding to this URL’s driver name.
translate_connect_args
(names=[], **kw)
Translate url attributes into a dictionary of connection arguments.
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.
Parameters: |
|
---|
Pooling
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.
For more information on connection pooling, see Connection Pooling.
Custom DBAPI connect() arguments
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:
db = create_engine('postgresql://scott:tiger@localhost/test?argument1=foo&argument2=bar')
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:
db = create_engine('postgresql://scott:tiger@localhost/test', connect_args = {'argument1':17, 'argument2':'bar'})
The most customizable connection method of all is to pass a creator
argument, which specifies a callable that returns a DBAPI connection:
def connect(): return psycopg.connect(user='scott', host='localhost') db = create_engine('postgresql://', creator=connect)
Configuring Logging
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 tologging.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.sqlalchemy.pool
- controls connection pool logging. set tologging.INFO
or lower to log connection pool checkouts/checkins.sqlalchemy.orm
- controls logging of various ORM functions. set tologging.INFO
for information on mapper configurations.
For example, to log SQL queries using Python logging instead of the echo=True
flag:
import logging logging.basicConfig() logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
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).