select in Python

The 2.0 query API: build a select, execute it, read scalars or rows.

from sqlalchemy import create_engine, func, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column


class Base(DeclarativeBase):
    pass


class Run(Base):
    __tablename__ = "runs"
    id: Mapped[int] = mapped_column(primary_key=True)
    lang: Mapped[str]
    tpm: Mapped[float]


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

with Session(engine) as session:
    session.add_all([Run(lang="python", tpm=104.0),
                     Run(lang="python", tpm=91.0),
                     Run(lang="rust", tpm=98.0)])
    session.commit()

    fast = session.scalars(
        select(Run).where(Run.tpm > 95).order_by(Run.tpm.desc())).all()
    print([(r.lang, r.tpm) for r in fast])

    per_lang = session.execute(
        select(Run.lang, func.avg(Run.tpm)).group_by(Run.lang)).all()
    print([(lang, round(avg, 1)) for lang, avg in per_lang])

How it works

1. select(Model) returns whole objects.
2. where, order_by and limit chain onto it.
3. scalars() unwraps single-column rows.

Keywords and builtins used here

• Base
• Run
• as
• class
• float
• for
• id
• int
• pass
• print
• round
• str
• with

The run, in numbers

Lines

31

Characters to type

813

Tokens

244

Three-star pace

110 tpm

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

Type this snippet

Step 4 of 5 in SQLAlchemy, step 16 of 19 in Web services & data access.

← Previous Next →

select in other languages

• Go
• R