API reference¶
Table¶
- class piccolo.table.Table(_data: Optional[Dict[Column, Any]] = None, _ignore_missing: bool = False, _exists_in_db: bool = False, **kwargs)¶
The class represents a database table. An instance represents a row.
The constructor can be used to assign column values.
Note
The
_data
,_ignore_missing
, and_exists_in_db
arguments are prefixed with an underscore to help prevent a clash with a column name which might be passed in via kwargs.- Parameters
_data –
There’s two ways of passing in the data for each column. Firstly, you can use kwargs:
Band(name="Pythonistas")
Secondly, you can pass in a dictionary which maps column classes to values:
Band({Band.name: 'Pythonistas'})
The advantage of this second approach is it’s more strongly typed, and linters such as flake8 or MyPy will more easily detect typos.
_ignore_missing – If
False
aValueError
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[Sequence[Union[str, Column]]] = None) List[Column] ¶
Used in conjunction with
select
queries. Just as we can useall_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 useall_related
on aForeignKey
, 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[Column, str]], 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 awhere
clause, to prevent accidental mass deletions.
- classmethod drop_index(columns: List[Union[Column, str]], 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]) TableInstance ¶
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 notBand.manager.x.y.z
.
- classmethod indexes() Indexes ¶
Returns a list of the indexes for this tables.
await Band.indexes()
- classmethod insert(*rows: TableInstance) Insert[TableInstance] ¶
Insert rows into the database.
await Band.insert( Band(name="Pythonistas", popularity=500, manager=1) )
- classmethod objects(*prefetch: Union[ForeignKey, List[ForeignKey]]) Objects[TableInstance] ¶
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: 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')
- refresh(columns: Optional[Sequence[Column]] = None) Refresh ¶
Used to fetch the latest data for this instance from the database. Modifies the instance in place, but also returns it as a convenience.
- Parameters
columns – If you only want to refresh certain columns, specify them here. Otherwise all columns are refreshed.
Example usage:
# Get an instance from the database. instance = await Band.objects.first() # Later on we can refresh this instance with the latest data # from the database, in case it has gotten stale. await instance.refresh() # Alternatively, running it synchronously: instance.refresh().run_sync()
- 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[Sequence[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 columns withsecret=True
are omitted from the response. For example, we use this for the password column ofBaseUser
. Even though the 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, use_auto_update: bool = True, **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 awhere
clause, to prevent accidental mass overriding of data.use_auto_update – Whether to use the
auto_update
values on any columns. See theauto_update
argument onColumn
for more information.
Refresh¶
- class piccolo.query.methods.refresh.Refresh(instance: Table, columns: t.Optional[t.Sequence[Column]] = None)¶
Used to refresh
Table
instances with the latest data data from the database. Accessible viarefresh
.- Parameters
instance – The instance to refresh.
columns – Which columns to refresh - it not specified, then all columns are refreshed.
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 thereferences
argument ofForeignKey
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¶
Indexes¶
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.
Testing¶
ModelBuilder¶
- class piccolo.testing.model_builder.ModelBuilder¶
- async classmethod build(table_class: Type[Table], defaults: Optional[Dict[Union[Column, str], Any]] = None, persist: bool = True, minimal: bool = False) Table ¶
Build a
Table
instance with random data and save async. If theTable
has any foreign keys, then the related rows are also created automatically.- Parameters
table_class – Table class to randomize.
defaults – Any values specified here will be used instead of random values.
persist – Whether to save the new instance in the database.
minimal – If
True
then any columns withnull=True
are assigned a value ofNone
.
Examples:
# Create a new instance with all random values: manager = await ModelBuilder.build(Manager) # Create a new instance, with certain defaults: manager = await ModelBuilder.build( Manager, {Manager.name: 'Guido'} ) # Create a new instance, but don't save it in the database: manager = await ModelBuilder.build(Manager, persist=False) # Create a new instance, with all null values set to None: manager = await ModelBuilder.build(Manager, minimal=True) # We can pass other table instances in as default values: band = await ModelBuilder.build(Band, {Band.manager: manager})
create_db_tables / drop_db_tables¶
- async piccolo.table.create_db_tables(*tables: Type[Table], if_not_exists: bool = False) None ¶
Creates the database table for each
Table
class passed in. The tables are created in the correct order, based on their foreign keys.- Parameters
tables – The tables to create in the database.
if_not_exists – No errors will be raised if any of the tables already exist in the database.
- piccolo.table.create_db_tables_sync(*tables: Type[Table], if_not_exists: bool = False) None ¶
A sync wrapper around
create_db_tables()
.
- async piccolo.table.drop_db_tables(*tables: Type[Table]) None ¶
Drops the database table for each
Table
class passed in. The tables are dropped in the correct order, based on their foreign keys.- Parameters
tables – The tables to delete from the database.
- piccolo.table.drop_db_tables_sync(*tables: Type[Table]) None ¶
A sync wrapper around
drop_db_tables()
.