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 objects:

>>> await Band.objects()
[<Band: 1>, <Band: 2>]

To get certain rows:

>>> await Band.objects().where(Band.name == 'Pythonistas')
[<Band: 1>]

To get a single row (or None if it doesn’t exist):

>>> await Band.objects().get(Band.name == 'Pythonistas')
<Band: 1>

To get the first row:

>>> await Band.objects().first()
<Band: 1>

You’ll notice that the API is similar to Select - except it returns all columns.

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

Objects have a save method, which is convenient for updating values:

band = await Band.objects().where(
    Band.name == 'Pythonistas'

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])

Deleting objects

Similarly, we can delete objects, using the remove method.

band = await Band.objects().where(
    Band.name == 'Pythonistas'

await band.remove()


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}


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'}


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])

Query clauses


See batch.


See callback.


See first.


See limit.


See offset.


See order_by.


See output.


See where .