# Python

Connect to ParticleDB from Python, run parameterized queries, handle transactions and errors.

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

Use the configured address for your client. See [connection addresses](/installation/ports/) and [security](/guides/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

The [`particledb`](https://pypi.org/project/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.

```bash
pip install particledb
```

The example connects as `app`, with the password in `PARTICLEDB_PASSWORD`. Start the server
with the [local start command](/getting-started/quickstart/#2-start-locally), 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.

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

## SQL client interface with psycopg

Use [psycopg](https://www.psycopg.org/psycopg3/) (version 3) over the SQL client interface (port 5432):
bind parameters, transactions, and everything in the [SQL reference](/sql/overview/) work as
they do against PostgreSQL. psycopg2 and other PostgreSQL drivers work the same way.

### Install

```bash
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](/connectivity/tls/#authentication).

### Quick start

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

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

```python
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 and vector search from SQL

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

```python
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](/connectivity/redis-commands/). See
[Vector Search](/sql/vector-search/) for index types and distance functions.

## Next steps

- [SQL Reference](/sql/overview/). SQL syntax and examples
- [Transactions](/sql/transactions/). isolation levels and guarantees
- [ORM compatibility](/connectivity/orm-compatibility/). SQLAlchemy, Django, and other ORMs
