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
27
28
29
30
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
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
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
162
163
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
211Let's say you have the following model defined:
```python
import databases
import orm
database = databases.Database("sqlite:///db.sqlite")
models = orm.ModelRegistry(database=database)
class Note(orm.Model):
tablename = "notes"
registry = models
fields = {
"id": orm.Integer(primary_key=True),
"text": orm.String(max_length=100),
"completed": orm.Boolean(default=False),
}
```
ORM supports two types of queryset methods.
Some queryset methods return another queryset and can be chianed together like `.filter()` and `order_by`:
```python
Note.objects.filter(completed=True).order_by("id")
```
Other queryset methods return results and should be used as final method on the queryset like `.all()`:
```python
Note.objects.filter(completed=True).all()
```
## Returning Querysets
### .exclude()
To exclude instances:
```python
notes = await Note.objects.exclude(completed=False).all()
```
### .filter()
To filter instances:
```python
notes = await Note.objects.filter(completed=True).all()
```
There are some special operators defined automatically on every column:
* `in` - SQL `IN` operator.
* `exact` - filter instances matching exact value.
* `iexact` - same as `exact` but case-insensitive.
* `contains` - filter instances containing value.
* `icontains` - same as `contains` but case-insensitive.
* `lt` - filter instances having value `Less Than`.
* `lte` - filter instances having value `Less Than Equal`.
* `gt` - filter instances having value `Greater Than`.
* `gte` - filter instances having value `Greater Than Equal`.
Example usage:
```python
notes = await Note.objects.filter(text__icontains="mum").all()
notes = await Note.objects.filter(id__in=[1, 2, 3]).all()
```
### .limit()
To limit number of results:
```python
await Note.objects.limit(1).all()
```
### .offset()
To apply offset to query results:
```python
await Note.objects.offset(1).all()
```
As mentioned before, you can chain multiple queryset methods together to form a query.
As an exmaple:
```python
await Note.objects.order_by("id").limit(1).offset(1).all()
await Note.objects.filter(text__icontains="mum").limit(2).all()
```
### .order_by()
To order query results:
```python
notes = await Note.objects.order_by("text", "-id").all()
```
!!! note
This will sort by ascending `text` and descending `id`.
## Returning results
### .all()
To retrieve all the instances:
```python
notes = await Note.objects.all()
```
### .create()
You need to pass the required model attributes and values to the `.create()` method:
```python
await Note.objects.create(text="Buy the groceries.", completed=False)
await Note.objects.create(text="Call Mum.", completed=True)
await Note.objects.create(text="Send invoices.", completed=True)
```
### .delete()
You can `delete` instances by calling `.delete()` on a queryset:
```python
await Note.objects.filter(completed=True).delete()
```
It's not very common, but to delete all rows in a table:
```python
await Note.objects.delete()
```
You can also call delete on a queried instance:
```python
note = await Note.objects.first()
await note.delete()
```
### .exists()
To check if any instances matching the query exist. Returns `True` or `False`.
```python
await Note.objects.filter(completed=True).exists()
```
### .first()
This will return the first instance or `None`:
```python
note = await Note.objects.filter(completed=True).first()
```
`pk` always refers to the model's primary key field:
```python
note = await Note.objects.get(pk=2)
note.pk # 2
```
### .get()
To get only one instance:
```python
note = await Note.objects.get(id=1)
```
!!! note
`.get()` expects to find only one instance. This can raise `NoMatch` or `MultipleMatches`.
### .update()
`.update()` method is defined on model instances.
You need to query to get a `Note` instance first:
```python
note = await Note.objects.first()
```
Then update the field(s):
```python
await note.update(completed=True)
```
## Convenience Methods
### .get_or_create()
To get an existing instance matching the query, or create a new one.
This will retuurn a tuple of `instance` and `created`.
```python
note, created = await Note.objects.get_or_create(text="Going to car wash")
```
!!! note
Since `get_or_create()` is doing a [get()](#get), it can raise `MultipleMatches` exception.