Piccolo

------

You can now use the ``returning`` clause with ``delete`` queries.

For example:

.. code-block:: python

>>> await Band.delete().where(Band.popularity < 100).returning(Band.name)
[{'name': 'Terrible Band'}, {'name': 'Awful Band'}]

This also means you can count the number of deleted rows:

.. code-block:: python

>>> len(await Band.delete().where(Band.popularity < 100).returning(Band.id))
2

Thanks to waldner for adding this feature.

-------------------------------------------------------------------------------

------

SQLite ``TransactionType``
~~~~~~~~~~~~~~~~~~~~~~~~~~

You can now specify the transaction type for SQLite.

This is useful when using SQLite in production, as it's possible to get
``database locked`` errors if you're running lots of transactions concurrently,
and don't use the correct transaction type.

In this example we use an ``IMMEDIATE`` transaction:

.. code-block:: python

from piccolo.engine.sqlite import TransactionType

async with Band._meta.db.transaction(
transaction_type=TransactionType.immediate
):
band = await Band.objects().get_or_create(Band.name == 'Pythonistas')
...

We've added a `new tutorial <https://piccolo-orm.readthedocs.io/en/latest/piccolo/tutorials/using_sqlite_and_asyncio_effectively.html>`_
which explains this in more detail, as well as other tips for using asyncio and
SQLite together effectively.

Thanks to powellnorma and sinisaos for their help with this.

Other changes
~~~~~~~~~~~~~

* Fixed a bug with camelCase column names (we recommend using snake_case, but
sometimes it's unavoidable when using Piccolo with an existing schema).
Thanks to sinisaos for this.
* Fixed a typo in the docs with ``raw`` queries - thanks to StitiFatah for
this.

-------------------------------------------------------------------------------

------

Some big improvements to ``order_by`` clauses.

It's now possible to combine ascending and descending:

.. code-block:: python

await Band.select(
Band.name,
Band.popularity
).order_by(
Band.name
).order_by(
Band.popularity,
ascending=False
)

You can also order by anything you want using ``OrderByRaw``:

.. code-block:: python

from piccolo.query import OrderByRaw

await Band.select(
Band.name
).order_by(
OrderByRaw('random()')
)

-------------------------------------------------------------------------------

------

Added the ``auto_update`` argument to ``Column``. Its main use case is columns
like ``modified_on`` where we want the value to be updated automatically each
time the row is saved.

.. code-block:: python

class Band(Table):
name = Varchar()
popularity = Integer()
modified_on = Timestamp(
null=True,
default=None,
auto_update=datetime.datetime.now
)

The `modified_on` column will automatically be updated to the current
timestamp:
>>> await Band.update({
... Band.popularity: Band.popularity + 100
... }).where(
... Band.name == 'Pythonistas'
... )

It works with ``MyTable.update`` and also when using the ``save`` method on
an existing row.

-------------------------------------------------------------------------------

------

Made improvements to the Piccolo playground.

* Syntax highlighting is now enabled.
* The example queries are now async (iPython supports top level await, so
this works fine).
* You can optionally use your own iPython configuration
``piccolo playground run --ipython_profile`` (for example if you want a
specific colour scheme, rather than the one we use by default).

Thanks to haffi96 for this. See `PR 656 <https://github.com/piccolo-orm/piccolo/pull/656>`_.

-------------------------------------------------------------------------------

------

Fixed a bug with ``MyTable.objects().create()`` and columns which are not
nullable. Thanks to metakot for reporting this issue.

We used to use ``logging.getLogger(__file__)``, but as Drapersniper pointed
out, the Python docs recommend ``logging.getLogger(__name__)``, so it has been
changed.

-------------------------------------------------------------------------------