Skip to content

writes

Insert templates of a model: the single-row insert, the statements of bulk_create and upserts. Updates, deletes and selects by primary key are built by write_statements.

Each runs one cached statement per model and set of written columns::

Text Only
1
2
3
insert_template(model_cls=Author, values={"name": "a", "score": 1})
# -> INSERT INTO authors (name, score) VALUES (:ormar_v0, :ormar_v1)
#    {"ormar_v0": "a", "ormar_v1": 1}

build_conflict_insert(table, names, rules, on_conflict, target, update_names, on_update, returning, model=None, update_values=None, update_variables=False)

Build the insert of the database's dialect that skips or updates a row conflicting with an existing one. The value binds are built here, on a cache miss only, so the rules get them ready made::

Text Only
1
2
3
INSERT INTO tags (name, score) VALUES (:ormar_v0, :ormar_v1)
ON CONFLICT (name) DO UPDATE SET score = excluded.score
-- MySQL: ON DUPLICATE KEY UPDATE score = VALUES(score)

Parameters:

Name Type Description Default
table Table

written table

required
names Tuple[str, ...]

inserted column names, sorted

required
rules DialectRules

rules of the database dialect

required
on_conflict str

ignore or update

required
target Tuple[str, ...]

conflicting column names (the primary key for ignore)

required
update_names Tuple[str, ...]

columns updated from the inserted row

required
on_update Tuple[str, ...]

columns updated with their on_update value

required
returning Tuple[str, ...]

columns of the written row to return, if any

required
model Optional[Type[Any]]

written model, resolving the fields of update_values

None
update_values Optional[Mapping[str, Any]]

value or expression node set by column name

None
update_variables bool

read the bound update values from MySQL user variables

False

Returns:

Type Description
Executable

insert statement

Source code in ormar/queryset/templates/writes.py
Python
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
def build_conflict_insert(
    table: sqlalchemy.Table,
    names: Tuple[str, ...],
    rules: "DialectRules",
    on_conflict: str,
    target: Tuple[str, ...],
    update_names: Tuple[str, ...],
    on_update: Tuple[str, ...],
    returning: Tuple[str, ...],
    model: Optional[Type[Any]] = None,
    update_values: Optional[Mapping[str, Any]] = None,
    update_variables: bool = False,
) -> Executable:
    """
    Build the insert of the database's dialect that skips or updates a row
    conflicting with an existing one. The value binds are built here, on a
    cache miss only, so the rules get them ready made::

        INSERT INTO tags (name, score) VALUES (:ormar_v0, :ormar_v1)
        ON CONFLICT (name) DO UPDATE SET score = excluded.score
        -- MySQL: ON DUPLICATE KEY UPDATE score = VALUES(score)

    :param table: written table
    :type table: sqlalchemy.Table
    :param names: inserted column names, sorted
    :type names: Tuple[str, ...]
    :param rules: rules of the database dialect
    :type rules: DialectRules
    :param on_conflict: ``ignore`` or ``update``
    :type on_conflict: str
    :param target: conflicting column names (the primary key for ``ignore``)
    :type target: Tuple[str, ...]
    :param update_names: columns updated from the inserted row
    :type update_names: Tuple[str, ...]
    :param on_update: columns updated with their ``on_update`` value
    :type on_update: Tuple[str, ...]
    :param returning: columns of the written row to return, if any
    :type returning: Tuple[str, ...]
    :param model: written model, resolving the fields of ``update_values``
    :type model: Optional[Type[Any]]
    :param update_values: value or expression node set by column name
    :type update_values: Optional[Mapping[str, Any]]
    :param update_variables: read the bound update values from MySQL user
        variables
    :type update_variables: bool
    :return: insert statement
    :rtype: Executable
    """
    statement = rules.conflict_insert(
        table=table,
        values=value_binds(table=table, names=names),
        on_conflict=on_conflict,
        target=target,
        update_names=update_names,
        on_update=on_update,
        returning=returning,
        value_updates=(
            functools.partial(
                update_value_clauses, model_cls=model, update_values=update_values
            )
            if model is not None and update_values
            else None
        ),
        update_variables=update_variables,
    )
    release_values(statement=statement)
    return statement

build_insert(table, names)

Build an insert of the given columns with named value binds::

Text Only
1
INSERT INTO authors (name, score) VALUES (:ormar_v0, :ormar_v1)

The names are sorted by the caller, so the same columns given in any order share one statement.

Parameters:

Name Type Description Default
table Table

written table

required
names Tuple[str, ...]

written column names, sorted

required

Returns:

Type Description
Executable

insert statement

Source code in ormar/queryset/templates/writes.py
Python
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
def build_insert(table: sqlalchemy.Table, names: Tuple[str, ...]) -> Executable:
    """
    Build an insert of the given columns with named value binds::

        INSERT INTO authors (name, score) VALUES (:ormar_v0, :ormar_v1)

    The names are sorted by the caller, so the same columns given in any
    order share one statement.

    :param table: written table
    :type table: sqlalchemy.Table
    :param names: written column names, sorted
    :type names: Tuple[str, ...]
    :return: insert statement
    :rtype: Executable
    """
    return table.insert().values(value_binds(table=table, names=names))

build_update_assignment(model, on_update, update_values)

Build the MySQL SET of the user variables a multi-row upsert reads its on_update values and the bound update_values from (see update_assignment).

Parameters:

Name Type Description Default
model Type[Any]

written model

required
on_update Tuple[str, ...]

columns updated with their on_update value

required
update_values Mapping[str, Any]

value or expression node by column name

required

Returns:

Type Description
Executable

assignment statement

Source code in ormar/queryset/templates/writes.py
Python
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
def build_update_assignment(
    model: Type[Any],
    on_update: Tuple[str, ...],
    update_values: Mapping[str, Any],
) -> Executable:
    """
    Build the MySQL ``SET`` of the user variables a multi-row upsert reads its
    ``on_update`` values and the bound ``update_values`` from (see
    ``update_assignment``).

    :param model: written model
    :type model: Type[Any]
    :param on_update: columns updated with their ``on_update`` value
    :type on_update: Tuple[str, ...]
    :param update_values: value or expression node by column name
    :type update_values: Mapping[str, Any]
    :return: assignment statement
    :rtype: Executable
    """
    table = model.ormar_config.table
    clauses = update_value_clauses(
        inserted=table.c, model_cls=model, update_values=update_values
    )
    return update_assignment(
        table=table,
        on_update=on_update,
        value_binds=tuple(
            element
            for clause in clauses.values()
            for element in visitors.iterate(clause)
            if isinstance(element, BindParameter)
        ),
    )

insert_statement(model_cls, names, on_conflict=None, conflict_target=None, update_fields=None, on_update=(), returning=(), update_values=None, update_variables=False)

Return the insert of a model for the given sorted column names, from the model's template cache. save() binds one row to it and bulk_create runs it with executemany for every row with the same columns.

With on_conflict the insert skips or updates conflicting rows (see build_conflict_insert): the target defaults to the primary key and the updated columns to the inserted ones but the target, the key and on_update, or to none with update_values. One statement is cached per shape of update_values (see update_values_key).

Parameters:

Name Type Description Default
model_cls Type[Any]

inserted model

required
names Tuple[str, ...]

inserted column names, sorted

required
on_conflict Optional[str]

ignore or update conflicting rows

None
conflict_target Optional[Sequence[str]]

names of the fields of the conflicting unique key

None
update_fields Optional[Sequence[str]]

names of the fields updated from the inserted row

None
on_update Tuple[str, ...]

columns updated with their on_update value

()
returning Tuple[str, ...]

columns of the written row to return, if any

()
update_values Optional[Mapping[str, Any]]

value or expression node set by column name

None
update_variables bool

read the bound update values from MySQL user variables set by update_assignment instead of binds

False

Returns:

Type Description
Executable

insert statement with ormar_v{i} binds

Source code in ormar/queryset/templates/writes.py
Python
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
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
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
def insert_statement(
    model_cls: Type[Any],
    names: Tuple[str, ...],
    on_conflict: Optional[str] = None,
    conflict_target: Optional[Sequence[str]] = None,
    update_fields: Optional[Sequence[str]] = None,
    on_update: Tuple[str, ...] = (),
    returning: Tuple[str, ...] = (),
    update_values: Optional[Mapping[str, Any]] = None,
    update_variables: bool = False,
) -> Executable:
    """
    Return the insert of a model for the given sorted column names, from the
    model's template cache. ``save()`` binds one row to it and
    ``bulk_create`` runs it with ``executemany`` for every row with the same
    columns.

    With ``on_conflict`` the insert skips or updates conflicting rows (see
    ``build_conflict_insert``): the target defaults to the primary key and the
    updated columns to the inserted ones but the target, the key and
    ``on_update``, or to none with ``update_values``. One statement is cached
    per shape of ``update_values`` (see ``update_values_key``).

    :param model_cls: inserted model
    :type model_cls: Type[Any]
    :param names: inserted column names, sorted
    :type names: Tuple[str, ...]
    :param on_conflict: ``ignore`` or ``update`` conflicting rows
    :type on_conflict: Optional[str]
    :param conflict_target: names of the fields of the conflicting unique key
    :type conflict_target: Optional[Sequence[str]]
    :param update_fields: names of the fields updated from the inserted row
    :type update_fields: Optional[Sequence[str]]
    :param on_update: columns updated with their ``on_update`` value
    :type on_update: Tuple[str, ...]
    :param returning: columns of the written row to return, if any
    :type returning: Tuple[str, ...]
    :param update_values: value or expression node set by column name
    :type update_values: Optional[Mapping[str, Any]]
    :param update_variables: read the bound update values from MySQL user
        variables set by ``update_assignment`` instead of binds
    :type update_variables: bool
    :return: insert statement with ``ormar_v{i}`` binds
    :rtype: Executable
    """
    config = model_cls.ormar_config
    if not on_conflict:
        return cached_statement(
            model_cls=model_cls,
            key=("insert", names),
            builder=build_insert,
            table=config.table,
            names=names,
        )
    pk = config.pk.column_aliases()
    target = (
        tuple(
            column
            for field_name in conflict_target or ()
            for column in model_cls.get_column_aliases(field_name=field_name)
        )
        or pk
    )
    update_names = tuple(
        sorted(
            column
            for field_name in update_fields or ()
            for column in model_cls.get_column_aliases(field_name=field_name)
        )
    )
    if not update_names and not update_values:
        update_names = tuple(
            column for column in names if column not in {*target, *pk, *on_update}
        )
    if (
        on_conflict == "update"
        and not update_names
        and not on_update
        and not update_values
    ):
        # nothing left to update: set a target column to itself, so the
        # conflicting row is still returned (DO NOTHING would return no row)
        update_names = target[:1]
    rules = config.database.rules
    conflict = dict(
        on_conflict=on_conflict,
        target=target,
        update_names=update_names,
        on_update=on_update,
        returning=returning,
        update_variables=update_variables,
    )
    return cached_statement(
        model_cls=model_cls,
        key=(
            "insert",
            names,
            rules.backend_name,
            *conflict.values(),
            update_values_key(update_values=update_values or {}),
        ),
        builder=build_conflict_insert,
        table=config.table,
        names=names,
        rules=rules,
        model=model_cls,
        update_values=update_values,
        **conflict,
    )

insert_template(model_cls, values, **statement_kwargs)

Return the insert of a model for the given column values, used by save() and one-row bulk_create::

Text Only
1
2
3
insert_template(model_cls=Author, values={"name": "a", "score": 1})
# -> INSERT INTO authors (name, score) VALUES (:ormar_v0, :ormar_v1)
#    {"ormar_v0": "a", "ormar_v1": 1}

Rows that leave out the primary key or a column with a server default write a different set of columns, so each set gets its own statement.

Parameters:

Name Type Description Default
model_cls Type[Any]

inserted model

required
values Mapping[str, Any]

values by column name

required
statement_kwargs Any

conflict handling (see insert_statement)

{}

Returns:

Type Description
StatementTemplate

statement and values to bind

Source code in ormar/queryset/templates/writes.py
Python
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
def insert_template(
    model_cls: Type[Any], values: Mapping[str, Any], **statement_kwargs: Any
) -> StatementTemplate:
    """
    Return the insert of a model for the given column values, used by
    ``save()`` and one-row ``bulk_create``::

        insert_template(model_cls=Author, values={"name": "a", "score": 1})
        # -> INSERT INTO authors (name, score) VALUES (:ormar_v0, :ormar_v1)
        #    {"ormar_v0": "a", "ormar_v1": 1}

    Rows that leave out the primary key or a column with a server default
    write a different set of columns, so each set gets its own statement.

    :param model_cls: inserted model
    :type model_cls: Type[Any]
    :param values: values by column name
    :type values: Mapping[str, Any]
    :param statement_kwargs: conflict handling (see ``insert_statement``)
    :type statement_kwargs: Any
    :return: statement and values to bind
    :rtype: StatementTemplate
    """
    names = tuple(sorted(values))
    statement = insert_statement(model_cls=model_cls, names=names, **statement_kwargs)
    params = value_params(names=names, values=values)
    return StatementTemplate(statement=statement, params=params)

update_value_clauses(inserted, model_cls, update_values)

Build the SQL of the update_values of an upsert, in sorted column order: a value binds slot ormar_u{i} typed like its column, an expression reads F fields from the written table (the existing row) and Inserted fields from inserted, with its literals bound as ormar_u{i}_{k}::

Text Only
1
2
3
4
5
6
update_value_clauses(
    inserted=insert.excluded,
    model_cls=Track,
    update_values={"completed": F("completed") + 1, "note": "x"},
)
# -> {"completed": tracks.completed + :ormar_u0_0, "note": :ormar_u1}

Parameters:

Name Type Description Default
inserted Any

the inserted row (excluded or MySQL inserted)

required
model_cls Type[Any]

written model

required
update_values Mapping[str, Any]

value or expression node by column name

required

Returns:

Type Description
Dict[str, Any]

SQL by column name

Source code in ormar/queryset/templates/writes.py
Python
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
def update_value_clauses(
    inserted: Any, model_cls: Type[Any], update_values: Mapping[str, Any]
) -> Dict[str, Any]:
    """
    Build the SQL of the ``update_values`` of an upsert, in sorted column
    order: a value binds slot ``ormar_u{i}`` typed like its column, an
    expression reads ``F`` fields from the written table (the existing row) and
    ``Inserted`` fields from ``inserted``, with its literals bound as
    ``ormar_u{i}_{k}``::

        update_value_clauses(
            inserted=insert.excluded,
            model_cls=Track,
            update_values={"completed": F("completed") + 1, "note": "x"},
        )
        # -> {"completed": tracks.completed + :ormar_u0_0, "note": :ormar_u1}

    :param inserted: the inserted row (``excluded`` or MySQL ``inserted``)
    :type inserted: Any
    :param model_cls: written model
    :type model_cls: Type[Any]
    :param update_values: value or expression node by column name
    :type update_values: Mapping[str, Any]
    :return: SQL by column name
    :rtype: Dict[str, Any]
    """
    table = model_cls.ormar_config.table
    clauses: Dict[str, Any] = {}
    for index, name in enumerate(sorted(update_values)):
        value = update_values[name]
        clauses[name] = (
            expression_clause(
                node=value,
                columns=expression_columns(node=value, model=model_cls, upsert=True),
                slot=f"ormar_u{index}",
                inserted=inserted,
            )
            if isinstance(value, Expression)
            else sqlalchemy.bindparam(key=f"ormar_u{index}", type_=table.c[name].type)
        )
    return clauses

update_values_key(update_values)

Return the shape of the update_values of an upsert for cache keys: the column of a value, the column and expression_key of an expression, so the literals do not split the cache.

Parameters:

Name Type Description Default
update_values Mapping[str, Any]

value or expression node by column name

required

Returns:

Type Description
Tuple[Any, ...]

hashable shape

Source code in ormar/queryset/templates/writes.py
Python
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
def update_values_key(update_values: Mapping[str, Any]) -> Tuple[Any, ...]:
    """
    Return the shape of the ``update_values`` of an upsert for cache keys: the
    column of a value, the column and ``expression_key`` of an expression, so
    the literals do not split the cache.

    :param update_values: value or expression node by column name
    :type update_values: Mapping[str, Any]
    :return: hashable shape
    :rtype: Tuple[Any, ...]
    """
    return tuple(
        (name, expression_key(node=update_values[name]))
        if isinstance(update_values[name], Expression)
        else name
        for name in sorted(update_values)
    )

update_values_params(update_values)

Return the values bound by the update_values of an upsert, named like update_value_clauses names them::

Text Only
1
2
update_values_params(update_values={"completed": F("completed") + 1})
# -> {"ormar_u0_0": 1}

Parameters:

Name Type Description Default
update_values Mapping[str, Any]

value or expression node by column name

required

Returns:

Type Description
Dict[str, Any]

values by bind parameter name

Source code in ormar/queryset/templates/writes.py
Python
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
def update_values_params(update_values: Mapping[str, Any]) -> Dict[str, Any]:
    """
    Return the values bound by the ``update_values`` of an upsert, named like
    ``update_value_clauses`` names them::

        update_values_params(update_values={"completed": F("completed") + 1})
        # -> {"ormar_u0_0": 1}

    :param update_values: value or expression node by column name
    :type update_values: Mapping[str, Any]
    :return: values by bind parameter name
    :rtype: Dict[str, Any]
    """
    params: Dict[str, Any] = {}
    for index, name in enumerate(sorted(update_values)):
        params.update(
            expression_params(value=update_values[name], slot=f"ormar_u{index}")
        )
    return params