API reference

Table

class piccolo.table.Table(ignore_missing: bool = False, exists_in_db: bool = False, **kwargs)

The class represents a database table. An instance represents a row.

Assigns any default column values to the class.

Parameters
  • ignore_missing – If False a ValueError will be raised if any column values haven’t been provided.

  • exists_in_db – Used internally to track whether this row exists in the database.

add_m2m(*rows: Table, m2m: M2M, extra_column_values: Dict[Union[Column, str], Any] = {}) M2MAddRelated

Save the row if it doesn’t already exist in the database, and insert an entry into the joining table.

>>> band = await Band.objects().get(Band.name == "Pythonistas")
>>> await band.add_m2m(
...     Genre(name="Punk rock"),
...     m2m=Band.genres
... )
[{'id': 1}]
Parameters

extra_column_values

If the joining table has additional columns besides the two required foreign keys, you can specify the values for those additional columns. For example, if this is our joining table:

class GenreToBand(Table):
    band = ForeignKey(Band)
    genre = ForeignKey(Genre)
    reason = Text()

We can provide the reason value:

await band.add_m2m(
    Genre(name="Punk rock"),
    m2m=Band.genres,
    extra_column_values={
        "reason": "Their second album was very punk."
    }
)

classmethod all_columns(exclude: Optional[List[Union[str, Column]]] = None) List[Column]

Used in conjunction with select queries. Just as we can use all_columns to retrieve all of the columns from a related table, we can also use it at the root of our query to get all of the columns for the root table. For example:

await Band.select(
    Band.all_columns(),
    Band.manager.all_columns()
)

This is mostly useful when the table has a lot of columns, and typing them out by hand would be tedious.

Parameters

exclude – You can request all columns, except these.

Used in conjunction with objects queries. Just as we can use all_related on a ForeignKey, you can also use it for the table at the root of the query, which will return each related row as a nested object. For example:

concert = await Concert.objects(
    Concert.all_related()
)

>>> concert.band_1
<Band: 1>
>>> concert.band_2
<Band: 2>
>>> concert.venue
<Venue: 1>

This is mostly useful when the table has a lot of foreign keys, and typing them out by hand would be tedious. It’s equivalent to:

concert = await Concert.objects(
    Concert.venue,
    Concert.band_1,
    Concert.band_2
)
Parameters

exclude – You can request all columns, except these.

classmethod alter() Alter

Used to modify existing tables and columns.

await Band.alter().rename_column(Band.popularity, 'rating')
classmethod count() Count

Count the number of matching rows.

await Band.count().where(Band.popularity > 1000)
classmethod create_index(columns: List[Union[str, Column]], method: IndexMethod = IndexMethod.btree, if_not_exists: bool = False) CreateIndex

Create a table index. If multiple columns are specified, this refers to a multicolumn index, rather than multiple single column indexes.

await Band.create_index([Band.name])
classmethod create_table(if_not_exists=False, only_default_columns=False) Create

Create table, along with all columns.

await Band.create_table()
classmethod delete(force=False) Delete

Delete rows from the table.

await Band.delete().where(Band.name == 'Pythonistas')
Parameters

force – Unless set to True, deletions aren’t allowed without a where clause, to prevent accidental mass deletions.

classmethod drop_index(columns: List[Union[str, Column]], if_exists: bool = True) DropIndex

Drop a table index. If multiple columns are specified, this refers to a multicolumn index, rather than multiple single column indexes.

await Band.drop_index([Band.name])
classmethod exists() Exists

Use it to check if a row exists, not if the table exists.

await Band.exists().where(Band.name == 'Pythonistas')
classmethod from_dict(data: Dict[str, Any]) Table

Used when loading fixtures. It can be overriden by subclasses in case they have specific logic / validation which needs running when loading fixtures.

get_m2m(m2m: M2M) M2MGetRelated

Get all matching rows via the join table.

>>> band = await Band.objects().get(Band.name == "Pythonistas")
>>> await band.get_m2m(Band.genres)
[<Genre: 1>, <Genre: 2>]
classmethod get_readable() Readable

Creates a readable representation of the row.

Used to fetch a Table instance, for the target of a foreign key.

band = await Band.objects().first()
manager = await band.get_related(Band.manager)
>>> print(manager.name)
'Guido'

It can only follow foreign keys one level currently. i.e. Band.manager, but not Band.manager.x.y.z.

classmethod indexes() Indexes

Returns a list of the indexes for this tables.

await Band.indexes()
classmethod insert(*rows: Table) Insert

Insert rows into the database.

await Band.insert(
    Band(name="Pythonistas", popularity=500, manager=1)
)
classmethod objects(*prefetch: Union[ForeignKey, List[ForeignKey]]) Objects

Returns a list of table instances (each representing a row), which you can modify and then call ‘save’ on, or can delete by calling ‘remove’.

pythonistas = await Band.objects().where(
    Band.name == 'Pythonistas'
).first()

pythonistas.name = 'Pythonistas Reborn'

await pythonistas.save()

# Or to remove it from the database:
await pythonistas.remove()
Parameters

prefetch

Rather than returning the primary key value of this related table, a nested object will be returned for the row on the related table.

# Without nested
band = await Band.objects().first()
>>> band.manager
1

# With nested
band = await Band.objects(Band.manager).first()
>>> band.manager
<Band 1>

property querystring: piccolo.querystring.QueryString

Used when inserting rows.

classmethod raw(sql: str, *args: Any) Raw

Execute raw SQL queries on the underlying engine - use with caution!

await Band.raw('select * from band')

Or passing in parameters:

await Band.raw("select * from band where name = {}", 'Pythonistas')
classmethod ref(column_name: str) Column

Used to get a copy of a column from a table referenced by a ForeignKey column. It’s unlikely an end user of this library will ever need to do this, but other libraries built on top of Piccolo may need this functionality.

Band.ref('manager.name')
remove() Delete

A proxy to a delete query.

remove_m2m(*rows: Table, m2m: M2M) M2MRemoveRelated

Remove the rows from the joining table.

>>> band = await Band.objects().get(Band.name == "Pythonistas")
>>> genre = await Genre.objects().get(Genre.name == "Rock")
>>> await band.remove_m2m(
...     genre,
...     m2m=Band.genres
... )
save(columns: Optional[List[Union[str, Column]]] = None) Union[Insert, Update]

A proxy to an insert or update query.

Parameters

columns

Only the specified columns will be synced back to the database when doing an update. For example:

band = await Band.objects().first()
band.popularity = 2000
await band.save(columns=[Band.popularity])

If columns=None (the default) then all columns will be synced back to the database.

classmethod select(*columns: t.Union[Selectable, str], exclude_secrets=False) Select

Get data in the form of a list of dictionaries, with each dictionary representing a row.

These are all equivalent:

await Band.select().columns(Band.name)
await Band.select(Band.name)
await Band.select('name')
Parameters

exclude_secrets – If True, any password fields are omitted from the response. Even though passwords are hashed, you still don’t want them being passed over the network if avoidable.

classmethod table_exists() TableExists

Check if the table exists in the database.

await Band.table_exists()
to_dict(*columns: Column) Dict[str, Any]

A convenience method which returns a dictionary, mapping column names to values for this table instance.

instance = await Manager.objects().get(
    Manager.name == 'Guido'
)

>>> instance.to_dict()
{'id': 1, 'name': 'Guido'}

If the columns argument is provided, only those columns are included in the output. It also works with column aliases.

>>> instance.to_dict(Manager.id, Manager.name.as_alias('title'))
{'id': 1, 'title': 'Guido'}
classmethod update(values: Optional[Dict[Union[Column, str], Any]] = None, force: bool = False, **kwargs) Update

Update rows.

All of the following work, though the first is preferable:

await Band.update(
    {Band.name: "Spamalot"}
).where(
    Band.name == "Pythonistas"
)

await Band.update(
    {"name": "Spamalot"}
).where(
    Band.name == "Pythonistas"
)

await Band.update(
    name="Spamalot"
).where(
    Band.name == "Pythonistas"
)
Parameters

force – Unless set to True, updates aren’t allowed without a where clause, to prevent accidental mass overriding of data.


LazyTableReference

class piccolo.columns.LazyTableReference(table_class_name: str, app_name: Optional[str] = None, module_path: Optional[str] = None)

Holds a reference to a Table subclass. Used to avoid circular dependencies in the references argument of ForeignKey columns.

Parameters
  • table_class_name – The name of the Table subclass. For example, 'Manager'.

  • app_name – If specified, the Table subclass is imported from a Piccolo app with the given name.

  • module_path – If specified, the Table subclass is imported from this path. For example, 'my_app.tables'.


Enums

Foreign Keys

class piccolo.columns.OnDelete(value)

Used by ForeignKey to specify the behaviour when a related row is deleted.

cascade = 'CASCADE'
no_action = 'NO ACTION'
restrict = 'RESTRICT'
set_default = 'SET DEFAULT'
set_null = 'SET NULL'
class piccolo.columns.OnUpdate(value)

Used by ForeignKey to specify the behaviour when a related row is updated.

cascade = 'CASCADE'
no_action = 'NO ACTION'
restrict = 'RESTRICT'
set_default = 'SET DEFAULT'
set_null = 'SET NULL'

Indexes

class piccolo.columns.indexes.IndexMethod(value)

Used to specify the index method for a Column.

btree = 'btree'
gin = 'gin'
gist = 'gist'
hash = 'hash'

Column defaults

Date

class piccolo.columns.defaults.DateOffset(days: int)

This makes the default value for a Date column the current date, but offset by a number of days.

For example, if you wanted the default to be tomorrow, you can specify DateOffset(days=1):

class DiscountCode(Table):
    expires = Date(default=DateOffset(days=1))
Parameters

days – The number of days to offset.