`derp.orm.query` – Query Builder¶

Query builder module for Derp ORM.

class derp.orm.query.SelectQuery[source]¶

Bases: _WhereShorthandMixin, Generic

SELECT query - T is the result element type (Table subclass or dict).

__init__(pool, columns, *, cache_store=None, router=None)[source]¶

Parameters:

pool (Pool | Connection | None)
columns (tuple[type[Table] | Column[Any] | Expression, ...])
cache_store (KVClient | None)
router (ReplicaRouter | None)

from_(table)[source]¶

Set the FROM table. Accepts a Table class, string, or subquery.

Parameters:: table (type[Table] | str | SubqueryExpr)
Return type:: Self

where(cond)[source]¶

Add WHERE clause. Multiple calls combine with AND.

Parameters:: cond (Expression)
Return type:: Self

inner_join(table, condition)[source]¶

Add INNER JOIN.

Parameters:

table (type[Table])
condition (Expression)

Return type:

Self

left_join(table, condition)[source]¶

Add LEFT JOIN.

Parameters:

table (type[Table])
condition (Expression)

Return type:

Self

right_join(table, condition)[source]¶

Add RIGHT JOIN.

Parameters:

table (type[Table])
condition (Expression)

Return type:

Self

full_join(table, condition)[source]¶

Add FULL OUTER JOIN.

Parameters:

table (type[Table])
condition (Expression)

Return type:

Self

cross_join(table)[source]¶

Add CROSS JOIN.

Parameters:: table (type[Table])
Return type:: Self

order_by(column, *, asc=True)[source]¶

Add ORDER BY clause.

Parameters:

column (Column[Any] | str)
asc (bool)

Return type:

Self

limit(n)[source]¶

Add LIMIT clause.

Parameters:: n (int)
Return type:: Self

offset(n)[source]¶

Add OFFSET clause.

Parameters:: n (int)
Return type:: Self

group_by(*columns)[source]¶

Add GROUP BY clause.

Parameters:: columns (Column[Any] | str)
Return type:: Self

having(cond)[source]¶

Add HAVING clause. Multiple calls combine with AND.

Parameters:: cond (Expression)
Return type:: Self

cache(ttl, *, lock_ttl=None, retry_delay=None)[source]¶

Cache this query’s results for ttl seconds.

Parameters:

ttl (float)
lock_ttl (float | None)
retry_delay (float | None)

Return type:

Self

use_primary()[source]¶

Force this query to run against the primary database.

Return type:: Self

distinct()[source]¶

Add DISTINCT to SELECT.

Return type:: Self

distinct_on(*columns)[source]¶

Add DISTINCT ON to SELECT (PostgreSQL-specific).

Parameters:: columns (Column[Any])
Return type:: Self

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

Add FOR UPDATE row lock.

Parameters:

nowait (bool)
skip_locked (bool)

Return type:

Self

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

Add FOR SHARE row lock.

Parameters:

nowait (bool)
skip_locked (bool)

Return type:

Self

as_(alias)[source]¶

Wrap this query as a subquery expression with an alias.

Parameters:: alias (str)
Return type:: SubqueryExpr

exists()[source]¶

Wrap this query as an EXISTS expression.

Return type:: ExistsExpr

with_cte(name, query)[source]¶

Add a Common Table Expression (WITH clause).

Parameters:

name (str)
query (SelectQuery[Any])

Return type:

Self

union(other)[source]¶

Combine with another query using UNION.

Parameters:: other (SelectQuery[Any])
Return type:: SetOperationQuery[T]

union_all(other)[source]¶

Combine with another query using UNION ALL.

Parameters:: other (SelectQuery[Any])
Return type:: SetOperationQuery[T]

intersect(other)[source]¶

Combine with another query using INTERSECT.

Parameters:: other (SelectQuery[Any])
Return type:: SetOperationQuery[T]

except_(other)[source]¶

Combine with another query using EXCEPT.

Parameters:: other (SelectQuery[Any])
Return type:: SetOperationQuery[T]

build()[source]¶

Build the SQL query and parameters.

Return type:: tuple[str, list[Any]]

build_count()[source]¶

Build a COUNT(*) SQL query and parameters.

Return type:: tuple[str, list[Any]]

async execute()[source]¶

Execute the query and return results.

Return type:: list[T]

async first_or_none()[source]¶

Execute and return first result or None.

Return type:: T | None

async first()[source]¶

Execute and return first result.

Return type:: T

async count()[source]¶

Execute a COUNT(*) query and return the count.

Return type:: int

class derp.orm.query.InsertQuery[source]¶

Bases: _InsertQueryBase, Generic

INSERT query without RETURNING - execute() returns None.

values(**kwargs)[source]¶

Set values to insert.

Parameters:: kwargs (Any)
Return type:: InsertQuery[T]

values_list(rows)[source]¶

Set multiple rows to insert. Returns a bulk query.

Parameters:: rows (list[dict[str, Any]])
Return type:: InsertBulkQuery[T]

columns(*cols)[source]¶

Set column names for INSERT … SELECT.

Parameters:: cols (Column[Any] | str)
Return type:: InsertQuery[T]

from_select(query)[source]¶

Set the SELECT query for INSERT … SELECT.

Parameters:: query (SelectQuery[Any])
Return type:: InsertQuery[T]

ignore_conflicts(*, target)[source]¶

Add ON CONFLICT DO NOTHING.

Returns a query whose returning().execute() yields T | None instead of T, since the conflict may suppress the insert.

Parameters:: target (Column[Any] | tuple[Column[Any], ...])
Return type:: InsertQueryIgnoreConflicts[T]

upsert(*, target, **kwargs)[source]¶

Add ON CONFLICT DO UPDATE SET (upsert).

Pass the columns to update as keyword arguments:

.upsert(target=User.email, name="Updated")

Parameters:

target (Column[Any] | tuple[Column[Any], ...])
kwargs (Any)

Return type:

InsertQuery[T]

returning(table: type[T], /) → ReturningOne[T][source]¶

returning(c1: Column[V], /) → ReturningOneScalar[T, V]

returning(c1: Column[A], c2: Column[B], /) → ROT2[T, A, B]

returning(c1: Column[A], c2: Column[B], c3: Column[C], /) → ROT3[T, A, B, C]

returning(c1: Column[A], c2: Column[B], c3: Column[C], c4: Column[D], /) → ROT4[T, A, B, C, D]

returning(c1: Column[A], c2: Column[B], c3: Column[C], c4: Column[D], c5: Column[E], /) → ROT5[T, A, B, C, D, E]

returning(c1: Column[A], c2: Column[B], c3: Column[C], c4: Column[D], c5: Column[E], c6: Column[F], /) → ROT6[T, A, B, C, D, E, F]

returning(c1: Column[A], c2: Column[B], c3: Column[C], c4: Column[D], c5: Column[E], c6: Column[F], c7: Column[G], /) → ROT7[T, A, B, C, D, E, F, G]

returning(c1: Column[A], c2: Column[B], c3: Column[C], c4: Column[D], c5: Column[E], c6: Column[F], c7: Column[G], c8: Column[H], /) → ROT8[T, A, B, C, D, E, F, G, H]

returning(c1: Column[A], c2: Column[B], c3: Column[C], c4: Column[D], c5: Column[E], c6: Column[F], c7: Column[G], c8: Column[H], c9: Column[I], /) → ROT9[T, A, B, C, D, E, F, G, H, I]

returning(c1: Column[A], c2: Column[B], c3: Column[C], c4: Column[D], c5: Column[E], c6: Column[F], c7: Column[G], c8: Column[H], c9: Column[I], c10: Column[J], /) → ROT10[T, A, B, C, D, E, F, G, H, I, J]

Add RETURNING clause.

Parameters:: columns (Any)
Return type:: ReturningOne[T] | ReturningOneScalar[T, Any] | ReturningOneTuple[T]

build()[source]¶

Build the SQL query and parameters.

Return type:: tuple[str, list[Any]]

async execute()[source]¶

Execute the insert.

Return type:: None

class derp.orm.query.UpdateQuery[source]¶

Bases: _WhereShorthandMixin, _ReturningManyMixin, Generic

UPDATE query — execute() returns None, returning() for results.

__init__(pool, table, *, router=None)[source]¶

Parameters:

pool (asyncpg.Pool | asyncpg.Connection | None)
table (type[T])
router (ReplicaRouter | None)

set(**kwargs)[source]¶

Set values to update.

Parameters:: kwargs (Any)
Return type:: UpdateQuery[T]

where(cond)[source]¶

Add WHERE clause. Multiple calls combine with AND.

Parameters:: cond (Expression)
Return type:: UpdateQuery[T]

build()[source]¶

Return type:: tuple[str, list[Any]]

async execute()[source]¶

Execute the update.

Return type:: None

class derp.orm.query.DeleteQuery[source]¶

Bases: _WhereShorthandMixin, _ReturningManyMixin, Generic

DELETE query — execute() returns None, returning() for results.

__init__(pool, table, *, router=None)[source]¶

Parameters:

pool (asyncpg.Pool | asyncpg.Connection | None)
table (type[T])
router (ReplicaRouter | None)

where(cond)[source]¶

Add WHERE clause. Multiple calls combine with AND.

Parameters:: cond (Expression)
Return type:: DeleteQuery[T]

build()[source]¶

Return type:: tuple[str, list[Any]]

async execute()[source]¶

Execute the delete.

Return type:: None

class derp.orm.query.TableRef[source]¶

Bases: object

Non ORM table reference for string-based queries.

Example:

db.table("users").select("*").eq("id", 1).execute()
db.table("users").insert({"name": "Bob"}).execute()
db.table("users").update({"name": "Robert"}).eq("id", 1).execute()
db.table("users").delete().eq("id", 1).execute()

__init__(table_name, pool, *, cache_store=None, router=None)[source]¶

Parameters:

table_name (str)
pool (Pool | Connection | None)
cache_store (KVClient | None)
router (ReplicaRouter | None)

select(*columns)[source]¶

Start a SELECT query.

Parameters:: *columns (str) – Column names to select. Use "*" or omit for all columns. A single comma-separated string like "name, email" is also accepted.
Returns:: SelectQuery[dict[str, Any]]
Return type:: SelectQuery[dict[str, Any]]

insert(values)[source]¶

Start an INSERT query.

Parameters:: values (dict[str, Any]) – Column-value mapping to insert.
Returns:: UntypedInsertQuery
Return type:: UntypedInsertQuery

update(values)[source]¶

Start an UPDATE query.

Parameters:: values (dict[str, Any]) – Column-value mapping of SET assignments.
Returns:: UntypedUpdateQuery
Return type:: UntypedUpdateQuery

delete()[source]¶

Start a DELETE query.

Returns:: UntypedDeleteQuery
Return type:: UntypedDeleteQuery

class derp.orm.query.JoinType[source]¶

Bases: StrEnum

SQL JOIN types.

INNER = 'INNER'¶

LEFT = 'LEFT'¶

RIGHT = 'RIGHT'¶

FULL = 'FULL OUTER'¶

CROSS = 'CROSS'¶

__new__(value)¶

class derp.orm.query.LockMode[source]¶

Bases: StrEnum

SQL row-level locking modes.

UPDATE = 'FOR UPDATE'¶

NO_KEY_UPDATE = 'FOR NO KEY UPDATE'¶

SHARE = 'FOR SHARE'¶

KEY_SHARE = 'FOR KEY SHARE'¶

__new__(value)¶

class derp.orm.query.SortOrder[source]¶

Bases: StrEnum

Column sort order within an index.

ASC = 'ASC'¶

DESC = 'DESC'¶

__new__(value)¶

class derp.orm.query.LogicalOperator[source]¶

Bases: StrEnum

SQL logical operators.

AND = 'AND'¶

OR = 'OR'¶

__new__(value)¶

class derp.orm.query.ComparisonOperator[source]¶

Bases: StrEnum

SQL comparison operators.

EQ = '='¶

NE = '<>'¶

GT = '>'¶

GTE = '>='¶

LT = '<'¶

LTE = '<='¶

__new__(value)¶

class derp.orm.query.Expression[source]¶

Bases: ABC

Base class for SQL expressions.

abstractmethod to_sql(params)[source]¶

Generate SQL string with parameterized values.

Parameters:: params (list[Any])
Return type:: str

in_(values)[source]¶

Parameters:: values (Any)
Return type:: Any

not_in(values)[source]¶

Parameters:: values (Any)
Return type:: Any

like(pattern)[source]¶

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

ilike(pattern)[source]¶

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

is_null()[source]¶

Return type:: Any

is_not_null()[source]¶

Return type:: Any

between(low, high)[source]¶

Parameters:

low (Any)
high (Any)

Return type:

Any

matches(query, *, language='english', style='websearch', stored=False)[source]¶

Full-text search match using @@.

stored: set to True when the column is a pre-computed tsvector (skips the to_tsvector() wrapper). style: "websearch" (default), "plain", or "phrase".

Parameters:

query (str)
language (str)
style (Literal['websearch', 'plain', 'phrase'])
stored (bool)

Return type:

Any

ts_rank(query, *, language='english', style='websearch', stored=False)[source]¶

Full-text search rank for ORDER BY.

stored: set to True when the column is a pre-computed tsvector (skips the to_tsvector() wrapper).

Parameters:

query (str)
language (str)
style (Literal['websearch', 'plain', 'phrase'])
stored (bool)

Return type:

Any

ts_headline(query, *, language='english', style='websearch', max_words=None, min_words=None, max_fragments=None, start_sel=None, stop_sel=None, fragment_delimiter=None, highlight_all=None, short_word=None)[source]¶

Highlighted search snippet for display in results.

Parameters:

query (str)
language (str)
style (Literal['websearch', 'plain', 'phrase'])
max_words (int | None)
min_words (int | None)
max_fragments (int | None)
start_sel (str | None)
stop_sel (str | None)
fragment_delimiter (str | None)
highlight_all (bool | None)
short_word (int | None)

Return type:

Any

class derp.orm.query.RawSQL[source]¶

Bases: Expression

Raw SQL fragment with optional parameterized values.

Use the sql() factory function to create instances:

sql("NOW()")
sql("age > {}", 18)
sql("age > {} AND name = {}", 18, "Alice")

template: str¶

values: tuple[Any, ...]¶

to_sql(params)[source]¶

Generate SQL string with parameterized values.

Parameters:: params (list[Any])
Return type:: str

as_(alias)[source]¶

Return a copy with an AS alias.

Parameters:: alias (str)
Return type:: RawSQL

__init__(template, values, _alias=None)¶

Parameters:

template (str)
values (tuple[Any, ...])
_alias (str | None)

Return type:

None

derp.orm.query.sql(template, *values)[source]¶

Create a raw SQL expression fragment.

Use {} as placeholder for parameterized values:

sql("NOW()")
sql("age > {}", 18)
sql("CONCAT({}, name)", "Dr. ")

Parameters:

template (str)
values (Any)

Return type:

RawSQL

derp.orm.query – Query Builder¶

`derp.orm.query` – Query Builder¶