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:
| id | name | age |
|---|---|---|
| 1 | Marcus Holloway | 28 |
| 2 | Astrid Keller | 42 |
| 3 | Jasper Okafor | 36 |
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 asselect(Employee)- It changes the return type from
list[Employee]toEmployee - It adds
LIMIT 1to the generated SQL - If no rows match,
DBConnection.exec()raisesNoObjectFound
.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:
NULL = NULLreturns NULL (not TRUE)NULL != NULLreturns NULL (not FALSE)NULL IS NULLreturns TRUENULL IS NOT NULLreturns FALSE
In Python:
None == Nonereturns TrueNone != Nonereturns FalseNone is Nonereturns TrueNone is not Nonereturns 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:
| id | name | age |
|---|---|---|
| 1 | Marcus Holloway | 28 |
| 2 | Astrid Keller | 42 |
| 3 | Jasper Okafor | 36 |
| 4 | Kaitlyn Winters | 36 |
| 5 | Merilyn Keller | 36 |
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
Iceaxe's query builder supports operations it can type correctly. For queries outside that support, use raw SQL and declare the expected result types.
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:
| Method | Input | Output | Description |
|---|---|---|---|
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.