Lesson 3 - Querying with select() and where()

Welcome to Querying with Core#

You have a real database with related tables. Now you will do what you came here for: ask it questions. SQLAlchemy Core’s select() function builds a query object piece by piece — you add .where() to filter, .order_by() to sort, .group_by() to aggregate — and each method returns a new query you can keep chaining, without ever concatenating a SQL string.

By the end of this lesson, you will be able to:

• Retrieve all or specific columns with select()
• Filter rows with where(), combining conditions with and_(), or_(), and between()
• Filter for missing values with is_() and is_not()
• Sort results with order_by() and limit them with limit()
• Compute per-group statistics with group_by() and func

Let’s put the employees table to work.

Selecting Columns and Rows#

Set up the connection and reflect the table once, at the top of your script:

from sqlalchemy import create_engine, MetaData, Table, select

engine = create_engine("sqlite:///company.db")
metadata = MetaData()
employees = Table("employees", metadata, autoload_with=engine)

select(employees) builds a query for every column in the table:

with engine.connect() as conn:
    result = conn.execute(select(employees)).fetchall()
    for row in result[:3]:
        print(row)

Output:

(1, 'JOHNSON', 'ADMIN', 6, datetime.date(1990, 12, 17), Decimal('18000.00'), None, 4)
(2, 'HARDING', 'MANAGER', 9, datetime.date(1998, 2, 2), Decimal('52000.00'), Decimal('300.00'), 3)
(3, 'TAFT', 'SALES I', 2, datetime.date(1996, 1, 2), Decimal('25000.00'), Decimal('500.00'), 3)

More often, you only need specific columns. Pass them individually through the table’s .c (short for “columns”) accessor:

with engine.connect() as conn:
    result = conn.execute(select(employees.c.name, employees.c.salary)).fetchall()
    for row in result[:3]:
        print(row)

Output:

('JOHNSON', Decimal('18000.00'))
('HARDING', Decimal('52000.00'))
('TAFT', Decimal('25000.00'))

Just like the sqlite3 cursor from the earlier module, execute() returns rows you can loop over — but you did not write a single character of SQL to get them.

Filtering with where()#

.where() narrows a query to matching rows, using Python’s ordinary comparison operators on column objects:

stmt = (
    select(employees.c.name, employees.c.salary)
    .where(employees.c.salary > 30000)
    .order_by(employees.c.salary.desc())
)

with engine.connect() as conn:
    for row in conn.execute(stmt):
        print(row)

Output:

('JACKSON', 75000)
('FILLMORE', 56000)
('GARFIELD', 54000)
('HARDING', 52000)
('ROOSEVELT', 35000)
('ADAMS', 34000)
('GRANT', 32000)

employees.c.salary > 30000 looks like an ordinary Python comparison, but it does not evaluate to True or False — it builds a SQL expression object that .where() understands. This is the core trick behind all of Core’s query building: operators on columns produce SQL, not booleans.

Combining conditions with and_() and or_()#

To match more than one condition, use and_() or or_():

from sqlalchemy import or_

stmt = select(employees.c.name, employees.c.job).where(
    or_(employees.c.job == "MANAGER", employees.c.job == "CEO")
)

with engine.connect() as conn:
    for row in conn.execute(stmt):
        print(row)

Output:

('HARDING', 'MANAGER')
('GARFIELD', 'MANAGER')
('JACKSON', 'CEO')
('FILLMORE', 'MANAGER')

or_() matches a row if any of its arguments are true; and_() (used in the exercises below) requires all of them.

Ranges with between()#

.between() reads naturally for inclusive ranges:

stmt = select(employees.c.name, employees.c.salary).where(
    employees.c.salary.between(25000, 40000)
)

This is equivalent to employees.c.salary >= 25000 combined with employees.c.salary <= 40000, but reads closer to how you would describe the range out loud.

Finding missing values#

Because commission is nullable, some employees have no value in that column. Comparing to None with == will not match NULL the way SQL expects — you need .is_() and .is_not():

from sqlalchemy import func

stmt = select(func.count()).select_from(employees).where(employees.c.commission.is_(None))

with engine.connect() as conn:
    print("Employees with no commission:", conn.execute(stmt).scalar())

Output:

Employees with no commission: 11

Sorting and Limiting#

You have already seen .order_by() above. Combine it with .limit() to answer “top N” questions directly in the database, instead of pulling every row into Python first:

stmt = (
    select(employees.c.name, employees.c.salary)
    .order_by(employees.c.salary.desc())
    .limit(3)
)

with engine.connect() as conn:
    for row in conn.execute(stmt):
        print(row)

Output:

('JACKSON', 75000)
('FILLMORE', 56000)
('GARFIELD', 54000)

.order_by(employees.c.salary.desc()) sorts highest first; drop .desc() (or call .asc() explicitly) to sort ascending.

Aggregating with group_by()#

To answer “what does the average look like per group” rather than across the whole table, pair .group_by() with a function from SQLAlchemy’s func namespace, which maps directly onto SQL’s aggregate functions:

stmt = (
    select(
        employees.c.department_id,
        func.avg(employees.c.salary).label("avg_salary"),
        func.count().label("headcount"),
    )
    .group_by(employees.c.department_id)
    .order_by(employees.c.department_id)
)

with engine.connect() as conn:
    for row in conn.execute(stmt):
        print(row.department_id, round(row.avg_salary, 2), row.headcount)

Output:

1 35000.0 1
2 38000.0 4
3 34666.67 3
4 35583.33 6

Two details worth noticing:

• func.avg(...) and func.count() compile to AVG(...) and COUNT(*) in the generated SQL. func gives you access to any SQL function this way, even ones SQLAlchemy has no dedicated Python method for.
• .label("avg_salary") names the computed column, so you can read the result with row.avg_salary instead of guessing what SQLAlchemy called it — the same trick you will use constantly once queries have several computed columns.

Department IDs are not very readable on their own — in the next lesson you will join this query against departments to show department names instead.

Practice Exercises#

Continue using company.db and the employees table.

Exercise 1: Employees Between Two Salaries#

Write a query that returns the name and salary of every employee earning between 25000 and 35000 inclusive, sorted by salary ascending.

# Your code here

Exercise 2: Engineers with a Manager#

Using and_(), find employees whose job is "ENGINEER" and whose mgr_id is not null.

# Your code here

Exercise 3: Highest Salary Per Department#

Group by department_id and use func.max() to find the highest salary in each department, ordered by that maximum descending.

# Your code here

Summary#

You built queries with select(), chaining .where() to filter, .order_by() to sort, and .limit() to cap the result set — all as method calls on a query object rather than string concatenation. You combined conditions with and_(), or_(), and .between(), handled missing values correctly with .is_() and .is_not(), and computed per-group statistics with .group_by() and SQLAlchemy’s func namespace.

Key Concepts#

• select(table) / select(table.c.col, ...) — query all columns, or specific ones.
• .where(condition) — filters rows; column comparisons like employees.c.salary > 30000 build SQL expressions, not Python booleans.
• and_() / or_() — combine multiple conditions with logical AND / OR.
• .between(low, high) — an inclusive range filter.
• .is_(None) / .is_not(None) — the correct way to filter for NULL / non-NULL values.
• .order_by(col.asc() / col.desc()) — sorts results.
• .limit(n) — caps the number of rows returned.
• .group_by(col) with func.avg() / func.count() / func.max() — computes an aggregate per group.
• .label("name") — names a computed column so you can read it off the result row by attribute.

Why This Matters#

Building queries this way pays off the moment a query needs to change. Adding a filter, a sort order, or a group is a one-line addition to an existing Python expression, not a careful edit of a hand-written SQL string where a misplaced AND silently changes the query’s meaning. As your queries grow more complex in the lessons ahead — joins, subqueries, and conditional logic — this same chainable style scales with them.

Continue Building Your Skills#

Grouping by department_id got you real numbers, but a bare integer is not as useful as a department’s actual name. In the next lesson you will join employees and departments together, and use a self-join to look up each employee’s manager.