piccolo

ModelBuilder now works with LazyTableReference (which is used when we have circular references caused by a ForeignKey).

With this table:

class Band(Table):
    manager = ForeignKey(
        LazyTableReference(
            'Manager',
            module_path='some.other.folder.tables'
        )
    )

We can now create a dynamic test fixture:

my_model = await ModelBuilder.build(Band)

If you have lots of Piccolo apps, you can now create auto migrations for them all in one go:

piccolo migrations new all --auto

Thanks to @hoosnick for suggesting this new feature.

The documentation for running migrations has also been improved, as well as improvements to the sorting of migrations based on their dependencies.

Support for Python 3.7 was dropped in this release as it's now end of life.

Version pinning Pydantic to v1, as v2 has breaking changes.

We will add support for Pydantic v2 in a future release.

Thanks to @sinisaos for helping with this.

Fixture formatting

When creating a fixture:

piccolo fixtures dump

The JSON output is now nicely formatted, which is useful because we can pipe it straight to a file, and commit it to Git without having to manually run a formatter on it.

piccolo fixtures dump > my_fixture.json

Thanks to @sinisaos for this.

Protected table names

We used to raise a ValueError if a table was called user.

class User(Table):  # ValueError!
    ...

It's because user is already used by Postgres (e.g. try SELECT user or SELECT * FROM user).

We now emit a warning instead for these reasons:

• Piccolo wraps table names in quotes to avoid clashes with reserved keywords.
• Sometimes you're stuck with a table name from a pre-existing schema, and can't easily rename it.

Re-export WhereRaw

If you want to write raw SQL in your where queries you use WhereRaw:

>>> Band.select().where(WhereRaw('TRIM(name) = {}', 'Pythonistas'))

You can now import it from piccolo.query to be consistent with SelectRaw and OrderByRaw.

from piccolo.query import WhereRaw

Fixture upserting

Fixtures can now be upserted. For example:

piccolo fixtures load my_fixture.json --on_conflict='DO UPDATE'

The options are:

• DO NOTHING, meaning any rows with a matching primary key will be left alone.
• DO UPDATE, meaning any rows with a matching primary key will be updated.

This is really useful, as you can now edit fixtures and load them multiple times without getting foreign key constraint errors.

Schema fixes

We recently added support for schemas, for example:

class Band(Table, schema='music'):
    ...

This release contains:

• A fix for migrations when changing a table's schema back to 'public' (thanks to @sinisaos for discovering this).
• A fix for M2M queries, when the tables are in a schema other than 'public' (thanks to @quinnalfaro for reporting this).

Added distinct method to count queries

We recently added support for COUNT DISTINCT queries. The syntax is:

await Concert.count(distinct=[Concert.start_date])

The following alternative syntax now also works (just to be consistent with other queries like select):

await Concert.count().distinct([Concert.start_date])

count queries can now return the number of distinct rows. For example, if we have this table:

class Concert(Table):
    band = Varchar()
    start_date = Date()

With this data:

We can easily get the number of unique concert dates:

>>> await Concert.count(distinct=[Concert.start_date])
 2

We could have just done this instead:

len(await Concert.select(Concert.start_date).distinct())

But it's far less efficient when you have lots of rows, because all of the distinct rows need to be returned from the database.

Also, the docs for the count query, aggregate functions, and group_by clause were significantly improved.

Many thanks to @lqmanh and @sinisaos for their help with this.

If Piccolo detects a renamed table in an auto migration, it sometimes has to ask the user for confirmation. When lots of tables have been renamed, Piccolo is now more intelligent about when to ask for confirmation.

For example, if we had TableA, TableB, TableC and now we have TableA_1, TableB_1, TableC_1, then we now ask up to 6 questions instead of 9:

Thanks to @sumitsharansatsangi for suggesting this change, and @sinisaos for reviewing.

Also, fixed the type annotations for MigrationManager.add_table.

Fixed a bug with serialising table classes in migrations.

Added support for schemas in Postgres and CockroachDB. This is a way of organising the tables within a database.

For example:

class Band(Table, schema="music"):
    ...

When creating the table, the schema will be created automatically if it doesn't already exist.

>>> await Band.create_table()

It also works with migrations. If we change the schema value for the table, Piccolo will detect this, and create a migration for moving it to the new schema.

class Band(Table, schema="music_2"):
    ...

# Piccolo will detect that the table needs to be moved to a new schema.
>>> piccolo migrations new my_app --auto

Fixing a bug with ModelBuilder and Decimal / Numeric columns.

Added the on_conflict clause for insert queries. This enables upserts.

For example, here we insert some bands, and if they already exist then do nothing:

await Band.insert(
    Band(name='Pythonistas'),
    Band(name='Rustaceans'),
    Band(name='C-Sharps'),
).on_conflict(action='DO NOTHING')

Here we insert some albums, and if they already exist then we update the price:

await Album.insert(
    Album(title='OK Computer', price=10.49),
    Album(title='Kid A', price=9.99),
    Album(title='The Bends', price=9.49),
).on_conflict(
    action='DO UPDATE',
    target=Album.title,
    values=[Album.price]
)

Thanks to @sinisaos for helping with this.

ASGI frameworks

The ASGI frameworks in piccolo asgi new have been updated. starlite has been renamed to litestar. Thanks to @sinisaos for this.

ModelBuilder

Generic types are now used in ModelBuilder.

# mypy knows this is a `Band` instance:
band = await ModelBuilder.build(Band)

DISTINCT ON

Added support for DISTINCT ON queries. For example, here we fetch the most recent album for each band:

>>> await Album.select().distinct(
...     on=[Album.band]
... ).order_by(
...     Album.band
... ).order_by(
...     Album.release_date,
...     ascending=False
... )

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

Full changelog

• Typo: "OnDelete.cascade" -> "OnUpdate.cascade" by @BurkhalterY in https://github.com/piccolo-orm/piccolo/pull/787
• Typo: "import OnDelete" -> "import OnUpdate" by @BurkhalterY in https://github.com/piccolo-orm/piccolo/pull/788
• clarify Piccolo's support for SQLite by @dantownsend in https://github.com/piccolo-orm/piccolo/pull/803
• ignore docs when deciding whether to run tests by @dantownsend in https://github.com/piccolo-orm/piccolo/pull/805
• update ASGI frameworks by @sinisaos in https://github.com/piccolo-orm/piccolo/pull/802
• Streamline docs by @dantownsend in https://github.com/piccolo-orm/piccolo/pull/806
• use generic types for ModelBuilder by @dantownsend in https://github.com/piccolo-orm/piccolo/pull/811
• initial implementation for DISTINCT ON by @dantownsend in https://github.com/piccolo-orm/piccolo/pull/813

New Contributors

• @BurkhalterY made their first contribution in https://github.com/piccolo-orm/piccolo/pull/787

Full Changelog: https://github.com/piccolo-orm/piccolo/compare/0.109.0...0.110.0

Joins are now possible without foreign keys using join_on.

For example:

class Manager(Table):
    name = Varchar(unique=True)
    email = Varchar()

class Band(Table):
    name = Varchar()
    manager_name = Varchar()

>>> await Band.select(
...     Band.name,
...     Band.manager_name.join_on(Manager.name).email
... )

Added support for savepoints within transactions.

async with DB.transaction() as transaction:
    await Manager.objects().create(name="Great manager")
    savepoint = await transaction.savepoint()
    await Manager.objects().create(name="Great manager")
    await savepoint.rollback_to()
    # Only the first manager will be inserted.

The behaviour of nested context managers has also been changed slightly.

async with DB.transaction():
    async with DB.transaction():
        # This used to raise an exception

We no longer raise an exception if there are nested transaction context managers, instead the inner ones do nothing.

If you want the existing behaviour:

async with DB.transaction():
    async with DB.transactional(allow_nested=False):
        # TransactionError!

Added the log_responses option to the database engines. This makes the engine print out the raw response from the database for each query, which is useful during debugging.

# piccolo_conf.py

DB = PostgresEngine(
    config={'database': 'my_database'},
    log_queries=True,
    log_responses=True
)

We also updated the Starlite ASGI template - it now uses the new import paths (thanks to @sinisaos for this).

Joins now work within update queries. For example:

await Band.update({
    Band.name: 'Amazing Band'
}).where(
    Band.manager.name == 'Guido'
)

Other changes:

• Improved the template used by piccolo app new when creating a new Piccolo app (it now uses table_finder).

Improved the performance of select queries with complex joins. Many thanks to @powellnorma and @sinisaos for their help with this.

Major improvements to Piccolo's typing / auto completion support.

>>> bands = await Band.objects()  # List[Band]

>>> band = await Band.objects().first()  # Optional[Band]

>>> bands = await Band.select().output(as_json=True)  # str

Happy New Year!

SelectRaw

This allows you to access features in the database which aren't exposed directly by Piccolo. For example, Postgres functions:

from piccolo.query import SelectRaw

>>> await Band.select(
...     Band.name,
...     SelectRaw("log(popularity) AS log_popularity")
... )
[{'name': 'Pythonistas', 'log_popularity': 3.0}]

Large fixtures

Piccolo can now load large fixtures using piccolo fixtures load. The rows are inserted in batches, so the database adapter doesn't raise any errors. Thanks to @lgblkb for reporting this.

Migration file names

The naming convention for migrations has changed slightly. It used to be just a timestamp - for example:

2021-09-06T13-58-23-024723.py

By convention Python files should start with a letter, and only contain a-z, 0-9 and _, so the new format is:

 my_app_2021_09_06T13_58_23_024723.py

Note: You can name a migration file anything you want (it's the ID value inside it which is important), so this change doesn't break anything.

Enhanced Pydantic configuration

We now expose all of Pydantic's configuration options to create_pydantic_model:

class MyPydanticConfig(pydantic.BaseConfig):
    extra = 'forbid'

model = create_pydantic_model(
    table=MyTable,
    pydantic_config_class=MyPydanticConfig
)

Thanks to @waldner for this.

Other changes

• Fixed a bug with get_or_create and null columns (thanks to @powellnorma for reporting this issue).
• Updated the Starlite ASGI template, so it uses the latest syntax for mounting Piccolo Admin (thanks to @sinisaos for this, and the Starlite team).