SQLAlchemy core statements
When a query is easier to write in SQLAlchemy core than with the QuerySet API,
run it on ormar's own connection with database.get_query_executor(). The statement
runs on the same connection pool and, inside database.transaction(), on the same
connection and in the same transaction as ormar's queries.
The examples below use this model:
| Python | |
|---|---|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | |
The model's table
Model.ormar_config.table is the sqlalchemy.Table of the model. Its columns are
named like the columns in the database, so a field declared with name= is reached
by that name: the owner field above is accounts.c.owner_name.
Query executor
get_query_executor() is an async context manager that yields an executor bound to
a connection:
| Python | |
|---|---|
1 2 3 4 5 6 | |
Where the statements run:
- inside
database.transaction(): on the transaction's connection, so they see the transaction's uncommitted changes and are committed or rolled back with it, - outside a transaction: on a pooled connection in autocommit mode, each statement is committed on its own,
- with
get_query_executor(transactional=True)outside a transaction: in a transaction opened for theasync withblock (inside a transaction the parameter changes nothing).
Every method takes a SQLAlchemy statement and optional params, values of named
bindparam()s of the statement.
| Method | Returns |
|---|---|
fetch_all(query, params=None, as_tuples=False) |
list of rows |
fetch_one(query, params=None, as_tuples=False) |
first row or None |
fetch_val(query, column=0, params=None) |
value of the column at position column of the first row, or None without rows |
execute(query, params=None) |
for an INSERT the primary key of the inserted row, for UPDATE / DELETE the number of matched rows (also those already holding the new values) |
execute_many(query, values) |
None; runs the statement once per mapping in values |
iterate(query, params=None) |
async iterator over the rows, fetched in batches |
Reading rows
Rows are mappings keyed by the result labels: the column name or the name given with
.label(). The selected column objects work as keys too. Rows are not tuples, so
row[0] raises a KeyError; to read columns by position pass as_tuples=True to
fetch_all() or fetch_one(), which return tuples of values in select order (with the
same type conversion), or use fetch_val(query, column=...) for a single value.
| Python | |
|---|---|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | |
Treat rows as read-only, use dict(row) for a copy you can change.
Writing rows
| Python | |
|---|---|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | |
Outside a transaction each row of execute_many() can be committed on its own, use
transactional=True (or database.transaction()) to make the batch atomic.
Warning
Statements run through the executor skip what ormar does in Python: field
validation, default and on_update values of fields and signals. Of the
defaults only server_default applies.
Iterating
iterate() fetches the rows in batches instead of loading them all at once. Some
drivers (asyncpg) stream only inside a transaction, so open one:
| Python | |
|---|---|
1 2 3 4 5 | |
In one transaction with ormar
Inside database.transaction() core statements and ormar queries share the
connection, so you can lock a row with core and update it with ormar, and both are
committed or rolled back together:
| Python | |
|---|---|
1 2 3 4 5 6 7 8 9 10 11 | |
The ValueError rolls back the transaction. For row locks on ormar's own queries see
select_for_update.
Rows back to models
A row with all columns of the model's table becomes a model with
translate_row_to_fields(), which maps the column names to field names:
| Python | |
|---|---|
1 2 3 4 5 | |
Or select only the primary keys with core and load the models with ormar, which also
runs select_related() and the other QuerySet options:
| Python | |
|---|---|
1 2 | |
When you need plain data keyed by field names rather than models, values() and
values_list() skip core altogether, see raw data:
| Python | |
|---|---|
1 2 | |