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 calledvalue
, 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
, orMyTable.save
on an existing row). A common use case is having amodified_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¶
- class piccolo.columns.column_types.Bytea(default: bytes | bytearray | Enum | Callable[[], bytes] | Callable[[], bytearray] | None = b'', **kwargs)¶
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 thebool
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 theTable
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¶
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 therange
, 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. Thefloat
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¶
- class piccolo.columns.column_types.UUID(default: UUID4 | UUID | str | Enum | None | Callable[[], UUID] = UUID4(), **kwargs)¶
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 usingcreate_pydantic_model
- we add email validation to the Pydantic model. This means that Piccolo Admin also validates email addresses.
Time¶
Date¶
- class piccolo.columns.column_types.Date(default: DateOffset | DateCustom | DateNow | Enum | None | date | Callable[[], date] = DateNow(), **kwargs)¶
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¶
- class piccolo.columns.column_types.Time(default: TimeCustom | TimeNow | TimeOffset | Enum | None | time | Callable[[], time] = TimeNow(), **kwargs)¶
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¶
- class piccolo.columns.column_types.Timestamp(default: TimestampCustom | TimestampNow | TimestampOffset | Enum | None | datetime | DatetimeDefault | Callable[[], datetime] = TimestampNow(), **kwargs)¶
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¶
- class piccolo.columns.column_types.Timestamptz(default: TimestamptzCustom | TimestamptzNow | TimestamptzOffset | Enum | None | datetime | Callable[[], datetime] = TimestamptzNow(), **kwargs)¶
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
orlist
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
orlist
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¶
not_any¶
all¶
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)