piccolo

You can now use pip install piccolo[all], which will install all optional requirements.

Added the fixtures app. This is used to dump data from a database to a JSON file, and then reload it again. It's useful for seeding a database with essential data, whether that's a colleague setting up their local environment, or deploying to production.

To create a fixture:

piccolo fixtures dump --apps=blog > fixture.json

To load a fixture:

piccolo fixtures load fixture.json

As part of this change, Piccolo's Pydantic support was brought into this library (prior to this it only existed within the piccolo_api library). At a later date, the piccolo_api library will be updated, so it's Pydantic code just proxies to what's within the main piccolo library.

Improvements to piccolo schema generate. It's now smarter about which imports to include. Also, the Table classes output will now be sorted based on their ForeignKey columns. Internally the sorting algorithm has been changed to use the graphlib module, which was added in Python 3.9.

Added the piccolo schema graph command for visualising your database structure, which outputs a Graphviz file. It can then be turned into an image, for example:

piccolo schema map | dot -Tpdf -o graph.pdf

Also made some minor changes to the ASGI templates, to reduce MyPy errors.

Updated to_dict so it works with nested objects, as introduced by the prefetch functionality in v0.44.0

For example:

band = Band.objects(Band.manager).first().run_sync()

>>> band.to_dict()
{'id': 1, 'name': 'Pythonistas', 'manager': {'id': 1, 'name': 'Guido'}}

It also works with filtering:

>>> band.to_dict(Band.name, Band.manager.name)
{'name': 'Pythonistas', 'manager': {'name': 'Guido'}}

Added the ability to prefetch related objects. Here's an example:

band = await Band.objects(Band.manager).run()
>>> band.manager
<Manager: 1>

If a table has a lot of ForeignKey columns, there's a useful shortcut, which will return all of the related rows as objects.

concert = await Concert.objects(Concert.all_related()).run()
>>> concert.band_1
<Band: 1>
>>> concert.band_2
<Band: 2>
>>> concert.venue
<Venue: 1>

Thanks to @wmshort for all the input.

Migrations containing Array, JSON and JSONB columns should be more reliable now. More unit tests were added to cover edge cases.

You can now use all_columns at the root. For example:

await Band.select(
    Band.all_columns(),
    Band.manager.all_columns()
).run()

You can also exclude certain columns if you like:

await Band.select(
    Band.all_columns(exclude=[Band.id]),
    Band.manager.all_columns(exclude=[Band.manager.id])
).run()

Fixes a regression where if multiple tables are created in a single migration file, it could potentially fail by applying them in the wrong order.

Fixed a bug where if all_columns was used two or more levels deep, it would fail. Thanks to @wmshort for reporting this issue.

Here's an example:

Concert.select(
    Concert.venue.name,
    *Concert.band_1.manager.all_columns()
).run_sync()

Also, the ColumnsDelegate has now been tweaked, so unpacking of all_columns is optional.

# This now works the same as the code above (we have omitted the *)
Concert.select(
    Concert.venue.name,
    Concert.band_1.manager.all_columns()
).run_sync()

Loosen the typing-extensions requirement, as it was causing issues when installing asyncpg.

Added nested output option, which makes the response from a select query use nested dictionaries:

>>> await Band.select(Band.name, *Band.manager.all_columns()).output(nested=True).run()
[{'name': 'Pythonistas', 'manager': {'id': 1, 'name': 'Guido'}}]

Thanks to @wmshort for the input.

Added to_dict method to Table.

If you just use __dict__ on a Table instance, you get some non-column values. By using to_dict it's just the column values. Here's an example:

class MyTable(Table):
    name = Varchar()

instance = MyTable.objects().first().run_sync()

>>> instance.__dict__
{'_exists_in_db': True, 'id': 1, 'name': 'foo'}

>>> instance.to_dict()
{'id': 1, 'name': 'foo'}

Thanks to @wmshort for the idea, and @aminalaee and @sinisaos for investigating edge cases.

Removed problematic type hint which assumed pytest was installed.

Minor changes to get_or_create to make sure it handles joins correctly.

instance = (
    Band.objects()
    .get_or_create(
        (Band.name == "My new band")
        & (Band.manager.name == "Excellent manager")
    )
    .run_sync()
)

In this situation, there are two columns called 'name' - we need to make sure the correct value is applied if the row doesn't exist.

get_or_create now supports more complex where clauses. For example:

  row = await Band.objects().get_or_create(
      (Band.name == 'Pythonistas') & (Band.popularity == 1000)
  ).run()

And you can find out whether the row was created or not using row._was_created.

Thanks to @wmshort for reporting this issue.

Added ModelBuilder, which can be used to generate data for tests (courtesy @aminalaee).

Fixed an issue where like and ilike clauses required a wildcard (%). For example:

await Manager.select().where(Manager.name.ilike('Guido%')).run()

You can now omit wildcards if you like:

await Manager.select().where(Manager.name.ilike('Guido')).run()

Which would match on 'guido' and 'Guido', but not 'Guidoxyz'.

Thanks to @wmshort for reporting this issue.

• Improved PrimaryKey deprecation warning (courtesy @tonybaloney).
• Added piccolo schema generate which creates a Piccolo schema from an existing database.
• Added piccolo tester run which is a wrapper around pytest, and temporarily sets PICCOLO_CONF, so a test database is used.
• Added the get convenience method (courtesy @aminalaee). It returns the first matching record, or None if there's no match. For example:

manager = await Manager.objects().get(Manager.name == 'Guido').run()

# This is equivalent to:
manager = await Manager.objects().where(Manager.name == 'Guido').first().run()

Added the get_or_create convenience method (courtesy @aminalaee). Example usage:

manager = await Manager.objects().get_or_create(
    Manager.name == 'Guido'
).run()