Query
- Python course
- Advanced
- Lesson 47/83
- 5 min
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.
Practice 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).
Starter code
# bring in create_engine, Column, Integer, String from sqlalchemy
# bring in declarative_base and sessionmaker
# define Base and User class (id, name columns)
# create the engine and tables (create_all)
# open a session, add a User and commit
# query with query(User).all() and print the first record's nameSample 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)Expected output
Ayşe
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Complete the query call
users = session.____(User).all()users = session.query(User).all()Explanation: session.query(Model) starts a query.
2 What does query(Model).all() return?
- A list of all results Correct answer
- A single object
- A query string
- An Engine object
Explanation: .all() returns all results as a list.
3 Match the Query concepts
| Term | Meaning |
|---|---|
session.query |
Starts a query |
.all() |
Returns a list |
.filter(...) |
Adds a condition |
.first() |
Gives first result |