RpRelPyDBRelational Python

Documentation

6. Querying

Filter, sort, project, and retrieve rows using RelPyDB's chainable query builder.

Query execution order

RelPyDB processes a query as a pipeline. You can chain methods in any order syntactically, but the engine always executes them in this sequence:

  1. Source rows from the table (or join result)
  2. where — filter rows
  3. order_by — sort the filtered set
  4. select — project columns
  5. distinct — deduplicate
  6. offset — skip N rows
  7. limit — take at most N rows

For grouped queries the order is: source → where → group_by → aggregate → having → order_by → select → distinct → offset → limit.

Starting a query

rows = db.query("users").to_list()

db.query(table_name) returns a Query object. Nothing is executed until you call a terminal method like to_list(), first(), or count().

Filtering with where and col

Use col("column_name") to build conditions.

from relpy import RelPy, AutoNumber, col

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.add_column("users", "status", str, nullable=False, default="active")
db.add_column("users", "age",    int, nullable=True)

db.insert("users", {"name": "Alice", "age": 30})
db.insert("users", {"name": "Bob",   "age": 17, "status": "inactive"})
db.insert("users", {"name": "Carol", "age": 25})

# equality
result = db.query("users").where(col("status") == "active").to_list()
Output
idnamestatusage
1Aliceactive30
3Carolactive25

All col operators

ExpressionSQL equivalentExample
col("x") == vx = vcol("status") == "active"
col("x") != vx != vcol("status") != "inactive"
col("x") > vx > vcol("age") > 18
col("x") >= vx >= vcol("age") >= 18
col("x") < vx < vcol("amount") < 100
col("x") <= vx <= vcol("score") <= 99
col("x").like(pattern)x LIKE patterncol("email").like("%@gmail.com")
col("x").in_([...])x IN (...)col("status").in_(["paid","pending"])
col("x").between(lo, hi)x BETWEEN lo AND hicol("amount").between(100, 500)
col("x").is_null()x IS NULLcol("deleted_at").is_null()
col("x").is_not_null()x IS NOT NULLcol("email").is_not_null()

Combining conditions

Do not use Python's and / or. Use &, |, ~ or the AND(), OR(), NOT() functions instead.
Operator style
from relpy import col

result = (
    db.query("users")
      .where(
          (col("age") >= 18) &
          (col("status") == "active")
      )
      .to_list()
)
Function style
from relpy import col, AND, OR, NOT

result = (
    db.query("users")
      .where(
          AND(
              col("age") >= 18,
              col("status") == "active",
          )
      )
      .to_list()
)

Both styles produce the same result. Use whichever is more readable for your team.

select — project columns

RelPyDB
result = (
    db.query("users")
      .select("id", "name")
      .to_list()
)
SQL equivalent
SELECT id, name
FROM users;
Output
idname
1Alice
2Bob
3Carol

order_by — sort

# single column
db.query("users").order_by("name").to_list()

# descending
db.query("users").order_by("age", descending=True).to_list()

# multiple columns
db.query("users").order_by(("status", "asc"), ("age", "desc")).to_list()

# alias
db.query("users").orderby("name").to_list()

limit and offset

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

# pagination: page 2, 10 per page
db.query("users").offset(10).limit(10).to_list()
SQL equivalent
SELECT * FROM users LIMIT 10;

SELECT * FROM users
LIMIT 10 OFFSET 10;

distinct

RelPyDB
statuses = (
    db.query("users")
      .select("status")
      .distinct()
      .to_list()
)
SQL equivalent
SELECT DISTINCT status
FROM users;
Output
status
active
inactive

Terminal methods

MethodReturnsNotes
.to_list()list[dict]All matching rows as plain Python dicts.
.first()dict | NoneFirst matching row, or None if no rows match.
.one()dictExactly one row. Raises NoRowsFoundError or MultipleRowsFoundError otherwise.
.exists()boolTrue if at least one row matches.
.pluck("col")listFlat list of values for a single column.
.count()intNumber of matching rows.
.sum("col")numberSum of column values.
.avg("col")floatAverage of column values.
.min("col")valueMinimum value.
.max("col")valueMaximum value.
# practical examples
db.query("users").where(col("status") == "active").count()       # 2
db.query("users").pluck("name")                                  # ["Alice", "Bob", "Carol"]
db.query("users").where(col("age").is_not_null()).avg("age")     # 24.0
db.query("users").where(col("status") == "active").exists()      # True

Full chained example

RelPyDB
result = (
    db.query("users")
      .where(
          (col("age") >= 18) &
          col("status").in_(["active", "pending"])
      )
      .order_by("name")
      .select("id", "name", "age")
      .limit(5)
      .to_list()
)
SQL equivalent
SELECT id, name, age
FROM users
WHERE age >= 18
  AND status IN ('active', 'pending')
ORDER BY name
LIMIT 5;
← 5. Data Operations 7. Joins →