3. Common Recipes

Common recipes

Recipes are task-oriented examples. Use them when you know what you want to do, but not which function to look for.

Create a table with an auto id

from relpy import RelPy, AutoNumber

db = RelPy()
db.create_table("users")
db.add_column("users", "id", AutoNumber, is_primary_key=True)
db.add_column("users", "name", str, nullable=False)

Insert many rows from JSON

This is the most important import pattern for API data, JSON files and generated test data.

users_json = [
    {"name": "Alice", "status": "active"},
    {"name": "Bob", "status": "inactive"},
    {"name": "Carol", "status": "active"},
]

db.insert_many("users", users_json)

Query active users

from relpy import col

active_users = (
    db.query("users")
      .where(col("status") == "active")
      .select("id", "name")
      .order_by("name")
      .to_list()
)

Join two tables

orders_with_users = (
    db.query("orders")
      .join("users")
      .select("orders.id", "users.name", "orders.amount")
      .to_list()
)

If automatic FK inference is not available, use explicit columns:

db.query("orders").join("users", on=("user_id", "id"))

Use join shortcuts

db.query("orders").inner_join("users")
db.query("orders").left_join("users")
db.query("orders").right_join("users")
db.query("orders").full_join("users")
db.query("orders").cross_join("users")
db.query("orders").natural_join("users")

Group and aggregate

from relpy import count, sum_

summary = (
    db.query("orders")
      .group_by("status")
      .aggregate(
          order_count=count(),
          total_amount=sum_("amount"),
      )
      .to_list()
)

Export to pandas

df = db.to_pandas("users")

Save and load

db.save("database.relpy.json")
loaded_db = RelPy.load("database.relpy.json")

Use encrypted columns

from relpy import RelPy, AutoNumber

key = RelPy.generate_encryption_key()
db = RelPy(encryption_key=key)

db.create_table("users")
db.add_column("users", "id", AutoNumber, is_primary_key=True)
db.add_column("users", "email", str, nullable=False, is_encrypted=True)

db.insert("users", {"email": "alice@example.com"})

print(db.to_list("users"))
print(db.to_list("users", decrypt=True))

Catch RelPyDB errors

from relpy import RelPyError

try:
    db.insert("users", {"unknown_column": "value"})
except RelPyError as exc:
    print("RelPyDB error:", exc)