Piccolo

------

Python 3.13 is now officially supported.

``JSON`` / ``JSONB`` querying has been significantly improved. For example, if
we have this table:

.. code-block:: python

class RecordingStudio(Table):
facilities = JSONB()

And the ``facilities`` column contains the following JSON data:

.. code-block:: python

{
"technicians": [
{"name": "Alice Jones"},
{"name": "Bob Williams"},
]
}

We can get the first technician name as follows:

.. code-block:: python

>>> await RecordingStudio.select(
... RecordingStudio.facilities["technicians"][0]["name"].as_alias("name")
... ).output(load_json=True)
[{'name': 'Alice Jones'}, ...]

``TableStorage`` (used for dynamically creating Piccolo ``Table`` classes from
an existing database) was improved, to support a Dockerised version of Piccolo
Admin, which is coming soon.

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

------

Postgres 17 is now officially supported.

Fixed a bug with joins, when a ``ForeignKey`` column had ``db_column_name``
specified. Thanks to jessemcl-flwls for reporting this issue.

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

------

``get_related`` now works multiple layers deep:

.. code-block:: python

concert = await Concert.objects().first()
manager = await concert.get_related(Concert.band_1._.manager)

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

------

Fixed a bug with the ``get_m2m`` method, which would raise a ``ValueError``
when no objects were found. It now handles this gracefully and returns an empty
list instead. Thanks to nVitius for this fix.

Improved the ASGI templates (including a fix for the latest Litestar version).
Thanks to sinisaos for this.

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

------

Added support for row locking (i.e. ``SELECT ... FOR UPDATE``).

For example, if we have this table:

.. code-block:: python

class Concert(Table):
name = Varchar()
tickets_available = Integer()

And we want to make sure that ``tickets_available`` never goes below 0, we can
do the following:

.. code-block:: python

async def book_tickets(ticket_count: int):
async with Concert._meta.db.transaction():
concert = await Concert.objects().where(
Concert.name == "Awesome Concert"
).first().lock_rows()

if concert.tickets_available >= ticket_count:
await concert.update_self({
Concert.tickets_available: Concert.tickets_available - ticket_count
})
else:
raise ValueError("Not enough tickets are available!")

This means that when multiple transactions are running at the same time, it
isn't possible to book more tickets than are available.

Thanks to dkopitsa for adding this feature.

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

------

``update_self``
~~~~~~~~~~~~~~~

Added the ``update_self`` method, which is an alternative to the ``save``
method. Here's an example where it's useful:

.. code-block:: python

If we have a band object:
>>> band = await Band.objects().get(name="Pythonistas")
>>> band.popularity
1000

We can increment the popularity, based on the current value in the
database:
>>> await band.update_self({
... Band.popularity: Band.popularity + 1
... })

The new value is set on the object:
>>> band.popularity
1001

It's safer than using the `save` method, because the popularity value on
the object might be out of date with what's in the database:
band.popularity += 1
await band.save()

Thanks to trondhindenes for suggesting this feature.

Batch raw queries
~~~~~~~~~~~~~~~~~

The ``batch`` method can now be used with ``raw`` queries. For example:

.. code-block:: python

async with await MyTable.raw("SELECT * FROM my_table").batch() as batch:
async for _batch in batch:
print(_batch)

This is useful when you expect a raw query to return a lot of data.

Thanks to devsarvesh92 for suggesting this feature.

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