Column Types

Hint

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

Column

class piccolo.columns.column_types.Column(null: bool = False, primary_key: bool = False, unique: bool = False, index: bool = False, index_method: piccolo.columns.indexes.IndexMethod = IndexMethod.btree, required: bool = False, help_text: Optional[str] = None, choices: Optional[Type[enum.Enum]] = None, db_column_name: Optional[str] = None, **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 contraint 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")
    
    >>> MyTable.select(MyTable.class_).run_sync()
    [{'id': 1, 'class': 'test'}]
    

    This is an advanced feature which you should only need in niche situations.


Bytea

class piccolo.columns.column_types.Bytea(default: Union[bytes, bytearray, enum.Enum, Callable[[], bytes], Callable[[], bytearray], None] = b'', **kwargs)

Used for storing bytes.

Example

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

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

# Query
>>> Token.select(Token.token).run_sync()
{'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: Union[bool, enum.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
>>> Band(has_drummer=True).save().run_sync()

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

ForeignKey

class piccolo.columns.column_types.ForeignKey(references: t.Union[t.Type[Table], LazyTableReference, str], default: t.Any = None, null: bool = True, on_delete: OnDelete = OnDelete.cascade, on_update: OnUpdate = OnUpdate.cascade, **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
>>> Band(manager=1).save().run_sync()

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

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

Joins

You also use it to perform joins:

>>> await Band.select(Band.name, Band.manager.name).first().run()
{'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().run()
{'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
).run()

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

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

To change the manager:

band.manager = some_manager_id
await band.save().run()
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 OnDelete.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 OnDelete
    
    class Band(Table):
        name = ForeignKey(
            references=Manager,
            on_update=OnUpdate.cascade
        )
    

Number

BigInt

class piccolo.columns.column_types.BigInt(default: Union[int, enum.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
>>> Band(popularity=1000000).save().run_sync()

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

BigSerial

class piccolo.columns.column_types.BigSerial(null: bool = False, primary_key: bool = False, unique: bool = False, index: bool = False, index_method: piccolo.columns.indexes.IndexMethod = IndexMethod.btree, required: bool = False, help_text: Optional[str] = None, choices: Optional[Type[enum.Enum]] = None, db_column_name: Optional[str] = None, **kwargs)

An alias to a large autoincrementing integer column in Postgres.

Double Precision

class piccolo.columns.column_types.DoublePrecision(default: Union[float, enum.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: Union[int, enum.Enum, Callable[[], int], None] = 0, **kwargs)

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

Example

class Band(Table):
    popularity = Integer()

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

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

Numeric

class piccolo.columns.column_types.Numeric(digits: Optional[Tuple[int, int]] = None, default: Union[decimal.Decimal, enum.Enum, Callable[[], decimal.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
>>> Ticket(price=Decimal('50.0')).save().run_sync()

# Query
>>> Ticket.select(Ticket.price).run_sync()
{'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: Union[float, enum.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
>>> Concert(rating=7.8).save().run_sync()

# Query
>>> Concert.select(Concert.rating).run_sync()
{'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: piccolo.columns.indexes.IndexMethod = IndexMethod.btree, required: bool = False, help_text: Optional[str] = None, choices: Optional[Type[enum.Enum]] = None, db_column_name: Optional[str] = None, **kwargs)

An alias to an autoincrementing integer column in Postgres.

SmallInt

class piccolo.columns.column_types.SmallInt(default: Union[int, enum.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
>>> Band(popularity=1000).save().run_sync()

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

UUID

class piccolo.columns.column_types.UUID(default: Union[piccolo.columns.defaults.uuid.UUID4, uuid.UUID, str, enum.Enum, None] = 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
>>> DiscountCode(code=uuid.uuid4()).save().run_sync()

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

Text

Secret

class piccolo.columns.column_types.Secret(length: int = 255, default: Union[str, enum.Enum, Callable[[], str], None] = '', **kwargs)

The database treats it the same as a Varchar, but Piccolo may treat it differently internally - for example, allowing a user to automatically omit any secret fields when doing a select query, to help prevent inadvertant leakage. A common use for a Secret field is a password.

Uses the str type for values.

Example

class Door(Table):
    code = Secret(length=100)

# Create
>>> Door(code='123abc').save().run_sync()

# Query
>>> Door.select(Door.code).run_sync()
{'code': '123abc'}

Text

class piccolo.columns.column_types.Text(default: Union[str, enum.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
>>> Band(name='Pythonistas').save().run_sync()

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

Varchar

class piccolo.columns.column_types.Varchar(length: int = 255, default: Union[str, enum.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
>>> Band(name='Pythonistas').save().run_sync()

# Query
>>> Band.select(Band.name).run_sync()
{'name': 'Pythonistas'}
Parameters:length – The maximum number of characters allowed.

Time

Date

class piccolo.columns.column_types.Date(default: Union[piccolo.columns.defaults.date.DateOffset, piccolo.columns.defaults.date.DateCustom, piccolo.columns.defaults.date.DateNow, enum.Enum, None, datetime.date] = DateNow(), **kwargs)

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

Example

import datetime

class Concert(Table):
    starts = Date()

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

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

Interval

class piccolo.columns.column_types.Interval(default: Union[piccolo.columns.defaults.interval.IntervalCustom, enum.Enum, None, datetime.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
>>> Concert(
>>>    duration=timedelta(hours=2)
>>> ).save().run_sync()

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

Time

class piccolo.columns.column_types.Time(default: Union[piccolo.columns.defaults.time.TimeCustom, piccolo.columns.defaults.time.TimeNow, piccolo.columns.defaults.time.TimeOffset, enum.Enum, None, datetime.time] = TimeNow(), **kwargs)

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

Example

import datetime

class Concert(Table):
    starts = Time()

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

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

Timestamp

class piccolo.columns.column_types.Timestamp(default: Union[piccolo.columns.defaults.timestamp.TimestampCustom, piccolo.columns.defaults.timestamp.TimestampNow, piccolo.columns.defaults.timestamp.TimestampOffset, enum.Enum, None, datetime.datetime, piccolo.columns.defaults.timestamp.DatetimeDefault] = TimestampNow(), **kwargs)

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

Example

import datetime

class Concert(Table):
    starts = Timestamp()

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

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

Timestamptz

class piccolo.columns.column_types.Timestamptz(default: Union[piccolo.columns.defaults.timestamptz.TimestamptzCustom, piccolo.columns.defaults.timestamptz.TimestamptzNow, piccolo.columns.defaults.timestamptz.TimestamptzOffset, enum.Enum, None, datetime.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
>>> Concert(
>>>    starts=datetime.datetime(
>>>        year=2050, month=1, day=1, tzinfo=datetime.timezone.tz
>>>    )
>>> ).save().run_sync()

# Query
>>> Concert.select(Concert.starts).run_sync()
{
    '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

class piccolo.columns.column_types.JSON(default: Union[str, List[T], Dict[KT, VT], Callable[[], Union[str, List[T], Dict[KT, VT]]], 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.

JSONB

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

Used for storing JSON strings - Postgres only. The data is stored in a binary format, and can be queried. 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.

arrow

JSONB columns have an arrow function, which is useful for retrieving a subset of the JSON data, and for filtering in a where clause.

# Example schema:
class Booking(Table):
    data = JSONB()

Booking.create_table().run_sync()

# Example data:
Booking.insert(
    Booking(data='{"name": "Alison"}'),
    Booking(data='{"name": "Bob"}')
).run_sync()

# Example queries
>>> Booking.select(
>>>     Booking.id, Booking.data.arrow('name').as_alias('name')
>>> ).run_sync()
[{'id': 1, 'name': '"Alison"'}, {'id': 2, 'name': '"Bob"'}]

>>> Booking.select(Booking.id).where(
>>>     Booking.data.arrow('name') == '"Alison"'
>>> ).run_sync()
[{'id': 1}]

Array

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

class piccolo.columns.column_types.Array(base_column: piccolo.columns.base.Column, default: Union[List[T], enum.Enum, Callable[[], List[T]], None] = <class 'list'>, **kwargs)

Used for storing lists of data.

Example

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

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

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

Accessing individual elements

Array.__getitem__(value: int) → piccolo.columns.column_types.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:

https://www.postgresql.org/docs/current/arrays.html

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

For example:

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

any

Array.any(value: Any) → piccolo.columns.combination.Where

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

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

all

Array.all(value: Any) → piccolo.columns.combination.Where

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

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