Column Types¶

Hint

You’ll notice that the column names tend to match their SQL equivalents.

Column¶

class piccolo.columns.base.Column(null: bool = False, primary_key: bool = False, unique: bool = False, index: bool = False, index_method: IndexMethod = IndexMethod.btree, required: bool = False, help_text: str | None = None, choices: Type[Enum] | None = None, db_column_name: str | None = None, secret: bool = False, auto_update: Any = Ellipsis, **kwargs)

All other columns inherit from Column. Don’t use it directly.

The following arguments apply to all column types:

Parameters:

null – Whether the column is nullable.
primary_key – If set, the column is used as a primary key.
default – The column value to use if not specified by the user.
unique – If set, a unique constraint will be added to the column.
index – Whether an index is created for the column, which can improve the speed of selects, but can slow down inserts.
index_method – If index is set to True, this specifies what type of index is created.
required – This isn’t used by the database - it’s to indicate to other tools that the user must provide this value. Example uses are in serialisers for API endpoints, and form fields.
help_text – This provides some context about what the column is being used for. For example, for a Decimal column called value, it could say 'The units are millions of dollars'. The database doesn’t use this value, but tools such as Piccolo Admin use it to show a tooltip in the GUI.
choices – An optional Enum - when specified, other tools such as Piccolo Admin will render the available options in the GUI.
db_column_name –
If specified, you can override the name used for the column in the database. The main reason for this is when using a legacy database, with a problematic column name (for example 'class', which is a reserved Python keyword). Here’s an example:
```
class MyTable(Table):
    class_ = Varchar(db_column_name="class")

>>> await MyTable.select(MyTable.class_)
[{'id': 1, 'class': 'test'}]
```
This is an advanced feature which you should only need in niche situations.

secret –

If secret=True is specified, it allows a user to automatically omit any fields when doing a select query, to help prevent inadvertent leakage of sensitive data.

class Band(Table):
    name = Varchar()
    net_worth = Integer(secret=True)

>>> await Band.select(exclude_secrets=True)
[{'name': 'Pythonistas'}]

auto_update –

Allows you to specify a value to set this column to each time it is updated (via MyTable.update, or MyTable.save on an existing row). A common use case is having a modified_on column.

class Band(Table):
    name = Varchar()
    popularity = Integer()
    # The value can be a function or static value:
    modified_on = Timestamp(auto_update=datetime.datetime.now)

# This will automatically set the `modified_on` column to the
# current timestamp, without having to explicitly set it:
>>> await Band.update({
...     Band.popularity: Band.popularity + 100
... }).where(Band.name == 'Pythonistas')

Note - this feature is implemented purely within the ORM. If you want similar functionality on the database level (i.e. if you plan on using raw SQL to perform updates), then you may be better off creating SQL triggers instead.

Bytea¶

Used for storing bytes.

Example

class Token(Table):
    token = Bytea(default=b'token123')

# Create
>>> await Token(token=b'my-token').save()

# Query
>>> await Token.select(Token.token)
{'token': b'my-token'}

Hint

There is also a Blob column type, which is an alias for Bytea.

Boolean¶

class piccolo.columns.column_types.Boolean(default: bool | Enum | Callable[[], bool] | None = False, **kwargs)¶

Used for storing True / False values. Uses the bool type for values.

Example

class Band(Table):
    has_drummer = Boolean()

# Create
>>> await Band(has_drummer=True).save()

# Query
>>> await Band.select(Band.has_drummer)
{'has_drummer': True}

ForeignKey¶

class piccolo.columns.column_types.ForeignKey(references: Type[ReferencedTable], default: Any = None, null: bool = True, on_delete: OnDelete = OnDelete.cascade, on_update: OnUpdate = OnUpdate.cascade, target_column: str | Column | None = None, **kwargs)¶

class piccolo.columns.column_types.ForeignKey(references: LazyTableReference, default: Any = None, null: bool = True, on_delete: OnDelete = OnDelete.cascade, on_update: OnUpdate = OnUpdate.cascade, target_column: str | Column | None = None, **kwargs)

class piccolo.columns.column_types.ForeignKey(references: str, default: Any = None, null: bool = True, on_delete: OnDelete = OnDelete.cascade, on_update: OnUpdate = OnUpdate.cascade, target_column: str | Column | None = None, **kwargs)

Used to reference another table. Uses the same type as the primary key column on the table it references.

Example

class Band(Table):
    manager = ForeignKey(references=Manager)

# Create
>>> await Band(manager=1).save()

# Query
>>> await Band.select(Band.manager)
{'manager': 1}

# Query object
>>> band = await Band.objects().first()
>>> band.manager
1

Joins

You also use it to perform joins:

>>> await Band.select(Band.name, Band.manager.name).first()
{'name': 'Pythonistas', 'manager.name': 'Guido'}

To retrieve all of the columns in the related table:

>>> await Band.select(Band.name, *Band.manager.all_columns()).first()
{'name': 'Pythonistas', 'manager.id': 1, 'manager.name': 'Guido'}

To get a referenced row as an object:

manager = await Manager.objects().where(
    Manager.id == some_band.manager
)

Or use either of the following, which are just a proxy to the above:

manager = await band.get_related('manager')
manager = await band.get_related(Band.manager)

To change the manager:

band.manager = some_manager_id
await band.save()

Parameters:

references –

The Table being referenced.

class Band(Table):
    manager = ForeignKey(references=Manager)

A table can have a reference to itself, if you pass a references argument of 'self'.

class Musician(Table):
    name = Varchar(length=100)
    instructor = ForeignKey(references='self')

In certain situations, you may be unable to reference a Table class if it causes a circular dependency. Try and avoid these by refactoring your code. If unavoidable, you can specify a lazy reference. If the Table is defined in the same file:

class Band(Table):
    manager = ForeignKey(references='Manager')

If the Table is defined in a Piccolo app:

from piccolo.columns.reference import LazyTableReference

class Band(Table):
    manager = ForeignKey(
        references=LazyTableReference(
           table_class_name="Manager", app_name="my_app",
        )
    )

If you aren’t using Piccolo apps, you can specify a Table in any Python module:

from piccolo.columns.reference import LazyTableReference

class Band(Table):
    manager = ForeignKey(
        references=LazyTableReference(
           table_class_name="Manager",
           module_path="some_module.tables",
        )
        # Alternatively, Piccolo will interpret this string as
        # the same as above:
        # references="some_module.tables.Manager"
    )

on_delete –
Determines what the database should do when a row is deleted with foreign keys referencing it. If set to OnDelete.cascade, any rows referencing the deleted row are also deleted.

Options:
- OnDelete.cascade (default)
- OnDelete.restrict
- OnDelete.no_action
- OnDelete.set_null
- OnDelete.set_default
To learn more about the different options, see the Postgres docs.
```
from piccolo.columns import OnDelete

class Band(Table):
    name = ForeignKey(
        references=Manager,
        on_delete=OnDelete.cascade
    )
```
on_update –
Determines what the database should do when a row has it’s primary key updated. If set to OnUpdate.cascade, any rows referencing the updated row will have their references updated to point to the new primary key.

Options:
- OnUpdate.cascade (default)
- OnUpdate.restrict
- OnUpdate.no_action
- OnUpdate.set_null
- OnUpdate.set_default
To learn more about the different options, see the Postgres docs.
```
from piccolo.columns import OnUpdate

class Band(Table):
    name = ForeignKey(
        references=Manager,
        on_update=OnUpdate.cascade
    )
```

target_column –

By default the ForeignKey references the primary key column on the related table. You can specify an alternative column (it must have a unique constraint on it though). For example:

# Passing in a column reference:
ForeignKey(references=Manager, target_column=Manager.passport_number)

# Or just the column name:
ForeignKey(references=Manager, target_column='passport_number')

Number¶

BigInt¶

class piccolo.columns.column_types.BigInt(default: int | Enum | Callable[[], int] | None = 0, **kwargs)¶

In Postgres, this column supports large integers. In SQLite, it’s an alias to an Integer column, which already supports large integers. Uses the int type for values.

Example

class Band(Table):
    value = BigInt()

# Create
>>> await Band(popularity=1000000).save()

# Query
>>> await Band.select(Band.popularity)
{'popularity': 1000000}

BigSerial¶

class piccolo.columns.column_types.BigSerial(null: bool = False, primary_key: bool = False, unique: bool = False, index: bool = False, index_method: IndexMethod = IndexMethod.btree, required: bool = False, help_text: str | None = None, choices: Type[Enum] | None = None, db_column_name: str | None = None, secret: bool = False, auto_update: Any = Ellipsis, **kwargs)¶: An alias to a large autoincrementing integer column in Postgres.

Double Precision¶

class piccolo.columns.column_types.DoublePrecision(default: float | Enum | Callable[[], float] | None = 0.0, **kwargs)¶: The same as Real, except the numbers are stored with greater precision.

Integer¶

class piccolo.columns.column_types.Integer(default: int | Enum | Callable[[], int] | None = 0, **kwargs)¶

Used for storing whole numbers. Uses the int type for values.

Example

class Band(Table):
    popularity = Integer()

# Create
>>> await Band(popularity=1000).save()

# Query
>>> await Band.select(Band.popularity)
{'popularity': 1000}

Numeric¶

class piccolo.columns.column_types.Numeric(digits: Tuple[int, int] | None = None, default: Decimal | Enum | Callable[[], Decimal] | None = Decimal('0'), **kwargs)¶

Used for storing decimal numbers, when precision is important. An example use case is storing financial data. The value is returned as a Decimal.

Example

from decimal import Decimal

class Ticket(Table):
    price = Numeric(digits=(5,2))

# Create
>>> await Ticket(price=Decimal('50.0')).save()

# Query
>>> await Ticket.select(Ticket.price)
{'price': Decimal('50.0')}

Parameters:: digits – When creating the column, you specify how many digits are allowed using a tuple. The first value is the precision, which is the total number of digits allowed. The second value is the range, which specifies how many of those digits are after the decimal point. For example, to store monetary values up to £999.99, the digits argument is (5,2).

Hint

There is also a Decimal column type, which is an alias for Numeric.

Real¶

class piccolo.columns.column_types.Real(default: float | Enum | Callable[[], float] | None = 0.0, **kwargs)¶

Can be used instead of Numeric for storing numbers, when precision isn’t as important. The float type is used for values.

Example

class Concert(Table):
    rating = Real()

# Create
>>> await Concert(rating=7.8).save()

# Query
>>> await Concert.select(Concert.rating)
{'rating': 7.8}

Hint

There is also a Float column type, which is an alias for Real.

Serial¶

class piccolo.columns.column_types.Serial(null: bool = False, primary_key: bool = False, unique: bool = False, index: bool = False, index_method: IndexMethod = IndexMethod.btree, required: bool = False, help_text: str | None = None, choices: Type[Enum] | None = None, db_column_name: str | None = None, secret: bool = False, auto_update: Any = Ellipsis, **kwargs)¶: An alias to an autoincrementing integer column in Postgres.

SmallInt¶

class piccolo.columns.column_types.SmallInt(default: int | Enum | Callable[[], int] | None = 0, **kwargs)¶

In Postgres, this column supports small integers. In SQLite, it’s an alias to an Integer column. Uses the int type for values.

Example

class Band(Table):
    value = SmallInt()

# Create
>>> await Band(popularity=1000).save()

# Query
>>> await Band.select(Band.popularity)
{'popularity': 1000}

UUID¶

Used for storing UUIDs - in Postgres a UUID column type is used, and in SQLite it’s just a Varchar. Uses the uuid.UUID type for values.

Example

import uuid

class Band(Table):
    uuid = UUID()

# Create
>>> await DiscountCode(code=uuid.uuid4()).save()

# Query
>>> await DiscountCode.select(DiscountCode.code)
{'code': UUID('09c4c17d-af68-4ce7-9955-73dcd892e462')}

Text¶

Secret¶

class piccolo.columns.column_types.Secret(*args, **kwargs)¶: This is just an alias to Varchar(secret=True). It’s here for backwards compatibility.

Text¶

class piccolo.columns.column_types.Text(default: str | Enum | None | Callable[[], str] = '', **kwargs)¶

Use when you want to store large strings, and don’t want to limit the string size. Uses the str type for values.

Example

class Band(Table):
    name = Text()

# Create
>>> await Band(name='Pythonistas').save()

# Query
>>> await Band.select(Band.name)
{'name': 'Pythonistas'}

Varchar¶

class piccolo.columns.column_types.Varchar(length: int | None = 255, default: str | Enum | Callable[[], str] | None = '', **kwargs)¶

Used for storing text when you want to enforce character length limits. Uses the str type for values.

Example

class Band(Table):
    name = Varchar(length=100)

# Create
>>> await Band(name='Pythonistas').save()

# Query
>>> await Band.select(Band.name)
{'name': 'Pythonistas'}

Parameters:: length – The maximum number of characters allowed.

Email¶

class piccolo.columns.column_types.Email(length: int | None = 255, default: str | Enum | Callable[[], str] | None = '', **kwargs)¶: Used for storing email addresses. It’s identical to Varchar, except when using create_pydantic_model - we add email validation to the Pydantic model. This means that Piccolo Admin also validates email addresses.

Time¶

Date¶

Used for storing dates. Uses the date type for values.

Example

import datetime

class Concert(Table):
    starts = Date()

# Create
>>> await Concert(
...     starts=datetime.date(year=2020, month=1, day=1)
... ).save()

# Query
>>> await Concert.select(Concert.starts)
{'starts': datetime.date(2020, 1, 1)}

Interval¶

class piccolo.columns.column_types.Interval(default: IntervalCustom | Enum | None | timedelta | Callable[[], timedelta] = IntervalCustom(weeks=0, days=0, hours=0, minutes=0, seconds=0, milliseconds=0, microseconds=0), **kwargs)¶

Used for storing timedeltas. Uses the timedelta type for values.

Example

from datetime import timedelta

class Concert(Table):
    duration = Interval()

# Create
>>> await Concert(
...    duration=timedelta(hours=2)
... ).save()

# Query
>>> await Concert.select(Concert.duration)
{'duration': datetime.timedelta(seconds=7200)}

Time¶

Used for storing times. Uses the time type for values.

Example

import datetime

class Concert(Table):
    starts = Time()

# Create
>>> await Concert(
...    starts=datetime.time(hour=20, minute=0, second=0)
... ).save()

# Query
>>> await Concert.select(Concert.starts)
{'starts': datetime.time(20, 0, 0)}

Timestamp¶

Used for storing datetimes. Uses the datetime type for values.

Example

import datetime

class Concert(Table):
    starts = Timestamp()

# Create
>>> await Concert(
...    starts=datetime.datetime(year=2050, month=1, day=1)
... ).save()

# Query
>>> await Concert.select(Concert.starts)
{'starts': datetime.datetime(2050, 1, 1, 0, 0)}

Timestamptz¶

Used for storing timezone aware datetimes. Uses the datetime type for values. The values are converted to UTC in the database, and are also returned as UTC.

Example

import datetime

class Concert(Table):
    starts = Timestamptz()

# Create
>>> await Concert(
...    starts=datetime.datetime(
...        year=2050, month=1, day=1, tzinfo=datetime.timezone.tz
...    )
... ).save()

# Query
>>> await Concert.select(Concert.starts)
{
    'starts': datetime.datetime(
        2050, 1, 1, 0, 0, tzinfo=datetime.timezone.utc
    )
}

JSON¶

Storing JSON can be useful in certain situations, for example - raw API responses, data from a Javascript app, and for storing data with an unknown or changing schema.

`JSON` / `JSONB`¶

class piccolo.columns.column_types.JSON(default: str | List | Dict | Callable[[], str | List | Dict] | None = '{}', **kwargs)¶

Used for storing JSON strings. The data is stored as text. This can be preferable to JSONB if you just want to store and retrieve JSON without querying it directly. It works with SQLite and Postgres.

Parameters:: default – Either a JSON string can be provided, or a Python dict or list which is then converted to a JSON string.

class piccolo.columns.column_types.JSONB(default: str | List | Dict | Callable[[], str | List | Dict] | None = '{}', **kwargs)¶

Used for storing JSON strings - Postgres / CochroachDB only. The data is stored in a binary format, and can be queried more efficiently. Insertion can be slower (as it needs to be converted to the binary format). The benefits of JSONB generally outweigh the downsides.

Parameters:: default – Either a JSON string can be provided, or a Python dict or list which is then converted to a JSON string.

Serialising¶

Piccolo automatically converts Python values into JSON strings:

studio = RecordingStudio(
    name="Abbey Road",
    facilities={"restaurant": True, "mixing_desk": True}  # Automatically serialised
)
await studio.save()

You can also pass in a JSON string if you prefer:

studio = RecordingStudio(
    name="Abbey Road",
    facilities='{"restaurant": true, "mixing_desk": true}'
)
await studio.save()

Deserialising¶

The contents of a JSON / JSONB column are returned as a string by default:

>>> await RecordingStudio.select(RecordingStudio.facilities)
[{facilities: '{"restaurant": true, "mixing_desk": true}'}]

However, we can ask Piccolo to deserialise the JSON automatically (see load_json):

>>> await RecordingStudio.select(
...     RecordingStudio.facilities
... ).output(
...     load_json=True
... )
[facilities: {"restaurant": True, "mixing_desk": True}}]

With objects queries, we can modify the returned JSON, and then save it:

studio = await RecordingStudio.objects().get(
    RecordingStudio.name == 'Abbey Road'
).output(load_json=True)

studio['facilities']['restaurant'] = False
await studio.save()

Getting elements¶

JSON and JSONB columns have an arrow method (representing the -> operator in Postgres), which is useful for retrieving a child element from the JSON data.

Note

Postgres and CockroachDB only.

`select` queries¶

If we have the following JSON stored in the RecordingStudio.facilities column:

{
    "instruments": {
        "drum_kits": 2,
        "electric_guitars": 10
    },
    "restaurant": true,
    "technicians": [
        {
            "name": "Alice Jones"
        },
        {
            "name": "Bob Williams"
        }
    ]
}

We can retrieve the restaurant value from the JSON object:

>>> await RecordingStudio.select(
...     RecordingStudio.facilities.arrow('restaurant')
...     .as_alias('restaurant')
... ).output(load_json=True)
[{'restaurant': True}, ...]

As a convenience, you can use square brackets, instead of calling arrow explicitly:

>>> await RecordingStudio.select(
...     RecordingStudio.facilities['restaurant']
...     .as_alias('restaurant')
... ).output(load_json=True)
[{'restaurant': True}, ...]

You can drill multiple levels deep by calling arrow multiple times (or alternatively use the from_path method - see below).

Here we fetch the number of drum kits that the recording studio has:

>>> await RecordingStudio.select(
...     RecordingStudio.facilities["instruments"]["drum_kits"]
...     .as_alias("drum_kits")
... ).output(load_json=True)
[{'drum_kits': 2}, ...]

If you have a JSON object which consists of arrays and objects, then you can navigate the array elements by passing in an integer to arrow.

Here we fetch the first technician from the array:

>>> await RecordingStudio.select(
...     RecordingStudio.facilities["technicians"][0]["name"]
...     .as_alias("technician_name")
... ).output(load_json=True)

[{'technician_name': 'Alice Jones'}, ...]

`where` clauses¶

The arrow operator can also be used for filtering in a where clause:

>>> await RecordingStudio.select(RecordingStudio.name).where(
...     RecordingStudio.facilities['mixing_desk'].eq(True)
... )
[{'name': 'Abbey Road'}]

`from_path`¶

This works the same as arrow but is more optimised if you need to return part of a highly nested JSON structure.

>>> await RecordingStudio.select(
...     RecordingStudio.facilities.from_path([
...         "technicians",
...         0,
...         "name"
...     ]).as_alias("technician_name")
... ).output(load_json=True)

[{'technician_name': 'Alice Jones'}, ...]

Handling null¶

When assigning a value of None to a JSON or JSONB column, this is treated as null in the database.

await RecordingStudio(name="ABC Studios", facilities=None).save()

>>> await RecordingStudio.select(
...     RecordingStudio.facilities
... ).where(
...     RecordingStudio.name == "ABC Studios"
... )
[{'facilities': None}]

If instead you want to store JSON null in the database, assign a value of 'null' instead.

await RecordingStudio(name="ABC Studios", facilities='null').save()

>>> await RecordingStudio.select(
...     RecordingStudio.facilities
... ).where(
...     RecordingStudio.name == "ABC Studios"
... )
[{'facilities': 'null'}]

Array¶

Arrays of data can be stored, which can be useful when you want to store lots of values without using foreign keys.

class piccolo.columns.column_types.Array(base_column: Column, default: List | Enum | Callable[[], List] | None = list, **kwargs)¶

Used for storing lists of data.

Example

class Ticket(Table):
    seat_numbers = Array(base_column=Integer())

# Create
>>> await Ticket(seat_numbers=[34, 35, 36]).save()

# Query
>>> await Ticket.select(Ticket.seat_numbers)
{'seat_numbers': [34, 35, 36]}

Accessing individual elements¶

Array.__getitem__(value: int) → Array¶

Allows queries which retrieve an item from the array. The index starts with 0 for the first value. If you were to write the SQL by hand, the first index would be 1 instead (see Postgres array docs).

However, we keep the first index as 0 to fit better with Python.

For example:

>>> await Ticket.select(Ticket.seat_numbers[0]).first()
{'seat_numbers': 325}

any¶

Array.any(value: Any) → Where¶

Check if any of the items in the array match the given value.

>>> await Ticket.select().where(Ticket.seat_numbers.any(510))

not_any¶

Array.not_any(value: Any) → Where¶

Check if the given value isn’t in the array.

>>> await Ticket.select().where(Ticket.seat_numbers.not_any(510))

all¶

Array.all(value: Any) → Where¶

Check if all of the items in the array match the given value.

>>> await Ticket.select().where(Ticket.seat_numbers.all(510))

cat¶

Array.cat(value: Any | List[Any]) → QueryString¶

Used in an update query to append items to an array.

>>> await Ticket.update({
...     Ticket.seat_numbers: Ticket.seat_numbers.cat([1000])
... }).where(Ticket.id == 1)

You can also use the + symbol if you prefer:

>>> await Ticket.update({
...     Ticket.seat_numbers: Ticket.seat_numbers + [1000]
... }).where(Ticket.id == 1)

Column Types¶

Column¶

Bytea¶

Boolean¶

ForeignKey¶

Number¶

BigInt¶

BigSerial¶

Double Precision¶

Integer¶

Numeric¶

Real¶

Serial¶

SmallInt¶

UUID¶

Text¶

Secret¶

Text¶

Varchar¶

Email¶

Time¶

Date¶

Interval¶

Time¶

Timestamp¶

Timestamptz¶

JSON¶

JSON / JSONB¶

Serialising¶

Deserialising¶

Getting elements¶

select queries¶

where clauses¶

from_path¶

Handling null¶

Array¶

Accessing individual elements¶

any¶

not_any¶

all¶

cat¶

`JSON` / `JSONB`¶

`select` queries¶

`where` clauses¶

`from_path`¶