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 .