piccolo

Added Many-To-Many support.

from piccolo.columns.column_types import (
    ForeignKey,
    LazyTableReference,
    Varchar
)
from piccolo.columns.m2m import M2M


class Band(Table):
    name = Varchar()
    genres = M2M(LazyTableReference("GenreToBand", module_path=__name__))


class Genre(Table):
    name = Varchar()
    bands = M2M(LazyTableReference("GenreToBand", module_path=__name__))


# This is our joining table:
class GenreToBand(Table):
    band = ForeignKey(Band)
    genre = ForeignKey(Genre)


>>> await Band.select(Band.name, Band.genres(Genre.name, as_list=True))
[
    {
        "name": "Pythonistas",
        "genres": ["Rock", "Folk"]
    },
    ...
]

See the docs for more details.

Many thanks to @sinisaos and @yezz123 for all the input.

Fixed some edge cases where migrations would fail if a column name clashed with a reserved Postgres keyword (for example order or select).

We now have more robust tests for piccolo asgi new - as part of our CI we actually run the generated ASGI app to make sure it works (thanks to @AliSayyah and @yezz123 for their help with this).

We also improved docstrings across the project.

Nicer ASGI template

When using piccolo asgi new to generate a web app, it now has a nicer home page template, with improved styles.

Improved schema generation

Fixed a bug with piccolo schema generate where it would crash if the column type was unrecognised, due to failing to parse the column's default value. Thanks to @gmos for reporting this issue, and figuring out the fix.

Fix Pylance error

Added start_connection_pool and close_connection_pool methods to the base Engine class (courtesy @gmos).

The save method now supports a columns argument, so when updating a row you can specify which values to sync back. For example:

band = await Band.objects().get(Band.name == "Pythonistas")
band.name = "Super Pythonistas"
await band.save([Band.name])

# Alternatively, strings are also supported:
await band.save(['name'])

Thanks to @trondhindenes for suggesting this feature.

Fixed a bug with asyncio.gather not working with some query types. It was due to them being dataclasses, and they couldn't be hashed properly. Thanks to @brnosouza for reporting this issue.

Modified the import path for MigrationManager in migration files. It was confusing Pylance (VSCode's type checker). Thanks to @gmos for reporting and investigating this issue.

Secret columns

All column types can now be secret, rather than being limited to the Secret column type which is a Varchar under the hood (courtesy @sinisaos).

class Manager(Table):
    name = Varchar()
    net_worth = Integer(secret=True)

The reason this is useful is you can do queries such as:

>>> Manager.select(exclude_secrets=True).run_sync()
[{'id': 1, 'name': 'Guido'}]

In the Piccolo API project we have PiccoloCRUD which is an incredibly powerful way of building an API with very little code. PiccoloCRUD has an exclude_secrets option which lets you safely expose your data without leaking sensitive information.

Pydantic improvements

max_recursion_depth

create_pydantic_model now has a max_recursion_depth argument, which is useful when using nested=True on large database schemas.

>>> create_pydantic_model(MyTable, nested=True, max_recursion_depth=3)

Nested tuple

You can now pass a tuple of columns as the argument to nested:

>>> create_pydantic_model(Band, nested=(Band.manager,))

This gives you more control than just using nested=True.

include_columns / exclude_columns

You can now include / exclude columns from related tables. For example:

>>> create_pydantic_model(Band, nested=(Band.manager,), exclude_columns=(Band.manager.country))

Similarly:

>>> create_pydantic_model(Band, nested=(Band.manager,), include_columns=(Band.name, Band.manager.name))

• When using piccolo asgi new to generate a FastAPI app, the generated code is now cleaner. It also contains a conftest.py file, which encourages people to use piccolo tester run rather than using pytest directly.
• Tidied up docs, and added logo.
• Clarified the use of the PICCOLO_CONF environment variable in the docs (courtesy @theelderbeever).
• create_pydantic_model now accepts an include_columns argument, in case you only want a few columns in your model, it's faster than using exclude_columns (courtesy @sinisaos).
• Updated linters, and fixed new errors.

Improved Pydantic docs

The Pydantic docs used to be in the Piccolo API repo, but have been moved over to this repo. We took this opportunity to improve them significantly with additional examples. Courtesy @sinisaos.

Internal code refactoring

Some of the code has been optimised and cleaned up. Courtesy @yezz123.

Schema generation for recursive foreign keys

When using piccolo schema generate, it would get stuck in a loop if a table had a foreign key column which referenced itself. Thanks to @knguyen5 for reporting this issue, and @wmshort for implementing the fix. The output will now look like:

class Employee(Table):
    name = Varchar()
    manager = ForeignKey("self")

Fixing a bug with Alter.add_column

When using the Alter.add_column API directly (not via migrations), it would fail with foreign key columns. For example:

SomeTable.alter().add_column(
    name="my_fk_column",
    column=ForeignKey(SomeOtherTable)
).run_sync()

This has now been fixed. Thanks to @wmshort for discovering this issue.

create_pydantic_model improvements

Additional fields can now be added to the Pydantic schema. This is useful when using Pydantic's JSON schema functionality:

my_model = create_pydantic_model(Band, my_extra_field="Hello")
>>> my_model.schema()
{..., "my_extra_field": "Hello"}

This feature was added to support new features in Piccolo Admin.

Fixing a bug with import clashes in migrations

In certain situations it was possible to create a migration file with clashing imports. For example:

from uuid import UUID
from piccolo.columns.column_types import UUID

Piccolo now tries to detect these clashes, and prevent them. If they can't be prevented automatically, a warning is shown to the user. Courtesy @0scarB.

Added Python 3.10 support (courtesy @kennethcheo).

Fixed schema generation bug

When using piccolo schema generate to auto generate Piccolo Table classes from an existing database, it would fail in this situation:

• A table has a column with an index.
• The column name clashed with a Postgres type.

For example, we couldn't auto generate this Table class:

class MyTable(Table):
    time = Timestamp(index=True)

This is because time is a builtin Postgres type, and the CREATE INDEX statement being inspected in the database wrapped the column name in quotes, which broke our regex.

Thanks to @knguyen5 for fixing this.

Improved testing docs

A convenience method called get_table_classes was added to Finder.

Finder is the main class in Piccolo for dynamically importing projects / apps / tables / migrations etc.

get_table_classes lets us easily get the Table classes for a project. This makes writing unit tests easier, when we need to setup a schema.

from unittest import TestCase

from piccolo.table import create_tables, drop_tables
from piccolo.conf.apps import Finder

TABLES = Finder().get_table_classes()

class TestApp(TestCase):
    def setUp(self):
        create_tables(*TABLES)

    def tearDown(self):
        drop_tables(*TABLES)

    def test_app(self):
        # Do some testing ...
        pass

The docs were updated to reflect this.

When dropping tables in a unit test, remember to use piccolo tester run, to make sure the test database is used.

get_output_schema

get_output_schema is the main entrypoint for database reflection in Piccolo. It has been modified to accept an optional Engine argument, which makes it more flexible.

Table._meta.refresh_db

Added the ability to refresh the database engine.

MyTable._meta.refresh_db()

This causes the Table to fetch the Engine again from your piccolo_conf.py file. The reason this is useful, is you might change the PICCOLO_CONF environment variable, and some Table classes have already imported an engine. This is now used by the piccolo tester run command to ensure all Table classes have the correct engine.

ColumnMeta edge cases

Fixed an edge case where ColumnMeta couldn't be copied if it had extra attributes added to it.

Improved column type conversion

When running migrations which change column types, Piccolo now provides the USING clause to the ALTER COLUMN DDL statement, which makes it more likely that type conversion will be successful.

For example, if there is an Integer column, and it's converted to a Varchar column, the migration will run fine. In the past, running this in reverse would fail. Now Postgres will try and cast the values back to integers, which makes reversing migrations more likely to succeed.

Added drop_tables

There is now a convenience function for dropping several tables in one go. If the database doesn't support CASCADE, then the tables are sorted based on their ForeignKey columns, so they're dropped in the correct order. It all runs inside a transaction.

from piccolo.table import drop_tables

drop_tables(Band, Manager)

This is a useful tool in unit tests.

Index support in schema generation

When using piccolo schema generate, Piccolo will now reflect the indexes from the database into the generated Table classes. Thanks to @wmshort for this.

Added the db_column_name option to columns. This is for edge cases where a legacy database is being used, with problematic column names. For example, if a column is called class, this clashes with a Python builtin, so the following isn't possible:

class MyTable(Table):
    class = Varchar()  # Syntax error!

You can now do the following:

class MyTable(Table):
    class_ = Varchar(db_column_name='class')

Here are some example queries using it:

# Create - both work as expected
MyTable(class_='Test').save().run_sync()
MyTable.objects().create(class_='Test').run_sync()

# Objects
row = MyTable.objects().first().where(MyTable.class_ == 'Test').run_sync()
>>> row.class_
'Test'

# Select
>>> MyTable.select().first().where(MyTable.class_ == 'Test').run_sync()
{'id': 1, 'class': 'Test'}

An internal code clean up (courtesy @yezz123).

Dramatically improved CLI appearance when running migrations (courtesy @wmshort).

Added a runtime reflection feature, where Table classes can be generated on the fly from existing database tables (courtesy @AliSayyah). This is useful when dealing with very dynamic databases, where tables are frequently being added / modified, so hard coding them in a tables.py file is impractical. Also, for exploring databases on the command line. It currently just supports Postgres.

Here's an example:

from piccolo.table_reflection import TableStorage

storage = TableStorage()
Band = await storage.get_table('band')
>>> await Band.select().run()
[{'id': 1, 'name': 'Pythonistas', 'manager': 1}, ...]

Lots of improvements to piccolo schema generate:

• Dramatically improved performance, by executing more queries in parallel (courtesy @AliSayyah).
• If a table in the database has a foreign key to a table in another schema, this will now work (courtesy @AliSayyah).
• The column defaults are now extracted from the database (courtesy @wmshort).
• The scale and precision values for Numeric / Decimal column types are extracted from the database (courtesy @wmshort).
• The ON DELETE and ON UPDATE values for ForeignKey columns are now extracted from the database (courtesy @wmshort).

Added BigSerial column type (courtesy @aliereno).

Added GitHub issue templates (courtesy @AbhijithGanesh).

Fixing a bug with on_delete and on_update not being set correctly. Thanks to @wmshort for discovering this.

Modified create_pydantic_model, so JSON and JSONB columns have a format attribute of 'json'. This will be used by Piccolo Admin for improved JSON support. Courtesy @sinisaos.

Fixing a bug where the piccolo fixtures load command wasn't registered with the Piccolo CLI.

There are lots of great improvements in this release:

where clause changes

The where clause can now accept multiple arguments (courtesy @AliSayyah):

Concert.select().where(
    Concert.venue.name == 'Royal Albert Hall',
    Concert.band_1.name == 'Pythonistas'
).run_sync()

It's another way of expressing AND. It's equivalent to both of these:

Concert.select().where(
    Concert.venue.name == 'Royal Albert Hall'
).where(
    Concert.band_1.name == 'Pythonistas'
).run_sync()

Concert.select().where(
    (Concert.venue.name == 'Royal Albert Hall') & (Concert.band_1.name == 'Pythonistas')
).run_sync()

create method

Added a create method, which is an easier way of creating objects (courtesy @AliSayyah).

# This still works:
band = Band(name="C-Sharps", popularity=100)
band.save().run_sync()

# But now we can do it in a single line using `create`:
band = Band.objects().create(name="C-Sharps", popularity=100).run_sync()

piccolo schema generate bug fix

Fixed a bug with piccolo schema generate where columns with unrecognised column types were omitted from the output (courtesy @AliSayyah).

--trace docs

Added docs for the --trace argument, which can be used with Piccolo commands to get a traceback if the command fails (courtesy @hipertracker).

DoublePrecision column type

Added DoublePrecision column type, which is similar to Real in that it stores float values. However, those values are stored with greater precision (courtesy @AliSayyah).

AppRegistry improvements

Improved AppRegistry, so if a user only adds the app name (e.g. blog), instead of blog.piccolo_app, it will now emit a warning, and will try to import blog.piccolo_app (courtesy @aliereno).

Fixed a bug with create_pydantic_model when used with a Decimal / Numeric column when no digits arguments was set (courtesy @AliSayyah).

Added the create_tables function, which accepts a sequence of Table subclasses, then sorts them based on their ForeignKey columns, and creates them. This is really useful for people who aren't using migrations (for example, when using Piccolo in a simple data science script). Courtesy @AliSayyah.

from piccolo.tables import create_tables

create_tables(Band, Manager, if_not_exists=True)

# Equivalent to:
Manager.create_table(if_not_exists=True).run_sync()
Band.create_table(if_not_exists=True).run_sync()

Fixed typos with the new fixtures app - sometimes it was referred to as fixture and other times fixtures. It's now standardised as fixtures (courtesy @hipertracker).

The piccolo user create command can now be used without using the interactive prompt, by passing in command line arguments instead (courtesy @AliSayyah).

For example piccolo user create --username=bob ....

This is useful when you want to create users in a script.