Selects

Use SELECT to fetch rows as Python objects or individual column values. We'll query this employee table:

from iceaxe import DBConnection, select, TableBase, Field

class Employee(TableBase):
    id: int = Field(primary_key=True)
    name: str
    age: int

Assume it contains these employees:

idnameage
1Marcus Holloway28
2Astrid Keller42
3Jasper Okafor36

Pass a comparison to where to find Marcus by name. The equality check uses the same syntax as a Python if statement:

query = select(Employee).where(Employee.name == "Marcus Holloway")

The annotated attributes on Employee act as columns in a query. They support comparisons such as ==, !=, >, and <=. Use build to inspect the SQL and its parameters before executing it:

query.build()
> ('SELECT "employee".* FROM "employee" WHERE "employee"."name" = $1', ['Marcus Holloway'])

$1 is a placeholder for "Marcus Holloway". Iceaxe sends the value separately from the SQL, so Postgres treats it as data rather than executable SQL. This parameter binding prevents values from becoming SQL injection attacks. Execute the query with conn.exec:

results = await conn.exec(query)
results
> [Employee(id=1, name='Marcus Holloway', age=28)]

The result is a list of Employee instances. You can read and change their attributes like any Python object. Changes stay in memory until you explicitly save them; see update statements.

Single-row lookups

If you want the first matching model directly instead of a list, call .one() on a full-model select:

from iceaxe import NoObjectFound, select

try:
    employee = await conn.exec(
        select(Employee)
        .where(Employee.name == "Marcus Holloway")
        .order_by(Employee.id, "ASC")
        .one()
    )
except NoObjectFound:
    employee = None

Calling .one() changes both the query and its result:

  • .one() only works for a single full table selection such as select(Employee)
  • It changes the return type from list[Employee] to Employee
  • It adds LIMIT 1 to the generated SQL
  • If no rows match, DBConnection.exec() raises NoObjectFound

.one() does not enforce uniqueness. If multiple rows match, it returns the first row from the query after ordering and filtering have been applied.

NULL Comparisons and Column Comparisons

SQL comparisons involving NULL behave differently from Python comparisons with None:

  1. NULL = NULL returns NULL (not TRUE)
  2. NULL != NULL returns NULL (not FALSE)
  3. NULL IS NULL returns TRUE
  4. NULL IS NOT NULL returns FALSE

In Python:

  1. None == None returns True
  2. None != None returns False
  3. None is None returns True
  4. None is not None returns False

Iceaxe's == and != operators account for NULL values. They use IS NULL for comparisons with None and IS [NOT] DISTINCT FROM for column comparisons.

For these examples, assume Employee also has nullable manager_id and supervisor_id columns:

# These handle NULL values correctly automatically
query = select(Employee).where(Employee.manager_id == None)  # Uses IS NULL
query = select(Employee).where(Employee.manager_id != None)  # Uses IS NOT NULL

# These also handle NULL values correctly when comparing columns
query = select(Employee).where(Employee.manager_id == Employee.supervisor_id)  # Uses IS NOT DISTINCT FROM
query = select(Employee).where(Employee.manager_id != Employee.supervisor_id)  # Uses IS DISTINCT FROM

To use SQL's ordinary = and != behavior, call equals() or not_equals():

# Explicitly use non-null-safe comparisons
query = select(Employee).where(column(Employee.manager_id).equals(None))  # Uses = NULL
query = select(Employee).where(column(Employee.manager_id).not_equals(None))  # Uses != NULL

# Explicitly use non-null-safe comparisons for columns
query = select(Employee).where(
    column(Employee.manager_id).equals(Employee.supervisor_id)  # Uses = instead of IS NOT DISTINCT FROM
)

With ordinary SQL equality, comparing two NULL values produces NULL, which does not pass a WHERE filter. Keep == and != when you want two NULL values to compare equal, as two None values do in Python.

Chaining conditions

where combines multiple conditions with AND. Use or_ to accept either condition, such as employees who are 28 or 36:

from iceaxe import and_, or_

query = select(Employee).where(or_(Employee.age == 28, Employee.age == 36))

query.build()
> ('SELECT "employee".* FROM "employee"
    WHERE ("employee"."age" = $1 OR "employee"."age" = $2)', [28, 36])

You can nest and_ and or_. Require an age of 28 or 36 and a name of Marcus Holloway or Astrid Keller. Only Marcus matches both groups:

query = select(Employee).where(
    and_(
        or_(
            Employee.age == 28, Employee.age == 36,
        ),
        or_(
            Employee.name == "Marcus Holloway", Employee.name == "Astrid Keller"
        )
    )
)

query.build()
> ('SELECT "employee".* FROM "employee"
    WHERE (
        ("employee"."age" = $1 OR "employee"."age" = $2)
        AND
        ("employee"."name" = $3 OR "employee"."name" = $4)
    )',
    [28, 36, 'Marcus Holloway', 'Astrid Keller'])

The outer and_ is optional here: passing the two or_ groups directly to where combines them with AND too.

Limit columns

Selecting Employee returns every column. For a query that only needs IDs and names, select those columns to avoid transferring the other values:

query = select((Employee.id, Employee.name)).where(Employee.name == "Marcus Holloway")

query.build()
> ('SELECT "employee"."id", "employee"."name" FROM "employee"
    WHERE "employee"."name" = $1', ['Marcus Holloway'])

Pass multiple selections as a tuple. This query returns list[tuple[int, str]] instead of list[Employee].

Filter lists

When several values should match, use in_ with a list. Here it replaces the two age comparisons from the previous example:

from iceaxe import column

query = select(Employee).where(column(Employee.age).in_([28, 36]))

query.build()
> ('SELECT "employee".* FROM "employee" WHERE "employee"."age" = ANY($1)', [[28, 36]])

Employee.age is annotated as int, which has no in_ method. Wrapping it in column tells mypy or pyright to treat it as a query column. The query works at runtime without that wrapper, but a type checker needs it.

Aggregation queries

Aggregate functions calculate values across rows. Add two employees with the same age so we can also try grouping:

idnameage
1Marcus Holloway28
2Astrid Keller42
3Jasper Okafor36
4Kaitlyn Winters36
5Merilyn Keller36

Let's get the average age of all employees:

from iceaxe import func

query = select(func.avg(Employee.age))

query.build()
> ('SELECT avg("employee"."age") AS aggregate_0 FROM "employee"', [])

await conn.exec(query)
> [35.6]

To count employees of each age, group by Employee.age before counting:

query = select(
  (func.count(Employee.id), Employee.age),
).group_by(Employee.age)

query.build()
> ('SELECT count("employee"."id") AS aggregate_0, "employee"."age" FROM "employee" GROUP BY "employee"."age"', [])

await conn.exec(query)
> [(1, 28), (3, 36), (1, 42)]

Raw SQL

Call text() on a select query to supply your own SQL while retaining typed results. It replaces the SQL the query builder would otherwise generate, including any filters or joins:

from iceaxe import sql

query = select(Employee).text(
    f"""
    SELECT {sql.select(Employee)} FROM employee
    WHERE {sql(Employee.age)} = $1
    """,
    28
)
query.build()
> 'SELECT "employee"."id" AS "employee_id", "employee"."name" AS "employee_name", "employee"."age" AS "employee_age"
> FROM employee
> WHERE "employee"."age" = $1'
>, [28]

The sql helper formats table and column references inside an f-string. Those references still use your Python definitions, so a type checker can catch a reference to a renamed column.

Choose the formatter for the SQL position:

MethodInputOutputDescription
sql.select(Table)User"users"."id" AS "users_id", "users"."name" AS "users_name"Full table selection with aliased columns
sql.select(Table.column)User.name"users"."name" AS "users_name"Single column selection with alias
sql(Table)User"users"Qualified table name
sql(Table.column)User.name"users"."name"Qualified column name
sql.raw(Table)User"users"Raw table name
sql.raw(Table.column)User.name"name"Raw column name

Use sql.select for selected tables and columns. It generates the aliases Iceaxe needs to map the returned values back to Python objects.

Keep the original select(...) arguments in sync with the columns your SQL returns; those arguments determine the result types.

Pass query values after the SQL string as positional arguments. The first maps to $1, the second to $2, and so on. Always pass user input this way so it is bound as data; do not interpolate it into the SQL string.

results = await conn.exec(query)
> [Employee(id=1, name='Marcus Holloway', age=28)]

For calculated values, use alias to associate a SQL result name with its Python type or a typed func expression.

For the department totals, assume Employee also defines department and salary columns:

from iceaxe import alias, func

# Simple count with alias
query = select((alias("age_int", int))).text(
    f"""
    SELECT COUNT(*) as age_int FROM employee 
    WHERE {sql(Employee.age)} = $1
    """,
    28
)

# Using functions with aliases
query = select((
    Employee,
    alias("name_length", func.length(Employee.name)),
    alias("total_employees", func.count(Employee.id))
)).text(
    f"""
    SELECT 
        {sql.select(Employee)},
        length({sql(Employee.name)}) as name_length,
        COUNT({sql(Employee.id)}) as total_employees
    FROM employee
    GROUP BY {sql(Employee.id)}
    """
)

# Complex example with multiple functions and joins
query = select((
    Employee,
    alias("avg_age", func.avg(Employee.age)),
    alias("department_size", func.count(Employee.id)),
    alias("total_salary", func.sum(Employee.salary))
)).text(
    f"""
    WITH department_stats AS (
        SELECT 
            {sql(Employee.department)},
            avg({sql(Employee.age)}) as avg_age,
            count({sql(Employee.id)}) as department_size,
            sum({sql(Employee.salary)}) as total_salary
        FROM employee
        GROUP BY {sql(Employee.department)}
    )
    SELECT 
        {sql.select(Employee)},
        ds.avg_age,
        ds.department_size,
        ds.total_salary
    FROM employee
    JOIN department_stats ds ON ds.department = {sql(Employee.department)}
    """
)

The names passed to alias must match the aliases in your SQL, such as avg_age and department_size above.