Objects¶
When doing Select queries, you get data back in the form of a list of dictionaries (where each dictionary represents a row). This is useful in a lot of situations, but it’s sometimes preferable to get objects back instead, as we can manipulate them, and save the changes back to the database.
In Piccolo, an instance of a Table
class represents a row. Let’s do some
examples.
Fetching objects¶
To get all rows:
>>> await Band.objects()
[<Band: 1>, <Band: 2>, <Band: 3>]
To limit the number of rows returned, use the order_by and limit clauses:
>>> await Band.objects().order_by(Band.popularity, ascending=False).limit(2)
[<Band: 1>, <Band: 3>]
To filter the rows we use the where clause:
>>> await Band.objects().where(Band.name == 'Pythonistas')
[<Band: 1>]
To get a single row (or None
if it doesn’t exist) use the first
clause:
>>> await Band.objects().where(Band.name == 'Pythonistas').first()
<Band: 1>
Alternatively, you can use this abbreviated syntax:
>>> await Band.objects().get(Band.name == 'Pythonistas')
<Band: 1>
You’ll notice that the API is similar to Select (expect with select
you can specify which columns are returned).
Creating objects¶
You can pass the column values using kwargs:
>>> band = Band(name="C-Sharps", popularity=100)
>>> await band.save()
Alternatively, you can pass in a dictionary, which is friendlier to static analysis tools like Mypy (it can easily detect typos in the column names):
>>> band = Band({Band.name: "C-Sharps", Band.popularity: 100})
>>> await band.save()
We also have this shortcut which combines the above into a single line:
>>> band = await Band.objects().create(name="C-Sharps", popularity=100)
Updating objects¶
save
¶
Objects have a save
method, which is
convenient for updating values:
band = await Band.objects().where(
Band.name == 'Pythonistas'
).first()
band.popularity = 100000
# This saves all values back to the database.
await band.save()
# Or specify specific columns to save:
await band.save([Band.popularity])
update_self
¶
The save
method is fine in the majority of
cases, but there are some situations where the update_self
method is preferable.
For example, if we want to increment the popularity
value, we can do this:
await band.update_self({
Band.popularity: Band.popularity + 1
})
Which does the following:
Increments the popularity in the database
Assigns the new value to the object
This is safer than:
band.popularity += 1
await band.save()
Because update_self
increments the current popularity
value in the
database, not the one on the object, which might be out of date.
Deleting objects¶
Similarly, we can delete objects, using the remove
method.
band = await Band.objects().where(
Band.name == 'Pythonistas'
).first()
await band.remove()
get_or_create
¶
With get_or_create
you can get an existing record matching the criteria,
or create a new one with the defaults
arguments:
band = await Band.objects().get_or_create(
Band.name == 'Pythonistas', defaults={Band.popularity: 100}
)
# Or using string column names
band = await Band.objects().get_or_create(
Band.name == 'Pythonistas', defaults={'popularity': 100}
)
You can find out if an existing row was found, or if a new row was created:
band = await Band.objects.get_or_create(
Band.name == 'Pythonistas'
)
band._was_created # True if it was created, otherwise False if it was already in the db
Complex where clauses are supported, but only within reason. For example:
# This works OK:
band = await Band.objects().get_or_create(
(Band.name == 'Pythonistas') & (Band.popularity == 1000),
)
# This is problematic, as it's unclear what the name should be if we
# need to create the row:
band = await Band.objects().get_or_create(
(Band.name == 'Pythonistas') | (Band.name == 'Rustaceans'),
defaults={'popularity': 100}
)
to_dict
¶
If you need to convert an object into a dictionary, you can do so using the
to_dict
method.
band = await Band.objects().first()
>>> band.to_dict()
{'id': 1, 'name': 'Pythonistas', 'manager': 1, 'popularity': 1000}
If you only want a subset of the columns, or want to use aliases for some of the columns:
band = await Band.objects().first()
>>> band.to_dict(Band.id, Band.name.as_alias('title'))
{'id': 1, 'title': 'Pythonistas'}
refresh
¶
If you have an object which has gotten stale, and want to refresh it, so it
has the latest data from the database, you can use the
refresh
method.
# If we have an instance:
band = await Band.objects().first()
# And it has gotten stale, we can refresh it:
await band.refresh()
# Or just refresh certain columns:
await band.refresh([Band.name])
It works with prefetch
too:
# If we have an instance with a child object:
band = await Band.objects(Band.manager).first()
# And it has gotten stale, we can refresh it:
await band.refresh()
# The nested object will also be updated if it was stale:
>>> band.manager.name
"New value"
refresh
is very useful in unit tests:
# If we have an instance:
band = await Band.objects().where(Band.name == "Pythonistas").first()
# Call an API endpoint which updates the object (e.g. with httpx):
await client.patch(f"/band/{band.id}/", json={"popularity": 5000})
# Make sure the instance was updated:
await band.refresh()
assert band.popularity == 5000
Query clauses¶
batch¶
See batch.
callback¶
See callback.
first¶
See first.
limit¶
See limit.
lock_rows¶
See lock_rows.
offset¶
See offset.
order_by¶
See order_by.
output¶
See output.
where¶
See where .