1.28.0

Playground improvements

• Added an Array column to the playground (Album.awards), for easier experimentation with array columns.
• CoachroachDB is now supported in the playground (thanks to @sinisaos for this).

piccolo playground run --engine=cockroach

Functions

Added lots of useful array functions (thanks to @sinisaos for this).

Here's an example, where we can easily fix a typo in an array using replace:

>>> await Album.update({
...     Album.awards: Album.awards.replace('Grammy Award 2021', 'Grammy Award 2022')
... }, force=True)

The documentation for functions has also been improved (e.g. how to create a custom function).

The Cast function is now more flexible.

Array concantenation

Values can be prepended:

>>> await Album.update({
...     Album.awards: ['Grammy Award 2020'] + Album.awards
... }, force=True)

And multiple arrays can be concatenated in one go:

>>> await Album.update({
...     Album.awards: ['Grammy Award 2020'] + Album.awards + ['Grammy Award 2025']
... }, force=True)

is_in and not_in sub queries

You can now use sub queries within is_in and not_in Thanks to @sinisaos for this.

>>> await Band.select().where(
...     Band.id.is_in(
...         Concert.select(Concert.band_1).where(
...             Concert.starts >= datetime.datetime(year=2025, month=1, day=1)
...         )
...     )
... )

Other improvements

• Auto convert a default value of 0 to 0.0 in Float columns.
• Modernised the type hints throughout the codebase (e.g. using list instead of typing.List). Thanks to @sinisaos for this.
• Fixed a bug with auto migrations, where the Array base column class wasn't being imported.
• Improved M2M query performance by using sub selects (thanks to @sinisaos for this).

1.27.1

Improved the type annotations in ColumnKwargs - made some optional. Thanks to @stronk7 and @sinisaos for their help with this.

1.27.0

Improved auto completion / typo detection for column arguments.

For example:

  class Band(Table):
      name = Varchar(nul=True)  # linters will now warn that nul is a typo (should be null)

Thanks to @sinisaos for this.

1.26.1

Updated the BlackSheep ASGI template - thanks to @sinisaos for this.

Fixed a bug with auto migrations when a ForeignKey specifies target_column - multiple primary key columns were added to the migration file. Thanks to @waldner for reporting this issue.

Added a tutorial for moving tables between Piccolo apps - thanks to @sarvesh4396 for this.

1.26.0

Improved auto migrations - ON DELETE and ON UPDATE can be modified on ForeignKey columns. Thanks to @sinisaos for this.

1.25.0

Improvements to Piccolo app creation. When running the following:

piccolo app new my_app

It now validates that the app name (my_app in this case) is valid as a Python package.

Also, there is now a --register flag, which automatically adds the new app to the APP_REGISTRY in piccolo_conf.py.

piccolo app new my_app --register

Other changes:

• table_finder can now use relative modules.
• Updated the Esmerald ASGI template (thanks to @sinisaos for this).
• When using the remove method to delete a row from the database (e.g. await some_band.remove()), then some_band._exists_in_db is now set to False. Thanks to @sinisaos for this fix.

1.24.2

Fixed a bug with delete queries which have joins in the where clause. For example:

>>> await Band.delete().where(Band.manager.name == 'Guido')

Thanks to @HakierGrzonzo for reporting the issue, and @sinisaos for the fix.

1.24.1

Fixed a bug with default values in Timestamp and Timestamptz columns. Thanks to @splch for this.

1.24.0

• Fixed a bug with get_or_create when a table has a column with both null=False and default=None - thanks to @bymoye for reporting this issue.
• If a PostgresEngine uses the dsn argument for asyncpg, this is now used by piccolo sql_shell run. Thanks to @abhishek-compro for suggesting this.
• Fixed the type annotation for the length argument of Varchar - it is allowed to be None. Thanks to @Compro-Prasad for this.

1.23.0

• Added Quart, Sanic, and Falcon as supported ASGI frameworks (thanks to @sinisaos for this).
• Fixed a bug with very large integers in SQLite.
• Fixed type annotation for Timestamptz default values (thanks to @Skelmis for this).