Skip to content

expressions

Column expressions: arithmetic and functions over columns evaluated by the database, used as filter values and as values of QuerySet.update()::

Text Only
1
2
3
4
Slot.objects.filter(taken__lt=F("capacity"))
Slot.objects.filter(id=1).update(taken=Slot.taken + 1)
Member.objects.filter(id=1).update(read_id=Greatest(F("read_id"), 10))
Player.objects.update(each=True, score=Random())

and in update_values of upserts, where Inserted("path") is the value the insert tried to write::

Text Only
1
2
3
4
5
6
7
8
Level.objects.create(
    ...,
    on_conflict="update",
    update_values={
        "completed": F("completed") + 1,
        "read_id": Greatest(F("read_id"), Inserted("read_id")),
    },
)

Model.field accessors are expressions themselves, F("path") names a field by its path. Expression classes define only dunders and underscored names, so they never shadow a field reached through a FieldAccessor.

Coalesce

Bases: Function

The first argument that is not NULL.

Source code in ormar/queryset/expressions.py
Python
185
186
187
188
class Coalesce(Function):
    """The first argument that is not ``NULL``."""

    _sql_name = "coalesce"

Combined

Bases: Expression

Two operands combined with an arithmetic operator.

Source code in ormar/queryset/expressions.py
Python
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
class Combined(Expression):
    """Two operands combined with an arithmetic operator."""

    def __init__(
        self, left: Expression, op: Callable[[Any, Any], Any], right: Any
    ) -> None:
        """
        :param left: left operand
        :type left: Expression
        :param op: arithmetic operator
        :type op: Callable[[Any, Any], Any]
        :param right: right operand, an expression or a literal
        :type right: Any
        """
        self._left = left
        self._op = op
        self._right = right

__init__(left, op, right)

Parameters:

Name Type Description Default
left Expression

left operand

required
op Callable[[Any, Any], Any]

arithmetic operator

required
right Any

right operand, an expression or a literal

required
Source code in ormar/queryset/expressions.py
Python
119
120
121
122
123
124
125
126
127
128
129
130
131
132
def __init__(
    self, left: Expression, op: Callable[[Any, Any], Any], right: Any
) -> None:
    """
    :param left: left operand
    :type left: Expression
    :param op: arithmetic operator
    :type op: Callable[[Any, Any], Any]
    :param right: right operand, an expression or a literal
    :type right: Any
    """
    self._left = left
    self._op = op
    self._right = right

Expression

Base of column expressions, combined with +, - and *. The literal operand has to be on the right side (F("a") + 1).

Source code in ormar/queryset/expressions.py
Python
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
class Expression:
    """
    Base of column expressions, combined with ``+``, ``-`` and ``*``. The
    literal operand has to be on the right side (``F("a") + 1``).
    """

    def _node(self) -> "Expression":
        """
        :return: the expression as a node of an expression tree
        :rtype: Expression
        """
        return self

    def _combine(self, op: Callable[[Any, Any], Any], other: Any) -> "Combined":
        """
        :param op: ``operator.add``, ``operator.sub`` or ``operator.mul``
        :type op: Callable[[Any, Any], Any]
        :param other: right operand, an expression or a literal
        :type other: Any
        :raises QueryDefinitionError: if the operand is None or a bool
        :return: the combined expression
        :rtype: Combined
        """
        if other is None or isinstance(other, bool):
            raise QueryDefinitionError("Expressions take no None or bool operands")
        if isinstance(other, Expression):
            other = other._node()
        return Combined(left=self._node(), op=op, right=other)

    def __add__(self, other: Any) -> "Combined":
        """SQL ``expression + other``"""
        return self._combine(op=operator.add, other=other)

    def __sub__(self, other: Any) -> "Combined":
        """SQL ``expression - other``"""
        return self._combine(op=operator.sub, other=other)

    def __mul__(self, other: Any) -> "Combined":
        """SQL ``expression * other``"""
        return self._combine(op=operator.mul, other=other)

__add__(other)

SQL expression + other

Source code in ormar/queryset/expressions.py
Python
73
74
75
def __add__(self, other: Any) -> "Combined":
    """SQL ``expression + other``"""
    return self._combine(op=operator.add, other=other)

__mul__(other)

SQL expression * other

Source code in ormar/queryset/expressions.py
Python
81
82
83
def __mul__(self, other: Any) -> "Combined":
    """SQL ``expression * other``"""
    return self._combine(op=operator.mul, other=other)

__sub__(other)

SQL expression - other

Source code in ormar/queryset/expressions.py
Python
77
78
79
def __sub__(self, other: Any) -> "Combined":
    """SQL ``expression - other``"""
    return self._combine(op=operator.sub, other=other)

F

Bases: Expression

Field by its path from the queried model: F("capacity"), or in filters also a field of a related model (filter(taken__lt=F("owner__limit"))).

Source code in ormar/queryset/expressions.py
Python
86
87
88
89
90
91
92
93
94
95
96
97
class F(Expression):
    """
    Field by its path from the queried model: ``F("capacity")``, or in filters
    also a field of a related model (``filter(taken__lt=F("owner__limit"))``).
    """

    def __init__(self, path: str) -> None:
        """
        :param path: field name, prefixed by its relation path if any
        :type path: str
        """
        self._path = path

__init__(path)

Parameters:

Name Type Description Default
path str

field name, prefixed by its relation path if any

required
Source code in ormar/queryset/expressions.py
Python
92
93
94
95
96
97
def __init__(self, path: str) -> None:
    """
    :param path: field name, prefixed by its relation path if any
    :type path: str
    """
    self._path = path

Function

Bases: Expression

SQL function of two or more arguments, at least one of them an expression; the others may be literals.

Source code in ormar/queryset/expressions.py
Python
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
class Function(Expression):
    """
    SQL function of two or more arguments, at least one of them an expression;
    the others may be literals.
    """

    _sql_name = ""

    def __init__(self, *arguments: Any) -> None:
        """
        :param arguments: expressions and literals
        :type arguments: Any
        :raises QueryDefinitionError: if there are less than two arguments, none
            of them is an expression or a literal is None or a bool
        """
        name = type(self).__name__
        if len(arguments) < 2:
            raise QueryDefinitionError(f"{name}() takes at least two arguments")
        if not any(isinstance(argument, Expression) for argument in arguments):
            raise QueryDefinitionError(
                f"{name}() needs a field or an expression among its arguments"
            )
        if any(
            argument is None or isinstance(argument, bool) for argument in arguments
        ):
            raise QueryDefinitionError(f"{name}() takes no None or bool arguments")
        self._arguments = tuple(
            argument._node() if isinstance(argument, Expression) else argument
            for argument in arguments
        )

__init__(*arguments)

Parameters:

Name Type Description Default
arguments Any

expressions and literals

()

Raises:

Type Description
QueryDefinitionError

if there are less than two arguments, none of them is an expression or a literal is None or a bool

Source code in ormar/queryset/expressions.py
Python
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
def __init__(self, *arguments: Any) -> None:
    """
    :param arguments: expressions and literals
    :type arguments: Any
    :raises QueryDefinitionError: if there are less than two arguments, none
        of them is an expression or a literal is None or a bool
    """
    name = type(self).__name__
    if len(arguments) < 2:
        raise QueryDefinitionError(f"{name}() takes at least two arguments")
    if not any(isinstance(argument, Expression) for argument in arguments):
        raise QueryDefinitionError(
            f"{name}() needs a field or an expression among its arguments"
        )
    if any(
        argument is None or isinstance(argument, bool) for argument in arguments
    ):
        raise QueryDefinitionError(f"{name}() takes no None or bool arguments")
    self._arguments = tuple(
        argument._node() if isinstance(argument, Expression) else argument
        for argument in arguments
    )

Greatest

Bases: Function

The largest of the arguments. PostgreSQL ignores NULL arguments, MySQL and SQLite return NULL when any argument is NULL.

Source code in ormar/queryset/expressions.py
Python
167
168
169
170
171
172
173
class Greatest(Function):
    """
    The largest of the arguments. PostgreSQL ignores ``NULL`` arguments,
    MySQL and SQLite return ``NULL`` when any argument is ``NULL``.
    """

    _sql_name = "greatest"

GreatestOf

Bases: ReturnTypeFromArgs

SQL greatest(), the scalar max() on SQLite.

Source code in ormar/queryset/expressions.py
Python
198
199
200
201
202
203
class GreatestOf(ReturnTypeFromArgs):
    """SQL ``greatest()``, the scalar ``max()`` on SQLite."""

    name = "greatest"
    inherit_cache = True
    _register = False

Inserted

Bases: Expression

Value a conflicting insert tried to write into a field, only in update_values of create() / bulk_create() with on_conflict="update": excluded.<column> on PostgreSQL and SQLite, VALUES(<column>) on MySQL. F() there is the existing row's column.

Source code in ormar/queryset/expressions.py
Python
100
101
102
103
104
105
106
107
108
109
110
111
112
113
class Inserted(Expression):
    """
    Value a conflicting insert tried to write into a field, only in
    ``update_values`` of ``create()`` / ``bulk_create()`` with
    ``on_conflict="update"``: ``excluded.<column>`` on PostgreSQL and SQLite,
    ``VALUES(<column>)`` on MySQL. ``F()`` there is the existing row's column.
    """

    def __init__(self, path: str) -> None:
        """
        :param path: name of a non-relation field of the inserted model
        :type path: str
        """
        self._path = path

__init__(path)

Parameters:

Name Type Description Default
path str

name of a non-relation field of the inserted model

required
Source code in ormar/queryset/expressions.py
Python
108
109
110
111
112
113
def __init__(self, path: str) -> None:
    """
    :param path: name of a non-relation field of the inserted model
    :type path: str
    """
    self._path = path

Least

Bases: Function

The smallest of the arguments. PostgreSQL ignores NULL arguments, MySQL and SQLite return NULL when any argument is NULL.

Source code in ormar/queryset/expressions.py
Python
176
177
178
179
180
181
182
class Least(Function):
    """
    The smallest of the arguments. PostgreSQL ignores ``NULL`` arguments,
    MySQL and SQLite return ``NULL`` when any argument is ``NULL``.
    """

    _sql_name = "least"

LeastOf

Bases: ReturnTypeFromArgs

SQL least(), the scalar min() on SQLite.

Source code in ormar/queryset/expressions.py
Python
206
207
208
209
210
211
class LeastOf(ReturnTypeFromArgs):
    """SQL ``least()``, the scalar ``min()`` on SQLite."""

    name = "least"
    inherit_cache = True
    _register = False

Random

Bases: Expression

Random float from 0 (inclusive) to 1 (exclusive), drawn by the database for each row: update(each=True, score=Random()), Random() * 100.

Source code in ormar/queryset/expressions.py
Python
191
192
193
194
195
class Random(Expression):
    """
    Random float from 0 (inclusive) to 1 (exclusive), drawn by the database
    for each row: ``update(each=True, score=Random())``, ``Random() * 100``.
    """

RandomFloat

Bases: GenericFunction

SQL random() returning a float in [0, 1): rand() on MySQL, the low 53 bits of SQLite's 64-bit integer random() scaled into the range.

Source code in ormar/queryset/expressions.py
Python
236
237
238
239
240
241
242
243
244
245
class RandomFloat(GenericFunction):
    """
    SQL ``random()`` returning a float in ``[0, 1)``: ``rand()`` on MySQL, the
    low 53 bits of SQLite's 64-bit integer ``random()`` scaled into the range.
    """

    name = "random"
    type = Float()
    inherit_cache = True
    _register = False

compile_greatest_on_sqlite(element, compiler, **kwargs)

SQLite has no greatest(), its max() of several arguments is the same function.

Source code in ormar/queryset/expressions.py
Python
214
215
216
217
218
219
220
221
222
@compiles(GreatestOf, "sqlite")
def compile_greatest_on_sqlite(
    element: GreatestOf, compiler: SQLCompiler, **kwargs: Any
) -> str:
    """
    SQLite has no ``greatest()``, its ``max()`` of several arguments is the same
    function.
    """
    return f"max({compiler.process(element.clauses, **kwargs)})"

compile_least_on_sqlite(element, compiler, **kwargs)

SQLite has no least(), its min() of several arguments is the same function.

Source code in ormar/queryset/expressions.py
Python
225
226
227
228
229
230
231
232
233
@compiles(LeastOf, "sqlite")
def compile_least_on_sqlite(
    element: LeastOf, compiler: SQLCompiler, **kwargs: Any
) -> str:
    """
    SQLite has no ``least()``, its ``min()`` of several arguments is the same
    function.
    """
    return f"min({compiler.process(element.clauses, **kwargs)})"

compile_random_on_mysql(element, compiler, **kwargs)

MySQL names the function rand().

Source code in ormar/queryset/expressions.py
Python
248
249
250
251
252
253
@compiles(RandomFloat, "mysql")
def compile_random_on_mysql(
    element: RandomFloat, compiler: SQLCompiler, **kwargs: Any
) -> str:
    """MySQL names the function ``rand()``."""
    return "rand()"

compile_random_on_sqlite(element, compiler, **kwargs)

SQLite's random() is a 64-bit integer; its remainder by 253 is exact as a double, so the absolute value divided by 253 stays below 1.

Source code in ormar/queryset/expressions.py
Python
256
257
258
259
260
261
262
263
264
@compiles(RandomFloat, "sqlite")
def compile_random_on_sqlite(
    element: RandomFloat, compiler: SQLCompiler, **kwargs: Any
) -> str:
    """
    SQLite's ``random()`` is a 64-bit integer; its remainder by 2**53 is exact
    as a double, so the absolute value divided by 2**53 stays below 1.
    """
    return "(abs(random() % 9007199254740992) / 9007199254740992.0)"

expression_clause(node, columns, slot, index=None, inserted=None)

Build the SQL of an expression, reading each field from the aliased column of its action in columns. With a slot the literals become bind parameters named {slot}_{k} and typed from the left operand and the literal (release_values drops the literal afterwards), without one they stay plain values.

Inserted fields are read from inserted, the row a conflicting insert tried to write (only upserts resolve them, see expression_columns).

Parameters:

Name Type Description Default
node Any

expression node

required
columns Mapping[str, QueryAction]

resolved field actions by field path (see expression_columns)

required
slot Optional[str]

slot name of a statement template, if built for one

required
index Optional[Iterator[int]]

counter of the literals already named

None
inserted Any

columns of the inserted row (excluded or MySQL inserted) of an upsert

None

Returns:

Type Description
Any

SQL expression

Source code in ormar/queryset/expressions.py
Python
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
def expression_clause(
    node: Any,
    columns: Mapping[str, "QueryAction"],
    slot: Optional[str],
    index: Optional[Iterator[int]] = None,
    inserted: Any = None,
) -> Any:
    """
    Build the SQL of an expression, reading each field from the aliased column
    of its action in ``columns``. With a ``slot`` the literals become bind
    parameters named ``{slot}_{k}`` and typed from the left operand and the
    literal (``release_values`` drops the literal afterwards), without one
    they stay plain values.

    ``Inserted`` fields are read from ``inserted``, the row a conflicting
    insert tried to write (only upserts resolve them, see
    ``expression_columns``).

    :param node: expression node
    :type node: Any
    :param columns: resolved field actions by field path
        (see ``expression_columns``)
    :type columns: Mapping[str, QueryAction]
    :param slot: slot name of a statement template, if built for one
    :type slot: Optional[str]
    :param index: counter of the literals already named
    :type index: Optional[Iterator[int]]
    :param inserted: columns of the inserted row (``excluded`` or MySQL
        ``inserted``) of an upsert
    :type inserted: Any
    :return: SQL expression
    :rtype: Any
    """
    if isinstance(node, F):
        return columns[node._path].aliased_column
    if isinstance(node, Inserted):
        return inserted[columns[node._path].column.name]
    if isinstance(node, Random):
        return RandomFloat()
    index = index or itertools.count()
    if isinstance(node, Function):
        return function_clause(
            node=node, columns=columns, slot=slot, index=index, inserted=inserted
        )
    left = expression_clause(
        node=node._left, columns=columns, slot=slot, index=index, inserted=inserted
    )
    right = node._right
    if isinstance(right, Expression):
        right = expression_clause(
            node=right, columns=columns, slot=slot, index=index, inserted=inserted
        )
    elif slot is not None:
        right = BindParameter(
            key=f"{slot}_{next(index)}",
            value=right,
            _compared_to_operator=node._op,
            _compared_to_type=left.type,
        )
    return node._op(left, right)

expression_key(node)

Return the shape of an expression for template cache keys: operators, fields and the types of the literals, which decide how they are bound::

Text Only
1
2
3
4
F("a") + 1  # -> (operator.add, ("f", "a"), int)
Greatest(F("a"), 0)  # -> ("greatest", (("f", "a"), int))
Random()  # -> ("random",)
Inserted("a")  # -> ("inserted", "a")

Parameters:

Name Type Description Default
node Any

expression node

required

Returns:

Type Description
Any

hashable shape

Source code in ormar/queryset/expressions.py
Python
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
def expression_key(node: Any) -> Any:
    """
    Return the shape of an expression for template cache keys: operators,
    fields and the types of the literals, which decide how they are bound::

        F("a") + 1  # -> (operator.add, ("f", "a"), int)
        Greatest(F("a"), 0)  # -> ("greatest", (("f", "a"), int))
        Random()  # -> ("random",)
        Inserted("a")  # -> ("inserted", "a")

    :param node: expression node
    :type node: Any
    :return: hashable shape
    :rtype: Any
    """
    if isinstance(node, Combined):
        return (
            node._op,
            expression_key(node=node._left),
            expression_key(node=node._right),
        )
    if isinstance(node, Function):
        return (
            node._sql_name,
            tuple(expression_key(node=argument) for argument in node._arguments),
        )
    if isinstance(node, Random):
        return ("random",)
    if isinstance(node, Inserted):
        return ("inserted", node._path)
    return ("f", node._path) if isinstance(node, F) else type(node)

expression_leaves(node)

Yield the fields (F, Inserted) and literals of an expression, left to right; Random() is neither.

Parameters:

Name Type Description Default
node Any

expression node

required

Returns:

Type Description
Iterator[Any]

leaves in order

Source code in ormar/queryset/expressions.py
Python
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
def expression_leaves(node: Any) -> Iterator[Any]:
    """
    Yield the fields (``F``, ``Inserted``) and literals of an expression, left
    to right; ``Random()`` is neither.

    :param node: expression node
    :type node: Any
    :return: leaves in order
    :rtype: Iterator[Any]
    """
    if isinstance(node, Combined):
        yield from expression_leaves(node=node._left)
        yield from expression_leaves(node=node._right)
    elif isinstance(node, Function):
        for argument in node._arguments:
            yield from expression_leaves(node=argument)
    elif not isinstance(node, Random):
        yield node

expression_params(value, slot)

Return the values bound for a slot: the value itself, or for an expression its literals named {slot}_{k} (as expression_clause names them).

Parameters:

Name Type Description Default
value Any

bound value or expression

required
slot str

name of the slot

required

Returns:

Type Description
dict[str, Any]

values by bind parameter name

Source code in ormar/queryset/expressions.py
Python
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
def expression_params(value: Any, slot: str) -> dict[str, Any]:
    """
    Return the values bound for a slot: the value itself, or for an expression
    its literals named ``{slot}_{k}`` (as ``expression_clause`` names them).

    :param value: bound value or expression
    :type value: Any
    :param slot: name of the slot
    :type slot: str
    :return: values by bind parameter name
    :rtype: dict[str, Any]
    """
    if not isinstance(value, Expression):
        return {slot: value}
    literals = [
        leaf
        for leaf in expression_leaves(node=value)
        if not isinstance(leaf, (F, Inserted))
    ]
    return {f"{slot}_{index}": literal for index, literal in enumerate(literals)}

function_clause(node, columns, slot, index, inserted=None)

Build the SQL of a function. Literal arguments are bound like the literals of expression_clause: named {slot}_{k} in order and typed from the first expression argument and the literal, so Greatest(F("count"), 2.5) binds a float.

Parameters:

Name Type Description Default
node Function

function node

required
columns Mapping[str, QueryAction]

resolved field actions by field path

required
slot Optional[str]

slot name of a statement template, if built for one

required
index Iterator[int]

counter of the literals already named

required
inserted Any

columns of the inserted row of an upsert

None

Returns:

Type Description
Any

SQL function call

Source code in ormar/queryset/expressions.py
Python
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
def function_clause(
    node: Function,
    columns: Mapping[str, "QueryAction"],
    slot: Optional[str],
    index: Iterator[int],
    inserted: Any = None,
) -> Any:
    """
    Build the SQL of a function. Literal arguments are bound like the literals
    of ``expression_clause``: named ``{slot}_{k}`` in order and typed from the
    first expression argument and the literal, so ``Greatest(F("count"), 2.5)``
    binds a float.

    :param node: function node
    :type node: Function
    :param columns: resolved field actions by field path
    :type columns: Mapping[str, QueryAction]
    :param slot: slot name of a statement template, if built for one
    :type slot: Optional[str]
    :param index: counter of the literals already named
    :type index: Iterator[int]
    :param inserted: columns of the inserted row of an upsert
    :type inserted: Any
    :return: SQL function call
    :rtype: Any
    """
    # literals are named in argument order, but typed after the first
    # expression argument, which may come later
    expression_clauses: dict[int, Any] = {}
    literal_keys: dict[int, Optional[str]] = {}
    for position, argument in enumerate(node._arguments):
        if isinstance(argument, Expression):
            expression_clauses[position] = expression_clause(
                node=argument,
                columns=columns,
                slot=slot,
                index=index,
                inserted=inserted,
            )
        else:
            literal_keys[position] = f"{slot}_{next(index)}" if slot else None
    reference_type = next(iter(expression_clauses.values())).type
    arguments = [
        expression_clauses[position]
        if position in expression_clauses
        else BindParameter(
            key=literal_keys[position],
            value=argument,
            unique=literal_keys[position] is None,
            _compared_to_operator=sql_operators.eq,
            _compared_to_type=reference_type,
        )
        for position, argument in enumerate(node._arguments)
    ]
    return SQL_FUNCTIONS[node._sql_name](*arguments)