# Write Python Particles

Define typed Python functions, aggregates and window functions, call them from SQL, and handle nulls, errors and dependencies.

Source: https://particledb.ai/particles/python/

Start with the requirements, the complete function and the loading instructions in [your first Particle](/particles/overview/). Every example on this page loads with `--python-functions` on the standard free-edition server.

## Inputs and outputs

Annotate each argument and the return value. `str`, `int`, `float` and `bool` map to SQL text, integer, double precision and boolean. Save this as `kinds.py`:

```python
from particledb import scalar, aggregate, window

@scalar
def shout(value: str) -> str:
    return value.upper() + "!"

@scalar
def add_one(n: int) -> int:
    return n + 1

@scalar
def is_even(n: int) -> bool:
    return n % 2 == 0

@scalar(called_on_null=True)
def label_or_missing(value: str) -> str:
    return "missing" if value is None else value

@aggregate
def spread(values: list[float]) -> float:
    return max(values) - min(values)

@window
def running_total(values: list[float]) -> list[float]:
    totals, total = [], 0.0
    for value in values:
        total += value
        totals.append(total)
    return totals

@scalar
def checked_ratio(n: int) -> int:
    raise ValueError("ratio input rejected")
```

Load it beside `tools.py` by repeating the flag: `--python-functions ./tools.py --python-functions ./kinds.py`.

```sql
SELECT shout('hi') AS shouted, add_one(41) AS next, is_even(4) AS even;
```

The row is `HI!`, `42` and `true`. Keep each function name distinct from SQL built-ins; see [names and errors](/particles/collisions/).

## Null inputs

By default a scalar function is strict: it returns SQL NULL without calling Python when an input is NULL.

```sql
SELECT priority(NULL, 0.8) AS score;
```

With `called_on_null=True`, Python receives `None` instead:

```sql
SELECT label_or_missing(NULL) AS label;
```

The result is `missing`.

## Aggregates and window functions

An `@aggregate` function receives the values of one group as a list and returns one value. A `@window` function receives the partition's values in the window's `ORDER BY` order and returns a list of the same length.

```sql
CREATE TABLE particle_readings (sensor int, reading float8, seq int);
INSERT INTO particle_readings VALUES (1, 1.0, 1), (1, 4.0, 2), (2, 2.0, 1), (2, 7.5, 2), (2, 3.0, 3);
SELECT sensor, spread(reading) AS spread FROM particle_readings GROUP BY sensor ORDER BY sensor;
SELECT sensor, seq, running_total(reading) OVER (PARTITION BY sensor ORDER BY seq) AS total
FROM particle_readings ORDER BY sensor, seq;
```

The spreads are 3 and 5.5; the running totals are 1, 5 for sensor 1 and 2, 9.5, 12.5 for sensor 2.

## Errors

An exception raised by the function fails the statement with SQLSTATE `38000`, and the message names the function and the exception:

```sql
SELECT checked_ratio(1);
```

Inside a transaction, the failure aborts the transaction; roll back before reusing the connection.

## Time and memory limits

Each call has a time budget: 10,000 ms by default, set server-wide with `--particles-call-timeout-ms` or per function with `@scalar(timeout_ms=500)`. A call that exceeds it fails with SQLSTATE `57014` and its worker process is stopped. `--particles-memory-limit-mb` (default 1024) caps each worker; a call past it fails with `53200`. `--particles-workers` sets the maximum number of worker processes.

## Dependencies

Install the packages your module imports into the same interpreter the server runs. A module whose import fails stops the server at startup with the Python error. Test with the same interpreter and dependency versions used in deployment. Keep secrets out of the module and review [runtime and trust](/particles/limits/).

[Loading](/particles/loading/) · [Testing](/particles/testing/)

## Related

[Documentation home](/docs/) · [Troubleshooting](/guides/troubleshooting/)
