8.3. SQLAlchemy Connection Engine

Important

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

https://docs.sqlalchemy.org/en/stable/core/engines.html#sqlalchemy.create_engine.params.connect_args