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.

classmethod all_related(exclude: Optional[List[Union[str, ForeignKey]]] = None) → List[Column]¶

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.

get_related(foreign_key: Union[ForeignKey, str]) → Objects¶

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.