Select 2.0 Core Mechanics

The Modern `select()`

In SQLAlchemy 2.0, the select() function is the primary way you'll build queries. It replaces the older Query object you might be familiar with. The key idea is that select() creates a SQL statement object, which you can then pass to a session to be executed against the database.

This approach separates building the query from running it. You construct a complete, executable statement object first. This object is just a representation of a SQL query; it hasn't touched the database yet. Let's see how simple it is to create one.

from sqlalchemy import select
from my_models import User

# Create a statement to select all users
stmt = select(User)

# The 'stmt' object represents the SQL:
# SELECT user_account.id, user_account.name, ... 
# FROM user_account

This stmt object is highly composable. You can build it up with filters, ordering, and joins before you finally decide to execute it. We'll get into those details later, but for now, the important takeaway is that select() is your starting point for fetching data.

Entities vs. Columns

What you pass into the select() function determines what you get back. You have two main choices: retrieve full ORM entity objects or retrieve specific columns as a tuple-like Row object.

Requesting the full ORM entity is the most common use case. You simply pass the mapped class itself.

# This statement asks for full User objects
stmt_entities = select(User)

When this query is executed, SQLAlchemy will fetch all columns for the User table and use them to construct instances of your User class.

Alternatively, you can request only the columns you need. This is more efficient if you're only working with a subset of an object's data, as it reduces the amount of data transferred from the database.

# This statement asks for just the name and fullname columns
stmt_columns = select(User.name, User.fullname)

When you select specific columns, the results come back as Row objects. These behave like Python tuples but also allow you to access columns by name, like row.name.

Executing and Getting Results

Once you've constructed your statement object, you execute it using a Session. The session.execute() method sends the SQL to the database and returns a Result object, which holds the fetched data.

Let's see how this works with our column-specific query.

# Assuming 'session' is an active SQLAlchemy Session
result = session.execute(stmt_columns)

for row in result:
    # Access columns by index or by name
    print(f"Name: {row[0]}, Fullname: {row.fullname}")

When you select a full ORM entity, iterating directly over the Result object still gives you Row objects. However, these Row objects contain a single element: your entity instance.

# Executing the statement that selects the full User entity
result = session.execute(stmt_entities)

for user_row in result:
    # Each 'user_row' is a Row object with one element
    user_object = user_row[0]
    print(f"User: {user_object.name}")

This feels a bit clunky. Because fetching a single column or entity is so common, SQLAlchemy provides a more direct way: Result.scalars().

Calling .scalars() on a Result object returns a ScalarResult, which yields the first element of each row directly. This simplifies your code significantly.

# Get a ScalarResult to unpack the User objects directly
result = session.execute(stmt_entities)
scalar_result = result.scalars()

for user_object in scalar_result:
    # No more indexing! We get the User object directly.
    print(f"User: {user_object.name}")

This pattern is so useful that there's a shortcut for it: session.scalars(). It combines the execute and .scalars() calls into one.

# The most direct way to get ORM objects
for user_object in session.scalars(stmt_entities):
    print(f"User: {user_object.name}")

Use session.execute() when selecting multiple columns. Use session.scalars() when selecting a single entity or a single column for cleaner, more direct access to your data.

Typing Your Queries

SQLAlchemy 2.0 fully embraces Python's type hinting system. You can—and should—add type hints to your select() statements. This provides huge benefits for static analysis tools like Mypy and improves code completion in your editor, making your code easier to write and maintain.

To type a select statement, you use sqlalchemy.Select and provide the expected return type in brackets.

from sqlalchemy import Select

# This statement is hinted to return User objects
stmt: Select[User] = select(User)

When you select specific columns, the return type is a Row object, which is effectively a tuple. You can hint this accordingly.

from sqlalchemy.engine import Row

# This statement returns rows containing a string and an optional string
stmt_cols: Select[Row[str, str | None]] = select(User.name, User.nickname)

These hints help your IDE and static analysis tools understand what kind of data is returned when you execute the statement. This prevents bugs and makes your data access layer much more predictable.

Quiz Questions 1/5

In SQLAlchemy 2.0, what is the primary role of the select() function?

Quiz Questions 2/5

You've executed a query with stmt = select(User) where User is an ORM class. When you iterate through the Result object using for row in session.execute(stmt):, what will each row be?

SQLAlchemy 2.0 Querying Mastery