8.3. SQLAlchemy Connection Engine

  • create_engine() builds a factory for database connections

  • create_engine() uses Database Source Name (DSN) for configuration

  • echo=True - if True, the Engine will log all statements to stdout

  • future=True - v2.0 compatibility mode (works only in v1.4)

  • Engine is lazily connected

  • Engine object supports context managers with block

  • engine.connect() method explicitly connects to the database

8.3.1. Glossary

engine

Provides a facade over the Python DBAPI. Used to create a lazy connection to the database.

8.3.2. Create Engine

Function create_engine() builds a factory for database connections. It supports Database Source Name (DSN). In the following example we will create a database connection factory to SQLite3 database using Python builtin driver. The database works in memory, so it will not create any files. Note that the connection is lazy, and creating an engine does not implies connection to the database.

>>> from sqlalchemy import create_engine
>>>
>>>
>>> DATABASE = 'sqlite:///:memory:'
>>> engine = create_engine(DATABASE)

8.3.3. Parameters

  • echo=True - if True, the Engine will log all statements to stdout

  • future=True - v2.0 compatibility mode (works only in v1.4)

  • Full List [1]

8.3.4. 2.x Style

>>> from sqlalchemy import create_engine
>>>
>>>
>>> DATABASE = 'sqlite:///:memory:'
>>> engine = create_engine(DATABASE)
>>>
>>> type(engine)
<class 'sqlalchemy.engine.base.Engine'>

8.3.5. Establishing Connection

Engine is lazily connected and does not connect on creating engine right away. It does that in last possible moment (such as attribute access) or on explicit .connect() method call.

>>> from sqlalchemy import create_engine
>>>
>>>
>>> DATABASE = 'sqlite:///:memory:'
>>> engine = create_engine(DATABASE, future=True)
>>>
>>> with engine.connect() as db:
...     pass

8.3.6. Example

>>> from sqlalchemy import create_engine
>>>
>>>
>>> DATABASE = 'sqlite:///:memory:'
>>> engine = create_engine(DATABASE)
>>>
>>> with engine.connect() as db:
...     result = db.execute('SELECT * FROM users')

8.3.7. Show Parameters

>>> from sqlalchemy import create_engine
>>>
>>>
>>> DATABASE = 'postgresql+psycopg2://mwatney:nasa@localhost:5432/ares3?charset=utf8'
>>>
>>> engine = create_engine(DATABASE)
>>> args, kwargs = engine.dialect.create_connect_args(engine.url)
>>>
>>> args
[]
>>>
>>> kwargs  
{'host': 'localhost',
 'dbname': 'ares3',
 'user': 'mwatney',
 'password': 'nasa',
 'port': 5432,
 'charset': 'utf8'}

8.3.8. Further Reading

8.3.9. References