SQLAlchemy

SQLAlchemy#

This section overviews popular library that provides a tools to operate with database SQLAlchemy python library. Check main page.

import sqlalchemy

Engine#

An engine is a Python object that implements interaction with the database.

For details check:

The following cell creates inmemory sqlite engine:

engine = sqlalchemy.create_engine("sqlite+pysqlite:///:memory:")
engine

Engine(sqlite+pysqlite:///:memory:)

Now, to prove that it works as a regular sqlite query, retrieve the version of the database.

with engine.connect() as conn:
    ans = conn.execute(sqlalchemy.text("SELECT sqlite_version();"))
ans.fetchall()

[('3.37.2',)]

Log DB commands#

To check what exact commands where executed by the SQLAlchemy you can set echo=True argument to the Engine. After that every command executed by SQLAlchmy will be displayed in the corresponding logger.

The following cell executes some SQL and extracts its result.

engine = sqlalchemy.create_engine("sqlite+pysqlite:///:memory:", echo=True)
with engine.connect() as conn:
    result = conn.execute(sqlalchemy.text("SELECT 'hello world'"))
    result.all()

2025-01-13 18:34:07,420 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2025-01-13 18:34:07,421 INFO sqlalchemy.engine.Engine SELECT 'hello world'
2025-01-13 18:34:07,422 INFO sqlalchemy.engine.Engine [generated in 0.00154s] ()
2025-01-13 18:34:07,422 INFO sqlalchemy.engine.Engine ROLLBACK

The corresponding records are shown in the standard output.

Metadata#

Metadata contains information that defines the structure of the database: tables, relationships between tables, and table schemas. Check the more in:

Following code creates MetaData.

metadata = sqlalchemy.MetaData()
metadata

MetaData()

Since it should be a representation of a database in Python code, you can easily create tables using it. The folowing cell adds test_table and displays awaible tables.

sqlalchemy.Table(
    "test_table",
    metadata,
    sqlalchemy.Column("id", sqlalchemy.Integer)
)
metadata.tables

FacadeDict({'test_table': Table('test_table', MetaData(), Column('id', Integer(), table=<test_table>), schema=None)})

Apply all changes to the specified database by using metadata.create_all(<engine for the database>).

engine = sqlalchemy.create_engine("sqlite+pysqlite:///:memory:")
metadata.create_all(engine)

After that, the schema of the database should match the schema created in SQLAlchemy.

with engine.connect() as conn:
    res = conn.execute(
        sqlalchemy.text(
            "SELECT name FROM sqlite_master WHERE type='table'"
        )
    )
    for row in res:
        print(row)

('test_table',)
Contents