No history yet

Select Statement Basics

From Text to Objects

You're already familiar with creating Table objects and executing raw SQL strings. Now, we'll shift from writing SQL as plain text to building queries programmatically. This is the core strength of SQLAlchemy's Expression Language. It lets you construct queries using Python objects, making your code more readable, reusable, and less prone to errors like SQL injection.

The foundation of this approach is the select() function. Instead of writing "SELECT * FROM users", you'll create a Select object that represents the query. Let's assume we have a users table object ready to go.

from sqlalchemy import select

# This is equivalent to 'SELECT * FROM users'
stmt = select(users)

# Execute the statement
with engine.connect() as connection:
    result = connection.execute(stmt)
    for row in result:
        print(row)

When you execute a statement, you get back a Result object. Iterating over this object yields Row objects. Think of a Row as a tuple-like object that also lets you access data by column name, like a dictionary.

You can access data in a Row by its integer index (like row[0]) or by its column key (like row.name or row['name']).

Selecting Specific Columns

Querying for all columns with * can be inefficient, especially with large tables. It's better to ask only for the data you need. To do this, you pass individual Column objects from your Table object into the select() function.

Each Column is accessible through the .c attribute of your Table object.

# This is equivalent to 'SELECT name, email FROM users'
stmt = select(users.c.name, users.c.email)

with engine.connect() as connection:
    result = connection.execute(stmt)
    for row in result:
        print(f"Name: {row.name}, Email: {row.email}")

The Row objects in this result will only contain the name and email columns. Trying to access row.age would raise an error.

Filtering with `where`

To filter your results, you chain the .where() method onto your select() construct. This is SQLAlchemy's version of the SQL WHERE clause. The real power here is that you can use standard Python comparison operators like ==, !=, >, and < directly with the column objects.

SQLAlchemy intercepts these comparisons and translates them into the appropriate SQL syntax for the database you're connected to.

# SELECT * FROM users WHERE name = 'Alice'
stmt = select(users).where(users.c.name == 'Alice')

# SELECT * FROM users WHERE age > 30
stmt_two = select(users).where(users.c.age > 30)

You can combine column selection and filtering to create precise queries. This is where the programmatic approach really shines, allowing you to build complex logic piece by piece.

# SELECT name, email FROM users WHERE age > 30
stmt = select(users.c.name, users.c.email).where(users.c.age > 30)

with engine.connect() as connection:
    result = connection.execute(stmt)
    for row in result:
        print(row)

Let's test your understanding of these fundamental building blocks.

Quiz Questions 1/5

What is the primary advantage of using SQLAlchemy's Expression Language over writing raw SQL strings in your Python code?

Quiz Questions 2/5

After executing a select statement like conn.execute(stmt), you receive a Result object. What do you get when you iterate over this Result object?

With these basics, you can already build a wide range of queries to retrieve exactly the data you need from your tables.