PiccoloCRUD¶

This creates an ASGI app, which exposes the usual CRUD methods on your Piccolo table, as well as some extras, via a REST API.

Endpoints¶

Path	Methods	Description
/	GET, POST, DELETE	Get all rows, post a new row, or delete all matching rows.
/<id>/	GET, PUT, DELETE, PATCH	Get, update or delete a single row.
/schema/	GET	Returns a JSON schema for the table. This allows clients to auto generate forms.
/ids/	GET	Returns a mapping of all row ids to a description of the row.
/count/	GET	Returns the number of matching rows.
/new/	GET	Returns all of the default values for a new row - can be used to dynamically generate forms by the client.

Creating an ASGI app¶

Using it is as simple as this:

# app.py
from piccolo_api.crud.endpoints import PiccoloCRUD

from movies.tables import Movie


# If we just want to expose the GET endpoints:
app = PiccoloCRUD(table=Movie)

# This will expose all the endpoints:
app = PiccoloCRUD(table=Movie, read_only=False)

To expose several CRUD endpoints in our app, we use Starlette’s Router.

# app.py
from piccolo_api.crud.endpoints import PiccoloCRUD
from starlette.routing import Mount, Router

from movies.tables import Movie, Director


app = Router([
    Mount(
        path='/movie',
        app=PiccoloCRUD(table=Movie),
    ),
    Mount(
        path='/director',
        app=PiccoloCRUD(table=Director),
    ),
])

You can then run it using an ASGI server such as Uvicorn:

uvicorn app:app

Filters¶

Example schema¶

PiccoloCRUD makes filtering your data really easy using query parameters in your HTTP request. Using the following Piccolo schema as an example:

# tables.py
from piccolo.table import Table
from piccolo.columns import (
    Varchar,
    Integer,
    ForeignKey,
    Boolean,
    Text,
    Timestamp,
    Numeric,
    Real
)


class Director(Table):
    name = Varchar(length=300, null=False)


class Movie(Table):
    name = Varchar(length=300)
    rating = Real()
    duration = Integer()
    director = ForeignKey(references=Director)
    won_oscar = Boolean()
    description = Text()
    release_date = Timestamp()
    box_office = Numeric(digits=(5, 1))

Basic queries¶

Get all movies with ‘star wars’ in the name:

GET /movie/?name=star%20wars

Hint

You can try these queries for yourself, but first login at https://demo1.piccolo-orm.com/ using username: piccolo, password: piccolo123. Then prefix https://demo1.piccolo-orm.com/api/tables to all your queries.

Operators¶

As shown above you can specify which operator to use. For numeric, and date / time fields the following operators are allowed:

lt: Less Than
lte: Less Than or Equal
gt: Greater Than
gte: Greater Than or Equal
e: Equal (default)
ne: Not Equal

To specify which operator to use, pass a query parameter like field__operator=operator_name. For example duration__operator=gte.

Here’s a query which fetches all movies lasting more than 200 minutes:

GET /movie/?duration=200&duration__operator=gte

`is_null` / `not_null`¶

All field types also support the is_null and not_null operators.

For example:

# Get all rows with a null duration
GET /movie/duration__operator=is_null

# Get all rows without a null duration
GET /movie/duration__operator=not_null

Match type¶

When querying text fields (like Varchar and Text), you can specify the kind of match you’re looking for.

contains
exact
starts
ends

To specify which match type to use, pass a query parameter like field__match=match_type. For example name__match=starts.

A query which fetches all movies whose name begins with ‘star wars’:

GET /movie/?name=star%20wars&name__match=starts

Order¶

To specify which field to sort by, pass a query parameter like __order=field. For example __order=name.

A query which fetches all movies, sorted by duration:

GET /movie/?__order=duration

You can reverse the sort by prepending '-' to the field. For example:

GET /movie/?__order=-duration

Multiple columns can be used for the sort. Just separate them with a comma:

GET /movie/?__order=-duration,name

Visible fields¶

You can request a subset of columns from the GET endpoint. It means we’re not overfetching data when we’re only interested in some of it.

For example __visible_fields=id,name will only fetch the values for id and name from the Movie table.

GET /movie/?__visible_fields=id,name

{
    "rows": [
        {
            "id": 17,
            "name": "The Hobbit: The Battle of the Five Armies"
        },
        ...
    ]
}

It can even work with joins. However, you need to enable this by setting the max_joins parameter of PiccoloCRUD. Notice how we pass in director.name:

GET /movie/?__visible_fields=id,name,director.name

{
    "rows": [
        {
            "id": 17,
            "name": "The Hobbit: The Battle of the Five Armies",
            "director": {
                "name": "Peter Jackson"
            }
        },
        ...
    ]
}

Pagination¶

You can specify how many results to return, and which page to return, using the __page and __page_size query parameters.

For example, to return results 11 to 20:

GET /movie/?__page=2&__page_size=10

Readable¶

As foreign keys are just integers in Piccolo, they aren’t very descriptive about what is being pointed to. To get around this, each Table subclass can specify a ‘readable’ representation, which is more descriptive, and readable for humans. See the Piccolo docs for more details.

If you’d like to retrieve the readable representations for each foreign key in the queried table, you can do so by appending the __readable=true parameter to your GET requests.

GET /movie/?__readable=true

Which returns something like this:

{
    "rows": [
        {
            "id": 17,
            "name": "The Hobbit: The Battle of the Five Armies",
            "rating": 59,
            "duration": 164,
            "director": 1,
            "director_readable": "Peter Jackson",  // <- Note
            "won_oscar": false,
            "description": "Bilbo fights against a number of enemies to save the life of his Dwarf friends and protects the Lonely Mountain after a conflict arises.",
            "release_date": "2014-12-01T00:00:00",
            "box_office": 956
        },
        ...
    ]
}

You can also use this on GET requests when retrieving a single row, for example:

GET /movie/1/?__readable=true

Content-Range header¶

In some applications it can be practical to get information about the total number of records without invoking a separate call to the count endpoint. Piccolo API will supply this information in the Content-Range response header if the __range_header GET parameter is set to true.

You can use the __range_header_name GET parameter to configure the “plural name” used in the Content-Range response header.

The contents of the Content-Range header might look something like this for the “Movie” table: movie 0-9/100.

Example usage:

GET /movie/?__page=2&__page_size=10&__range_header=true

Source¶

PiccoloCRUD¶

class piccolo_api.crud.endpoints.PiccoloCRUD(table: Type[Table], read_only: bool = True, allow_bulk_delete: bool = False, page_size: int = 15, exclude_secrets: bool = True, validators: Validators | None = None, schema_extra: Dict[str, Any] | None = None, max_joins: int = 0, hooks: List[Hook] | None = None)[source]¶

Wraps a Piccolo table with CRUD methods for use in a REST API.

Parameters:

table – The Piccolo Table to expose CRUD methods for.
read_only – If True, only the GET method is allowed.
allow_bulk_delete – If True, allows a delete request to the root to delete all matching records. It is dangerous, so is disabled by default.
page_size – The number of results shown on each page by default.
exclude_secrets – Any Piccolo columns with secret=True will be omitted from the response.
validators – Used to provide extra validation on certain endpoints - can be easier than subclassing.
schema_extra – Additional information included in the Pydantic schema.
max_joins –
Determines whether a query can request data from related tables using joins. For example /movie/?__visible_fields=name,director.name, which would return:
```
{
    'rows': [
        {
            'name': 'Star Wars',
            'director': {
                'name': 'George Lucas'
            }
        }
    ]
}
```
This is a very powerful feature, but before enabling it, bear in mind the following:
- If set too high, it could be used maliciously to craft slow queries which contain lots of joins, which could slow down your site.
- Don’t enable it if sensitive data is contained in related tables, as this feature can be used to retrieve that data.
It’s best used when the data in related tables is not of a sensitive nature and the client is highly trusted. Consider using it with exclude_secrets=True.

To see which fields can be filtered in this way, you can check the visible_fields_options value returned by the /schema endpoint.

Validators¶

class piccolo_api.crud.endpoints.Validators(every: t.List[ValidatorFunction] = [], get_single: t.List[ValidatorFunction] = [], put_single: t.List[ValidatorFunction] = [], patch_single: t.List[ValidatorFunction] = [], delete_single: t.List[ValidatorFunction] = [], post_single: t.List[ValidatorFunction] = [], get_all: t.List[ValidatorFunction] = [], delete_all: t.List[ValidatorFunction] = [], get_references: t.List[ValidatorFunction] = [], get_ids: t.List[ValidatorFunction] = [], get_new: t.List[ValidatorFunction] = [], get_schema: t.List[ValidatorFunction] = [], get_count: t.List[ValidatorFunction] = [], extra_context: t.Dict[str, t.Any] = {})[source]¶

These validators are run by the corresponding method on PiccoloCRUD.

The validator function is given the PiccoloCRUD instance, and the Starlette Request instance, and should raise a Starlette HTTPException if there is a problem.

Async functions are also supported. Here are some examples:

def validator_1(piccolo_crud: PiccoloCRUD, request: Request):
    if not request.user.user.superuser:
        raise HTTPException(
            status_code=403,
            "Only a superuser can do this"
        )

async def validator_2(piccolo_crud: PiccoloCRUD, request: Request):
    if not await my_check_user_function(request.user.user):
        raise HTTPException(
            status_code=403,
            "The user can't do this."
        )

PiccoloCRUD¶

Endpoints¶

Creating an ASGI app¶

Filters¶

Example schema¶

Basic queries¶

Operators¶

is_null / not_null¶

Match type¶

Order¶

Visible fields¶

Pagination¶

Readable¶

Content-Range header¶

Source¶

PiccoloCRUD¶

Validators¶

`is_null` / `not_null`¶