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.
Fixed a bug with auto migrations. If renaming multiple columns at once, it could get confused. Thanks to @theelderbeever for reporting this issue, and @sinisaos for helping to replicate it. See PR 457.
We ran a profiler on the Piccolo codebase and identified some optimisations.
For example, we were calling self.querystring
multiple times in a method,
rather than assigning it to a local variable.
We also ran a linter which identified when list / set / dict comprehensions could be more efficient.
The performance is now slightly improved (especially when fetching large numbers of rows from the database).
Example query times on a MacBook, when fetching 1000 rows from a local Postgres
database (using await SomeTable.select()
):
- 8 ms without a connection pool
- 2 ms with a connection pool
As you can see, having a connection pool is the main thing you can do to improve performance.
Thanks to @AliSayyah for all his work on this.
Made improvements to piccolo schema generate
, which automatically generates
Piccolo Table
classes from an existing database.
There were situations where it would fail ungracefully when it couldn't parse an index definition. It no longer crashes, and we print out the problematic index definitions. See PR 449. Thanks to @gmos for originally reporting this issue.
We also improved the error messages if schema generation fails for some reason by letting the user know which table caused the error. Courtesy @AliSayyah.
We used to raise a ValueError
if a column was both null=False
and
default=None
. This has now been removed, as there are situations where
it's valid for columns to be configured that way. Thanks to @gmos for
suggesting this change.
The where
clause now raises a ValueError
if a boolean value is
passed in by accident. This was possible in the following situation:
await Band.select().where(Band.has_drummer is None)
Piccolo can't override the is
operator because Python doesn't allow it,
so Band.has_drummer is None
will always equal False
. Thanks to
@trondhindenes for reporting this issue.
We've also put a lot of effort into improving documentation throughout the project.
- Lots of documentation improvements, including how to customise
BaseUser
(courtesy @sinisaos). - Fixed a bug with creating indexes when the column name clashes with a SQL
keyword (e.g.
'order'
). See Pr 433. Thanks to @wmshort for reporting this issue. - Fixed an issue where some slots were incorrectly configured (courtesy @ariebovenberg). See PR 426.
Fixed a bug with auto migrations which rename columns - see PR 423. Thanks to @theelderbeever for reporting this, and @sinisaos for help investigating.
Added Xpresso as a supported ASGI framework when
using piccolo asgi new
to generate a web app.
Thanks to @sinisaos for adding this template, and @adriangb for reviewing.
We also took this opportunity to update our FastAPI and BlackSheep ASGI templates.
If you try and perform an update query without a where
clause you will now
get an error:
>>> await Band.update({Band.name: 'New Band'})
UpdateError
If you want to update all rows in the table, you can still do so, but you must
pass force=True
.
>>> await Band.update({Band.name: 'New Band'}, force=True)
This is a similar to delete
queries, which require a where
clause or
force=True
.
It was pointed out by @theelderbeever that an accidental mass update is almost as bad as a mass deletion, which is why this safety measure has been added.
See PR 412.
Warning
This is a breaking change. It you're doing update queries without
a where clause, you will need to add force=True
.
Fixed some bugs with nullable JSONB
columns. A value of None
is now
stored as null
in the database, instead of the JSON string 'null'
.
Thanks to @theelderbeever for reporting this.
See PR 413.
BaseUser
now has a create_user
method, which adds some extra password
validation vs just instantiating and saving BaseUser
directly.
>>> await BaseUser.create_user(username='bob', password='abc123XYZ')
<BaseUser: 1>
We check that passwords are a reasonable length, and aren't already hashed. See PR 402.
All of the docs have been updated to show the async version of queries.
For example:
# Previous:
Band.select().run_sync()
# Now:
await Band.select()
Most people use Piccolo in async apps, and the playground supports top level
await, so you can just paste in await Band.select()
and it will still work.
See PR 407.
We decided to use await Band.select()
instead of await Band.select().run()
.
Both work, and have their merits, but the simpler version is probably easier
for newcomers.
In Piccolo you can print out any query to see the SQL which will be generated:
>>> print(Band.select())
SELECT "band"."id", "band"."name", "band"."manager", "band"."popularity" FROM band
It didn't represent UUID
and datetime
values correctly, which is now fixed (courtesy @theelderbeever).
See PR 405.
Using descriptors to improve MyPy support (PR 399).
MyPy is now able to correctly infer the type in lots of different scenarios:
class Band(Table):
name = Varchar()
# MyPy knows this is a Varchar
Band.name
band = Band()
band.name = "Pythonistas" # MyPy knows we can assign strings when it's a class instance
band.name # MyPy knows we will get a string back
band.name = 1 # MyPy knows this is an error, as we should only be allowed to assign strings
Fixed bug with BaseUser
and Piccolo API.
The BaseUser
table hashes passwords before storing them in the database.
When we create a fixture from the BaseUser
table (using piccolo fixtures dump
),
it looks something like:
{
"id": 11,
"username": "bob",
"password": "pbkdf2_sha256$10000$abc123",
}
When we load the fixture (using piccolo fixtures load
) we need to be
careful in case BaseUser
tries to hash the password again (it would then be a hash of
a hash, and hence incorrect). We now have additional checks in place to prevent
this.
Thanks to @mrbazzan for implementing this, and @sinisaos for help reviewing.
Added initial support for ForeignKey
columns referencing non-primary key
columns. For example:
class Manager(Table):
name = Varchar()
email = Varchar(unique=True)
class Band(Table):
manager = ForeignKey(Manager, target_column=Manager.email)
Thanks to @theelderbeever for suggesting this feature, and with help testing.
Fixed an issue with the value_type
of ForeignKey
columns when
referencing a table with a custom primary key column (such as a UUID
).
Added an exclude_imported
option to table_finder
.
APP_CONFIG = AppConfig(
table_classes=table_finder(['music.tables'], exclude_imported=True)
)
It's useful when we want to import Table
subclasses defined within a
module itself, but not imported ones:
# tables.py
from piccolo.apps.user.tables import BaseUser # excluded
from piccolo.columns.column_types import ForeignKey, Varchar
from piccolo.table import Table
class Musician(Table): # included
name = Varchar()
user = ForeignKey(BaseUser)
This was also possible using tags, but was less convenient. Thanks to @sinisaos for reporting this issue.
Fixed the error message in LazyTableReference
.
Fixed a bug with create_pydantic_model
with nested models. For example:
create_pydantic_model(Band, nested=(Band.manager,))
Sometimes Pydantic couldn't uniquely identify the nested models. Thanks to @wmshort and @sinisaos for their help with this.
Added a max password length to the BaseUser
table. By default it's set to
128 characters.
Fixed a bug with Readable
when it contains lots of joins.
Readable
is used to create a user friendly representation of a row in
Piccolo Admin.
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.
When using piccolo asgi new
to generate a web app, it now has a nicer home
page template, with improved styles.
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.
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.
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.
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)
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
.
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 aconftest.py
file, which encourages people to usepiccolo tester run
rather than usingpytest
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 aninclude_columns
argument, in case you only want a few columns in your model, it's faster than usingexclude_columns
(courtesy @sinisaos).- Updated linters, and fixed new errors.
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.
Some of the code has been optimised and cleaned up. Courtesy @yezz123.
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")
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.
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.
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).
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.
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
is the main entrypoint for database reflection in
Piccolo. It has been modified to accept an optional Engine
argument, which
makes it more flexible.
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.
Fixed an edge case where ColumnMeta
couldn't be copied if it had extra
attributes added to it.
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.
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.
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
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
andprecision
values forNumeric
/Decimal
column types are extracted from the database (courtesy @wmshort). - The
ON DELETE
andON UPDATE
values forForeignKey
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.
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()
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()
Fixed a bug with piccolo schema generate
where columns with unrecognised
column types were omitted from the output (courtesy @AliSayyah).
Added docs for the --trace
argument, which can be used with Piccolo
commands to get a traceback if the command fails (courtesy @hipertracker).
Added DoublePrecision
column type, which is similar to Real
in that
it stores float
values. However, those values are stored at greater
precision (courtesy @AliSayyah).
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 by passing in command line
arguments, instead of using the interactive prompt (courtesy @AliSayyah).
For example piccolo user create --username=bob ...
.
This is useful when you want to create users in a script.
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.
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()
Fix 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 idea.
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 setsPICCOLO_CONF
, so a test database is used.Added the
get
convenience method (courtesy @aminalaee). It returns the first matching record, orNone
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()
- Bug fix, where
compare_dicts
was failing in migrations if anyColumn
had an unhashable type as an argument. For example:Array(default=[])
. Thanks to @hipertracker for reporting this problem. - Increased the minimum version of orjson, so binaries are available for Macs running on Apple silicon (courtesy @hipertracker).
Fix for auto migrations when using custom primary keys (thanks to @adriangb and @aminalaee for investigating this issue).
Migrations can now have a description, which is shown when using
piccolo migrations check
. This makes migrations easier to identify (thanks
to @davidolrik for the idea).
Added an all_columns
method, to make it easier to retrieve all related
columns when doing a join. For example:
await Band.select(Band.name, *Band.manager.all_columns()).first().run()
Changed the instructions for installing additional dependencies, so they're
wrapped in quotes, to make sure it works on ZSH (i.e.
pip install 'piccolo[postgres]'
instead of
pip install piccolo[postgres]
).
The database drivers are now installed separately. For example:
pip install piccolo[postgres]
(courtesy @aminalaee).
For some users this might be a breaking change - please make sure that for
existing Piccolo projects, you have either asyncpg
, or
piccolo[postgres]
in your requirements.txt
file.
The user can now specify the primary key column (courtesy @aminalaee). For example:
class RecordingStudio(Table):
pk = UUID(primary_key=True)
The BlackSheep template generated by piccolo asgi new
now supports mounting
of the Piccolo Admin (courtesy @sinisaos).
Added aggregations functions, such as Sum
, Min
, Max
and Avg
,
for use in select queries (courtesy @sinisaos).
Added uvloop as an optional dependency, installed via pip install piccolo[uvloop] (courtesy @aminalaee). uvloop is a faster implementation of the asyncio event loop found in Python's standard library. When uvloop is installed, Piccolo will use it to increase the performance of the Piccolo CLI, and web servers such as Uvicorn will use it to increase the performance of your ASGI app.
Added eq
and ne
methods to the Boolean
column, which can be used
if linters complain about using SomeTable.some_column == True
.
- Changed the migration IDs, so the timestamp now includes microseconds. This is to make clashing migration IDs much less likely.
- Added a lot of end-to-end tests for migrations, which revealed some bugs
in
Column
defaults.
A bug fix for migrations. See issue 123 for more information.
Lots of improvements to JSON
and JSONB
columns. Piccolo will now
automatically convert between Python types and JSON strings. For example, with
this schema:
class RecordingStudio(Table):
name = Varchar()
facilities = JSON()
We can now do the following:
RecordingStudio(
name="Abbey Road",
facilities={'mixing_desk': True} # Will automatically be converted to a JSON string
).save().run_sync()
Similarly, when fetching data from a JSON column, Piccolo can now automatically deserialise it.
>>> RecordingStudio.select().output(load_json=True).run_sync()
[{'id': 1, 'name': 'Abbey Road', 'facilities': {'mixing_desk': True}]
>>> studio = RecordingStudio.objects().first().output(load_json=True).run_sync()
>>> studio.facilities
{'mixing_desk': True}
Added the create_table_class
function, which can be used to create
Table
subclasses at runtime. This was required to fix an existing bug,
which was effecting migrations (see issue 111
for more details).
- An error is now raised if a user tries to create a Piccolo app using
piccolo app new
with the same name as a builtin Python module, as it will cause strange bugs. - Fixing a strange bug where using an expression such as
Concert.band_1.manager.id
in a query would cause an error. It only happened if multiple joins were involved, and the last column in the chain wasid
. where
clauses can now acceptTable
instances. For example:await Band.select().where(Band.manager == some_manager).run()
, instead of having to explicity reference theid
.
Fixing a bug with serialising Enum
instances in migrations. For example:
Varchar(default=Colour.red)
.
Fix missing imports in FastAPI and Starlette app templates.
- Added a
freeze
method toQuery
. - Added BlackSheep as an option to
piccolo asgi new
.
Added choices
option to Column
.
- Added
piccolo user change_permissions
command. - Added aliases for CLI commands.
Changes to the BaseUser
table - added a superuser
, and last_login
column. These are required for upgrades to Piccolo Admin.
If you're using migrations, then running piccolo migrations forwards all
should add these new columns for you.
If not using migrations, the BaseUser
table can be upgraded using the
following DDL statements:
ALTER TABLE piccolo_user ADD COLUMN "superuser" BOOLEAN NOT NULL DEFAULT false
ALTER TABLE piccolo_user ADD COLUMN "last_login" TIMESTAMP DEFAULT null
- Fixed a bug when multiple tables inherit from the same mixin (thanks to @brnosouza).
- Added a
log_queries
option toPostgresEngine
, which is useful during debugging. - Added the inflection library for converting
Table
class names to database table names. Previously, a class calledTableA
would wrongly have a table calledtable
instead oftable_a
. - Fixed a bug with
SerialisedBuiltin.__hash__
not returning a number, which could break migrations (thanks to @sinisaos).
Improved Array
column serialisation - needed to fix auto migrations.
Added support for filtering Array
columns.
Add the Array
column type as a top level import in piccolo.columns
.
- Refactored
forwards
andbackwards
commands for migrations, to make them easier to run programatically. - Added a simple
Array
column type. table_finder
now works if just a string is passed in, instead of having to pass in an array of strings.
Catching database connection exceptions when starting the default ASGI app
created with piccolo asgi new
- these errors exist if the Postgres
database hasn't been created yet.
Added a help_text
option to the Table
metaclass. This is used in
Piccolo Admin to show tooltips.
Added a help_text
option to the Column
constructor. This is used in
Piccolo Admin to show tooltips.
- Exposing
index_type
in theColumn
constructor. - Fixing a typo with
start_connection_pool` and ``close_connection_pool
- thanks to paolodina for finding this. - Fixing a typo in the
PostgresEngine
docs - courtesy of paolodina.
Fixing a bug with SchemaSnapshot
if column types were changed in migrations
- the snapshot didn't reflect the changes.
- Migrations now directly import
Column
classes - this allows users to create customColumn
subclasses. Migrations previously only worked with the builtin column types. - Migrations now detect if the column type has changed, and will try and convert it automatically.
The Postgres extensions that PostgresEngine
tries to enable at startup
can now be configured.
- Fixed a bug with
MyTable.column != None
- Added
is_null
andis_not_null
methods, to avoid linting issues when comparing with None.
- Added
WhereRaw
, so raw SQL can be used in where clauses. piccolo shell run
now uses syntax highlighting - courtesy of Fingel.
Reordering the dependencies in requirements.txt when using piccolo asgi new
as the latest FastAPI and Starlette versions are incompatible.
Added Timestamptz
column type, for storing datetimes which are timezone
aware.
- Fixed a bug with creating a
ForeignKey
column withreferences="self"
in auto migrations. - Changed migration file naming, so there are no characters in there which are unsupported on Windows.
Changing the status code when creating a migration, and no changes were detected. It now returns a status code of 0, so it doesn't fail build scripts.
Added Bytea
/ Blob
column type.
Fixing a bug with migrations which drop column defaults.
- Fixing a bug where re-running
Table.create(if_not_exists=True)
would fail if it contained columns with indexes. - Raising a
ValueError
if a relative path is provided toForeignKey
references
. For example,.tables.Manager
. The paths must be absolute for now.
Fixing a bug with Boolean
column defaults, caused by the Table
metaclass not being explicit enough when checking falsy values.
- The
ForeignKey
references
argument can now be specified using a string, or aLazyTableReference
instance, rather than just aTable
subclass. This allows aTable
to be specified which is in a Piccolo app, or Python module. TheTable
is only loaded after imports have completed, which prevents circular import issues. - Faster column copying, which is important when specifying joins, e.g.
await Band.select(Band.manager.name).run()
. - Fixed a bug with migrations and foreign key contraints.
Modified the exit codes for the forwards
and backwards
commands when no
migrations are left to run / reverse. Otherwise build scripts may fail.
- Improved the method signature of the
output
query clause (explicitly added args, instead of using**kwargs
). - Fixed a bug where
output(as_list=True)
would fail if no rows were found. - Made
piccolo migrations forwards
command output more legible. - Improved renamed table detection in migrations.
- Added the
piccolo migrations clean
command for removing orphaned rows from the migrations table. - Fixed a bug where
get_migration_managers
wasn't inclusive. - Raising a
ValueError
ifis_in
ornot_in
query clauses are passed an empty list. - Changed the migration commands to be top level async.
- Combined
print
andsys.exit
statements.
- Added missing type annotation for
run_sync
. - Updating type annotations for column default values - allowing callables.
- Replaced instances of
asyncio.run
withrun_sync
. - Tidied up aiosqlite imports.
- Added JSON and JSONB column types, and the arrow function for JSONB.
- Fixed a bug with the distinct clause.
- Added
as_alias
, so select queries can override column names in the response (i.e. SELECT foo AS bar from baz). - Refactored JSON encoding into a separate utils file.
- Removed old iPython version recommendation in the
piccolo shell run
andpiccolo playground run
, and enabled top level await. - Fixing outstanding mypy warnings.
- Added optional requirements for the playground to setup.py
- Added
piccolo sql_shell run
command, which launches the psql or sqlite3 shell, using the connection parameters defined inpiccolo_conf.py
. This is convenient when you want to run raw SQL on your database. run_sync
now handles more edge cases, for example if there's already an event loop in the current thread.- Removed asgiref dependency.
- Queries can be directly awaited -
await MyTable.select()
, as an alternative to using the run methodawait MyTable.select().run()
. - The
piccolo asgi new
command now accepts aname
argument, which is used to populate the default database name within the template.
- Centralised code for importing Piccolo apps and tables - laying the foundation for fixtures.
- Made orjson an optional dependency, installable using
pip install piccolo[orjson]
. - Improved version number parsing in Postgres.
Fixing a bug with dropping tables in auto migrations.
Added Interval
column type.
- Added
allowed_hosts
tocreate_admin
in ASGI template. - Fixing bug with default
root
argument in some piccolo commands.
- Fixed bug with
SchemaSnapshot
when dropping columns. - Added custom
__repr__
method toTable
.
Added piccolo shell run
command for running adhoc queries using Piccolo.
- Fixing bug with auto migrations when dropping columns.
- Added a
root
argument topiccolo asgi new
,piccolo app new
andpiccolo project new
commands, to override where the files are placed.
Added support for group_by
and Count
for aggregate queries.
Added required argument to Column
. This allows the user to indicate which
fields must be provided by the user. Other tools can use this value when
generating forms and serialisers.
- Fixing a typo in
TimestampCustom
arguments. - Fixing bug in
TimestampCustom
SQL representation. - Added more extensive deserialisation for migrations.
- Improved
PostgresEngine
docstring. - Resolving rename migrations before adding columns.
- Fixed bug serialising
TimestampCustom
. - Fixed bug with altering column defaults to be non-static values.
- Removed
response_handler
fromAlter
query.
Using orjson for JSON serialisation when using the output(as_json=True)
clause. It supports more Python types than ujson.
Improved piccolo user create
command - defaults the username to the current
system user.
Fixing bug when sorting extra_definitions
in auto migrations.
- Fixed typos.
- Bumped requirements.
- Added
Date
andTime
columns. - Improved support for column default values.
- Auto migrations can now serialise more Python types.
- Added
Table.indexes
method for listing table indexes. - Auto migrations can handle adding / removing indexes.
- Improved ASGI template for FastAPI.
ASGI template fix.
- Improved
UUID
columns in SQLite - prepending 'uuid:' to the stored value to make the type more explicit for the engine. - Removed SQLite as an option for
piccolo asgi new
until auto migrations are supported.
Added support for FastAPI to piccolo asgi new
.
Fixed bug in BaseMigrationManager.get_migration_modules
- wasn't
excluding non-Python files well enough.
- Stopped
piccolo migrations new
from creating a config.py file - was legacy. - Added a README file to the piccolo_migrations folder in the ASGI template.
Fixed __pycache__ bug when using piccolo asgi new
.
- Showing a warning if trying auto migrations with SQLite.
- Added a command for creating a new ASGI app -
piccolo asgi new
. - Added a meta app for printing out the Piccolo version -
piccolo meta version
. - Added example queries to the playground.
- Added
table_finder
, for use inAppConfig
. - Added support for concatenating strings using an update query.
- Added more tables to the playground, with more column types.
- Improved consistency between SQLite and Postgres with
UUID
columns,Integer
columns, andexists
queries.
Added Numeric
and Real
column types.
Fixing a bug where Postgres versions without a patch number couldn't be parsed.
Improving release script.
Sorting out packaging issue - old files were appearing in release.
Auto migrations can now run backwards.
Fixing some typos with Table
imports. Showing a traceback when piccolo_conf
can't be found by engine_finder
.
Adding missing jinja templates to setup.py.
Fixing a bug when using piccolo project new
in a new project.
Fixing bug with enum default values.
Using targ for the CLI. Refactored some core code into apps.
Suppressing exceptions when trying to find the Postgres version, to avoid
an ImportError
when importing piccolo_conf.py.
.first()
bug fix.
Auto migration fixes, and .first()
method now returns None if no match is
found.
Added support for auto migrations.
Can use operators in update queries, and fixing 'new' migration command.
Fixing release issue.
Improved transaction support - can now use a context manager. Added Secret
,
BigInt
and SmallInt
column types. Foreign keys can now reference the
parent table.
Fixing bug when joining across several tables. Can pass values directly into
the Table.update
method. Added if_not_exists
option when creating a
table.
Column sequencing matches the definition order.
Supporting ON DELETE and ON UPDATE for foreign keys. Recording reverse foreign key relationships.
Made response_handler
async. Made it easier to rename columns.
Bug fixes and dependency updates.
Adding missing __int__.py
file.
Changed migration import paths.
Added remove_db_file
method to SQLiteEngine
- makes testing easier.
Renamed create
to create_table
, and can register commands via
piccolo_conf.
Adding missing __init__.py
files.
Moved BaseUser
. Migration refactor.
Moved drop table under Alter
- to help prevent accidental drops.
Added batch
support.
Refactored the Table
Metaclass - much simpler now. Scoped more of the
attributes on Column
to avoid name clashes. Added engine_finder
to make
database configuration easier.
SQLite is now returning datetime objects for timestamp fields.
Refactored to improve code completion, along with bug fixes.
Allowing Update
queries in SQLite.
Falling back to LIKE instead of ILIKE for SQLite.
Renamed User
to BaseUser
.
Added ilike
.
Added value types to columns.
Default values infer the engine type.
Update click version.
Tweaked API to support more auto completion. Join support in where clause. Basic SQLite support - mostly for playground.
Using QueryString
internally to represent queries, instead of raw strings,
to harden against SQL injection.
Allowing joins across multiple tables.
Added playground.