Python
Query
Learn to query with session.query.
Query
session.query(Model) returns a query object. .all() returns all results as a list; .filter(...) adds a condition; .first() returns the first result.
users = session.query(User).all()
active = session.query(User).filter(User.name == "Ayşe").first()
print(users[0].name)
The query object is lazy; it does not hit the database until .all(), .first() or .count() is called.
Conditions with filter
.filter(...) adds a condition. Since SQLAlchemy columns are Python objects, comparisons use operators like ==, !=, >, <.
# those named 'Ayşe'
session.query(User).filter(User.name == "Ayşe").all()
# those older than 18
session.query(User).filter(User.age > 18).all()
For multiple conditions, use filter(a, b) or filter(a).filter(b); both mean AND.
Exercise Save a User and query it
Start with from sqlalchemy import create_engine, Column, Integer, String and from sqlalchemy.orm import declarative_base, sessionmaker. Define Base = declarative_base() and class User(Base): with __tablename__ = "users", id = Column(Integer, primary_key=True) and name = Column(String). Create the engine with engine = create_engine("sqlite:///:memory:") and tables with Base.metadata.create_all(engine). Open a session with Session = sessionmaker(bind=engine) and session = Session(). Add a record with session.add(User(name="Ayşe")) and session.commit(). Query with users = session.query(User).all(). Print the name with print(users[0].name).
Show example solution
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base, sessionmaker
Base = declarative_base()
class User(Base):
__tablename__ = "users"
id = Column(Integer, primary_key=True)
name = Column(String)
engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
session.add(User(name="Ayşe"))
session.commit()
users = session.query(User).all()
print(users[0].name)
Test yourself
-
Complete the query call
users = session.____(User).all()
Show answer
users = session.query(User).all()
-
What does query(Model).all() return?
- A list of all results
- A single object
- A query string
- An Engine object
Show answer
A list of all results — .all() returns all results as a list.
-
Match the Query concepts
- session.query
- Starts a query
- .all()
- Returns a list
- .filter(...)
- Adds a condition
- .first()
- Gives first result
Next lesson: Summary - SQLAlchemy
