from relpy import RelPy, AutoNumber, col
from relpy import AND, OR, NOT
from relpy import count, sum_, avg, min_, max_

RelPyDB API Functions Reference

This document lists the main public functions and objects in RelPyDB, with a short description, inputs, outputs, and examples.

1. Core Objects

RelPy(encryption_key=None)

Creates an in-memory relational database object.

Inputs

Output

Example

from relpy import RelPy

db = RelPy()

With encryption:

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

AutoNumber

Special logical column type for auto-incrementing integer primary keys.

Inputs

None.

Output

Used as a column type, not as a normal runtime value.

Example

db.add_column("users", "id", AutoNumber, is_primary_key=True)

col(column_name)

Creates a column reference used in query conditions.

Inputs

Output

Example

db.query("users").where(col("age") >= 18)

2. Schema Functions

create_table(table_name)

Creates a new empty table.

Inputs

Output

Example

db.create_table("users")

add_column(table_name, column_name, data_type, ...)

Adds a column to an existing table.

Inputs

Output

Example

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

Foreign key:

db.add_column(
    "orders",
    "user_id",
    int,
    nullable=False,
    references="users.id",
    on_delete="CASCADE",
)

set_primary_key(table_name, columns, replace=False)

Sets a primary key using one or more existing columns.

Inputs

Output

Example

db.set_primary_key("order_items", ["order_id", "product_id"])

describe_table(table_name)

Returns metadata about one table.

Inputs

Output

Example

info = db.describe_table("users")

describe_schema()

Returns metadata about the entire schema.

Inputs

None.

Output

Example

schema = db.describe_schema()

3. Data Operation Functions

insert(table_name, values)

Inserts one row into a table.

Inputs

Output

Example

user = db.insert("users", {"name": "Alice"})

insert_many(table_name, rows)

Inserts many rows into a table efficiently.

Inputs

Output

Example

db.insert_many("users", [
    {"name": "Alice"},
    {"name": "Bob"},
])

update(table_name, values, where=None, allow_all=False)

Updates rows in a table.

Inputs

Output

Example

count = db.update(
    "users",
    {"status": "active"},
    where=lambda row: row["age"] >= 18,
)

Update all rows intentionally:

db.update("users", {"status": "active"}, allow_all=True)

delete(table_name, where=None, allow_all=False)

Deletes rows from a table.

Inputs

Output

Example

deleted = db.delete(
    "users",
    where=lambda row: row["id"] == 1,
)

Delete all rows intentionally:

db.delete("logs", allow_all=True)

4. Query Builder Functions

query(table_name)

Starts a query on a table.

Inputs

Output

Example

q = db.query("users")

where(condition_or_callable)

Filters query rows.

Inputs

Output

Example

db.query("users").where(col("status") == "active")

select(*columns)

Projects selected columns.

Inputs

Output

Example

db.query("users").select("id", "name")

order_by(*columns, descending=False)

Sorts query results.

Inputs

Output

Example

db.query("users").order_by("name")
db.query("users").order_by("age", descending=True)
db.query("users").order_by(("status", "asc"), ("age", "desc"))

orderby(...)

Alias for order_by(...).

Inputs

Same as order_by.

Output

Same as order_by.

Example

db.query("users").orderby("name")

limit(count)

Limits the number of returned rows.

Inputs

Output

Example

db.query("users").limit(10)

offset(count)

Skips rows before returning results.

Inputs

Output

Example

db.query("users").order_by("id").offset(20).limit(10)

distinct(enabled=True)

Removes duplicate result rows.

Inputs

Output

Example

db.query("users").select("status").distinct()

5. Condition Functions

Column comparisons

col("column") supports:

Output

like(pattern, case_sensitive=True)

SQL-like pattern matching.

Inputs

Output

Example

col("email").like("%@gmail.com")

in_(values)

Checks whether the value is included in a collection.

Inputs

Output

Example

col("status").in_(["paid", "pending"])

between(low, high, inclusive=True)

Checks whether a value is between two bounds.

Inputs

Output

Example

col("amount").between(100, 500)

is_null() and is_not_null()

Checks whether a column value is or is not None.

Inputs

None.

Output

Example

col("deleted_at").is_null()
col("email").is_not_null()

AND(*conditions)

Combines conditions with logical AND.

Inputs

Output

Example

AND(col("age") >= 18, col("status") == "active")

OR(*conditions)

Combines conditions with logical OR.

Output

Example

OR(col("tier") == "gold", col("tier") == "platinum")

NOT(condition)

Negates a condition.

Output

Example

NOT(col("status") == "inactive")

6. Join Functions

join(other, ...)

Joins the current query with another table, view, or query.

Inputs

Output

Example

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

Join shortcuts

Shortcut functions call join(..., method=...).

Example

db.query("orders").left_join("users")

7. Grouping Functions

group_by(*columns)

Starts a grouped query.

Inputs

Output

Example

db.query("orders").group_by("status")

aggregate(**aggregations)

Adds aggregate columns to a grouped query.

Inputs

Output

Example

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

having(condition)

Filters grouped results after aggregation.

Inputs

Output

Example

.having(col("total_amount") > 1000)

Aggregation specification functions

8. Query Terminal Methods

Query.to_list(decrypt=False)

Executes a query and returns rows as a list of dictionaries.

Output

Query.to_json(decrypt=False, ...)

Executes a query and returns JSON text.

Output

Query.to_pandas(decrypt=False)

Executes a query and returns a pandas DataFrame.

Output

Query.to_numpy(decrypt=False)

Executes a query and returns a NumPy array.

Output

first()

Returns the first row or None.

Output

one()

Returns exactly one row.

Output

Raises

• NoRowsFoundError if there are zero rows.
• MultipleRowsFoundError if there is more than one row.

exists()

Checks whether the query returns any rows.

Output

pluck(column_name)

Returns one column as a list.

Inputs

Output

Query aggregate terminal methods

9. View Functions

create_view(view_name, query_builder, description=None)

Creates a named logical view.

Inputs

Output

Example

db.create_view(
    "active_users",
    lambda db: db.query("users").where(col("status") == "active"),
)

view(view_name)

Evaluates and returns a view query.

Inputs

Output

drop_view(view_name)

Drops a view.

Output

list_views()

Lists view names.

Output

describe_view(view_name)

Describes one view.

Output

describe_views()

Describes all views.

Output

10. Index Functions

create_index(table_name, columns, name=None, unique=False, nulls_distinct=True)

Creates an index.

Inputs

Output

Example

db.create_index("users", "email")
db.create_index("orders", ["customer_id", "status"])

drop_index(index_name)

Drops an index.

Inputs

Output

list_indexes(table_name=None)

Lists indexes.

Inputs

Output

describe_index(index_name)

Describes one index.

Output

describe_indexes(table_name=None)

Describes all indexes, optionally filtered by table.

Output

11. Table Export Functions

These functions are methods on the RelPy object.

They usually support: - whole table export - single column export - single row export through where_key - encrypted export through decrypt=True

to_list(table_name, column_name=None, where_key=None, decrypt=False)

Exports table data as Python lists.

Inputs

Output

to_json(table_name, column_name=None, where_key=None, decrypt=False, ...)

Exports table data as JSON text.

Output

to_pandas(table_name, column_name=None, where_key=None, decrypt=False)

Exports table data to pandas.

Output

to_numpy(table_name, column_name=None, where_key=None, decrypt=False, dtype=None)

Exports table data to NumPy.

Output

to_sql(table_name, row=None, where_key=None, decrypt=False)

Exports table rows as SQL INSERT statements.

Inputs

Output

to_ddl()

Exports the schema as SQL DDL.

Inputs

None.

Output

print_table(table_name, limit=3, max_width=24, decrypt=False)

Prints a table preview.

Inputs

Output

12. Persistence Functions

save(file_path, indent=2)

Saves the database to a .relpy.json file.

Inputs

Output

load(file_path, encryption_key=None)

Loads a database from a .relpy.json file.

Inputs

Output

Example

db = RelPy.load("database.relpy.json")
db = RelPy.load("database.relpy.json", encryption_key=key)

13. Encryption Functions

generate_encryption_key()

Generates a key for encrypted columns.

Inputs

None.

Output

Example

key = RelPy.generate_encryption_key()

set_encryption_key(encryption_key)

Sets the encryption key on an existing database object.

Inputs

Output

Example

db.set_encryption_key(key)

14. Exceptions

RelPyDB defines a custom exception hierarchy.

Base exceptions

Specific exceptions

15. Minimal End-to-End Example

from relpy import RelPy, AutoNumber, col, count

db = RelPy()

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

db.create_table("orders")
db.add_column("orders", "id", AutoNumber, is_primary_key=True)
db.add_column("orders", "user_id", int, nullable=False, references="users.id")
db.add_column("orders", "status", str, nullable=False)

alice = db.insert("users", {"name": "Alice"})

db.insert_many("orders", [
    {"user_id": alice["id"], "status": "paid"},
    {"user_id": alice["id"], "status": "paid"},
])

result = (
    db.query("orders")
      .join("users")
      .where(col("orders.status") == "paid")
      .group_by("users.name")
      .aggregate(order_count=count())
      .to_list()
)

print(result)

Expected output:

[
    {
        "users.name": "Alice",
        "order_count": 2,
    }
]

db.query("orders").inner_join(
    "support_tickets",
    on=("customer_id", "customer_id"),
)

orders.customer_id = support_tickets.customer_id

db.query("orders").inner_join(
    "support_tickets",
    left_on="customer_id",
    right_on="customer_id",
)

db.query("orders").inner_join(
    "support_tickets",
    on=[
        ("customer_id", "customer_id"),
        ("region_id", "region_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)

db.create_table("orders")
db.add_column("orders", "id", AutoNumber, is_primary_key=True)
db.add_column("orders", "user_id", int, nullable=True, references="users.id")
db.add_column("orders", "amount", float, nullable=False)

alice = db.insert("users", {"name": "Alice"})
bob = db.insert("users", {"name": "Bob"})

db.insert_many("orders", [
    {"user_id": alice["id"], "amount": 120.0},
    {"user_id": alice["id"], "amount": 80.0},
])

inner_rows = db.query("orders").inner_join("users").to_list()
left_rows = db.query("orders").left_join("users").to_list()
right_rows = db.query("orders").right_join("users").to_list()
full_rows = db.query("orders").full_join("users").to_list()
cross_rows = db.query("orders").cross_join("users").to_list()

print(inner_rows)
print(left_rows)
print(right_rows)
print(full_rows)
print(cross_rows)