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. The allowed operators are:

  • lt: Less Than

  • lte: Less Equal Than

  • gt: Greater Than

  • gte: Greater Equal Than

  • e: Equal (default)

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

A query which fetches all movies lasting more than 200 minutes:

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

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

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: Optional[Validators] = None, schema_extra: Optional[Dict[str, Any]] = None, max_joins: int = 0, hooks: Optional[List[Hook]] = None)

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: List[Callable[[PiccoloCRUD, Request], None]] = [], get_single: List[Callable[[PiccoloCRUD, Request], None]] = [], put_single: List[Callable[[PiccoloCRUD, Request], None]] = [], patch_single: List[Callable[[PiccoloCRUD, Request], None]] = [], delete_single: List[Callable[[PiccoloCRUD, Request], None]] = [], post_single: List[Callable[[PiccoloCRUD, Request], None]] = [], get_all: List[Callable[[PiccoloCRUD, Request], None]] = [], delete_all: List[Callable[[PiccoloCRUD, Request], None]] = [], get_references: List[Callable[[PiccoloCRUD, Request], None]] = [], get_ids: List[Callable[[PiccoloCRUD, Request], None]] = [], get_new: List[Callable[[PiccoloCRUD, Request], None]] = [], get_schema: List[Callable[[PiccoloCRUD, Request], None]] = [], get_count: List[Callable[[PiccoloCRUD, Request], None]] = [], extra_context: Dict[str, Any] = {})

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.