piccolo

piccolo fixtures load is now more intelligent about how it loads data, to avoid foreign key constraint errors.

Array columns now support choices.

class Ticket(Table):
    class Extras(str, enum.Enum):
        drink = "drink"
        snack = "snack"
        program = "program"

    extras = Array(Varchar(), choices=Extras)

We can then use the Enum in our queries:

>>> await Ticket.insert(
...     Ticket(extras=[Extras.drink, Extras.snack]),
...     Ticket(extras=[Extras.program]),
... )

This will also be supported in Piccolo Admin in the next release.

You can now use the returning clause with delete queries.

For example:

>>> await Band.delete().where(Band.popularity < 100).returning(Band.name)
[{'name': 'Terrible Band'}, {'name': 'Awful Band'}]

This also means you can count the number of deleted rows:

>>> len(await Band.delete().where(Band.popularity < 100).returning(Band.id))
2

Thanks to @waldner for adding this feature.

SQLite TransactionType

You can now specify the transaction type for SQLite.

This is useful when using SQLite in production, as it's possible to get database locked errors if you're running lots of transactions concurrently, and don't use the correct transaction type.

In this example we use an IMMEDIATE transaction:

from piccolo.engine.sqlite import TransactionType

async with Band._meta.db.transaction(
    transaction_type=TransactionType.immediate
):
    band = await Band.objects().get_or_create(Band.name == 'Pythonistas')
    ...

We've added a new tutorial which explains this in more detail, as well as other tips for using asyncio and SQLite together effectively.

Thanks to @powellnorma and @sinisaos for their help with this.

Other changes

• Fixed a bug with camelCase column names (we recommend using snake_case, but sometimes it's unavoidable when using Piccolo with an existing schema). Thanks to @sinisaos for this.
• Fixed a typo in the docs with raw queries - thanks to @StitiFatah for this.

Some big improvements to order_by clauses.

It's now possible to combine ascending and descending:

await Band.select(
    Band.name,
    Band.popularity
).order_by(
    Band.name
).order_by(
    Band.popularity,
    ascending=False
)

You can also order by anything you want using OrderByRaw:

from piccolo.query import OrderByRaw

await Band.select(
    Band.name
).order_by(
    OrderByRaw('random()')
)

Added the auto_update argument to Column. Its main use case is columns like modified_on where we want the value to be updated automatically each time the row is saved.

class Band(Table):
    name = Varchar()
    popularity = Integer()
    modified_on = Timestamp(
        null=True,
        default=None,
        auto_update=datetime.datetime.now
    )
  
# The `modified_on` column will automatically be updated to the current
# timestamp:
>>> await Band.update({
...     Band.popularity: Band.popularity + 100
... }).where(
...     Band.name == 'Pythonistas'
... )

It works with MyTable.update and also when using the save method on an existing row.

Made improvements to the Piccolo playground.

• Syntax highlighting is now enabled.
• The example queries are now async (iPython supports top level await, so this works fine).
• You can optionally use your own iPython configuration piccolo playground run --ipython_profile (for example if you want a specific colour scheme, rather than the one we use by default).

Thanks to @haffi96 for this. See PR 656.

Fixed a bug with MyTable.objects().create() and columns which are not nullable. Thanks to @metakot for reporting this issue.

We used to use logging.getLogger(__file__), but as @Drapersniper pointed out, the Python docs recommend logging.getLogger(__name__), so it has been changed.

• Fixed a bug with nullable JSON / JSONB columns and create_pydantic_model - thanks to @eneacosta for this fix.
• Made the Time column type importable from piccolo.columns.
• Python 3.11 is now supported.
• Postgres 9.6 is no longer officially supported, as it's end of life, but Piccolo should continue to work with it just fine for now.
• Improved docs for transactions, added docs for the as_of clause in CockroachDB (thanks to @gnat for this), and added docs for add_raw_backwards.

Added initial support for Cockroachdb (thanks to @gnat for this massive contribution).

Fixed Pylance warnings (thanks to @MiguelGuthridge for this).

Added support for Starlite, another great ASGI framework, like BlackSheep, Starlette, and FastAPI.

If you use piccolo asgi new you'll see it as an option when selecting a router.

Thanks to @sinisaos for adding this, and @peterschutt for helping debug ASGI mounting.

Fixed an edge case, where a migration could fail if:

• 5 or more tables were being created at once.
• They all contained foreign keys to each other, as shown below.

class TableA(Table):
    pass

class TableB(Table):
    fk = ForeignKey(TableA)

class TableC(Table):
    fk = ForeignKey(TableB)

class TableD(Table):
    fk = ForeignKey(TableC)

class TableE(Table):
    fk = ForeignKey(TableD)

Thanks to @sumitsharansatsangi for reporting this issue.

Made it easier to access the Email columns on a table.

>>> MyTable._meta.email_columns
[MyTable.email_column_1, MyTable.email_column_2]

This was added for Piccolo Admin.

Fixed a bug with migrations - when using db_column_name it wasn't being used in some alter statements. Thanks to @theelderbeever for reporting this issue.

class Concert(Table):
    # We use `db_column_name` when the column name is problematic - e.g. if
    # it clashes with a Python keyword.
    in_ = Varchar(db_column_name='in')

When using get_or_create with prefetch the behaviour was inconsistent - it worked as expected when the row already existed, but prefetch wasn't working if the row was being created. This now works as expected:

>>> band = Band.objects(Band.manager).get_or_create(
...     (Band.name == "New Band 2") & (Band.manager == 1)
... )

>>> band.manager
<Manager: 1>

>>> band.manager.name
"Mr Manager"

Thanks to @backwardspy for reporting this issue.

Added the Email column type. It's basically identical to Varchar, except that when we use create_pydantic_model we add email validation to the generated Pydantic model.

from piccolo.columns.column_types import Email
from piccolo.table import Table
from piccolo.utils.pydantic import create_pydantic_model


class MyTable(Table):
    email = Email()


model = create_pydantic_model(MyTable)
model(email="not a valid email")  # ValidationError!

Thanks to @sinisaos for implementing this feature.

Fixed a bug with migrations - when run backwards, raw was being called instead of raw_backwards. Thanks to @translunar for the fix.

You can now append items to an array in an update query:

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

Currently Postgres only. Thanks to @sumitsharansatsangi for suggesting this feature.

You can now preview the DDL statements which will be run by Piccolo migrations.

piccolo migrations forwards my_app --preview

Thanks to @AliSayyah for adding this feature.

We added support for Postgres read-slaves a few releases ago, but the batch clause didn't support it until now. Thanks to @guruvignesh01 for reporting this issue, and @sinisaos for help implementing it.

# Returns 100 rows at a time from read_replica_db
async with await Manager.select().batch(
    batch_size=100,
    node="read_replica_db",
) as batch:
    async for _batch in batch:
        print(_batch)