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).
ParticleDB SDK
Section titled “ParticleDB SDK”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.
pip install particledbThe 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 osimport 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.
SQL client interface with psycopg
Section titled “SQL client interface with psycopg”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.
Install
Section titled “Install”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.
Quick start
Section titled “Quick start”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)Errors
Section titled “Errors”Server errors carry PostgreSQL SQLSTATEs, so psycopg raises the matching exception class:
import psycopgfrom 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 and vector search from SQL
Section titled “Key-value and vector search from SQL”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.
Next steps
Section titled “Next steps”- SQL Reference. SQL syntax and examples
- Transactions. isolation levels and guarantees
- ORM compatibility. SQLAlchemy, Django, and other ORMs
This site uses Google Analytics cookies to count visits. Privacy · License