Skip to content
ParticleDB
Release candidateParticleDB 1.1.19-rc.17 is not yet GA or ready for production data.Release status →

Python

Use the configured address for your client. See connection addresses and security. The ParticleDB SDK examples use the database port, 7173; the other examples use the SQL client address.

Python applications can use the ParticleDB SDK over the native client protocol (port 7173) or psycopg over the SQL client interface (port 5432).

The particledb package on PyPI, MIT licensed, from version 0.2.0; the current version is 0.2.2. It requires Python 3.11 or newer and server 1.1.19-rc.13 or later.

Terminal window
pip install particledb

The example connects as app, with the password in PARTICLEDB_PASSWORD. Start the server with the local start command, replacing --no-auth with --auth-method scram-sha-256 --pg-username app --pg-password-file ./pdb-password. The password file holds only the password. The SDK connects to localhost:7173 by default.

import os
import particledb
with particledb.connect(user="app", password=os.environ["PARTICLEDB_PASSWORD"]) as db:
db.execute("CREATE TABLE items (id BIGINT PRIMARY KEY, name TEXT)")
db.execute("INSERT INTO items VALUES ($1, $2)", [1, "first"])
with db.transaction() as tx:
tx.execute("UPDATE items SET name = $1 WHERE id = $2", ["updated", 1])
print(db.query("SELECT id, name FROM items WHERE id = $1", [1]))
db.kv_set(b"session:123", b"active")
print(db.kv_get(b"session:123"))

Output:

[{'id': 1, 'name': 'updated'}]
b'active'

Parameters are bound separately from the SQL text. The transaction block commits when it exits and rolls back on an exception.

Use psycopg (version 3) over the SQL client interface (port 5432): bind parameters, transactions, and everything in the SQL reference work as they do against PostgreSQL. psycopg2 and other PostgreSQL drivers work the same way.

Terminal window
pip install "psycopg[binary]"

The examples connect as postgres with the password secret, for a server started with particledb start --auth-method scram-sha-256 --pg-password secret. With the server’s default --auth-method password, add sslmode=require to the connection string; see TLS and authentication.

import psycopg
with psycopg.connect(
"host=localhost port=5432 dbname=particledb user=postgres password=secret"
, autocommit=True) as conn:
conn.execute("CREATE TABLE users (id BIGINT PRIMARY KEY, name TEXT, score DOUBLE PRECISION)")
conn.execute("INSERT INTO users VALUES (%s, %s, %s)", (1, "alice", 92.5))
conn.commit()
# Several rows in one call
with conn.cursor() as cur:
cur.executemany(
"INSERT INTO users VALUES (%s, %s, %s)",
[(2, "bob", 80.0), (3, "carol", 71.25)],
)
conn.commit()
row = conn.execute("SELECT name, score FROM users WHERE id = %s", (1,)).fetchone()
print(row) # ('alice', 92.5)
# Transaction: committed when the block exits, rolled back on an exception
with conn.transaction():
conn.execute("UPDATE users SET score = score + %s WHERE id = %s", (5, 2))
conn.execute("UPDATE users SET score = score - %s WHERE id = %s", (5, 3))
for r in conn.execute("SELECT id, name, score FROM users ORDER BY id"):
print(r)

Output:

('alice', 92.5)
(1, 'alice', 92.5)
(2, 'bob', 85.0)
(3, 'carol', 66.25)

Server errors carry PostgreSQL SQLSTATEs, so psycopg raises the matching exception class:

import psycopg
from psycopg import errors
with psycopg.connect("host=localhost port=5432 dbname=particledb user=postgres password=secret", autocommit=True) as conn:
try:
conn.execute("INSERT INTO users VALUES (%s, %s, %s)", (1, "dup", 0))
except errors.UniqueViolation as e:
print("duplicate key:", e.sqlstate)
conn.rollback()

prints duplicate key: 23505.

Key-value operations and vector distance are SQL, so they work from any driver:

import psycopg
with psycopg.connect("host=localhost port=5432 dbname=particledb user=postgres password=secret", autocommit=True) as conn:
conn.execute("SELECT KV_SET(%s, %s)", ("session:123", '{"user":"alice"}'))
print(conn.execute("SELECT KV_GET(%s)", ("session:123",)).fetchone()[0])
conn.execute("CREATE TABLE docs (id BIGINT PRIMARY KEY, content TEXT, embedding VECTOR(4))")
conn.execute("INSERT INTO docs VALUES (1, 'overview', '[0.1,0.2,0.3,0.4]'), (2, 'other', '[0.9,0.8,0.7,0.6]')")
q = "[0.1,0.15,0.28,0.42]"
for r in conn.execute(
"SELECT id, content, (embedding <-> %s::vector)::float8 AS distance FROM docs "
"ORDER BY embedding <-> %s::vector LIMIT 5", (q, q)):
print(r)

The distance operators return their value as text over the SQL client interface; cast to float8 (as above) to receive a Python float. Key-value data is also reachable over the Redis protocol. See Vector Search for index types and distance functions.