Query Results Demos

These demos show different ways to fetch query results, including grouped projection queries.

Fetch One

Get a single record from a query.

from sqliter import SqliterDB
from sqliter.model import BaseDBModel

class Task(BaseDBModel):
    title: str
    priority: int

db = SqliterDB(memory=True)
db.create_table(Task)

db.insert(Task(title="High priority", priority=1))
db.insert(Task(title="Medium priority", priority=2))
db.insert(Task(title="Low priority", priority=3))

task = db.select(Task).filter(priority__eq=1).fetch_one()
if task is not None:
    print(f"Single result: {task.title}")

# Also test no results case
no_task = db.select(Task).filter(priority__eq=999).fetch_one()
if no_task is None:
    print("No task found with priority 999")

db.close()

When No Results

Returns None if no records match the query.

Use Cases

Find specific user: When you expect only one result
Get first match: When you only need the first record
Existence checks: Quick check if any records match

Fetch All

Get all matching records.

from sqliter import SqliterDB
from sqliter.model import BaseDBModel

class User(BaseDBModel):
    name: str
    age: int

db = SqliterDB(memory=True)
db.create_table(User)

for i in range(5):
    db.insert(User(name=f"User {i}", age=20 + i))

results = db.select(User).fetch_all()
print(f"Total users: {len(results)}")
for user in results:
    print(f"  - {user.name}, age {user.age}")

db.close()

Return Type

Returns a list of model instances. Empty list if no results.

Memory Consideration

Be careful with large result sets - all records are loaded into memory.

Fetch First / Last

Get the first or last matching record.

from sqliter import SqliterDB
from sqliter.model import BaseDBModel

class Item(BaseDBModel):
    name: str

db = SqliterDB(memory=True)
db.create_table(Item)

for name in ["Alpha", "Beta", "Gamma", "Delta"]:
    db.insert(Item(name=name))

first = db.select(Item).fetch_first()
if first is not None:
    print(f"First: {first.name}")

last = db.select(Item).fetch_last()
if last is not None:
    print(f"Last: {last.name}")

db.close()

Use Cases

Pagination: Show first page of results
Previews: Display sample data
Limit load: Prevent loading too many records

Count Results

Count matching records without fetching them.

from sqliter import SqliterDB
from sqliter.model import BaseDBModel

class Product(BaseDBModel):
    name: str
    category: str

db = SqliterDB(memory=True)
db.create_table(Product)

db.insert(Product(name="Laptop", category="electronics"))
db.insert(Product(name="Phone", category="electronics"))
db.insert(Product(name="Desk", category="furniture"))

total = db.select(Product).count()
print(f"Total products: {total}")

electronics = db.select(Product).filter(category__eq="electronics").count()
print(f"Electronics: {electronics}")

db.close()

Benefits

Fast: Database counts without transferring data
Memory efficient: No records loaded into memory
Statistics: Quick counts for dashboards

Exists Check

Check if any records match without fetching them.

from sqliter import SqliterDB
from sqliter.model import BaseDBModel

class User(BaseDBModel):
    username: str

db = SqliterDB(memory=True)
db.create_table(User)

db.insert(User(username="alice"))
db.insert(User(username="bob"))

exists = db.select(User).filter(username__eq="alice").exists()
print(f"User 'alice' exists: {exists}")

not_exists = db.select(User).filter(username__eq="charlie").exists()
print(f"User 'charlie' exists: {not_exists}")

db.close()

Use Cases

Validation: Check if username/email already exists
Conditional logic: Branch based on existence
Fast checks: Quicker than fetching the actual record

Aggregates

Calculate grouped aggregates using projection mode.

from sqliter import SqliterDB
from sqliter.model import BaseDBModel
from sqliter.query import func

class Sale(BaseDBModel):
    category: str
    amount: float

db = SqliterDB(memory=True)
db.create_table(Sale)

db.insert(Sale(category="books", amount=10.0))
db.insert(Sale(category="books", amount=15.0))
db.insert(Sale(category="games", amount=40.0))
db.insert(Sale(category="games", amount=5.0))

grouped = (
    db.select(Sale)
    .group_by("category")
    .annotate(
        total=func.sum("amount"),
        average=func.avg("amount"),
        entries=func.count(),
    )
    .order("category")
    .fetch_dicts()
)

for row in grouped:
    print(
        f"{row['category']}: total=${row['total']:.2f}, "
        f"avg=${row['average']:.2f}, count={row['entries']}"
    )

db.close()

Important

Projection queries return dictionaries via fetch_dicts(), not model instances.

Aggregate-Only Projection

You can also use annotate() without group_by() to get summary values:

from sqliter import SqliterDB
from sqliter.model import BaseDBModel
from sqliter.query import func

class Sale(BaseDBModel):
    category: str
    amount: float

db = SqliterDB(memory=True)
db.create_table(Sale)

db.insert(Sale(category="books", amount=10.0))
db.insert(Sale(category="books", amount=15.0))
db.insert(Sale(category="games", amount=40.0))

summary = (
    db.select(Sale)
    .annotate(total_rows=func.count(), total_amount=func.sum("amount"))
    .fetch_dicts()
)
print(summary)

db.close()

HAVING on Aggregate Alias

Use having() with aggregate aliases to filter grouped output:

from sqliter import SqliterDB
from sqliter.model import BaseDBModel
from sqliter.query import func

class Sale(BaseDBModel):
    category: str
    amount: float

db = SqliterDB(memory=True)
db.create_table(Sale)

db.insert(Sale(category="books", amount=10.0))
db.insert(Sale(category="books", amount=15.0))
db.insert(Sale(category="games", amount=40.0))
db.insert(Sale(category="games", amount=5.0))

rows = (
    db.select(Sale)
    .group_by("category")
    .annotate(total=func.sum("amount"))
    .having(total__gt=20)
    .order("category")
    .fetch_dicts()
)
for row in rows:
    print(row)

db.close()

Group By Only

Use group_by() without annotate() to fetch unique grouped keys.

from sqliter import SqliterDB
from sqliter.model import BaseDBModel

class Sale(BaseDBModel):
    category: str
    amount: float

db = SqliterDB(memory=True)
db.create_table(Sale)

db.insert(Sale(category="books", amount=10.0))
db.insert(Sale(category="books", amount=15.0))
db.insert(Sale(category="games", amount=40.0))

grouped = db.select(Sale).group_by("category").order("category").fetch_dicts()
print("Unique categories:")
for row in grouped:
    print(f"  - {row['category']}")

db.close()

Projection Guard

Projection mode blocks model-fetch methods and requires fetch_dicts().

from sqliter import SqliterDB
from sqliter.exceptions import InvalidProjectionError
from sqliter.model import BaseDBModel

class Sale(BaseDBModel):
    category: str
    amount: float

db = SqliterDB(memory=True)
db.create_table(Sale)

db.insert(Sale(category="books", amount=10.0))
db.insert(Sale(category="games", amount=40.0))

query = db.select(Sale).group_by("category").order("category")

try:
    query.fetch_all()
except InvalidProjectionError as exc:
    print(f"fetch_all() blocked: {exc}")

grouped = query.fetch_dicts()
print(f"fetch_dicts() returned {len(grouped)} rows")

db.close()

Comparison Table

Method	Returns	Use When
`fetch_one()`	Single record or `None`	You need exactly one record
`fetch_all()`	List of records (all)	You need all matching records
`fetch_first()` / `fetch_last()`	Single record or `None`	You need boundary records
`count()`	Integer count	Statistics, validation
`exists()`	Boolean	Quick existence check
`fetch_dicts()`	List of dictionaries	Grouped/aggregate projection results

Performance Considerations

Large Datasets

# ❌ BAD: Loads all records into memory
all_users = db.select(User).fetch_all()

# ✅ GOOD: Process in batches using limit and offset
offset = 0
batch_size = 100
while True:
    batch = db.select(User).limit(batch_size).offset(offset).fetch_all()
    if not batch:
        break
    for user in batch:
        process(user)
    offset += batch_size

Counting

# ❌ BAD: Counts in Python (slow)
count = len(db.select(User).fetch_all())

# ✅ GOOD: Count in database (fast)
count = db.select(User).count()

Best Practices

DO

Use fetch_one() when you expect a single result
Use count() for statistics instead of counting in Python
Use limit() with offset() for pagination
Check for None when using fetch_one()

DON'T

Use fetch_all() on potentially huge datasets
Count results with len() - use count() instead
Forget that fetch_one() returns None if no results

Filtering - Filter which records are returned
Ordering - Sort results before fetching
Field Selection - Control which fields are returned
Guide: Aggregates and Grouping

Query Results Demos

Fetch One

When No Results

Use Cases

Fetch All

Return Type

Memory Consideration

Fetch First / Last

Use Cases

Count Results

Benefits

Exists Check

Use Cases

Aggregates

Important

Aggregate-Only Projection

HAVING on Aggregate Alias

Group By Only

Projection Guard

Comparison Table

Performance Considerations

Large Datasets

Counting

Best Practices

DO

DON'T

Related Documentation