SQLAlchemy engine and text in Python

The engine owns the connection pool; text() runs plain SQL through it.

from sqlalchemy import create_engine, text

engine = create_engine("sqlite+pysqlite:///:memory:", echo=False)

with engine.begin() as conn:
    conn.execute(text("CREATE TABLE runs (lang TEXT, tpm INTEGER)"))
    conn.execute(text("INSERT INTO runs VALUES (:lang, :tpm)"),
                 [{"lang": "python", "tpm": 104}, {"lang": "rust", "tpm": 98}])

with engine.connect() as conn:
    rows = conn.execute(text("SELECT lang, tpm FROM runs ORDER BY tpm DESC"))
    print([tuple(row) for row in rows])
    one = conn.execute(text("SELECT tpm FROM runs WHERE lang = :l"),
                       {"l": "rust"}).scalar_one()
    print(one)

How it works

create_engine takes a URL, not a connection.
begin gives a transaction that commits on exit.
Bound parameters are named with a colon.

Keywords and builtins used here

as
for
print
tuple
with

The run, in numbers

Lines: 15
Characters to type: 573
Tokens: 149
Three-star pace: 110 tpm

At the three-star pace of 110 tokens a minute, this run takes about 81 seconds.

Type this snippet

Step 1 of 5 in SQLAlchemy, step 13 of 19 in Web services & data access.

← Previous Next →