Piccolo uses Pydantic internally to serialize and deserialize data.


Using create_pydantic_model we can easily create a Pydantic model from a Piccolo Table.

Using this example schema:

from piccolo.columns import ForeignKey, Integer, Varchar
from piccolo.table import Table

class Manager(Table):
    name = Varchar()

class Band(Table):
    name = Varchar(length=100)
    manager = ForeignKey(Manager)
    popularity = Integer()

Creating a Pydantic model is as simple as:

from piccolo.utils.pydantic import create_pydantic_model

BandModel = create_pydantic_model(Band)

We can then create model instances from data we fetch from the database:

# If using objects:
band = await Band.objects().get( == 'Pythonistas')
model = BandModel(**band.to_dict())

# If using select:
band = await == 'Pythonistas').first()
model = BandModel(**band)


You have several options for configuring the model, as shown below.

include_columns / exclude_columns

If we want to exclude the popularity column from the Band table:

BandModel = create_pydantic_model(Band, exclude_columns=(Band.popularity,))

Conversely, if you only wanted the popularity column:

BandModel = create_pydantic_model(Band, include_columns=(Band.popularity,))


Another great feature is nested=True. For each ForeignKey in the Piccolo Table, the Pydantic model will contain a sub model for the related table.

For example:

BandModel = create_pydantic_model(Band, nested=True)

If we were to write BandModel by hand instead, it would look like this:

from pydantic import BaseModel

class ManagerModel(BaseModel):
    name: str

class BandModel(BaseModel):
    name: str
    manager: ManagerModel
    popularity: int

But with nested=True we can achieve this with one line of code.

To populate a nested Pydantic model with data from the database:

# If using objects:
band = await Band.objects(Band.manager).get( == 'Pythonistas')
model = BandModel(**band.to_dict())

# If using select:
band = await
).where( == 'Pythonistas'
model = BandModel(**band)



There is a video tutorial on YouTube.


Sometimes you’ll want to include the Piccolo Table’s primary key column in the generated Pydantic model. For example, in a GET endpoint, we usually want to include the id in the response:

// GET /api/bands/1/
// Response:
{"id": 1, "name": "Pythonistas", "popularity": 1000}

Other times, you won’t want the Pydantic model to include the primary key column. For example, in a POST endpoint, when using a Pydantic model to serialise the payload, we don’t expect the user to pass in an id value:

// POST /api/bands/
// Payload:
{"name": "Pythonistas", "popularity": 1000}

By default the primary key column isn’t included - you can add it using:

BandModel = create_pydantic_model(Band, include_default_columns=True)


piccolo.utils.pydantic.create_pydantic_model(table: Type[Table], nested: Union[bool, Tuple[ForeignKey, ...]] = False, exclude_columns: Tuple[Column, ...] = (), include_columns: Tuple[Column, ...] = (), include_default_columns: bool = False, include_readable: bool = False, all_optional: bool = False, model_name: Optional[str] = None, deserialize_json: bool = False, recursion_depth: int = 0, max_recursion_depth: int = 5, **schema_extra_kwargs) Type[BaseModel]

Create a Pydantic model representing a table.

  • table – The Piccolo Table you want to create a Pydantic serialiser model for.

  • nested – Whether ForeignKey columns are converted to nested Pydantic models. If False, none are converted. If True, they all are converted. If a tuple of ForeignKey columns is passed in, then only those are converted.

  • exclude_columns – A tuple of Column instances that should be excluded from the Pydantic model. Only specify include_columns or exclude_columns.

  • include_columns – A tuple of Column instances that should be included in the Pydantic model. Only specify include_columns or exclude_columns.

  • include_default_columns – Whether to include columns like id in the serialiser. You will typically include these columns in GET requests, but don’t require them in POST requests.

  • include_readable – Whether to include ‘readable’ columns, which give a string representation of a foreign key.

  • all_optional – If True, all fields are optional. Useful for filters etc.

  • model_name – By default, the classname of the Piccolo Table will be used, but you can override it if you want multiple Pydantic models based off the same Piccolo table.

  • deserialize_json – By default, the values of any Piccolo JSON or JSONB columns are returned as strings. By setting this parameter to True, they will be returned as objects.

  • recursion_depth – Not to be set by the user - used internally to track recursion.

  • max_recursion_depth – If using nested models, this specifies the max amount of recursion.

  • schema_extra_kwargs

    This can be used to add additional fields to the schema. This is very useful when using Pydantic’s JSON Schema features. For example:

    >>> my_model = create_pydantic_model(Band, my_extra_field="Hello")
    >>> my_model.schema()
    {..., "my_extra_field": "Hello"}


A Pydantic model.


A good place to see create_pydantic_model in action is PiccoloCRUD, as it uses create_pydantic_model extensively to create Pydantic models from Piccolo tables.

FastAPI template

Piccolo’s FastAPI template uses create_pydantic_model to create serializers.

To create a new FastAPI app using Piccolo, simply use:

piccolo asgi new

See the ASGI docs for more details.