Todoist フィルター with Alopex DB

import alopex
from datetime import date, timedelta

# Alopex DB を端末の中のデータの層にする（アプリに組み込む想定）
db = alopex.Database.open("todoist.alopex")

# タスクとラベルを SQL テーブルで持つ（ラベルはプロジェクトをまたいでまとめる）
db.execute_sql("""
    CREATE TABLE IF NOT EXISTS tasks (
        id INTEGER PRIMARY KEY,
        content TEXT NOT NULL,
        project TEXT,
        priority INTEGER,
        due DATE
    )
""")
db.execute_sql("""
    CREATE TABLE IF NOT EXISTS task_labels (
        task_id INTEGER,
        label TEXT
    )
""")

# タスクを書き留める
db.execute_sql(
    "INSERT INTO tasks (id, content, project, priority, due) VALUES (?, ?, ?, ?, ?)",
    [1, "請求書を送る", "仕事", 3, "2026-10-24"],
)
db.execute_sql(
    "INSERT INTO task_labels (task_id, label) VALUES (?, ?)",
    [1, "@お金"],
)

# フィルター＝SQL の条件。「今週が期限・優先度が高い・@お金のタスク」
week_end = (date.today() + timedelta(days=7)).isoformat()
rows = db.execute_sql(
    """
    SELECT t.id, t.content, t.due
    FROM tasks AS t
    JOIN task_labels AS l ON l.task_id = t.id
    WHERE t.due <= CAST(? AS DATE)
      AND t.priority >= 3
      AND l.label = ?
    ORDER BY t.due
    """,
    [week_end, "@お金"],
)

for row in rows:
    print(row["due"], row["content"])