Write Python Particles
Start with the requirements, the complete function and the loading instructions in your first Particle. Every example on this page loads with --python-functions on the standard free-edition server.
Inputs and outputs
Section titled “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:
from particledb import scalar, aggregate, window
@scalardef shout(value: str) -> str: return value.upper() + "!"
@scalardef add_one(n: int) -> int: return n + 1
@scalardef 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
@aggregatedef spread(values: list[float]) -> float: return max(values) - min(values)
@windowdef running_total(values: list[float]) -> list[float]: totals, total = [], 0.0 for value in values: total += value totals.append(total) return totals
@scalardef 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.
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.
Null inputs
Section titled “Null inputs”By default a scalar function is strict: it returns SQL NULL without calling Python when an input is NULL.
SELECT priority(NULL, 0.8) AS score;With called_on_null=True, Python receives None instead:
SELECT label_or_missing(NULL) AS label;The result is missing.
Aggregates and window functions
Section titled “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.
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 totalFROM 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
Section titled “Errors”An exception raised by the function fails the statement with SQLSTATE 38000, and the message names the function and the exception:
SELECT checked_ratio(1);Inside a transaction, the failure aborts the transaction; roll back before reusing the connection.
Time and memory limits
Section titled “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
Section titled “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.
Related
Section titled “Related”This site uses Google Analytics cookies to count visits. Privacy · License