7
The :class:`.Engine` is the starting point for any SQLAlchemy application. It's
8
"home base" for the actual database and its :term:`DBAPI`, delivered to the SQLAlchemy
9
application through a connection pool and a :class:`.Dialect`, which describes how
10
to talk to a specific kind of database/DBAPI combination.
12
The general structure can be illustrated as follows:
14
.. image:: sqla_engine_arch.png
16
Where above, an :class:`.Engine` references both a
17
:class:`.Dialect` and a :class:`.Pool`,
18
which together interpret the DBAPI's module functions as well as the behavior
21
Creating an engine is just a matter of issuing a single call,
22
:func:`.create_engine()`::
24
from sqlalchemy import create_engine
25
engine = create_engine('postgresql://scott:tiger@localhost:5432/mydatabase')
27
The above engine creates a :class:`.Dialect` object tailored towards
28
PostgreSQL, as well as a :class:`.Pool` object which will establish a DBAPI
29
connection at ``localhost:5432`` when a connection request is first received.
30
Note that the :class:`.Engine` and its underlying :class:`.Pool` do **not**
31
establish the first actual DBAPI connection until the :meth:`.Engine.connect`
32
method is called, or an operation which is dependent on this method such as
33
:meth:`.Engine.execute` is invoked. In this way, :class:`.Engine` and
34
:class:`.Pool` can be said to have a *lazy initialization* behavior.
36
The :class:`.Engine`, once created, can either be used directly to interact with the database,
37
or can be passed to a :class:`.Session` object to work with the ORM. This section
38
covers the details of configuring an :class:`.Engine`. The next section, :ref:`connections_toplevel`,
39
will detail the usage API of the :class:`.Engine` and similar, typically for non-ORM
47
SQLAlchemy includes many :class:`.Dialect` implementations for various
48
backends. Dialects for the most common databases are included with SQLAlchemy; a handful
49
of others require an additional install of a separate dialect.
51
See the section :ref:`dialect_toplevel` for information on the various backends available.
58
The :func:`.create_engine` function produces an :class:`.Engine` object based
59
on a URL. These URLs follow `RFC-1738
60
<http://rfc.net/rfc1738.html>`_, and usually can include username, password,
61
hostname, database name as well as optional keyword arguments for additional configuration.
62
In some cases a file path is accepted, and in others a "data source name" replaces
63
the "host" and "database" portions. The typical form of a database URL is::
65
dialect+driver://username:password@host:port/database
67
Dialect names include the identifying name of the SQLAlchemy dialect,
68
a name such as ``sqlite``, ``mysql``, ``postgresql``, ``oracle``, or ``mssql``.
69
The drivername is the name of the DBAPI to be used to connect to
70
the database using all lowercase letters. If not specified, a "default" DBAPI
71
will be imported if available - this default is typically the most widely
72
known driver available for that backend.
74
Examples for common connection styles follow below. For a full index of
75
detailed information on all included dialects as well as links to third-party dialects, see
76
:ref:`dialect_toplevel`.
81
The Postgresql dialect uses psycopg2 as the default DBAPI. pg8000 is
82
also available as a pure-Python substitute::
85
engine = create_engine('postgresql://scott:tiger@localhost/mydatabase')
88
engine = create_engine('postgresql+psycopg2://scott:tiger@localhost/mydatabase')
91
engine = create_engine('postgresql+pg8000://scott:tiger@localhost/mydatabase')
93
More notes on connecting to Postgresql at :ref:`postgresql_toplevel`.
98
The MySQL dialect uses mysql-python as the default DBAPI. There are many
99
MySQL DBAPIs available, including MySQL-connector-python and OurSQL::
102
engine = create_engine('mysql://scott:tiger@localhost/foo')
105
engine = create_engine('mysql+mysqldb://scott:tiger@localhost/foo')
107
# MySQL-connector-python
108
engine = create_engine('mysql+mysqlconnector://scott:tiger@localhost/foo')
111
engine = create_engine('mysql+oursql://scott:tiger@localhost/foo')
113
More notes on connecting to MySQL at :ref:`mysql_toplevel`.
118
The Oracle dialect uses cx_oracle as the default DBAPI::
120
engine = create_engine('oracle://scott:tiger@127.0.0.1:1521/sidname')
122
engine = create_engine('oracle+cx_oracle://scott:tiger@tnsname')
124
More notes on connecting to Oracle at :ref:`oracle_toplevel`.
129
The SQL Server dialect uses pyodbc as the default DBAPI. pymssql is
133
engine = create_engine('mssql+pyodbc://scott:tiger@mydsn')
136
engine = create_engine('mssql+pymssql://scott:tiger@hostname:port/dbname')
138
More notes on connecting to SQL Server at :ref:`mssql_toplevel`.
143
SQLite connects to file-based databases, using the Python built-in
144
module ``sqlite3`` by default.
146
As SQLite connects to local files, the URL format is slightly different.
147
The "file" portion of the URL is the filename of the database.
148
For a relative file path, this requires three slashes::
150
# sqlite://<nohostname>/<path>
151
# where <path> is relative:
152
engine = create_engine('sqlite:///foo.db')
154
And for an absolute file path, *four* slashes are used::
156
engine = create_engine('sqlite:////absolute/path/to/foo.db')
158
To use a SQLite ``:memory:`` database, specify an empty URL::
160
engine = create_engine('sqlite://')
162
More notes on connecting to SQLite at :ref:`sqlite_toplevel`.
167
See :ref:`dialect_toplevel`, the top-level page for all additional dialect
170
.. _create_engine_args:
175
.. autofunction:: sqlalchemy.create_engine
177
.. autofunction:: sqlalchemy.engine_from_config
179
.. autofunction:: sqlalchemy.engine.url.make_url
182
.. autoclass:: sqlalchemy.engine.url.URL
188
The :class:`.Engine` will ask the connection pool for a
189
connection when the ``connect()`` or ``execute()`` methods are called. The
190
default connection pool, :class:`~.QueuePool`, will open connections to the
191
database on an as-needed basis. As concurrent statements are executed,
192
:class:`.QueuePool` will grow its pool of connections to a
193
default size of five, and will allow a default "overflow" of ten. Since the
194
:class:`.Engine` is essentially "home base" for the
195
connection pool, it follows that you should keep a single
196
:class:`.Engine` per database established within an
197
application, rather than creating a new one for each connection.
201
:class:`.QueuePool` is not used by default for SQLite engines. See
202
:ref:`sqlite_toplevel` for details on SQLite connection pool usage.
204
For more information on connection pooling, see :ref:`pooling_toplevel`.
207
.. _custom_dbapi_args:
209
Custom DBAPI connect() arguments
210
=================================
212
Custom arguments used when issuing the ``connect()`` call to the underlying
213
DBAPI may be issued in three distinct ways. String-based arguments can be
214
passed directly from the URL string as query arguments:
216
.. sourcecode:: python+sql
218
db = create_engine('postgresql://scott:tiger@localhost/test?argument1=foo&argument2=bar')
220
If SQLAlchemy's database connector is aware of a particular query argument, it
221
may convert its type from string to its proper type.
223
:func:`~sqlalchemy.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:
225
.. sourcecode:: python+sql
227
db = create_engine('postgresql://scott:tiger@localhost/test', connect_args = {'argument1':17, 'argument2':'bar'})
229
The most customizable connection method of all is to pass a ``creator``
230
argument, which specifies a callable that returns a DBAPI connection:
232
.. sourcecode:: python+sql
235
return psycopg.connect(user='scott', host='localhost')
237
db = create_engine('postgresql://', creator=connect)
241
.. _dbengine_logging:
246
Python's standard `logging
247
<http://docs.python.org/library/logging.html>`_ module is used to
248
implement informational and debug log output with SQLAlchemy. This allows
249
SQLAlchemy's logging to integrate in a standard way with other applications
250
and libraries. The ``echo`` and ``echo_pool`` flags that are present on
251
:func:`~sqlalchemy.create_engine`, as well as the ``echo_uow`` flag used on
252
:class:`~sqlalchemy.orm.session.Session`, all interact with regular loggers.
254
This section assumes familiarity with the above linked logging module. All
255
logging performed by SQLAlchemy exists underneath the ``sqlalchemy``
256
namespace, as used by ``logging.getLogger('sqlalchemy')``. When logging has
257
been configured (i.e. such as via ``logging.basicConfig()``), the general
258
namespace of SA loggers that can be turned on is as follows:
260
* ``sqlalchemy.engine`` - controls SQL echoing. set to ``logging.INFO`` for SQL query output, ``logging.DEBUG`` for query + result set output.
261
* ``sqlalchemy.dialects`` - controls custom logging for SQL dialects. See the documentation of individual dialects for details.
262
* ``sqlalchemy.pool`` - controls connection pool logging. set to ``logging.INFO`` or lower to log connection pool checkouts/checkins.
263
* ``sqlalchemy.orm`` - controls logging of various ORM functions. set to ``logging.INFO`` for information on mapper configurations.
265
For example, to log SQL queries using Python logging instead of the ``echo=True`` flag::
269
logging.basicConfig()
270
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
272
By default, the log level is set to ``logging.WARN`` within the entire
273
``sqlalchemy`` namespace so that no log operations occur, even within an
274
application that has logging enabled otherwise.
276
The ``echo`` flags present as keyword arguments to
277
:func:`~sqlalchemy.create_engine` and others as well as the ``echo`` property
278
on :class:`~sqlalchemy.engine.Engine`, when set to ``True``, will first
279
attempt to ensure that logging is enabled. Unfortunately, the ``logging``
280
module provides no way of determining if output has already been configured
281
(note we are referring to if a logging configuration has been set up, not just
282
that the logging level is set). For this reason, any ``echo=True`` flags will
283
result in a call to ``logging.basicConfig()`` using sys.stdout as the
284
destination. It also sets up a default format using the level name, timestamp,
285
and logger name. Note that this configuration has the affect of being
286
configured **in addition** to any existing logger configurations. Therefore,
287
**when using Python logging, ensure all echo flags are set to False at all
288
times**, to avoid getting duplicate log lines.
290
The logger name of instance such as an :class:`~sqlalchemy.engine.Engine`
291
or :class:`~sqlalchemy.pool.Pool` defaults to using a truncated hex identifier
292
string. To set this to a specific name, use the "logging_name" and
293
"pool_logging_name" keyword arguments with :func:`sqlalchemy.create_engine`.
297
The SQLAlchemy :class:`.Engine` conserves Python function call overhead
298
by only emitting log statements when the current logging level is detected
299
as ``logging.INFO`` or ``logging.DEBUG``. It only checks this level when
300
a new connection is procured from the connection pool. Therefore when
301
changing the logging configuration for an already-running application, any
302
:class:`.Connection` that's currently active, or more commonly a
303
:class:`~.orm.session.Session` object that's active in a transaction, won't log any
304
SQL according to the new configuration until a new :class:`.Connection`
305
is procured (in the case of :class:`~.orm.session.Session`, this is
306
after the current transaction ends and a new one begins).