Understanding SQLAlchemy Query Options: db.session.query() vs. model.query()
db.session.query()
:- This is the fundamental way to create a query. It retrieves a query object directly from the current database session (
db.session
). - This method offers maximum flexibility as you can specify any table or combination of tables in the database to query from.
- This is the fundamental way to create a query. It retrieves a query object directly from the current database session (
model.query
:- This is a shortcut method defined on your SQLAlchemy model class (
model
). - It's simply a wrapper around
db.session.query(model)
– it creates a query specifically for that model's table. - It's convenient for basic queries where you only need data from a single model.
- This is a shortcut method defined on your SQLAlchemy model class (
When to use which:
- Use
db.session.query()
when you need to query from multiple tables or if you don't want to rely on the current database session (less common). - Use
model.query
for simple queries that only involve your current model's table. It improves code readability.
In essence:
- They both create queries, but
db.session.query()
offers more control. model.query
is a convenient shortcut for single-model queries.
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_Base
Base = declarative_Base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
address_id = Column(Integer, ForeignKey('addresses.id'))
address = relationship("Address", backref="user") # One-to-One relationship
class Address(Base):
__tablename__ = 'addresses'
id = Column(Integer, primary_key=True)
street = Column(String)
city = Column(String)
engine = create_engine('sqlite:///mydatabase.db')
Base.metadata.create_all(engine) # Create tables
Session = sessionmaker(bind=engine)
session = Session()
# Query user with address details (joins tables)
user_with_address = session.query(User).join(Address).filter(User.id == 1).first()
if user_with_address:
print(f"User: {user_with_address.name}, Address: {user_with_address.address.street}, {user_with_address.address.city}")
session.close()
This code defines two models, User
and Address
, with a one-to-one relationship. It then uses db.session.query()
to join these tables and fetch a user along with their address details.
Using model.query() (querying a single model):
from sqlalchemy import create_engine, Column, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_Base
Base = declarative_Base()
class Book(Base):
__tablename__ = 'books'
id = Column(Integer, primary_key=True)
title = Column(String)
engine = create_engine('sqlite:///mydatabase.db')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
# Get all books using model.query (simple query)
all_books = Book.query.all()
for book in all_books:
print(f"Book title: {book.title}")
session.close()
- This approach bypasses the SQLAlchemy ORM (Object Relational Mapper) and lets you write raw SQL statements.
- It offers maximum control over the query but requires writing SQL directly, which can be less readable for complex queries.
Here's an example:
from sqlalchemy import create_engine
engine = create_engine('sqlite:///mydatabase.db')
with engine.connect() as connection:
# Raw SQL query to get users with name containing "John"
result = connection.execute("SELECT * FROM users WHERE name LIKE '%John%'")
for row in result:
print(f"User: {row['name']}")
ORM Query API (SQLAlchemy 2.0+ only):
- Introduced in SQLAlchemy 2.0, this API provides a more low-level way to construct ORM queries compared to
db.session.query()
. - It offers finer control over building the query object but can have a steeper learning curve.
Here's a basic example (assuming you're using SQLAlchemy 2.0 or later):
from sqlalchemy import create_engine, orm
engine = create_engine('sqlite:///mydatabase.db')
session = orm.sessionmaker(bind=engine)()
# Using ORM Query API to get all users
users = session.query(orm.aliased(User)).all() # Alias required in ORM API
for user in users:
print(f"User: {user.name}")
session.close()
Choosing the right method:
- For most cases,
db.session.query()
ormodel.query
will be sufficient. - If you need raw SQL control, use
session.execute()
. - If you're using SQLAlchemy 2.0+ and want fine-grained control over ORM query construction, explore the ORM Query API (but be aware of the learning curve).
sqlalchemy