Selects

SELECT statements are likely the most common operation you'll perform with Iceaxe. They're used to fetch data from the database and receive them in Python instances or primitives. Let's consider the following table definition to show how we can interact with different types of data:

from iceaxe import DBConnection, select, TableBase, Field

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

Let's start off with the following users in the database:

If we want to get an exact match for a single row, we can use the select function with an exact match filter for Marcus. You'd write this in the same way that you would check for equality in a Python if statement.

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

All defined class attributes in the Employee class are available as columns, and these columns can be used in equality operations like ==, !=, >, <, >=, <=, etc. If we want, we can also preview the SQL and variables that are outputted for this query:

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

As you see from the build output from query.build(), we're constructing a SQL with a single WHERE filter that requests matches to $1. This is a placeholder for the actual value that is passed to the database engine separately. The database engine handles the escaping of any special values in this variable to avoid SQL injection attacks. Let's go ahead and execute this query against the database to get the actual values back:

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

This will return a list of Employee instances that match the query. These instances are full Python objects; you can access and modify their attributes as you would with any other object. Once received, it's detached from the database connection unless you explicitly choose to save it. For more details on how to update data, see the page on 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

This is a convenience for the common "give me one model" path:

• .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

When working with columns that might contain NULL values, you need to be careful about how you compare them. In SQL, NULL values have special comparison rules:

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

These evaluate to different values than the same logic would in Python if you're comparing two variables. Consider this:

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

Iceaxe tries to match Python's intuitions. The standard comparison operators (== and !=) are "null-safe" by default:

# 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

If you need more control over the comparison behavior, you can explicitly use the equals() and not_equals() methods with null_safe=False:

# 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
)

The default null-safe behavior means:

• When comparing with None, it automatically uses IS and IS NOT
• When comparing columns that might contain NULL values, it automatically uses IS [NOT] DISTINCT FROM
• This matches Python's behavior where None == None is True and None != None is False

Most of the time, you'll want to use the default behavior as it handles NULL values in a way that matches Python's semantics. Only override this if you have a specific need for SQL's standard NULL comparison behavior.

Chaining conditions

By default, conditions are combined with an AND operator. If you want to combine conditions with an OR operator, you can use the or_ function. For example, if you want to get all employees that are either 28 or 36 years old:

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])

These operations nest within themselves to create more complicated selection chains. Let's extend the filter above to also get anyone who's named Marcus Holloway or Astrid Keller. We expect to only receive one result.

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'])

Note that the outside and_ is optional in this case since where will take care of that for you. Still, it can sometimes make your code a bit more readable to include it for more complicated query chains.

Limit columns

In the above queries, we returned a full instance. For small queries the bandwidth of fetching an entire object is usually negligible compared to the convenience of having a full object that you can work with in other places in your pipeline. It's easier to track down references to Employee versus scattered strings that correspond to their name. Still - for larger queries or larger tables, there are times when it's way more efficient to only fetch the columns that you need. You can do this by specifying the columns you want within the initial selection:

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'])

Unlike before when we passed a regular Employee class to the select function, we're now passing a tuple of the different columns that we want to fetch. For any selection of more than one object, you'll need to pass a tuple.

Filter lists

Exact equality or boolean operations are not the only way to filter data. You can also use some extensions to filter data in a more complex way. For example, let's say that we want to get all employees that are either 28 or 36 years old. We could make two separate equality filters like the example above. But it's often easier to use the in_ function to filter data based on a list of values:

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]])

This will work at runtime with or without the call to column. But if you use static analysis like mypy or pyright to validate your code, you'll need to use the column typecast to avoid type errors. Why? In Iceaxe, the type of the column mirrors the type of the instance attribute. For the purposes of static analysis, Employee.age here validates to an integer. And the integer primitive in Python doesn't have a method called in_. By using the column typecast, you're explicitly telling the static analysis tool that you're working with a column object that supports this method accessor.

Aggregation queries

Aggregation queries allow you to perform calculations on groups of rows in your database. Iceaxe provides a simple way to construct these queries using the group_by method and aggregation functions. To make things more interesting, let's add a bit more data with some overlapping values:

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]

Grouping rows and then executing these functions is even more powerful, because you can perform different rollups. A simple one to find the amount of employees by age.

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

If you're working on a more complex, and still want to receive the results as Iceaxe typed objects, you can do a manual casting of the results. Using the text() method of a selection query will override any other dynamic query building and just pass your SQL directly to the database engine.

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 class is a utility class that allows you to cast Table definitions to a SQL fstring. This gives you the ability to use the same Table definitions in your SQL as you do in your Python code, alongside basic typehinting of table columns. If you change column names, your linter should detect the conflict in this SQL as well.

There are a few different sql methods depending on where you're inserting them in your SQL.

Always use sql.select for select queries, since these will internally map to our ORM convention that's required to cast the results to the correct Python object during postprocessing.

We will typecast the results as whatever you pass to the original select method, so make sure this value is always kept in sync with what you query in your manual SQL text.

The text method accept a single string of a SQL operation, then as many variables as you pass within the keyword arguments. These variables are accessible to your SQL string as one-indexed variables that correspond to the order of arguments. These are formatted as $1, $2, etc. Variables passed via Python variable will be encoded correctly to avoid SQL injection attacks so are recommended for any user input.

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

If you have more complicated selection logic that doesn't directly reference table selections via sql.select, we also support aliasing values to directly extract them from the query.

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 alias function allows you to map SQL results to typed Python values, while func provides type-safe SQL function calls. When combined, they enable you to write complex queries with proper type checking and automatic deserialization.