A fast, user friendly ORM and query builder which supports asyncio.
MIT License
Bot releases are hidden (Show)
Published by dantownsend almost 3 years ago
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.
Published by dantownsend almost 3 years ago
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.
Published by dantownsend almost 3 years ago
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).
Published by dantownsend almost 3 years ago
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.
Published by dantownsend almost 3 years ago
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.
Published by dantownsend almost 3 years ago
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.
Published by dantownsend almost 3 years ago
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))
Published by dantownsend almost 3 years ago
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.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).Published by dantownsend almost 3 years ago
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.
Published by dantownsend about 3 years ago
Added Python 3.10 support (courtesy @kennethcheo).
Published by dantownsend about 3 years ago
When using piccolo schema generate
to auto generate Piccolo Table
classes from an existing database, it would fail in this situation:
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.
Published by dantownsend about 3 years ago
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.
Published by dantownsend about 3 years ago
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'}
Published by dantownsend about 3 years ago
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}, ...]
Published by dantownsend about 3 years ago
Lots of improvements to piccolo schema generate
:
scale
and precision
values for Numeric
/ Decimal
column types are extracted from the database (courtesy @wmshort).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).
Published by dantownsend about 3 years ago
Fixing a bug with on_delete
and on_update
not being set correctly. Thanks to @wmshort for discovering this.
Published by dantownsend about 3 years ago
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.
Published by dantownsend about 3 years ago
There are lots of great improvements in this release:
where
clause changesThe 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
methodAdded 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 fixFixed a bug with piccolo schema generate
where columns with unrecognised column types were omitted from the output (courtesy @AliSayyah).
--trace
docsAdded docs for the --trace
argument, which can be used with Piccolo commands to get a traceback if the command fails (courtesy @hipertracker).
DoublePrecision
column typeAdded 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
improvementsImproved 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).
Published by dantownsend about 3 years ago
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).
Published by dantownsend about 3 years ago
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.