Skip to content

dialects

Dialect rules: the SQL behaviour that differs between database backends.

One rules object per DatabaseConnection, picked by the backend name of its url, so it is known before connect()::

Text Only
1
2
3
database.rules.conflict_insert(table=tags, values=binds, ...)
# PostgreSQL / SQLite: INSERT ... ON CONFLICT (name) DO UPDATE ...
# MySQL / MariaDB:     INSERT ... ON DUPLICATE KEY UPDATE ...

Rules only build statements and answer questions about the dialect. Running statements and reading results stays with the driver adapters.

DialectRules

Rules of a backend ormar has no specific rules for, and the base of the ones it has. Its conflict insert is the ON CONFLICT of PostgreSQL and SQLite, which only lack the insert construct here.

Source code in ormar/databases/dialects.py
Python
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
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
249
250
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
class DialectRules:
    """
    Rules of a backend ormar has no specific rules for, and the base of the
    ones it has. Its conflict insert is the ``ON CONFLICT`` of PostgreSQL and
    SQLite, which only lack the insert construct here.
    """

    #: the engine runs in AUTOCOMMIT and only transactions leave it, because
    #: switching a pooled connection into AUTOCOMMIT and back costs round trips
    autocommit_engine: ClassVar[bool] = False
    #: a skipping conflict insert returns the written primary key
    conflict_returning: ClassVar[bool] = True
    #: ``update_returning()`` runs one ``UPDATE ... RETURNING``; without it
    #: the rows are locked, updated and read back by primary key
    update_returning: ClassVar[bool] = True
    #: ``execute_many`` fills binds placed after the VALUES clause
    execute_many_with_trailing_binds: ClassVar[bool] = True
    #: foreign keys may reference a table in another schema
    cross_schema_foreign_keys: ClassVar[bool] = True
    #: connections run ``PRAGMA foreign_keys=ON`` when opened
    foreign_keys_pragma: ClassVar[bool] = False
    #: transactions send an explicit ``BEGIN`` so a released savepoint does
    #: not commit
    explicit_begin: ClassVar[bool] = False
    #: function that orders rows randomly, used by ``order_by("?")``
    random_order: ClassVar[str] = "random()"

    def __init__(self, *, backend_name: str) -> None:
        """
        :param backend_name: backend name of the database url
        :type backend_name: str
        """
        self.backend_name = backend_name

    def make_insert(
        self, *, table: sqlalchemy.Table
    ) -> Union[postgresql.Insert, sqlite.Insert]:
        """
        Return the dialect insert construct that takes a conflict clause.

        :raises QueryDefinitionError: the backend has no conflict insert
        :param table: written table
        :type table: sqlalchemy.Table
        :return: insert of the table
        :rtype: Union[postgresql.Insert, sqlite.Insert]
        """
        raise QueryDefinitionError(
            f"on_conflict is not supported on the {self.backend_name} backend"
        )

    def conflict_insert(
        self,
        *,
        table: sqlalchemy.Table,
        values: Dict[Any, Any],
        on_conflict: str,
        target: Tuple[str, ...],
        update_names: Tuple[str, ...],
        on_update: Tuple[str, ...],
        returning: Tuple[str, ...],
        value_updates: Optional[Callable[[Any], Dict[str, Any]]] = None,
        update_variables: bool = False,
    ) -> Executable:
        """
        Build an insert that skips or updates a row conflicting with an
        existing one::

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

        :param table: written table
        :type table: sqlalchemy.Table
        :param values: bind parameter per inserted column
        :type values: Dict[Any, Any]
        :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 value_updates: builds the ``update_values`` columns from the
            inserted row
        :type value_updates: Optional[Callable[[Any], Dict[str, Any]]]
        :param update_variables: read the bound update values from MySQL user
            variables (see ``update_assignment``)
        :type update_variables: bool
        :return: insert statement
        :rtype: Executable
        """
        statement = self.make_insert(table=table).values(values)
        if on_conflict == "update":
            statement = statement.on_conflict_do_update(
                index_elements=target,
                set_=conflict_updates(
                    table=table,
                    inserted=statement.excluded,
                    update_names=update_names,
                    on_update=on_update,
                    value_updates=value_updates,
                    update_variables=update_variables,
                ),
            )
        else:
            statement = statement.on_conflict_do_nothing()
        if not returning:
            return statement
        return statement.returning(*(table.c[name] for name in returning))

    def is_duplicate_key(self, *, error: IntegrityError) -> bool:
        """
        Tell whether the error of a plain insert run with ``on_conflict`` means
        the row was skipped. Dialects with ``conflict_returning`` skip in the
        statement and never run that plain insert.

        :param error: error raised by the insert
        :type error: IntegrityError
        :return: the row conflicted with an existing unique key
        :rtype: bool
        """
        return False

    def ordering_aggregate(self, *, field_type: Any, descending: bool) -> str:
        """
        Return the aggregate that orders by a column not in the GROUP BY of a
        limited sub query.

        :param field_type: python type of the ordered field
        :type field_type: Any
        :param descending: the order is descending
        :type descending: bool
        :return: name of the aggregate function
        :rtype: str
        """
        return "max" if descending else "min"

    def nulls_order(
        self, *, full_column: str, ordered: str, nulls_ordering: str
    ) -> str:
        """
        Return an ORDER BY expression that puts nulls first or last::

            authors.name desc nulls last

        :param full_column: fully-qualified, quoted column reference
        :type full_column: str
        :param ordered: the column with its direction
        :type ordered: str
        :param nulls_ordering: ``first`` or ``last``
        :type nulls_ordering: str
        :return: ORDER BY expression as raw SQL text
        :rtype: str
        """
        return f"{ordered} nulls {nulls_ordering}"

__init__(*, backend_name)

Parameters:

Name Type Description Default
backend_name str

backend name of the database url

required
Source code in ormar/databases/dialects.py
Python
191
192
193
194
195
196
def __init__(self, *, backend_name: str) -> None:
    """
    :param backend_name: backend name of the database url
    :type backend_name: str
    """
    self.backend_name = backend_name

conflict_insert(*, table, values, on_conflict, target, update_names, on_update, returning, value_updates=None, update_variables=False)

Build an insert that skips or updates a row conflicting with an existing one::

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

Parameters:

Name Type Description Default
table Table

written table

required
values Dict[Any, Any]

bind parameter per inserted column

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
value_updates Optional[Callable[[Any], Dict[str, Any]]]

builds the update_values columns from the inserted row

None
update_variables bool

read the bound update values from MySQL user variables (see update_assignment)

False

Returns:

Type Description
Executable

insert statement

Source code in ormar/databases/dialects.py
Python
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
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
def conflict_insert(
    self,
    *,
    table: sqlalchemy.Table,
    values: Dict[Any, Any],
    on_conflict: str,
    target: Tuple[str, ...],
    update_names: Tuple[str, ...],
    on_update: Tuple[str, ...],
    returning: Tuple[str, ...],
    value_updates: Optional[Callable[[Any], Dict[str, Any]]] = None,
    update_variables: bool = False,
) -> Executable:
    """
    Build an insert that skips or updates a row conflicting with an
    existing one::

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

    :param table: written table
    :type table: sqlalchemy.Table
    :param values: bind parameter per inserted column
    :type values: Dict[Any, Any]
    :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 value_updates: builds the ``update_values`` columns from the
        inserted row
    :type value_updates: Optional[Callable[[Any], Dict[str, Any]]]
    :param update_variables: read the bound update values from MySQL user
        variables (see ``update_assignment``)
    :type update_variables: bool
    :return: insert statement
    :rtype: Executable
    """
    statement = self.make_insert(table=table).values(values)
    if on_conflict == "update":
        statement = statement.on_conflict_do_update(
            index_elements=target,
            set_=conflict_updates(
                table=table,
                inserted=statement.excluded,
                update_names=update_names,
                on_update=on_update,
                value_updates=value_updates,
                update_variables=update_variables,
            ),
        )
    else:
        statement = statement.on_conflict_do_nothing()
    if not returning:
        return statement
    return statement.returning(*(table.c[name] for name in returning))

is_duplicate_key(*, error)

Tell whether the error of a plain insert run with on_conflict means the row was skipped. Dialects with conflict_returning skip in the statement and never run that plain insert.

Parameters:

Name Type Description Default
error IntegrityError

error raised by the insert

required

Returns:

Type Description
bool

the row conflicted with an existing unique key

Source code in ormar/databases/dialects.py
Python
276
277
278
279
280
281
282
283
284
285
286
287
def is_duplicate_key(self, *, error: IntegrityError) -> bool:
    """
    Tell whether the error of a plain insert run with ``on_conflict`` means
    the row was skipped. Dialects with ``conflict_returning`` skip in the
    statement and never run that plain insert.

    :param error: error raised by the insert
    :type error: IntegrityError
    :return: the row conflicted with an existing unique key
    :rtype: bool
    """
    return False

make_insert(*, table)

Return the dialect insert construct that takes a conflict clause.

Parameters:

Name Type Description Default
table Table

written table

required

Returns:

Type Description
Union[postgresql.Insert, sqlite.Insert]

insert of the table

Raises:

Type Description
QueryDefinitionError

the backend has no conflict insert

Source code in ormar/databases/dialects.py
Python
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
def make_insert(
    self, *, table: sqlalchemy.Table
) -> Union[postgresql.Insert, sqlite.Insert]:
    """
    Return the dialect insert construct that takes a conflict clause.

    :raises QueryDefinitionError: the backend has no conflict insert
    :param table: written table
    :type table: sqlalchemy.Table
    :return: insert of the table
    :rtype: Union[postgresql.Insert, sqlite.Insert]
    """
    raise QueryDefinitionError(
        f"on_conflict is not supported on the {self.backend_name} backend"
    )

nulls_order(*, full_column, ordered, nulls_ordering)

Return an ORDER BY expression that puts nulls first or last::

Text Only
1
authors.name desc nulls last

Parameters:

Name Type Description Default
full_column str

fully-qualified, quoted column reference

required
ordered str

the column with its direction

required
nulls_ordering str

first or last

required

Returns:

Type Description
str

ORDER BY expression as raw SQL text

Source code in ormar/databases/dialects.py
Python
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
def nulls_order(
    self, *, full_column: str, ordered: str, nulls_ordering: str
) -> str:
    """
    Return an ORDER BY expression that puts nulls first or last::

        authors.name desc nulls last

    :param full_column: fully-qualified, quoted column reference
    :type full_column: str
    :param ordered: the column with its direction
    :type ordered: str
    :param nulls_ordering: ``first`` or ``last``
    :type nulls_ordering: str
    :return: ORDER BY expression as raw SQL text
    :rtype: str
    """
    return f"{ordered} nulls {nulls_ordering}"

ordering_aggregate(*, field_type, descending)

Return the aggregate that orders by a column not in the GROUP BY of a limited sub query.

Parameters:

Name Type Description Default
field_type Any

python type of the ordered field

required
descending bool

the order is descending

required

Returns:

Type Description
str

name of the aggregate function

Source code in ormar/databases/dialects.py
Python
289
290
291
292
293
294
295
296
297
298
299
300
301
def ordering_aggregate(self, *, field_type: Any, descending: bool) -> str:
    """
    Return the aggregate that orders by a column not in the GROUP BY of a
    limited sub query.

    :param field_type: python type of the ordered field
    :type field_type: Any
    :param descending: the order is descending
    :type descending: bool
    :return: name of the aggregate function
    :rtype: str
    """
    return "max" if descending else "min"

MysqlRules

Bases: DialectRules

MySQL and MariaDB: ON DUPLICATE KEY UPDATE inserts and updates without RETURNING, no NULLS FIRST / NULLS LAST, rand() and an AUTOCOMMIT engine.

Source code in ormar/databases/dialects.py
Python
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
409
410
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
class MysqlRules(DialectRules):
    """
    MySQL and MariaDB: ``ON DUPLICATE KEY UPDATE`` inserts and updates without
    RETURNING,
    no ``NULLS FIRST`` / ``NULLS LAST``, ``rand()`` and an AUTOCOMMIT engine.
    """

    autocommit_engine = True
    conflict_returning = False
    update_returning = False
    execute_many_with_trailing_binds = False
    random_order = "rand()"

    def conflict_insert(
        self,
        *,
        table: sqlalchemy.Table,
        values: Dict[Any, Any],
        on_conflict: str,
        target: Tuple[str, ...],
        update_names: Tuple[str, ...],
        on_update: Tuple[str, ...],
        returning: Tuple[str, ...],
        value_updates: Optional[Callable[[Any], Dict[str, Any]]] = None,
        update_variables: bool = False,
    ) -> Executable:
        """
        Build an insert that skips or updates a row conflicting with an
        existing one::

            INSERT INTO tags (name, score) VALUES (%s, %s)
            ON DUPLICATE KEY UPDATE score = VALUES(score)

        MySQL reacts to a conflict on any unique key, so the target is not
        emitted and ``ignore`` becomes a no-op update of a key column (a
        duplicate key error would abort the whole ``executemany``). There is no
        RETURNING, so ``returning`` is not used. The other parameters are
        described in ``DialectRules.conflict_insert``.

        :return: insert statement
        :rtype: Executable
        """
        statement = mysql.insert(table=table).values(values)
        if on_conflict == "update":
            return statement.on_duplicate_key_update(
                conflict_updates(
                    table=table,
                    inserted=statement.inserted,
                    update_names=update_names,
                    on_update=on_update,
                    value_updates=value_updates,
                    update_variables=update_variables,
                )
            )
        return statement.on_duplicate_key_update({target[0]: table.c[target[0]]})

    def is_duplicate_key(self, *, error: IntegrityError) -> bool:
        """
        :param error: error raised by the insert
        :type error: IntegrityError
        :return: the server reported ER_DUP_ENTRY
        :rtype: bool
        """
        return error.orig is not None and error.orig.args[:1] == (
            MYSQL_DUPLICATE_ENTRY_ERROR,
        )

    def nulls_order(
        self, *, full_column: str, ordered: str, nulls_ordering: str
    ) -> str:
        """
        Emulate ``NULLS FIRST`` / ``NULLS LAST`` with an ``IS [NOT] NULL`` sort
        key in front, it only changes the order of the rows::

            authors.name is not null, authors.name desc

        :param full_column: fully-qualified, quoted column reference
        :type full_column: str
        :param ordered: the column with its direction
        :type ordered: str
        :param nulls_ordering: ``first`` or ``last``
        :type nulls_ordering: str
        :return: ORDER BY expression as raw SQL text
        :rtype: str
        """
        negation = "not " if nulls_ordering == "first" else ""
        return f"{full_column} is {negation}null, {ordered}"

conflict_insert(*, table, values, on_conflict, target, update_names, on_update, returning, value_updates=None, update_variables=False)

Build an insert that skips or updates a row conflicting with an existing one::

Text Only
1
2
INSERT INTO tags (name, score) VALUES (%s, %s)
ON DUPLICATE KEY UPDATE score = VALUES(score)

MySQL reacts to a conflict on any unique key, so the target is not emitted and ignore becomes a no-op update of a key column (a duplicate key error would abort the whole executemany). There is no RETURNING, so returning is not used. The other parameters are described in DialectRules.conflict_insert.

Returns:

Type Description
Executable

insert statement

Source code in ormar/databases/dialects.py
Python
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
def conflict_insert(
    self,
    *,
    table: sqlalchemy.Table,
    values: Dict[Any, Any],
    on_conflict: str,
    target: Tuple[str, ...],
    update_names: Tuple[str, ...],
    on_update: Tuple[str, ...],
    returning: Tuple[str, ...],
    value_updates: Optional[Callable[[Any], Dict[str, Any]]] = None,
    update_variables: bool = False,
) -> Executable:
    """
    Build an insert that skips or updates a row conflicting with an
    existing one::

        INSERT INTO tags (name, score) VALUES (%s, %s)
        ON DUPLICATE KEY UPDATE score = VALUES(score)

    MySQL reacts to a conflict on any unique key, so the target is not
    emitted and ``ignore`` becomes a no-op update of a key column (a
    duplicate key error would abort the whole ``executemany``). There is no
    RETURNING, so ``returning`` is not used. The other parameters are
    described in ``DialectRules.conflict_insert``.

    :return: insert statement
    :rtype: Executable
    """
    statement = mysql.insert(table=table).values(values)
    if on_conflict == "update":
        return statement.on_duplicate_key_update(
            conflict_updates(
                table=table,
                inserted=statement.inserted,
                update_names=update_names,
                on_update=on_update,
                value_updates=value_updates,
                update_variables=update_variables,
            )
        )
    return statement.on_duplicate_key_update({target[0]: table.c[target[0]]})

is_duplicate_key(*, error)

Parameters:

Name Type Description Default
error IntegrityError

error raised by the insert

required

Returns:

Type Description
bool

the server reported ER_DUP_ENTRY

Source code in ormar/databases/dialects.py
Python
431
432
433
434
435
436
437
438
439
440
def is_duplicate_key(self, *, error: IntegrityError) -> bool:
    """
    :param error: error raised by the insert
    :type error: IntegrityError
    :return: the server reported ER_DUP_ENTRY
    :rtype: bool
    """
    return error.orig is not None and error.orig.args[:1] == (
        MYSQL_DUPLICATE_ENTRY_ERROR,
    )

nulls_order(*, full_column, ordered, nulls_ordering)

Emulate NULLS FIRST / NULLS LAST with an IS [NOT] NULL sort key in front, it only changes the order of the rows::

Text Only
1
authors.name is not null, authors.name desc

Parameters:

Name Type Description Default
full_column str

fully-qualified, quoted column reference

required
ordered str

the column with its direction

required
nulls_ordering str

first or last

required

Returns:

Type Description
str

ORDER BY expression as raw SQL text

Source code in ormar/databases/dialects.py
Python
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
def nulls_order(
    self, *, full_column: str, ordered: str, nulls_ordering: str
) -> str:
    """
    Emulate ``NULLS FIRST`` / ``NULLS LAST`` with an ``IS [NOT] NULL`` sort
    key in front, it only changes the order of the rows::

        authors.name is not null, authors.name desc

    :param full_column: fully-qualified, quoted column reference
    :type full_column: str
    :param ordered: the column with its direction
    :type ordered: str
    :param nulls_ordering: ``first`` or ``last``
    :type nulls_ordering: str
    :return: ORDER BY expression as raw SQL text
    :rtype: str
    """
    negation = "not " if nulls_ordering == "first" else ""
    return f"{full_column} is {negation}null, {ordered}"

PostgresRules

Bases: DialectRules

PostgreSQL: ON CONFLICT inserts and bool_or to order by booleans, which min and max do not take.

Source code in ormar/databases/dialects.py
Python
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
class PostgresRules(DialectRules):
    """
    PostgreSQL: ``ON CONFLICT`` inserts and ``bool_or`` to order by booleans,
    which ``min`` and ``max`` do not take.
    """

    def make_insert(self, *, table: sqlalchemy.Table) -> postgresql.Insert:
        """
        :param table: written table
        :type table: sqlalchemy.Table
        :return: PostgreSQL insert of the table
        :rtype: postgresql.Insert
        """
        return postgresql.insert(table=table)

    def ordering_aggregate(self, *, field_type: Any, descending: bool) -> str:
        """
        :param field_type: python type of the ordered field
        :type field_type: Any
        :param descending: the order is descending
        :type descending: bool
        :return: ``bool_or`` for booleans, else ``min`` or ``max``
        :rtype: str
        """
        if field_type is bool:
            return "bool_or"
        return super().ordering_aggregate(field_type=field_type, descending=descending)

make_insert(*, table)

Parameters:

Name Type Description Default
table Table

written table

required

Returns:

Type Description
postgresql.Insert

PostgreSQL insert of the table

Source code in ormar/databases/dialects.py
Python
352
353
354
355
356
357
358
359
def make_insert(self, *, table: sqlalchemy.Table) -> postgresql.Insert:
    """
    :param table: written table
    :type table: sqlalchemy.Table
    :return: PostgreSQL insert of the table
    :rtype: postgresql.Insert
    """
    return postgresql.insert(table=table)

ordering_aggregate(*, field_type, descending)

Parameters:

Name Type Description Default
field_type Any

python type of the ordered field

required
descending bool

the order is descending

required

Returns:

Type Description
str

bool_or for booleans, else min or max

Source code in ormar/databases/dialects.py
Python
361
362
363
364
365
366
367
368
369
370
371
372
def ordering_aggregate(self, *, field_type: Any, descending: bool) -> str:
    """
    :param field_type: python type of the ordered field
    :type field_type: Any
    :param descending: the order is descending
    :type descending: bool
    :return: ``bool_or`` for booleans, else ``min`` or ``max``
    :rtype: str
    """
    if field_type is bool:
        return "bool_or"
    return super().ordering_aggregate(field_type=field_type, descending=descending)

SqliteRules

Bases: DialectRules

SQLite: ON CONFLICT inserts, foreign keys switched on per connection, an explicit BEGIN in transactions and no cross-schema foreign keys.

Source code in ormar/databases/dialects.py
Python
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
class SqliteRules(DialectRules):
    """
    SQLite: ``ON CONFLICT`` inserts, foreign keys switched on per connection,
    an explicit ``BEGIN`` in transactions and no cross-schema foreign keys.
    """

    autocommit_engine = True
    cross_schema_foreign_keys = False
    foreign_keys_pragma = True
    explicit_begin = True
    # RETURNING came with SQLite 3.35, aiosqlite runs the stdlib sqlite3
    update_returning = sqlite3.sqlite_version_info >= (3, 35)

    def make_insert(self, *, table: sqlalchemy.Table) -> sqlite.Insert:
        """
        :param table: written table
        :type table: sqlalchemy.Table
        :return: SQLite insert of the table
        :rtype: sqlite.Insert
        """
        return sqlite.insert(table=table)

make_insert(*, table)

Parameters:

Name Type Description Default
table Table

written table

required

Returns:

Type Description
sqlite.Insert

SQLite insert of the table

Source code in ormar/databases/dialects.py
Python
336
337
338
339
340
341
342
343
def make_insert(self, *, table: sqlalchemy.Table) -> sqlite.Insert:
    """
    :param table: written table
    :type table: sqlalchemy.Table
    :return: SQLite insert of the table
    :rtype: sqlite.Insert
    """
    return sqlite.insert(table=table)

conflict_updates(*, table, inserted, update_names, on_update, value_updates=None, update_variables=False)

Return the columns a conflicting row is updated with: update_names from the inserted row, on_update columns from ormar_on_update_<column> parameters and the update_values columns value_updates builds for the inserted row (see update_value_clauses)::

Text Only
1
2
score = excluded.score
completed = tracks.completed + :ormar_u0_0

With update_variables every bound value is read from a MySQL user variable instead (see update_assignment).

Parameters:

Name Type Description Default
table Table

written table

required
inserted Any

the inserted row (excluded or MySQL inserted)

required
update_names Tuple[str, ...]

columns updated from the inserted row

required
on_update Tuple[str, ...]

columns updated with their on_update value

required
value_updates Optional[Callable[[Any], Dict[str, Any]]]

builds the update_values columns from the inserted row

None
update_variables bool

read the bound values from user variables

False

Returns:

Type Description
Dict[str, Any]

value per updated column

Source code in ormar/databases/dialects.py
Python
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 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
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
def conflict_updates(
    *,
    table: sqlalchemy.Table,
    inserted: Any,
    update_names: Tuple[str, ...],
    on_update: Tuple[str, ...],
    value_updates: Optional[Callable[[Any], Dict[str, Any]]] = None,
    update_variables: bool = False,
) -> Dict[str, Any]:
    """
    Return the columns a conflicting row is updated with: ``update_names`` from
    the inserted row, ``on_update`` columns from ``ormar_on_update_<column>``
    parameters and the ``update_values`` columns ``value_updates`` builds for
    the inserted row (see ``update_value_clauses``)::

        score = excluded.score
        completed = tracks.completed + :ormar_u0_0

    With ``update_variables`` every bound value is read from a MySQL user
    variable instead (see ``update_assignment``).

    :param table: written table
    :type table: sqlalchemy.Table
    :param inserted: the inserted row (``excluded`` or MySQL ``inserted``)
    :type inserted: Any
    :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 value_updates: builds the ``update_values`` columns from the
        inserted row
    :type value_updates: Optional[Callable[[Any], Dict[str, Any]]]
    :param update_variables: read the bound values from user variables
    :type update_variables: bool
    :return: value per updated column
    :rtype: Dict[str, Any]
    """
    updates: Dict[str, Any] = {name: inserted[name] for name in update_names}
    for name in on_update:
        updates[name] = sqlalchemy.bindparam(
            key=f"ormar_on_update_{name}", type_=table.c[name].type
        )
    if value_updates is not None:
        updates.update(value_updates(inserted))
    if not update_variables:
        return updates
    variables = {
        f"ormar_on_update_{name}": on_update_variable(index=index)
        for index, name in enumerate(on_update)
    }

    def read_variable(element: Any, **kwargs: Any) -> Any:
        """
        :param element: element of an update clause
        :type element: Any
        :param kwargs: traversal options, unused
        :type kwargs: Any
        :return: the user variable of a bind parameter, None keeps the element
        :rtype: Any
        """
        if isinstance(element, BindParameter):
            return sqlalchemy.literal_column(
                variables.get(element.key, f"@{element.key}"), type_=element.type
            )
        return None

    return {
        name: replacement_traverse(value, {}, read_variable)
        for name, value in updates.items()
    }

on_update_variable(index)

Return the MySQL user variable holding the on_update value of the column at index of the sorted on_update columns; named by position, as variable names are limited to 64 characters::

Text Only
1
on_update_variable(index=0)  # -> @ormar_on_update_0

The values of update_values are held in variables named like their bind parameters (@ormar_u0, @ormar_u1_0).

Parameters:

Name Type Description Default
index int

position of the column in on_update

required

Returns:

Type Description
str

variable name

Source code in ormar/databases/dialects.py
Python
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
def on_update_variable(index: int) -> str:
    """
    Return the MySQL user variable holding the ``on_update`` value of the column
    at ``index`` of the sorted ``on_update`` columns; named by position, as
    variable names are limited to 64 characters::

        on_update_variable(index=0)  # -> @ormar_on_update_0

    The values of ``update_values`` are held in variables named like their
    bind parameters (``@ormar_u0``, ``@ormar_u1_0``).

    :param index: position of the column in ``on_update``
    :type index: int
    :return: variable name
    :rtype: str
    """
    return f"@ormar_on_update_{index}"

rules_for(*, backend_name)

Return the rules of a backend; one without its own rules gets the base ones, which keep the standard SQL and reject on_conflict.

Parameters:

Name Type Description Default
backend_name str

backend name of the database url, e.g. mariadb

required

Returns:

Type Description
DialectRules

rules of the backend

Source code in ormar/databases/dialects.py
Python
472
473
474
475
476
477
478
479
480
481
482
483
def rules_for(*, backend_name: str) -> DialectRules:
    """
    Return the rules of a backend; one without its own rules gets the base
    ones, which keep the standard SQL and reject ``on_conflict``.

    :param backend_name: backend name of the database url, e.g. ``mariadb``
    :type backend_name: str
    :return: rules of the backend
    :rtype: DialectRules
    """
    rules_class = DIALECT_RULES.get(backend_name, DialectRules)
    return rules_class(backend_name=backend_name)

update_assignment(table, on_update, value_binds=())

Build the MySQL statement that stores the values a conflicting row is updated with in user variables: the on_update values bound as ormar_on_update_<index> in the order of on_update and the bound values of update_values under their own names::

Text Only
1
SET @ormar_on_update_0 = %s, @ormar_u0_0 = %s

MySQL drivers turn executemany of an insert into one multi-row VALUES and leave binds after it unfilled, so a multi-row upsert reads the values from these variables instead (see conflict_updates).

Parameters:

Name Type Description Default
table Table

written table

required
on_update Tuple[str, ...]

columns updated with their on_update value

required
value_binds Tuple[BindParameter, ...]

bind parameters of the update_values columns

()

Returns:

Type Description
Executable

assignment statement

Source code in ormar/databases/dialects.py
Python
122
123
124
125
126
127
128
129
130
131
132
133
134
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
def update_assignment(
    table: sqlalchemy.Table,
    on_update: Tuple[str, ...],
    value_binds: Tuple[BindParameter, ...] = (),
) -> Executable:
    """
    Build the MySQL statement that stores the values a conflicting row is
    updated with in user variables: the ``on_update`` values bound as
    ``ormar_on_update_<index>`` in the order of ``on_update`` and the bound
    values of ``update_values`` under their own names::

        SET @ormar_on_update_0 = %s, @ormar_u0_0 = %s

    MySQL drivers turn ``executemany`` of an insert into one multi-row VALUES
    and leave binds after it unfilled, so a multi-row upsert reads the values
    from these variables instead (see ``conflict_updates``).

    :param table: written table
    :type table: sqlalchemy.Table
    :param on_update: columns updated with their ``on_update`` value
    :type on_update: Tuple[str, ...]
    :param value_binds: bind parameters of the ``update_values`` columns
    :type value_binds: Tuple[BindParameter, ...]
    :return: assignment statement
    :rtype: Executable
    """
    binds = [
        sqlalchemy.bindparam(key=f"ormar_on_update_{index}", type_=table.c[name].type)
        for index, name in enumerate(on_update)
    ]
    binds.extend(
        sqlalchemy.bindparam(key=bind.key, type_=bind.type) for bind in value_binds
    )
    variables = [on_update_variable(index=index) for index in range(len(on_update))] + [
        f"@{bind.key}" for bind in value_binds
    ]
    assignments = ", ".join(
        f"{variable} = :{bind.key}" for variable, bind in zip(variables, binds)
    )
    return sqlalchemy.text(f"SET {assignments}").bindparams(*binds)