Python Academy · Lesson

Querying with the Session

Read and filter data.

Querying with the Session is a free Python Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Python Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

The Session

The ORM does all its work through a Session. It manages a unit of work: tracking new, changed, and deleted objects, and talking to the database.

from sqlalchemy import create_engine
from sqlalchemy.orm import Session
engine = create_engine('sqlite:///:memory:')
with Session(engine) as session:
    print('Session open:', session)

Adding Objects

session.add() stages a new object. Nothing hits the database until you commit().

from sqlalchemy.orm import Session
# session.add(user) stages it
# session.commit() writes it
print('add stages, commit saves')

Committing Changes

session.commit() flushes pending changes and commits the transaction, making them permanent.

# with Session(engine) as session:
#     session.add(User(name='Alice'))
#     session.commit()
print('commit persists the work')

Querying with select()

Modern SQLAlchemy reads data with select(), executed through the session. scalars() returns model instances.

from sqlalchemy import select
# stmt = select(User)
# users = session.scalars(stmt).all()
print('select(User) then scalars().all()')

Getting All Rows

session.scalars(select(User)).all() returns a list of every User object in the table.

from sqlalchemy import select
# all_users = session.scalars(select(User)).all()
# for u in all_users:
#     print(u.name)
print('all() returns a list of objects')

Filtering with where()

Add a where() clause to filter. Compare a mapped column with a normal Python operator.

from sqlalchemy import select
# stmt = select(User).where(User.age > 28)
# adults = session.scalars(stmt).all()
print('where(User.age > 28) filters rows')

Getting One Object

session.scalars(stmt).first() returns the first match or None. one() expects exactly one row and raises otherwise.

from sqlalchemy import select
# stmt = select(User).where(User.name == 'Alice')
# user = session.scalars(stmt).first()
print('first() = first match or None')

Lookup by Primary Key

session.get(User, 1) is the fastest way to fetch a single object by its primary key.

# user = session.get(User, 1)
# print(user.name)
print('session.get(Model, pk) fetches by id')

Ordering Results

Chain order_by() to sort. Use .desc() on a column for descending order.

from sqlalchemy import select
# stmt = select(User).order_by(User.age.desc())
# sorted_users = session.scalars(stmt).all()
print('order_by(User.age.desc()) sorts results')

Updating an Object

Change an attribute on a loaded object, then commit(). The session detects the change and issues an UPDATE.

# user = session.get(User, 1)
# user.name = 'Alicia'
# session.commit()  # auto UPDATE
print('Edit attribute, then commit')

Deleting an Object

session.delete(obj) marks an object for removal. The DELETE runs on the next commit.

# user = session.get(User, 1)
# session.delete(user)
# session.commit()  # row removed
print('delete(obj) then commit removes the row')

Quick Check

Test your session knowledge.

Recap

You learned to read and change data through the Session.

The Session tracks your unit of work
add() stages, commit() persists
select() + scalars() reads objects; where() filters
session.get() fetches by primary key; edits and delete() apply on commit

Frequently asked questions

Is the “Querying with the Session” lesson free?

Yes — the full text of “Querying with the Session” is free to read here on the web, and the Python Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Python Academy course, upgrade to CoddyKit PRO.

What will I learn in “Querying with the Session”?

Read and filter data. You practise Python Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start Python Academy?

No prior experience is required. Python Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Querying with the Session” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this Python Academy lesson?

Yes. Every Python Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to Python Academy