CREATE TABLE records (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    type TEXT NOT NULL,        -- 收入 / 支出
    category TEXT NOT NULL,    -- 分類（工資、餐飲、交通等）
    amount REAL NOT NULL,      -- 金額
    note TEXT,                 -- 備注
    date TEXT NOT NULL         -- 日期（YYYY-MM-DD）
)

import sqlite3

conn = sqlite3.connect("ledger.db")
cursor = conn.cursor()

cursor.execute("""
CREATE TABLE IF NOT EXISTS records (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    type TEXT NOT NULL,
    category TEXT NOT NULL,
    amount REAL NOT NULL,
    note TEXT,
    date TEXT NOT NULL
)
""")

conn.commit()
conn.close()

print("? 數(shù)據(jù)庫初始化完成")

python init_db.py

import sqlite3
from datetime import datetime

def add_record():
    rtype = input("類型（收入/支出）：")
    category = input("分類（工資/餐飲等）：")
    amount = float(input("金額："))
    note = input("備注（可選）：")
    date = input("日期（默認今天，回車跳過）：") or datetime.now().strftime("%Y-%m-%d")

    conn = sqlite3.connect("ledger.db")
    cursor = conn.cursor()

    cursor.execute("""
        INSERT INTO records (type, category, amount, note, date)
        VALUES (?, ?, ?, ?, ?)
    """, (rtype, category, amount, note, date))

    conn.commit()
    conn.close()

    print("? 記錄添加成功")

if __name__ == "__main__":
    add_record()

import sqlite3

def list_records():
    conn = sqlite3.connect("ledger.db")
    cursor = conn.cursor()

    cursor.execute("SELECT * FROM records ORDER BY date DESC")
    rows = cursor.fetchall()

    total_income = 0
    total_expense = 0

    for row in rows:
        id, rtype, cat, amt, note, date = row
        print(f"{id}. [{rtype}] {cat} ￥{amt} - {note} ({date})")
        if rtype == "收入":
            total_income += amt
        else:
            total_expense += amt

    balance = total_income - total_expense
    print(f"\n?? 收入總計：￥{total_income:.2f}")
    print(f"?? 支出總計：￥{total_expense:.2f}")
    print(f"?? 當前余額：￥{balance:.2f}")

    conn.close()

if __name__ == "__main__":
    list_records()

import sqlite3

def filter_by_month(month):  # 格式：2025-07
    conn = sqlite3.connect("ledger.db")
    cursor = conn.cursor()

    cursor.execute("""
        SELECT * FROM records WHERE date LIKE ?
        ORDER BY date DESC
    """, (f"{month}%",))

    for row in cursor.fetchall():
        print(row)

    conn.close()

filter_by_month("2025-07")

python add.py       # 添加記錄
python view.py      # 查看總覽
python filter.py    # 按月過濾