piccolo

Traditionally, when instantiating a Table, you passed in column values using kwargs:

>>> await Manager(name='Guido').save()

You can now pass in a dictionary instead, which makes it easier for static typing analysis tools like Mypy to detect typos.

>>> await Manager({Manager.name: 'Guido'}).save()

See PR 565 for more info.

Added the returning clause to insert and update queries.

This can be used to retrieve data from the inserted / modified rows.

Here's an example, where we update the unpopular bands, and retrieve their names, in a single query:

>>> await Band.update({
...     Band.popularity: Band.popularity + 5
... }).where(
...     Band.popularity < 10
... ).returning(
...     Band.name
... )
[{'name': 'Bad sound band'}, {'name': 'Tone deaf band'}]

Fixed a bug with Combination.__str__, which meant that when printing out a query for debugging purposes it was wasn't showing correctly (courtesy @destos).

Fixed a bug with Piccolo Admin and _get_related_readable, which is used to show a human friendly identifier for a row, rather than just the ID.

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

There was a bug when doing joins with a JSONB column with as_alias.

class User(Table, tablename="my_user"):
    name = Varchar(length=120)
    config = JSONB(default={})


class Subscriber(Table, tablename="subscriber"):
    name = Varchar(length=120)
    user = ForeignKey(references=User)


async def main():
    # This was failing:
    await Subscriber.select(
        Subscriber.name,
        Subscriber.user.config.as_alias("config")
    )

Thanks to @Anton-Karpenko for reporting this issue.

Even though this is a bug fix, the minor version number has been bumped because the fix resulted in some refactoring of Piccolo's internals, so is a fairly big change.

Added a custom __repr__ method to Table's metaclass. It's needed to improve the appearance of our Sphinx docs. See issue 549 for more details.

Added the callback clause to select and objects queries (courtesy @backwardspy). For example:

>>> await Band.select().callback(my_callback)

The callback can be a normal function or async function, which is called when the query is successful. The callback can be used to modify the query's output.

It allows for some interesting and powerful code. Here's a very simple example where we modify the query's output:

>>> def get_uppercase_names() -> Select:
...     def make_uppercase(response):
...         return [{'name': i['name'].upper()} for i in response]
...
...    return Band.select(Band.name).callback(make_uppercase)

>>> await get_uppercase_names().where(Band.manager.name == 'Guido')
[{'name': 'PYTHONISTAS'}]

Here's another example, where we perform validation on the query's output:

>>> def get_concerts() -> Select:
...     def check_length(response):
...         if len(response) == 0:
...             raise ValueError('No concerts!')
...         return response
...
...     return Concert.select().callback(check_length)

>>> await get_concerts().where(Concert.band_1.name == 'Terrible Band')
ValueError: No concerts!

At the moment, callbacks are just triggered when a query is successful, but in the future other callbacks will be added, to hook into more of Piccolo's internals.

Added the refresh method. If you have an object which has gotten stale, and want to refresh it, so it has the latest data from the database, you can now do this:

# If we have an instance:
band = await Band.objects().first()

# And it has gotten stale, we can refresh it:
await band.refresh()

Thanks to @trondhindenes for suggesting this feature.

Fixed a bug with atomic when run async with a connection pool.

For example:

atomic = Band._meta.db.atomic()
atomic.add(query_1, query_1)
# This was failing:
await atomic.run()

Thanks to @Anton-Karpenko for reporting this issue.

create_db_tables / drop_db_tables

Added create_db_tables and create_db_tables_sync to replace create_tables. The problem was create_tables was sync only, and was inconsistent with the rest of Piccolo's API, which is async first. create_tables will continue to work for now, but is deprecated, and will be removed in version 1.0.

Likewise, drop_db_tables and drop_db_tables_sync have replaced drop_tables.

When calling create_tables / drop_tables within other async libraries (such as ward) it was sometimes unreliable - the best solution was just to make async versions of these functions. Thanks to @backwardspy for reporting this issue.

BaseUser password validation

We centralised the password validation logic in BaseUser into a method called _validate_password. This is needed by Piccolo API, but also makes it easier for users to override this logic if subclassing BaseUser.

More run_sync refinements

run_sync, which is the main utility function which Piccolo uses to run async code, has been further simplified for Python > v3.10 compatibility.

Changed how piccolo.utils.sync.run_sync works, to prevent a deprecation warning on Python 3.10. Thanks to @Drapersniper for reporting this issue.

Lots of documentation improvements - particularly around testing, and Docker deployment.

piccolo schema generate now outputs a warning when it can't detect the ON DELETE and ON UPDATE for a ForeignKey, rather than raising an exception. Thanks to @theelderbeever for reporting this issue.

run_sync doesn't use the connection pool by default anymore. It was causing issues when an app contained sync and async code. Thanks to @WintonLi for reporting this issue.

Added a tutorial to the docs for using Piccolo with an existing project and database. Thanks to @virajkanwade for reporting this issue.

If you had a table containing an array of BigInt, then migrations could fail:

from piccolo.table import Table
from piccolo.columns.column_types import Array, BigInt

class MyTable(Table):
    my_column = Array(base_column=BigInt())

It's because the BigInt base column needs access to the parent table to know if it's targeting Postgres or SQLite. See PR 501.

Thanks to @cheesycod for reporting this issue.

If a user created a custom Column subclass, then migrations would fail. For example:

class CustomColumn(Varchar):
    def __init__(self, custom_arg: str = '', *args, **kwargs):
        self.custom_arg = custom_arg
        super().__init__(*args, **kwargs)

    @property
    def column_type(self):
        return 'VARCHAR'

See PR 497. Thanks to @WintonLi for reporting this issue.

When using pip install piccolo[all] on Windows it would fail because uvloop isn't supported. Thanks to @jack1142 for reporting this issue.

We've had the ability to bulk modify rows for a while. Here we append '!!!' to each band's name:

>>> await Band.update({Band.name: Band.name + '!!!'}, force=True)

It only worked for some columns - Varchar, Text, Integer etc.

We now allow Date, Timestamp, Timestamptz and Interval columns to be bulk modified using a timedelta. Here we modify each concert's start date, so it's one day later:

>>> await Concert.update(
...     {Concert.starts: Concert.starts + timedelta(days=1)},
...     force=True
... )

Thanks to @theelderbeever for suggesting this feature.

You can now specify extra nodes for a database. For example, if you have a read replica.

  DB = PostgresEngine(
      config={'database': 'main_db', 'host': 'prod.my_db.com'},
      extra_nodes={
          'read_replica_1': PostgresEngine(
              config={
                  'database': 'main_db',
                  'host': 'read_replica_1.my_db.com'
              }
          )
      }
  )

And can then run queries on these other nodes:

  >>> await MyTable.select().run(node="read_replica_1")

See PR 481. Thanks to @dashsatish for suggesting this feature.

Also, the targ library has been updated so it tells users about the --trace argument which can be used to get a full traceback when a CLI command fails.

Fixed typos with drop_constraints. Courtesy @smythp.

Lots of documentation improvements, such as fixing Sphinx's autodoc for the Array column.

AppConfig now accepts a pathlib.Path instance. For example:

# piccolo_app.py

import pathlib

APP_CONFIG = AppConfig(
    app_name="blog",
    migrations_folder_path=pathlib.Path(__file__) /  "piccolo_migrations"
)

Thanks to @theelderbeever for recommending this feature.

Fixed a bug with ModelBuilder and nullable columns (see PR 462). Thanks to @fiolet069 for reporting this issue.

The ModelBuilder class, which is used to generate mock data in tests, now supports Array columns. Courtesy @backwardspy.

Lots of internal code optimisations and clean up. Courtesy @yezz123.

Added docs for troubleshooting common MyPy errors.

Also thanks to @adriangb for helping us with our dependency issues.