ORM basics¶
As been said before in the Introduction, Wefram-based project uses SQLAlchemy ORM.
Every storing in the relational database entity describes as Python-class. All those classes directly maps to the corresponding database tables. When the project programmer about to read objects from the database, about to create or update corresponding object, delete and deal with relationships - he or she deals not with database itself with raw SQL queries (it is not prohibitted at all, but strongly not recommended to), but uses corresponding method of Python classes.
It is great that Wefram project uses one of the most popular ORM library, so programmer easely can find answers on most questions on how to deal with it.
For better understanding what is ORM it is best to read corresponding documentation not here, but on the SQLAlchemy documentation site.
The library¶
To use ORM, the programmer about to import [ds] module from the [wefram] one:
from wefram import ds
The [ds] module has much of needed for ORM operation. Most of the SQLAlchemy types, classes and functions transparently re-exported within [ds] module.
Models¶
The every entity about to be defined as python classes, basing
on the wefram.ds.model.Model
parent class:
from wefram import ds
class MyModel(ds.Model):
id = ds.UUIDPrimaryKey()
name = ds.Column(ds.String(255), nullable=False, default='')
some_value = ds.Column(ds.Integer(), nullable=True)
class MyAnotherModel(ds.Model):
id = ds.BigAutoIncrement()
name = ds.Column(ds.String(60))
jsoned_data = ds.Column(ds.JSONB())
As you see here - we have defined two models. Each of them consists of several attributes. Most of them bases on the [ds.Column] whose defines the corresponding representation in the database. So, each attribute which is defined as [ds.Column(something)] - will be created as the corresponding column in the database with corresponding type and type parameters.
The very important part of the each model is a primary key. As you can notice - both of example models have first attribute named [id]. The exact name is not important - you may name this attribute (as any other) as you with, except of reserved SQL or PostgreSQL words.
We will not describe the relational database logics here, this is not the topic of the Wefram. But, we will show you how to make it simplier.
Defaultly, with SQLAlchemy, a primary key defines as any another column - using [ ds.Column(…) ]. The Wefram suggests you to use one of two primary key types:
UUID which is, as we think, the best way to identify objects in the relational database. It is best to identify single objects when their quantity is not very much.
Integer or BigInteger as an alternative. This type is best to identify objects when their quantity is much more than several. For example, when we storing history records (logs), or kind of orders.
And, to avoid misspelling, errors in declaration of primary keys and so on, Wefram offers helper functions.
UUID primary keys¶
The example of models with UUID primary key usage:
from wefram import ds
class MyModel(ds.Model):
id = ds.UUIDPrimaryKey()
...
The PostgreSQL nativelly supports UUIDs and the Wefram uses the PostgreSQL-based UUID as primary key. Additionally, Wefram cares about default primary key value.
Integer primary keys¶
Integer primary keys usually called auto-increment
types. The
PostgreSQL does not knows auto-increment, instead it uses own
special types: serial
or generated by default as identity
.
Let’s not flow deep into PostgreSQL logics and etc, but instead
Wefram offers more usual type AutoIncrement
and
BigAutoIncrement
.
from wefram import ds
class MyIntPkModel(ds.Model):
""" This is model with Integer auto-increment key """
id = ds.AutoIncrement()
...
class MyBigIntPkModel(ds.Model):
""" This is model with BigInteger auto-increment key """
id = ds.BigAutoIncrement()
...
CRUD basics¶
CRUD, or Create, Read, Update, Delete, like any other operations on ORM, are handled by SQLAlchemy logics. While the project programmer is free and, speaking more, advised to use SQLAlchemy v2.0 query writing for complex requests, Wefram offers helper methods on the [ds.Model] class to make work with simple cases much easier.
Fetching objects from database¶
Reading from the database - is the most often operation we about to do while speaking about relation databases. There are two approaches to this operation:
Using the default SQLAlchemy query writing algorithms;
Using simple Wefram helper methods.
The first approach is very useful when you have to make complex query. For example, when you need to create a report query fetching from many tables with different joins, subqueries and etc. Of cource, this approach is still 100 percent applicable to execute the simpliest query in you life, but you may make it much easier with the second one.
The SQLAlchemy based query writing is much better to read at the SQLAlchemy docs site, really not here. But, for the basic example how to begin, let’s give an example here:
from wefram import ds
# Declaring the model
class MyModel(ds.Model):
id = ds.UUIDPrimaryKey()
name = ds.Column(ds.String(100), nullable=True, default=None)
some = ds.Column(ds.Boolean(), nullable=True, default=False)
# And querying this model with SQLAlchemy based request
query = ds.select(MyModel).where(ds.or_(
MyModel.some.is_(False),
MyModel.name.is_(None)
)).order_by(MyModel.name)
results = await(
ds.execute(query).scalars().fetchall()
)
The second approach is very useful when you using simple queries. We do not have to use complex query modelling, we want to just call a single, simple method of the corresponding model. This approach uses Wefram extensions to the SQLAlchemy ORM model. When you declare your model with parent [ds.Model] class - you declare with extended logics already.
Lets repeat the example above with the simplier variant.
from wefram import ds
# Declaring the model
class MyModel(ds.Model):
id = ds.UUIDPrimaryKey()
name = ds.Column(ds.String(100), nullable=True, default=None)
some = ds.Column(ds.Boolean(), nullable=True, default=False)
# Using a simplier helper method
results = await MyModel.all(ds.or_(
MyModel.some.is_(False),
MyModel.name.is_(None),
order='name'
))
Or even simplier, if we have an AND filter in the query:
...
# Even more simplier query
results: List[MyModel] = await MyModel.all(
some=False, name=None, order='name'
)
And the last example, as simple as possible:
# Whooos maximum simple
results: List[MyModel] = await MyModel.all()
As we see - instead of writing a SQLAlchemy-based query we called a
single simple method all()
of the
corresponding Model
. Of cource, helper
methods cannot be used in all cases, but them makes a simple operations
handled simple.
Offset and limit¶
We can limit the quantity of resulting objects by using limit
named argument for all()
or
select()
methods. This argument
indicates maximal the quantity of objects we allow to be fetched
from the database.
# Fetch not more than 50 objects
instances = await MyModel.all(limit=50)
Like limit
limits the quantity of returned in the result objects,
offset
set the starting position of that array. In other words,
offset
tell the database “okey, fetch objects from X row”.
# Fetch objects begining from 75th one only
instances = await MyModel.all(offset=75)
Using limit
and offset
in couple we can make pagination. Let’s
look at example:
# Lets assume that every page must not exceed 50 rows
# Fetching the first page: offset=0, limit=50
page1 = await MyModel.all(offset=0, limit=50)
# The second page will have offset=50 and limit still = 50
page2 = await MyModel.all(offset=50, limit=50)
We think that we made a point on these two great parameters.
Ordering the query results¶
To order the query results we about to use named argument order
.
It assumes one of the next values:
The (str) name of the corresponding column
The list of (str) names of the corresponding columns
The column instance itself
The list of column instances
If the string (str) type have been used, then you may revert ordering by prepending the corresponding column name with minus sybmol (‘-‘).
For the corresponding column variant, we can use SQLAlchemy’s
approach with .desc()
method.
Let’s look at examples for better understanding:
# Sorting by one column 'name'
instances = await MyModel.all(order='name')
# The same with column instance
instances = await MyModel.all(order=MyModel.name)
# The same but in reverse
instances = await MyModel.all(order='-name')
instances = await MyModel.all(order=MyModel.name.desc())
# Sorting using two columns at a time
instances = await MyModel.all(order=['last_name', 'name'])
instances = await MyModel.all(order=[MyModel.last_name, MyModel.name])
Searching by the text term¶
The Wefram offers one interesting automation - the search facility. This is not the part of SQLAlchemy itself, as must other facilities, and not a simplification in general. This is a kind of automation which realises the one of the most used cases when fetching objects from the database - the search.
To make this happened, we need to use Meta
subclass (which is described
in the More about Models & Model.Meta. We will show a very short part of it here,
just to make sense how to make some model searchable. And how to use that.
The search for objects by textual term is done by calling the class method
like
or ilike
for case sensetive and case insensetive, respectively,
within corresponding fetching method, like wefram.ds.model.Model.all()
from wefram import ds
class Contant(ds.Model):
id = ds.UUIDPrimaryKey()
first_name = ds.Column(ds.String(100), nullable=False, default='')
middle_name = ds.Column(ds.String(100), nullable=False, default='')
last_name = ds.Column(ds.String(100), nullable=False, default='')
avatar = ds.Column(ds.Image(), nullable=True)
phone = ds.Column(ds.Phone(), nullable=False)
email = ds.Column(ds.Email(), nullable=False)
class Meta:
# Here we declares whose columns will be findable for
# the search facility
findable = ['first_name', 'last_name', 'email', 'phone']
# let's find something
found = await Contact.all(Contact.ilike("mike"))
Selecting for update (locking objects)¶
This is normal situation when you need to select objects whose about to be updated or even deleted in the request process. And you want to be sure that those objects may be read by other processes if only those processes does not wants to update them too, and guarantee that those processes (whose want to update the same array of objects) will wait until the current process finish.
In the raw SQL queries this usually done by adding special modifiers
to the SELECT query, SELECT .. FOR UPDATE
when we speaking about
PostgreSQL, for example.
To add this kind of modifier using Wefram helper methods such as
all()
,
select()
,
first()
,
get()
,
fetch()
,
you may pass named argument to the corresponding method call: update=True
.
# Selecting all objects with enabled=True and locking them
# using update=True
instances = await MyModel.all(enabled=False, update=True)
Fetching several objects by their primary key¶
The one special, but often used case is fetching several model objects from the database by their key (primary key). In this case, in general, we can write a query like:
instances = await MyModel.all(MyModel.id.in_(['1', '2', '3']))
# resulting list will consist of objects with primary key ('id')
# equals to '1', '2' or '3'... for example
But Wefram offers a simplier way (a little simplification for this case):
instances = await MyModel.fetch(['1', '2', '3'])
# resulting list will consist of objects with primary key
# equals to '1', '2' or '3'... for example
As we see - we even not cares about primary key column name(s)
in the query. Just calling the method
fetch()
giving primary key values to it as arguments.
Even if we need to fetch objects with complex primary key
declared - we can do this using
fetch`()
method like
get()
method (described below). Just give not plain values, but
tuple of primary key values instead, in order they been
declared in the model. For example:
from wefram import ds
class MyModel(ds.Model):
id1 = ds.Column(ds.Integer(), primary_key=True)
id2 = ds.Column(ds.Integer(), primary_key=True)
instances = await MyModel.fetch(
(1, 101), (2, 190), (3, 404)
)
# The resulting list will consist of objects with primary
# key values:
# (1) id1 = 1, id2 = 101
# (2) id1 = 2, id2 = 190
# (3) id1 = 3, id2 = 404
Fetching a single object¶
The previos section describes how to fetch objects (rows from the database) resuling with list of the corresponding model instances or a complied SQLAlchemy results. The result will always be a list (array) in that case.
Now, let’s fetch a single object. This approach has more variants to use.
As said before, there are two approachs for this situation: we can use SQLAlchemy query or we can use Wefram helper methods. This first one we wantn’t to describe here - because it is always best to read on the official site of the SQLAlchemy. The second will be reviewed here with several methods to use.
There are two typical situations when we expects only one object
to be returned from the query: when we want to fetch the exactly
object (we know it’s primary key value) and when we does not
knows the resulting object’s primary key, but knows that only
one object about to be returned (as a maximal special case, let’s
say we give limit=1
in the query).
Fetching by primary key value¶
To do that, we about to use get()
method of the corresponding model:
...
instance = await MyModel.get('5c9466b2-ebc1-4b76-ae89-72b7125815d8')
If the model has complex primary key, consisting of two or more columns, then the method above calls with all primary key columns’ values, ordered by the corresponding keys been declared in the corresponding model. For example:
class ComplexPkModel(ds.Model):
""" The very useless model, but okay for the example """
id1 = ds.Column(ds.UUID(), primary_key=True)
id2 = ds.Column(ds.Integer(), primary_key=True)
# Lets assume that we want to fetch a single object which
# has next primary key values:
# id1 = 5c9466b2-ebc1-4b76-ae89-72b7125815d8
# id2 = 1005
instance = await MyModel.get(
'5c9466b2-ebc1-4b76-ae89-72b7125815d8', 1005
)
The result will be a single object, or None if there is no object with given primary key. So, Wefram does not raises any exception when the requested object does not exists in the database, it returns None result instead.
Fetch only first object¶
There are cases when we querying for the request not by the
object’s primary key, but by another filters (or even without
any of them). But the case is that we expect only one object
to be returned, while all()
method returns a list.
Let’s assume that we have a query and we except only one
object to be returned or nothing. While using
all()
general
case we always will get a list, even empty. Not the best
options for this vase, yea?
As always, let’s look an example:
from wefram import ds
class User(ds.Model):
id = ds.UUIDPrimaryKey()
login = ds.Column(ds.String(100), nullable=False)
password = ds.Column(ds.String(255), nullable=False)
enabled = ds.Column(ds.Boolean(), nullable=False, default=True)
async def try_to_login(login: str, password: str) -> [User, None]:
""" We about to query the database searching for
the given login and password
"""
users = await User.all(login=login, password=password, enabled=True)
if users:
return users[0]
return None
Here we querying for the user with given login and password, which is enabled, and checking the resulting list for its length and returning the first returned user.
Let’s do the same query with a little simplification:
...
async def try_to_login(login: str, password: str) -> [User, None]:
""" We about to query the database searching for
the given login and password
"""
return await User.first(login=login, password=password, enabled=True)
The first()
method of the
Model
returns only first object
from the result (it even sets limit=1
automatically to not to
fetch more than one row from the database), or returns None
if
none objects with given criteria been found.
Creating the object¶
We will have nothing to fetch if nothing being created, right? So, the create is not less important operation then the read one.
To create an object we about to use create()
class method on the corresponding model. The created object will automatically be
assigned to the current default database session.
To create an object defining column’s values - just pass their values within named arguments (kwargs) defining the initial values.
Let’s look at example:
from wefram import ds
class Contant(ds.Model):
id = ds.UUIDPrimaryKey()
first_name = ds.Column(ds.String(100), nullable=False, default='')
middle_name = ds.Column(ds.String(100), nullable=False, default='')
last_name = ds.Column(ds.String(100), nullable=False, default='')
avatar = ds.Column(ds.Image(), nullable=True)
phone = ds.Column(ds.Phone(), nullable=False)
email = ds.Column(ds.Email(), nullable=False)
# Lets create a couple objects for this example model
contact1 = await Contact.create(
first_name='Mike',
last_name='Cooper',
phone='+71234561234'
)
contact2 = await Contact.create(
first_name='Car service',
phone='+70001112233'
)
# Ooh, we forgot to set email for Mike?
contact1.email = 'mike.cooper@domain.com'
# The next call IS NOT required. It is useful only if you
# need the previously created objects to be flushed to the
# database. Otherwise them will do that at the web request
# finish.
ds.flush()
Updating the existing object¶
The update operation modifies the existing object to update its corresponding values in the database. So, first of all, we need to fetch the corresponding object first.
To modify the object it is best to call the
update()
method on
the model instance, and pass new corresponding columns’ values
to it within named arguments (kwargs).
from wefram import ds
class Contant(ds.Model):
id = ds.UUIDPrimaryKey()
first_name = ds.Column(ds.String(100), nullable=False, default='')
middle_name = ds.Column(ds.String(100), nullable=False, default='')
last_name = ds.Column(ds.String(100), nullable=False, default='')
avatar = ds.Column(ds.Image(), nullable=True)
phone = ds.Column(ds.Phone(), nullable=False)
email = ds.Column(ds.Email(), nullable=False)
...
# Lets assume that we have made some objects prior to this place
instance = await ds.get('...') # We have fetched a single object
# Now let's update its columns' values
await instance.update(
first_name='Another',
phone=''
)
# That's it, we have updated the object. As been said before, we
# need to fetch the corresponding object(s) first.
Deleting the existing object¶
To delete the object, when we have fetched that object before, we just
can call instance method delete()
.
This will delete the object from the
database.
Again, the method delete()
is an
instance method, not the class one. This means that this method works
with previously fetched from the database object (using any selecting
method described before).
# Fetching a single object in this example
instance = await MyModel.get('...')
...
# And deleing that object
await instance.delete()
Deleting many objects or without fetching¶
As been described in the section above, to use
delete()
method we
have to fetch the corresponding object or objects first. This is the
approach the SQLAlchemy tells us.
This approach has one negative variant - when we need to delete many objetcs at a time, or even when we just do not want to fetch objects from the database just ot delete them (why?)
In this case, Wefram offers a class method (class, not instance)
named delete_where()
.
This method waits a clause or filter to be given like for
all()
or
first()
methods (really the same
syntax).
For example:
# The first example with SQLAlchemy-based where clause
await MyModel.delete_where(
ds.and_(MyModel.email == '', MyModel.phone == '')
)
# The second example with filter variant
await MyModel.delete_where(
email='', phone=''
)
Attention
Please note that delete_where()
is not capable of triggering
the history (because it uses the fast, direct SQL query like
DELETE FROM … WHERE …) and the history WILL NOT be
written on objects been deleted with this method! So, it is
really bad idea to delete object with history logging enabled
using exact this, maybe fast, but logging unfollowed method!
ORM models methods summary¶
- class wefram.ds.model.Model¶
The Model class which about to be used to map Python data class to the corresponding database table.
Important
The resulting database table name is not the same as the Python class name! The tablename is a merge of the app name where the corresponding Python class have been declared, and the class name itself.
For example, for the class
MyModel
declared within appmyapp
, the resulting table name will bemyappMyModel
.This behavior may be overriden with
__tablename__
attribute of the Python class:class MyModel(ds.Model): __tablename__ = 'whooshtable' ...
- async classmethod all(*clause, limit: Optional[int] = None, offset: Optional[int] = None, order: Optional[Union[Any, List[Any]]] = None, update: bool = False, **filters)¶
Returns a list of fetched from the database model objects by given criteria. The method params have been described in the
select()
method.
- async classmethod create(**initials)¶
Creates a new object of the model returning it to the caller. The new object automatically assignes to the current database session. You may set the new object’s columns values with needed ones by passing them as named arguments (kwargs).
new_object = await MyModel.create(column1='value1', column2='value2')
- async delete() None ¶
Removes the object from the database. The object must be fetched prior to removing, because the
delete()
is a instance-level (not class-level) method. Calls on the object which about to be deleted.instance = await MyModel.get('...') ... await instance.delete()
- async classmethod delete_where(*clause, **filters) None ¶
Removes object(s) from the database with direct
delete
request to the DB. This method does not loads corresponding objects from the DB prior to delete, instead it operates with given clause and filters.- Parameters
clause – A list of filtering clause (
WHERE
clause) as SQLAlchemy expression. If more than one argument be given - them all will be grouped byAND
expression.filters –
For the simplier case, when filtering with
AND
clause only, you may use named arguments to indicate whose objects/rows to fetch. For example:# Lets delete all rows where sold=True AND closed=True await MyModel.delete_where(sold=True, closed=True)
Attention
This method will not trigger history logging of deleting objects because it not load the corresponding objects prior to execute. So it is not a best idea to use it on logged objects.
- dict(attributes: Optional[List[str]] = None, exclude: Optional[List[str]] = None, deep: Optional[Union[bool, List[str]]] = False, set_name: Optional[str] = None, jsonify_names: bool = False, for_jsonify: bool = False, fq_urls: bool = True) Dict[str, Any] ¶
Returns a python dict type representation of the model object instance. Is very useful when you need to deal not with real values and attributes of the model object, but in some case - with the dict, which contains the model columns as keys and corresponding values.
By default, only
Column
type attributes will be included into resulting dict, excluding any declared additional model’s properties, non-column attributes and etc. This behavior may be changed byattributes
,exclude
, andset_name
params (see params explanation).- Parameters
attributes (optional, list) – A list of attributes whose to include into the resulting dict. May be useful when you need to include attributes whose are not columns, or exclude some attributes at all.
exclude (optional, list) – A list of column attributes whose must be excluded from the dict.
deep (bool, default = False) –
If set to
True
- the relationship-based attributes will be returned as dicts too (will be unpacked as sub-dict); otherwise only primary key(s) will be returned as a value for the relationship-based attributes.For example:
my_model_obj.dict(deep=False) # Will result as: # { # 'id': 1005, # 'name': 'Mike', # 'phones': [19432, 95123] # the related objects ids # } my_model_obj.dict(deep=True) # Will result as: # { # 'id': 1005, # 'name': 'Mike', # 'phones': [ # { # 'id': 19432', # 'phone_type': 'personal', # 'value': '+70001110011' # }, # { # 'id': 95123', # 'phone_type': 'work', # 'value': '1234' # } # ] # }
set_name (optional, str) – The name of the attributes set (see
wefram.ds.orm.model.Meta.attributes_sets
attribute of theMeta
class for explanation)jsonify_names (bool, default = False) – If set to
True
, the dict keys will have JSON-corrected names. This means that Pythonicsnake_case
names will be transformed to more JS/JSON usuallowerCamelCase
.for_jsonify (bool, default = False) – If set to
True
- the resulting dict will consist of only JSONifying keys and values.fq_urls (bool, default = True) – If set to
True
, theFile
andImage
columns will result with fully-qualified URLs instead or relative ones.
- Returns
The resulting dict containing the attributes’ keys and corresponding values
- Return type
dict
- async classmethod fetch(*pks, update: bool = False)¶
Returns a list of objects by their primary key values. Like the
get()
method, but queries for several objects at a time.- Parameters
pks (list of keys, or list of tuples of keys) – The list of primary keys of the model objects about to be fetched. It the model has complex primary key declared - the every primary key must be formed as tuple, consisting of corresponding values in the order they been declared in the model.
update (bool) – If set to
True
- resulting object will be write-locked (FOR UPDATE
)
- Returns
A list of the model objects instances.
- async classmethod first(*clause, offset: Optional[int] = None, order: Optional[Union[Any, List[Any]]] = None, update: bool = False, **filters) wefram.ds.orm.model.Model ¶
Returns a single model object by given criteria, or
None
if there is no object. The method params have been described in theselect()
method.
- async classmethod get(*pk, update: bool = False)¶
Returns a single object identified by the given primary key value.
- Parameters
pk – The primary key value criteria. If there is a complex primary key declared for the model - then pk must be a (tuple) of all primary key columns ordered the same as been declared in the model.
update (bool) – If set to
True
- resulting object will be write-locked (FOR UPDATE
)
- Returns
a single model object or
None
if there is no object with given primary key.- Return type
the model object instance, or None
- classmethod ilike(term: str) Optional[List[Union[sqlalchemy.sql.elements.BinaryExpression, sqlalchemy.sql.elements.UnaryExpression]]] ¶
Returns a case-insensetive filter clause by given textual search term. The resulting clause may be used in the corresponding fetching methods like
all()
,first()
and etc.
- json(attributes: Optional[List[str]] = None, exclude: Optional[List[str]] = None, set_name: Optional[str] = None, deep: Optional[Union[bool, List[str]]] = False) Dict[str, Any] ¶
The shortcut method for the
dict()
method, resulting with a dict with JSONed keys ready and relative URLs. For explanation of the method parameters please explore the parentdict()
method.
- property key: Union[Any, Dict[str, Any]]¶
Returns the this model instance’s primary key.
Returns a plain value if the primary key is simple (one column), or a dict contains columns’ names and corresponding values if the model has a complex primary key (two or more columns in the primary key constraint).
- classmethod like(term: str) Optional[List[Union[sqlalchemy.sql.elements.BinaryExpression, sqlalchemy.sql.elements.UnaryExpression]]] ¶
Returns a case-sensetive filter clause by given textual search term. The resulting clause may be used in the corresponding fetching methods like
all()
,first()
and etc.
- async update(**values) None ¶
Updates the existing model object with passed as named arguments values. For example:
await instance.update( name='the new name', # udpate attribute 'name' sold=True, # updaet attribute 'sold' )
- async classmethod select(*clause, limit: Optional[int] = None, offset: Optional[int] = None, order: Optional[Union[Any, List[Any]]] = None, update: bool = False, **filters) sqlalchemy.engine.result.ScalarResult ¶
Executes the
SELECT
request to fetch data from the database and returns scalars results. Those results may be used futher to retreive the requested type of data of the request results.- Parameters
clause –
A list of filtering clause (
WHERE
clause) as SQLAlchemy expression. If more than one argument be given - them all will be grouped byAND
expression.The example of the clause:
scalars = await MyModel.select( ds.or_( MyModel.option.in_(['op1', 'op2']), MyModel.another == 'the some value' ), order=MyModel.the_date.desc() )
limit (optional [int]) – Limit by given quantity of rows (transforms to the corresponding SQL “
LIMIT x
”)offset (optional [int]) – Offset from which to select rows from the corresponding table (transforms to the corresponding SQL “
OFFSET x
”)order –
Order resulting rows by given criteria. The order may be given using one of:
The (str) name of the corresponding column
The list of (str) names of the corresponding columns
The column instance itself
The list of column instances
For descending ordering prepend the column name with minus (
'-columnn'
) if (str) type used, or user corresponding SQLAlchemy.desc()
on the column instance.update (bool) – If set to
True
- then resulting rows will be locked on write preventing other processes from same selecting withupdate=True
waiting for the current process to finish. Transforms to the SQL “FOR UPDATE
” modifier.filters –
For the simplier case, when filtering with
AND
clause only, you may use named arguments to indicate whose objects/rows to fetch. For example:# Lets get all rows where sold=True AND closed=False scalars = await MyModel.select(sold=True, closed=False)
- Returns
Scalars results ready to be handled by the calling function
- Return type
sqlalchemy.engine.result.ScalarResult