### Install Piccolo with All Optional Dependencies

Source: https://github.com/piccolo-orm/piccolo/blob/master/README.md

Installs Piccolo ORM and all its optional dependencies for the easiest setup. Run this command in your terminal.

```bash
pip install 'piccolo[all]'
```

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

### Example Queries in Playground

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/changes/index.md

The Piccolo playground now includes example queries demonstrating various functionalities and column types.

```python
# Example queries available in the playground
```

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

### Example piccolo_conf.py configuration

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/projects_and_apps/piccolo_projects.md

This example demonstrates how to configure the database engine (PostgresEngine) and register Piccolo apps using AppRegistry in piccolo_conf.py.

```python
from piccolo.engine.postgres import PostgresEngine


from piccolo.conf.apps import AppRegistry


DB = PostgresEngine(
    config={
        "database": "piccolo_project",
        "user": "postgres",
        "password": "",
        "host": "localhost",
        "port": 5432,
    }
)


APP_REGISTRY = AppRegistry(
    apps=["home.piccolo_app", "piccolo_admin.piccolo_app"]
)
```

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

### Example piccolo_conf_test.py

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/engines/index.md

An example configuration file for testing purposes, specifying a different SQLite database path.

```python
# An example piccolo_conf_test.py
from piccolo.engine.sqlite import SQLiteEngine


DB = SQLiteEngine(path='my_test_db.sqlite')
```

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

### Start a Local CockroachDB Instance

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/contributing/index.md

Use this command to start a single-node CockroachDB instance in memory for testing.

```console
cockroach start-single-node --insecure --store=type=mem,size=2GiB
```

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

### Add Jinja Templates to Setup

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/changes/index.md

Missing Jinja templates have been added to the `setup.py` file.

```python
# Adding missing jinja templates to setup.py
```

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

### Install PostgreSQL on Ubuntu

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/getting_started/setup_postgres.md

Use apt to install PostgreSQL on Ubuntu systems. This command updates the package list and then installs the postgresql package.

```bash
sudo apt update
sudo apt install postgresql
```

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

### Run Piccolo Playground

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/index.md

Start the Piccolo playground to experiment with queries.

```bash
piccolo playground run
```

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

### Example Dockerfile for Piccolo Application

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/tutorials/deployment.md

A basic Dockerfile to build a Piccolo application. It specifies the base image, installs dependencies, copies application code, and sets environment variables for database connection. Ensure your requirements.txt is in the same directory as the Dockerfile.

```dockerfile
# Specify the base image:
FROM python:3.12-bookworm

# Install the pip requirements:
RUN pip install --upgrade pip
ADD app/requirements.txt /
RUN pip install -r /requirements.txt

# Add the application code:
ADD app /app

# Environment variables:
ENV PG_HOST=localhost
ENV PG_PORT=5432
ENV PG_USER=my_database_user
ENV PG_PASSWORD=""
ENV PG_DATABASE=my_database

CMD ["/usr/local/bin/python", "/app/main.py"]
```

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

### Run Migrations for Test Schema Setup

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/testing/index.md

Alternatively, set up your test schema by running migrations forwards and backwards using `run_forwards` and `run_backwards` in your test setup and teardown.

```python
from unittest import IsolatedAsyncioTestCase

from piccolo.apps.migrations.commands.backwards import run_backwards
from piccolo.apps.migrations.commands.forwards import run_forwards


class TestApp(IsolatedAsyncioTestCase):
    async def setUp(self):
        await run_forwards("all")

    async def tearDown(self):
        await run_backwards("all", auto_agree=True)

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

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

### Install Piccolo ORM

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/index.md

Use pip to install Piccolo.

```bash
pip install piccolo
```

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

### Build Model with ModelBuilder

Source: https://github.com/piccolo-orm/piccolo/blob/master/CHANGES.rst

Example of building a model using ModelBuilder. Ensure the Band model is defined.

```python
my_model = await ModelBuilder.build(Band)
```

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

### Instantiate Table with Kwargs in Python

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/changes/index.md

This Python example shows the traditional way to instantiate a Table using keyword arguments.

```python
>>> await Manager(name='Guido').save()
```

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

### Bulk Update Concert Start Dates

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/changes/index.md

Shows how to bulk modify Date and Timestamp columns by adding a timedelta. This example increments the start date of all concerts by one day.

```python
await Concert.update(
    {Concert.starts: Concert.starts + timedelta(days=1)},
    force=True
)
```

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

### Get Piccolo Version

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/projects_and_apps/included_apps.md

Displays the currently installed version of Piccolo.

```bash
piccolo meta version
```

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

### Install Piccolo with uvloop

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/getting_started/installing_piccolo.md

Install Piccolo with uvloop for an alternative event loop implementation. If using Uvicorn, it will automatically set uvloop as the default if installed.

```bash
pip install 'piccolo[uvloop]'
```

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

### Raw SQL ORDER BY Example

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/query_clauses/order_by.md

This SQL demonstrates the equivalent of ordering by a raw expression like `random()`.

```sql
SELECT "band"."name" FROM band ORDER BY random() ASC
```

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

### Install Piccolo with All Features (Windows)

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/getting_started/installing_piccolo.md

On Windows, you may need to use double quotes instead of single quotes when installing Piccolo with specific extras like 'all'.

```bash
pip install "piccolo[all]"
```

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

### Example of counting non-null columns

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/query_types/count.md

Demonstrates counting all rows and rows with non-null popularity using the `Band` table example.

```python
>>> await Band.count()
3
```

```python
>>> await Band.count(Band.popularity)
2
```

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

### Async Query Example

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/changes/index.md

Demonstrates the current async approach for executing queries in Piccolo ORM. Previously, `run_sync()` was used.

```python
await Band.select()
```

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

### BaseUser Fixture Example

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/changes/index.md

Example of a JSON fixture for the `BaseUser` table, showing hashed passwords. This format is used for loading and saving fixtures.

```json
{
  "id": 11,
  "username": "bob",
  "password": "pbkdf2_sha256$10000$abc123",
}
```

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

### Manual Piccolo Migration Example

Source: https://context7.com/piccolo-orm/piccolo/llms.txt

Illustrates how to define and execute manual database migrations in Python using Piccolo's MigrationManager. Includes functions for forward and backward migration steps.

```python
from piccolo.apps.migrations.auto.migration_manager import MigrationManager
from piccolo.table import Table

ID = "2025-07-28T09:51:54:296860"
VERSION = "1.27.1"
DESCRIPTION = "Updating band popularity"

class RawTable(Table):
    pass

async def forwards():
    manager = MigrationManager(migration_id=ID, app_name="my_app", description=DESCRIPTION)

    async def run():
        await RawTable.raw('UPDATE band SET popularity={}', 1000)

    async def run_backwards():
        await RawTable.raw('UPDATE band SET popularity={}', 0)

    manager.add_raw(run)
    manager.add_raw_backwards(run_backwards)
    return manager
```

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

### Start CockroachDB Single Node

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/playground/advanced.md

Initiate a single-node CockroachDB instance for testing purposes. This command should be run with the '--insecure' flag for non-production environments.

```bash
cockroach start-single-node --insecure
```

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

### Install Piccolo with orjson

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/getting_started/installing_piccolo.md

Install Piccolo with orjson for improved JSON serialization performance. This is an optional performance enhancement.

```bash
pip install 'piccolo[orjson]'
```

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

### Install Piccolo with SQLite Driver

Source: https://github.com/piccolo-orm/piccolo/blob/master/README.md

Installs Piccolo ORM with the driver for SQLite. Execute this command in your terminal.

```bash
pip install 'piccolo[sqlite]'
```

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

### Configure AppConfig with Pathlib Path

Source: https://github.com/piccolo-orm/piccolo/blob/master/CHANGES.rst

Example of configuring AppConfig using a pathlib.Path instance for the migrations folder path. This provides more flexibility in path handling.

```python
import pathlib
from piccolo.conf.apps import AppConfig

APP_CONFIG = AppConfig(
    app_name="blog",
    migrations_folder_path=pathlib.Path(__file__) /  "piccolo_migrations"
)
```

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

### Unit test setup with get_table_classes

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/changes/index.md

Use `Finder().get_table_classes()` to retrieve all `Table` classes for a project, simplifying the setup and teardown of database schemas in unit tests. Ensure `piccolo tester run` is used for tests.

```python
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
```

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

### Install Piccolo with PostgreSQL Driver

Source: https://github.com/piccolo-orm/piccolo/blob/master/README.md

Installs Piccolo ORM along with the necessary driver for PostgreSQL. This command should be run in your terminal.

```bash
pip install 'piccolo[postgres]'
```

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

### Connect to CockroachDB SQL CLI

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/getting_started/setup_cockroach.md

Connect to the CockroachDB SQL command-line interface. Ensure you are in the directory where CockroachDB was installed.

```bash
cd ~/wherever/you/installed/cockroachdb
cockroach sql --insecure
```

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

### Get First Matching Record or None

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/changes/index.md

The `get` convenience method returns the first matching record or `None` if no record is found. This is equivalent to using `.where().first().run()`.

```python
manager = await Manager.objects().get(Manager.name == 'Guido').run()
```

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

### Get or Create Convenience Method

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/changes/index.md

The `get_or_create` convenience method simplifies the process of retrieving a record or creating it if it doesn't exist.

```python
manager = await Manager.objects().get_or_create(
    Manager.name == 'Guido'
).run()
```

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

### Typing and Auto-completion Examples

Source: https://github.com/piccolo-orm/piccolo/blob/master/CHANGES.rst

Illustrates Piccolo ORM's improved typing and auto-completion support for common query operations.

```python
>>> bands = await Band.objects()  # List[Band]
```

```python
>>> band = await Band.objects().first()  # Optional[Band]
```

```python
>>> bands = await Band.select().output(as_json=True)  # str
```

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

### Instantiate Table with Dictionary in Python

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/changes/index.md

This Python example shows a new way to instantiate a Table by passing column values as a dictionary, which improves static typing analysis.

```python
>>> await Manager({Manager.name: 'Guido'}).save()
```

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

### Full example: Booking tickets with row locking

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/query_clauses/lock_rows.md

This example demonstrates how to use lock_rows within a transaction to ensure that ticket availability is accurately managed, preventing race conditions when multiple users book tickets simultaneously.

```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!")
```

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

### Query String Operators Example

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/changes/index.md

Demonstrates using arithmetic operators like multiplication within QueryString for more complex calculations on columns.

```python
>>> await Ticket.select(Round(Ticket.price) * 2)
[{'price': 100.0}]
```

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

### Handling Foreign Keys in Table Creation

Source: https://github.com/piccolo-orm/piccolo/blob/master/CHANGES.rst

Illustrates a scenario with multiple tables and foreign key relationships that previously caused migration failures. This example shows the structure that led to the bug fix.

```python
from piccolo.table import Table
from piccolo.columns import ForeignKey

class TableA(Table):
    pass

class TableB(Table):
    fk = ForeignKey(TableA)

class TableC(Table):
    fk = ForeignKey(TableB)

class TableD(Table):
    fk = ForeignKey(TableC)

class TableE(Table):
    fk = ForeignKey(TableD)
```

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

### Create and Drop Database Tables for Tests

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/testing/index.md

Use `create_db_tables` and `drop_db_tables` within your test setup and teardown methods to manage the test database schema. Synchronous equivalents are also available.

```python
from unittest import IsolatedAsyncioTestCase

from piccolo.table import create_db_tables, drop_db_tables
from piccolo.conf.apps import Finder


TABLES = Finder().get_table_classes()


class TestApp(IsolatedAsyncioTestCase):
    async def setUp(self):
        await create_db_tables(*TABLES)

    async def tearDown(self):
        await drop_db_tables(*TABLES)

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

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

### Configure Database with Extra Nodes

Source: https://github.com/piccolo-orm/piccolo/blob/master/CHANGES.rst

Example of configuring a database engine with extra nodes, such as a read replica. This allows running queries on different database nodes.

```python
from piccolo.engine import PostgresEngine

DB = PostgresEngine(
    config={'database': 'main_db'},
    extra_nodes={
        'read_replica_1': PostgresEngine(
            config={
                'database': 'main_db',
                'host': 'read_replica_1.my_db.com'
            }
        )
    }
)
```

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

### Extract Year from Concert Start Date

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/functions/datetime.md

Use the Year function to extract the year from a datetime field. This example demonstrates extracting the start year from a Concert model.

```python
>>> from piccolo.query.functions import Year
>>> await Concert.select(
...     Year(Concert.starts, alias="start_year")
... )
[{"start_year": 2024}]
```

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

### Configure PostgresEngine

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/engines/postgres_engine.md

Instantiate PostgresEngine with connection configuration. The config dictionary is passed directly to asyncpg.

```python
from piccolo.engine.postgres import PostgresEngine


DB = PostgresEngine(config={
    'host': 'localhost',
    'database': 'my_app',
    'user': 'postgres',
    'password': ''
})
```

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

### Configure SQLiteEngine

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/engines/sqlite_engine.md

Instantiate the SQLiteEngine by providing the path to the database file. The file will be created if it does not exist.

```python
from piccolo.engine.sqlite import SQLiteEngine


DB = SQLiteEngine(path='my_app.sqlite')
```

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

### Secret columns example

Source: https://github.com/piccolo-orm/piccolo/blob/master/CHANGES.rst

Demonstrates the use of secret columns, which can be applied to any column type, not just the Secret column type.

```python
# All column types can now be secret.
# For example, a Varchar column can be secret:
# secret_column = Varchar(secret=True)
```

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

### Update with Datetime Arithmetic

Source: https://context7.com/piccolo-orm/piccolo/llms.txt

Update the 'starts' timestamp by adding one day to it. This example shows how to perform datetime arithmetic in updates.

```python
# Update with datetime arithmetic
import datetime
await Concert.update({
    Concert.starts: Concert.starts + datetime.timedelta(days=1)
}, force=True)
```

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

### Define Piccolo Tables and ForeignKey

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/tutorials/avoiding_circular_imports.md

Example of defining Piccolo Table classes with a ForeignKey referencing another table by string name. This setup can lead to circular imports if not managed carefully, especially when creating Pydantic models before all tables are defined.

```python
# tables.py

from piccolo.columns import ForeignKey, Varchar
from piccolo.table import Table
from piccolo.utils.pydantic import create_pydantic_model


class Band(Table):
    name = Varchar()
    # This automatically gets converted into a LazyTableReference, because a
    # string is passed in:
    manager = ForeignKey("Manager")


# This is not recommended, as it will cause the LazyTableReference to be
# evaluated before Manager has imported.
# Instead, move this to a separate file, or below Manager.
BandModel = create_pydantic_model(Band)


class Manager(Table):
    name = Varchar()
```

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

### Configure AppConfig using table_finder

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/projects_and_apps/piccolo_apps.md

Use table_finder to automatically import Table subclasses from specified modules, simplifying AppConfig setup.

```python
from piccolo.conf.apps import table_finder

APP_CONFIG = AppConfig(
    app_name='blog',
    migrations_folder_path=os.path.join(
        CURRENT_DIRECTORY,
        'piccolo_migrations'
    ),
    table_classes=table_finder(modules=['blog.tables']),
    migration_dependencies=[],
    commands=[]
)
```

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

### Select columns from nested relationships using all_columns

Source: https://github.com/piccolo-orm/piccolo/blob/master/CHANGES.rst

This example demonstrates selecting columns from nested relationships, specifically the `name` of the `venue` and all columns from the manager of `band_1`. It shows how `all_columns` can be unpacked.

```python
Concert.select(
    Concert.venue.name,
    *Concert.band_1.manager.all_columns()
).run_sync()
```

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

### SQLite Transaction Type Example

Source: https://github.com/piccolo-orm/piccolo/blob/master/CHANGES.rst

Demonstrates how to specify an IMMEDIATE transaction type for SQLite, which can help prevent 'database locked' errors in concurrent scenarios.

```text
2021-09-06T13-58-23-024723.py
```

```text
my_app_2021_09_06T13_58_23_024723.py
```

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

### Create app in a subfolder

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/projects_and_apps/piccolo_apps.md

Use the --root option to specify a subfolder for your new app's files.

```bash
piccolo app new my_app --register --root=./apps
```

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

### Defining tables with foreign keys in Piccolo

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/changes/index.md

This example demonstrates how to define multiple tables with foreign key relationships between them. This was relevant for a bug fix in migration handling.

```python
class TableA(Table):
    pass

class TableB(Table):
    fk = ForeignKey(TableA)

class TableC(Table):
    fk = ForeignKey(TableB)

class TableD(Table):
    fk = ForeignKey(TableC)

class TableE(Table):
    fk = ForeignKey(TableD)
```

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

### Install orjson for Piccolo

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/changes/index.md

orjson is an optional dependency for improved JSON serialization. Install it with the [orjson] extra.

```bash
pip install piccolo[orjson]
```

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

### Fake apply initial migration

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/tutorials/migrate_existing_project.md

Since the tables already exist in an existing project, use `--fake` to mark the initial migration as applied without actually running SQL.

```bash
piccolo migrations forwards my_app --fake
```

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

### Get or create with prefetch in Piccolo

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/changes/index.md

Demonstrates the corrected behavior of `get_or_create` when used with `prefetch`. Prefetching now works as expected even when a new row is being created.

```python
>>> band = Band.objects(Band.manager).get_or_create(
...     (Band.name == "New Band 2") & (Band.manager == 1)
... )

>>> band.manager
<Manager: 1>
>>> band.manager.name
"Mr Manager"
```

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

### Define Custom Column Subclass

Source: https://github.com/piccolo-orm/piccolo/blob/master/CHANGES.rst

Example of creating a custom Column subclass. This was a fix for migrations failing when custom column subclasses were used.

```python
from piccolo.columns.column_types import Varchar

class CustomColumn(Varchar):
    def __init__(self, custom_arg: str = '', *args, **kwargs):
        self.custom_arg = custom_arg
        super().__init__(*args, **kwargs)

    @property
    def column_type(self):
        return 'VARCHAR'
```

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

### Execute Select Query with Callback in Python

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/changes/index.md

This Python example shows how to attach a callback function to a select query. The callback is executed upon successful query completion and can modify the output.

```python
>>> await Band.select().callback(my_callback)
```

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

### Basic Piccolo Queries in Playground

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/getting_started/playground.md

Execute these example queries in the Piccolo playground to test basic data retrieval operations on the 'Band' table. Tab completion is recommended for discovering available columns and query types.

```python
await Band.select()
```

```python
await Band.objects()
```

```python
await Band.select(Band.name)
```

```python
await Band.select(Band.name, Band.manager.name)
```

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

### Define Database with Extra Read Replica Node

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/changes/index.md

Example of configuring a PostgresEngine with an additional read replica node. This allows directing queries to specific nodes.

```python
DB = PostgresEngine(
    config={'database': 'main_db'},
    extra_nodes={
        'read_replica_1': PostgresEngine(
            config={
                'database': 'main_db',
                'host': 'read_replica_1.my_db.com'
            }
        )
    }
)
```

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

### Filter Bands by Concert Start Date using Subquery

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/changes/index.md

This Python snippet demonstrates how to use subqueries within `is_in` to filter `Band` records based on concert start dates. It selects bands that have concerts starting on or after January 1st, 2025.

```python
await Band.select().where(
    Band.id.is_in(
        Concert.select(Concert.band_1).where(
            Concert.starts >= datetime.datetime(year=2025, month=1, day=1)
        )
    )
)
```

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

### Run Piccolo Migrations

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/authentication/baseuser.md

Run migrations to create the BaseUser table.

```bash
piccolo migrations forwards user
```

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

### Run Piccolo Playground with Postgres

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/playground/advanced.md

Connect to a local Postgres database for the Piccolo playground. Ensure the database is set up with the default credentials or specify custom ones.

```bash
piccolo playground run --engine=postgres
```

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

### SELECT - Basic Usage

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/query_types/select.md

Demonstrates how to select all columns or specific columns from a table using the select method. It also shows how to use an alias for the table.

```APIDOC
## SELECT - Basic Usage

### Description
This section covers the basic usage of the `select` method to retrieve data from a table. You can select all columns or specify individual columns, and optionally use table aliases.

### Method
SELECT (implicitly via ORM method)

### Endpoint
N/A (ORM method)

### Parameters
None directly for the `select` method itself, but it operates on ORM models.

### Request Example
```python
# Select all columns
await Band.select()

# Select specific columns
await Band.select(Band.name)

# Using a table alias
b = Band
await b.select(b.name)
```

### Response
#### Success Response (200)
- **List of Dictionaries**: Each dictionary represents a row with selected columns as keys.

#### Response Example
```json
[
  {'id': 1, 'name': 'Pythonistas', 'manager': 1, 'popularity': 1000},
  {'id': 2, 'name': 'Rustaceans', 'manager': 2, 'popularity': 500}
]
```

```json
[{'name': 'Rustaceans'}, {'name': 'Pythonistas'}]
```
```

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

### Piccolo LazyTableReference Example

Source: https://github.com/piccolo-orm/piccolo/blob/master/CHANGES.rst

Example showing how Piccolo's ModelBuilder works with LazyTableReference for handling circular foreign key references.

```python
class Band(Table):
    manager = ForeignKey(
        LazyTableReference(
            'Manager',
            module_path='some.other.folder.tables'
        )
    )
```

```python

```

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

### Use WhereRaw for Raw SQL in WHERE Clause

Source: https://github.com/piccolo-orm/piccolo/blob/master/CHANGES.rst

Example of using WhereRaw to include raw SQL in a WHERE clause. TRIM(name) is used here as an example.

```python
Band.select().where(WhereRaw('TRIM(name) = {}', 'Pythonistas'))
```

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

### Create New App with Root Override

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/changes/index.md

Use the `root` argument with `piccolo app new` to specify where new app files should be placed.

```bash
piccolo app new --root /path/to/app
```

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

### Define Concert Table for Distinct Count Example

Source: https://github.com/piccolo-orm/piccolo/blob/master/CHANGES.rst

Definition of the Concert table used in the distinct count examples. Includes band and start_date columns.

```python
class Concert(Table):
    band = Varchar()
    start_date = Date()
```

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

### Create and save a new object

Source: https://context7.com/piccolo-orm/piccolo/llms.txt

Instantiate a new object and save it to the database using `save()`, or use the `create()` shortcut for a more concise approach.

```python
# Create and save object
band = Band(name="C-Sharps", popularity=100)
await band.save()
```

```python
# Or use create shortcut
band = await Band.objects().create(name="C-Sharps", popularity=100)
```

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

### Create New Project with Root Override

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/changes/index.md

Use the `root` argument with `piccolo project new` to specify where new project files should be placed.

```bash
piccolo project new --root /path/to/project
```

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

### Build and Serve Piccolo Documentation Locally

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/contributing/index.md

Commands to build the Sphinx documentation and serve it locally for development. The docs will auto-rebuild on changes.

```bash
make html
```

```bash
./scripts/run-docs.sh
```

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

### Example of import clash in migrations

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/changes/index.md

This example demonstrates a potential import clash scenario in migration files where a custom import conflicts with a Piccolo column type. Piccolo now attempts to detect and prevent these.

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

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

### Connect to PostgreSQL as postgres user on Ubuntu

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/getting_started/setup_postgres.md

Connect to the PostgreSQL server on Ubuntu by first switching to the postgres user and then launching psql. This is often required for administrative tasks.

```bash
sudo su postgres -c psql
```

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

### Create new record (Django vs Piccolo)

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/query_types/django_comparison.md

Demonstrates the process of creating a new record. Piccolo requires calling `.save().run_sync()` to persist the changes.

```python
# Django
>>> band = Band(name="Pythonistas")
>>> band.save()
>>> band
<Band: 1>
```

```python
# Piccolo
>>> band = Band(name="Pythonistas")
>>> band.save().run_sync()
>>> band
<Band: 1>
```

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

### Table Finder Exclude Imported Option

Source: https://github.com/piccolo-orm/piccolo/blob/master/CHANGES.rst

Configuration example for `table_finder` with the `exclude_imported` option.

```python
APP_CONFIG = AppConfig(

```

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

### Run Piccolo Playground with Custom IPython Profile

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/playground/advanced.md

Launch the Piccolo playground using a custom iPython profile located in '~/.ipython'. This allows for personalized iPython configurations.

```bash
piccolo playground run --ipython_profile
```

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

### Fetch All Rows

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/query_types/objects.md

Retrieve all rows from a table as a list of objects. This is the most basic way to get data.

```python
await Band.objects()
[<Band: 1>, <Band: 2>, <Band: 3>]
```

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

### Configure CockroachEngine

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/engines/cockroach_engine.md

Instantiate the CockroachEngine with connection details. The config dictionary is passed directly to the asyncpg adapter.

```python
# piccolo_conf.py
from piccolo.engine.cockroach import CockroachEngine


DB = CockroachEngine(config={
    'host': 'localhost',
    'database': 'piccolo',
    'user': 'root',
    'password': '',
    'port': '26257',
})

```

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

### Fake a Migration

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/migrations/running.md

Record a migration as ran without executing its code. This is useful when starting with an existing database.

```bash
piccolo migrations forwards my_app 2022-09-04T19:44:09 --fake
```

```python
async def forwards():
    manager = MigrationManager(
        migration_id=ID,
        app_name="app",
        description=DESCRIPTION,
        fake=True
    )
    ...
```

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

### Create CockroachDB Playground Database

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/playground/advanced.md

Set up the 'piccolo_playground' database in CockroachDB. This command drops the existing database if it exists and then creates a new one.

```bash
cockroach sql --insecure --execute="DROP DATABASE IF EXISTS piccolo_playground CASCADE;CREATE DATABASE piccolo_playground;"
```

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

### Table Definition with Excluded Import

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/changes/index.md

Example of a `tables.py` module where `BaseUser` is imported but excluded from `table_finder` by `exclude_imported=True`.

```python
# 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)
```

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

### JOIN ON with Subquery Example

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/query_clauses/where.md

An alternative to using .is_in() with a subquery, this demonstrates filtering based on a joined condition.

```python
await Band.select().where(
    Band.id.join_on(Concert.band_1).starts >= datetime.datetime(
       year=2025, month=1, day=1
    )
)
```

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

### Run Raw SQL in Migration - Python

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/migrations/create.md

This example demonstrates how to execute raw SQL queries within a Piccolo migration for both forward and backward operations. It uses a dummy RawTable class to facilitate SQL execution.

```python
from piccolo.apps.migrations.auto.migration_manager import MigrationManager
from piccolo.table import Table


ID = "2025-07-28T09:51:54:296860"
VERSION = "1.27.1"
DESCRIPTION = "Updating each band's popularity"


# This is just a dummy table we use to execute raw SQL with:
class RawTable(Table):
    pass


async def forwards():
    manager = MigrationManager(
        migration_id=ID,
        app_name="my_app",
        description=DESCRIPTION
    )

    #############################################################
    # This will get run when using `piccolo migrations forwards`:

    async def run():
        await RawTable.raw('UPDATE band SET popularity={}', 1000)

    manager.add_raw(run)

    #############################################################
    # If we want to run some code when reversing the migration,
    # using `piccolo migrations backwards`:

    async def run_backwards():
        await RawTable.raw('UPDATE band SET popularity={}', 0)

    manager.add_raw_backwards(run_backwards)

    #############################################################
    # We must always return the MigrationManager:

    return manager

```

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

### Piccolo CLI: Create Admin with Allowed Hosts

Source: https://github.com/piccolo-orm/piccolo/blob/master/CHANGES.rst

When creating an admin interface using the ASGI template, you can specify allowed hosts.

```python
create_admin(allowed_hosts=['example.com'])
```

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

### Get or Create with Complex Where Clauses

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/changes/index.md

The `get_or_create` method now supports more complex where clauses, allowing for combined conditions.

```python
instance = (
    Band.objects()
    .get_or_create(
        (Band.name == "My new band")
        & (Band.manager.name == "Excellent manager")
    )
    .run_sync()
)
```

```python
row = await Band.objects().get_or_create(
    (Band.name == 'Pythonistas') & (Band.popularity == 1000)
).run()
```

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

### Set default value for Enum column

Source: https://github.com/piccolo-orm/piccolo/blob/master/CHANGES.rst

Example of setting a default value for an Enum column in a Varchar type.

```python
Varchar(default=Colour.red)
```

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

### Run Piccolo Playground with CockroachDB

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/playground/advanced.md

Connect to a local CockroachDB database for the Piccolo playground. The database should be configured with the default credentials.

```bash
piccolo playground run --engine=cockroach
```

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

### Create and apply new migrations

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/tutorials/migrate_existing_project.md

After modifying `Table` definitions in `tables.py`, generate a new migration and then apply it to the database.

```bash
piccolo migrations new my_app --auto
piccolo migrations forwards my_app
```

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

### Aggregate Functions - Count

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/query_types/select.md

How to use the `Count` aggregate function to get the number of rows that match specific criteria.

```APIDOC
## Aggregate Functions - Count

### Description
The `Count` aggregate function returns the number of rows that satisfy the query conditions. It can be used independently or in conjunction with `group_by`.

### Method
SELECT (implicitly via ORM method)

### Endpoint
N/A (ORM method)

### Parameters
- **Count()**: The aggregate function itself.
- **where clause**: Optional conditions to filter rows before counting.

### Request Example
```python
from piccolo.query.functions.aggregate import Count

# Count rows where popularity is greater than 100
await Band.select(Count()).where(Band.popularity > 100)
```

### Response
#### Success Response (200)
- **List of Dictionaries**: Contains a single key 'count' with the total number of matching rows.

#### Response Example
```json
[{'count': 3}]
```
```

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

### Programmatic User Authentication with Piccolo

Source: https://context7.com/piccolo-orm/piccolo/llms.txt

Demonstrates how to programmatically create, authenticate, and update users using Piccolo's `BaseUser` table. Also shows how to extend `BaseUser` with custom profile information.

```python
from piccolo.apps.user.tables import BaseUser

# Create user programmatically
user = await BaseUser.create_user(
    username="bob",
    password="abc123XYZ",
    active=True
)

# Authenticate user
>>> user_id = await BaseUser.login(username="bob", password="abc123XYZ")
>>> user_id
1  # Returns user id on success, None on failure

# Update password
await BaseUser.update_password(user="bob", password="new_password123")
```

```python
from piccolo.table import Table
from piccolo.columns import ForeignKey, Varchar, Text

# Extend BaseUser with profile
class Profile(Table):
    user = ForeignKey(BaseUser)
    phone_number = Varchar()
    bio = Text()
```

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

### Get First Band Object

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/query_clauses/first.md

Retrieves the first band object from the database. Returns `None` if no band is found.

```python
>>> await Band.objects().first()
<Band: 1>
```

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

### Load data from a JSON column

Source: https://github.com/piccolo-orm/piccolo/blob/master/CHANGES.rst

Example of selecting data and loading JSON columns as Python dictionaries using `load_json=True`.

```python
RecordingStudio.select().output(load_json=True).run_sync()
```

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

### Create New ASGI App with Name

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/changes/index.md

The `piccolo asgi new` command now accepts a `name` argument to set the default database name in the template.

```bash
piccolo asgi new my_app --name my_database
```

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

### Create Users via Command Line

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/changes/index.md

The `piccolo user create` command now supports passing arguments directly on the command line, such as `--username=bob`, for script-based user creation.

```bash
piccolo user create --username=bob ...
```

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

### Connect to PostgreSQL using psql

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/getting_started/setup_postgres.md

Connect to the PostgreSQL server using the psql command-line client. This is typically run as the postgres user.

```bash
psql
```

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

### Piccolo JSONB Column Example

Source: https://context7.com/piccolo-orm/piccolo/llms.txt

Defines a Piccolo table with a JSONB column for storing and querying structured JSON data.

```python
from piccolo.table import Table
from piccolo.columns import JSONB, Varchar

class RecordingStudio(Table):
    name = Varchar()
    facilities = JSONB()
```

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

### Conditional WHERE Clauses

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/query_clauses/where.md

Build queries dynamically by adding WHERE clauses conditionally, for example, based on user input or application logic.

```python
async def get_band_names(only_popular_bands: bool) -> list[str]:
    query = Band.select(Band.name).output(as_list=True)

    if only_popular_bands:
        query = query.where(Band.popularity >= 1000)

    return await query
```

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

### Get Related Object using get_related with join_on

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/schema/one_to_one.md

Use `get_related` with `join_on` to fetch the associated object from the related table.

```python
fan_club = await band.get_related(Band.id.join_on(FanClub.band))
```

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

### Use Table Classes in Migration - Python

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/migrations/create.md

This migration example shows how to use existing Table classes from your project to perform updates. It's important to be cautious as changes to the Table class can break old migrations.

```python
from piccolo.apps.migrations.auto.migration_manager import MigrationManager

# We're importing a table from our project:
from music.tables import Band


ID = "2025-07-28T09:51:54:296860"
VERSION = "1.27.1"
DESCRIPTION = "Updating each band's popularity"


async def forwards():
    manager = MigrationManager(
        migration_id=ID,
        app_name="my_app",
        description=DESCRIPTION
    )

    async def run():
        await Band.update({Band.popularity: 1000}, force=True)

    manager.add_raw(run)
    return manager

```

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

### Create Piccolo Database in CockroachDB

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/contributing/index.md

Connect to CockroachDB and create the 'piccolo' database required for testing.

```sql
create database piccolo;
use piccolo;
```

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

### Accessing the Engine from a Table

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/query_types/transactions.md

Get the database engine associated with a table. This is useful for performing database operations like transactions.

```python
DB = Band._meta.db
```

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

### Get First Band Record

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/query_clauses/first.md

Retrieves the first band record from the database as a dictionary. Returns `None` if no band is found.

```python
>>> await Band.select().first()
{'name': 'Pythonistas', 'manager': 1, 'popularity': 1000, 'id': 1}
```

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

### Run Query on a Specific Database Node

Source: https://github.com/piccolo-orm/piccolo/blob/master/CHANGES.rst

Example of running a query on a specific database node, identified by its name in the extra_nodes configuration.

```python
>>> await MyTable.select().run(node="read_replica_1")
```

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

### Piccolo ORM: Async and Sync Usage Patterns

Source: https://context7.com/piccolo-orm/piccolo/llms.txt

Illustrates the different ways to execute Piccolo ORM queries, highlighting the preferred async pattern and the `run_sync` method for synchronous execution.

```python
# Async usage (preferred)
async def get_bands():
    return await Band.select()
```

```python
# Sync usage with run_sync
bands = Band.select().run_sync()
```

```python
# Direct await (shorthand)
bands = await Band.select()
```

```python
# Equivalent to
bands = await Band.select().run()
```

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

### Define Manager and Band Tables

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/getting_started/example_schema.md

Defines the basic Manager and Band tables using Piccolo ORM. These are commonly used in examples.

```python
from piccolo.table import Table
from piccolo.columns import ForeignKey, Integer, Varchar


class Manager(Table):
    name = Varchar(length=100)


class Band(Table):
    name = Varchar(length=100)
    manager = ForeignKey(references=Manager)
    popularity = Integer()
```

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

### Fetch First Row

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/query_types/objects.md

Get the first row that matches the filter criteria, or `None` if no rows are found. Use the `first` clause.

```python
await Band.objects().where(Band.name == 'Pythonistas').first()
<Band: 1>
```

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

### Create Table

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/query_types/create_table.md

Creates a table and its columns in the database. Use migrations for schema changes instead of manual alteration.

```python
await Band.create_table()
```

```python
await Band.create_table(if_not_exists=True)
```

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

### Count Distinct Concert Dates

Source: https://github.com/piccolo-orm/piccolo/blob/master/CHANGES.rst

Example demonstrating how to count the number of unique concert dates using the count() method with the distinct parameter.

```python
>>> await Concert.count(distinct=[Concert.start_date])
    2
```

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

### Configure PostgreSQL Database Engine

Source: https://context7.com/piccolo-orm/piccolo/llms.txt

Configure the database connection for PostgreSQL using PostgresEngine. Ensure the connection details are correct.

```python
from piccolo.engine.postgres import PostgresEngine

DB = PostgresEngine(config={
    'host': 'localhost',
    'database': 'my_app',
    'user': 'postgres',
    'password': 'secret'
})
```

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

### Prefetch Related Objects

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/changes/index.md

Prefetch related objects to efficiently retrieve nested data. This example shows prefetching a `Manager` object for a `Band`.

```python
band = await Band.objects(Band.manager).run()
>>> band.manager
<Manager: 1>
```

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

### Create a new Piccolo project

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/tutorials/migrate_existing_project.md

Use this command to create a new Piccolo project structure. Recommended for new users to understand Piccolo's workflow.

```bash
mkdir test_project
cd test_project
piccolo asgi new
```

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

### Async Query Syntax in Piccolo

Source: https://github.com/piccolo-orm/piccolo/blob/master/CHANGES.rst

Adopt the `await Band.select()` syntax for async queries, which is simpler and preferred over `await Band.select().run()` for newcomers.

```python
# Previous:
Band.select().run_sync()

# Now:
await Band.select()
```

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

### Select data from a Piccolo Table

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/tutorials/migrate_existing_project.md

Example of how to perform a basic SELECT query using Piccolo ORM. Ensure `MyTable` is defined in `my_app/tables.py`.

```python
from my_app.tables import MyTable

async def my_endpoint():
    data = await MyTable.select()
    return data
```

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

### Nested Output for Select Queries

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/changes/index.md

Use the `nested=True` option in select queries to get a response with nested dictionaries, useful for related data.

```python
await Band.select(Band.name, *Band.manager.all_columns()).output(nested=True).run()
```

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

### Dump Fixtures to File

Source: https://github.com/piccolo-orm/piccolo/blob/master/CHANGES.rst

Command to dump fixtures and redirect the formatted JSON output to a file.

```bash
piccolo fixtures dump > my_fixture.json
```

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

### Select distinct values

Source: https://context7.com/piccolo-orm/piccolo/llms.txt

Retrieve unique values for specified columns using the `distinct()` clause. This is useful for getting a list of unique entries.

```python
# Distinct
await Band.select(Band.name).distinct()
```

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

### Create New ASGI Web App with Piccolo

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/index.md

Generate a new ASGI web application using Piccolo.

```bash
piccolo asgi new
```

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

### Get Related Object using Foreign Key

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/schema/one_to_one.md

Retrieve a related object by querying the referencing table using the foreign key column.

```python
band = await Band.objects().where(Band.name == "Pythonistas").first()
```

```python
fan_club = await FanClub.objects().where(FanClub.band == band).first()
```

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

### Create a new Piccolo app

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/projects_and_apps/piccolo_apps.md

Use the Piccolo CLI to generate a new app. The --register flag automatically adds the app to your APP_REGISTRY.

```bash
piccolo app new my_app --register
```

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

### Run Piccolo Performance Profiling

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/contributing/index.md

Execute the profiling script to gather performance data. Ensure the 'piccolo_profile' database exists.

```bash
./scripts/profile.sh
```

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

### Create Object Shortcut

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/query_types/objects.md

A concise method to create and save a new object in a single line, passing column values as keyword arguments.

```python
band = await Band.objects().create(name="C-Sharps", popularity=100)
```

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

### First Method Returns None

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/changes/index.md

The `.first()` method now returns `None` if no matching record is found, improving behavior for auto migration fixes.

```python
# .first() now returns None if no match is found
```

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

### Run Migrations Forwards

Source: https://github.com/piccolo-orm/piccolo/blob/master/docs/src/piccolo/migrations/running.md

Execute the forwards function for a specific app. Use 'all' to run migrations for all apps.

```bash
piccolo migrations forwards my_app
```

```bash
piccolo migrations forwards all
```