Query API¶

QuerySet¶

class nextorm.query.QuerySet[source]¶

Bases: Generic

A lazy, immutable query builder for a single entity type.

Each method returns a new QuerySet — the original is unchanged — so chaining is safe:

base = db.select(User)
young = base.filter(User.age < 30)
old = base.filter(User.age >= 30)  # independent of `young`

__init__(entity_class, table, db, builder)[source]¶

Parameters:

entity_class (type[ET])
table (Table)
db (Database)
builder (SQLBuilder)

Return type:

None

filter(*conditions)[source]¶

Narrow results with one or more WHERE conditions.

Multiple conditions are combined with AND:

qs.filter(User.age >= 18, User.active == True)
# → WHERE age >= 18 AND active = 1

Parameters:: conditions (SqlNode)
Return type:: QuerySet[ET]

where(predicate)[source]¶

Narrow results using a lambda predicate over a column proxy.

The lambda receives an EntityProxy whose attribute accesses return ColumnExpr objects, so comparison operators build SQL conditions transparently:

qs.where(lambda p: p.price > 100)
qs.where(lambda u: u.name == "alice")

Chain multiple .where() calls to combine conditions with AND:

qs.where(lambda u: u.age >= 18).where(lambda u: u.active == True)

Parameters:: predicate (Callable[[Any], BinOp])
Return type:: QuerySet[ET]

order_by(*items)[source]¶

Set the ORDER BY clause, replacing any previous ordering.

Use asc() / desc() on a column expression to build order items:

qs.order_by(User.name.asc(), User.age.desc())

Parameters:: items (OrderItem)
Return type:: QuerySet[ET]

limit(n)[source]¶

Limit the number of rows returned.

Parameters:: n (int)
Return type:: QuerySet[ET]

offset(n)[source]¶

Skip the first n rows.

Parameters:: n (int)
Return type:: QuerySet[ET]

join(table_or_entity, on, *, join_type='INNER', alias=None)[source]¶

Add a JOIN clause to the query.

Parameters¶

table_or_entity:: The table (name string) or entity class to join.
on:: The ON condition.
join_type:: SQL join type: "INNER" (default), "LEFT", "RIGHT", or "FULL".
alias:: Optional alias for the joined table.

Example:

db.select(Post).join(Comment, Comment.post_id == Post.id, join_type="LEFT").fetch_all()

Parameters:

table_or_entity (type[Entity] | str)
on (BinOp)
join_type (str)
alias (str | None)

Return type:

QuerySet[ET]

prefetch(*relation_attrs)[source]¶

Declare relations to eager-load alongside the main query.

After fetch_all() executes the main SELECT, one additional query per prefetched relation is issued and the results are attached to the returned entity instances. This avoids the N+1 query problem.

Parameters¶

*relation_attrs:: Attribute descriptors from the entity class, e.g. Post.tags. String attribute names are also accepted.

Example:

posts = db.select(Post).prefetch(Post.comments).fetch_all()
for post in posts:
    # post.comments is pre-populated — no extra query fired
    print(post.comments.count())

Parameters:: relation_attrs (Any)
Return type:: QuerySet[ET]

fetch_all()[source]¶

Execute the query and return all matching entity instances.

Return type:: list[ET]

fetch_one()[source]¶

Execute with LIMIT 1 and return the first entity, or None.

Return type:: ET | None

first()[source]¶

Alias for fetch_one().

Return type:: ET | None

count()[source]¶

Return the number of rows matching the current filter.

Return type:: int

exists()[source]¶

Return True if at least one row matches the current filter.

Return type:: bool

get()[source]¶

Return the single matching entity, or None if no rows match.

Raises MultipleObjectsFoundError if more than one row matches the current filter — use fetch_one() when you only want the first row regardless of how many exist.

Example:

user = db.select(User).filter(User.name == "alice").get()

Return type:: ET | None

get_or_raise()[source]¶

Return the single matching entity; raise if zero or more-than-one match.

Raises ObjectNotFound when no row matches. Raises MultipleObjectsFoundError when more than one row matches.

Example:

user = db.select(User).filter(User.id == pk).get_or_raise()

Return type:: ET

delete()[source]¶

Delete all rows matched by the current filter.

Returns the number of deleted rows.

Return type:: int

update(**field_values)[source]¶

Bulk-update matched rows with the given field-value pairs.

Returns the number of updated rows. Only columns that correspond to direct entity fields (not FK columns) are accepted:

db.select(User).filter(User.active == False).update(active=True)

Parameters:: field_values (Any)
Return type:: int

distinct()[source]¶

Enable SELECT DISTINCT for this query.

Return type:: QuerySet[ET]

without_distinct()[source]¶

Disable SELECT DISTINCT (reverses a previous distinct() call).

Return type:: QuerySet[ET]

for_update(*, skip_locked=False, nowait=False)[source]¶

Append a FOR UPDATE (or FOR UPDATE SKIP LOCKED / FOR UPDATE NOWAIT) clause.

Useful for pessimistic locking. The behaviour is provider-specific: SQLite does not support FOR UPDATE natively (it locks at the connection level instead).

Parameters¶

skip_locked:: When True, rows already locked by another transaction are silently skipped rather than causing a wait or error.
nowait:: When True, an attempt to lock already-locked rows raises an error immediately instead of waiting. Mutually exclusive with skip_locked.

Parameters:

skip_locked (bool)
nowait (bool)

Return type:

QuerySet[ET]

page(pagenum, pagesize=10)[source]¶

Return a page of results.

Page numbers are 1-based. Equivalent to .offset((pagenum-1)*pagesize).limit(pagesize):

db.select(Product).order_by(Product.name).page(2, pagesize=20)

Parameters:

pagenum (int)
pagesize (int)

Return type:

QuerySet[ET]

random(n)[source]¶

Return n randomly ordered rows.

Uses ORDER BY RANDOM() (SQLite / PostgreSQL) or ORDER BY RAND() (MariaDB).

Parameters:: n (int)
Return type:: QuerySet[ET]

sum(attr)[source]¶

Return SUM(attr) or None when no rows match.

Parameters:: attr (str)
Return type:: Any

avg(attr)[source]¶

Return AVG(attr) or None when no rows match.

Parameters:: attr (str)
Return type:: Any

min(attr)[source]¶

Return MIN(attr) or None when no rows match.

Parameters:: attr (str)
Return type:: Any

max(attr)[source]¶

Return MAX(attr) or None when no rows match.

Parameters:: attr (str)
Return type:: Any

group_concat(attr, sep=',')[source]¶

Return the concatenation of all non-NULL values of attr.

Uses the database-native aggregate:

SQLite / MariaDB: GROUP_CONCAT(col, sep)
PostgreSQL: STRING_AGG(col, sep)

Returns None when no rows match or all values are NULL.

Example:

names = db.select(User).filter(User.active == True).group_concat("name", ", ")

Parameters:

attr (str)
sep (str)

Return type:

str | None

get_sql()[source]¶

Return the SQL string that would be executed by fetch_all().

Useful for debugging or logging. Parameters are shown as ? (SQLite) or %s (PostgreSQL / MariaDB) placeholders:

print(db.select(User).filter(User.age > 18).get_sql())

Return type:: str

show(width=120, *, file=None)[source]¶

Pretty-print query results as a plain-text table.

Fetches all rows and renders them to file (default: sys.stdout) with aligned columns bounded by width characters total. Useful for interactive debugging.

Parameters¶

width:: Maximum total table width in characters. Column content is truncated proportionally when the natural width exceeds this.
file:: Output stream; defaults to sys.stdout.

Example:

db.select(User).show()
# +----+---------+-----+
# | id | name    | age |
# +----+---------+-----+
# |  1 | alice   |  30 |
# |  2 | bob     |  25 |
# +----+---------+-----+

Parameters:

width (int)
file (IO[str] | None)

Return type:

None

raw(sql, params=None)[source]¶

Execute sql and map each result row to an entity instance.

Column names in the cursor description are matched to entity fields by name, so the column order in sql does not have to follow the schema order. Columns that don’t match any field are silently ignored.

Parameters¶

sql:: Raw SQL SELECT statement.
params:: Positional bind parameters (? placeholders for SQLite, %s for PostgreSQL / MariaDB).

Example:

users = db.select(User).raw("SELECT * FROM user WHERE age > ?", [18])

Parameters:

sql (str)
params (list[Any] | None)

Return type:

list[ET]

raw_one(sql, params=None)[source]¶

Execute sql and return the first mapped entity, or None.

Behaves like raw() but returns at most one result. The SQL should ideally include LIMIT 1 for efficiency.

Parameters:

sql (str)
params (list[Any] | None)

Return type:

ET | None

EntityProxy¶

class nextorm.query.EntityProxy[source]¶

Bases: object

A lightweight proxy passed to a where lambda.

Attribute access returns a ColumnExpr so the lambda can build SQL predicates without referencing the entity class directly:

qs.where(lambda p: p.price > 100)
qs.where(lambda u: u.name == "alice")

The proxy is created automatically by QuerySet.where(); direct construction is only needed in tests or advanced usage.

__init__(table_name)[source]¶

Parameters:: table_name (str)
Return type:: None

Generator expressions¶

Generator-expression query syntax.

Usage:

from nextorm import Database, Entity, Req
from nextorm.generators import select


class Product(Entity):
    name: Req[str]
    price: Req[float]


db = Database(entities=[Product])
db.bind("sqlite", ":memory:")
db.generate_mapping(create_tables=True)

# Equivalent to db.select(Product).filter(Product.price > 100)
results = select(p for p in Product if p.price > 100)

Supported filter expressions¶

Simple comparisons: p.attr == val, p.attr > val, etc.
Logical conjunctions: p.a > 1 and p.b < 5
Logical disjunctions: p.a == 1 or p.b == 2
Negation: not p.active

Limitations¶

The decompiler translates Python bytecode back to SQL AST nodes. It handles the common single-attribute comparison patterns. Complex Python expressions (function calls, multi-level attribute access, etc.) are not supported and will raise DecompileError.

The query is executed against the database that the entity was last registered with (either through a db_session() or by calling db.save() / db.select(...) at least once). To use with a specific database, prefer db.select(Entity).filter(...) instead.

nextorm.generators.select(gen)[source]¶

Execute a generator-expression query and return a QuerySet.

The generator expression must iterate over a single entity class:

select(p for p in Product if p.price > 100)

This is syntactic sugar for:

db.select(Product).filter(Product.price > 100)

The database to use is determined by inspecting the _db_ attribute on the entity class’s iterator (set from EntityMeta.__iter__()). The entity class must have been registered with a bound, mapped database.

Raises¶

DecompileError: If the filter condition cannot be decompiled.
RuntimeError: If the entity class has no associated database context.

Parameters:: gen (Generator[T, None, None])
Return type:: QuerySet[T]

nextorm.generators.count(gen)[source]¶

Return the number of entities matching the generator-expression filter.

Example:

n = count(p for p in Product if p.price > 100)

Equivalent to:

db.select(Product).filter(Product.price > 100).count()

Parameters:: gen (Generator[T, None, None])
Return type:: int

nextorm.generators.avg(gen)[source]¶

Compute AVG of the attribute yielded by the generator expression.

The generator must yield a field attribute, not the entity itself:

mean_price = avg(p.price for p in Product if p.active)

Equivalent to:

db.select(Product).filter(Product.active == True).avg("price")

Raises¶

DecompileError: If the generator yields the entity rather than a field attribute.

Parameters:: gen (Generator[Any, None, None])
Return type:: Any

nextorm.generators.sum(gen)[source]¶

Compute SUM of the attribute yielded by the generator expression.

The generator must yield a field attribute, not the entity itself:

total = sum(p.price for p in Product if p.in_stock)

Equivalent to:

db.select(Product).filter(Product.in_stock == True).sum("price")

Raises¶

DecompileError: If the generator yields the entity rather than a field attribute.

Parameters:: gen (Generator[Any, None, None])
Return type:: Any

nextorm.generators.min(gen)[source]¶

Compute MIN of the attribute yielded by the generator expression.

The generator must yield a field attribute, not the entity itself:

cheapest = min(p.price for p in Product)

Equivalent to:

db.select(Product).min("price")

Raises¶

DecompileError: If the generator yields the entity rather than a field attribute.

Parameters:: gen (Generator[Any, None, None])
Return type:: Any

nextorm.generators.max(gen)[source]¶

Compute MAX of the attribute yielded by the generator expression.

The generator must yield a field attribute, not the entity itself:

priciest = max(p.price for p in Product)

Equivalent to:

db.select(Product).max("price")

Raises¶

DecompileError: If the generator yields the entity rather than a field attribute.

Parameters:: gen (Generator[Any, None, None])
Return type:: Any

exception nextorm.generators.DecompileError[source]¶

Bases: Exception

Raised when the bytecode decompiler cannot translate a generator expression.