用 Python + SQLite 做一个个人记账本，还能自动出统计图表

"""
finance_db.py - 数据库操作模块
负责建表、增删改查
"""
import sqlite3
from datetime import datetime

DB_PATH = "finance.db"


def get_connection():
    """获取数据库连接"""
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row  # 让结果可以用列名访问
    return conn


def init_db():
    """初始化数据库表结构"""
    conn = get_connection()
    cursor = conn.cursor()
    
    # 分类表
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS categories (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT UNIQUE NOT NULL,
            type TEXT CHECK(type IN ('收入', '支出')) NOT NULL
        )
    """)
    
    # 交易记录表
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS transactions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            category_id INTEGER,
            amount REAL NOT NULL,
            type TEXT CHECK(type IN ('收入', '支出')) NOT NULL,
            note TEXT,
            date TEXT NOT NULL,
            FOREIGN KEY (category_id) REFERENCES categories(id)
        )
    """)
    
    # 插入默认分类
    default_categories = [
        ("工资", "收入"), ("奖金", "收入"), ("理财收益", "收入"),
        ("餐饮", "支出"), ("交通", "支出"), ("购物", "支出"),
        ("住房", "支出"), ("娱乐", "支出"), ("医疗", "支出"),
        ("其他", "支出"), ("其他收入", "收入")
    ]
    for name, cat_type in default_categories:
        cursor.execute(
            "INSERT OR IGNORE INTO categories (name, type) VALUES (?, ?)",
            (name, cat_type)
        )
    
    conn.commit()
    conn.close()
    print("✅ 数据库初始化完成")


def add_transaction(category_name, amount, transaction_type, note=""):
    """添加一笔交易记录"""
    conn = get_connection()
    cursor = conn.cursor()
    
    # 查找分类 ID
    cursor.execute(
        "SELECT id FROM categories WHERE name = ? AND type = ?",
        (category_name, transaction_type)
    )
    row = cursor.fetchone()
    if not row:
        print(f"❌ 分类 '{category_name}' 不存在")
        conn.close()
        return False
    
    date = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    cursor.execute(
        "INSERT INTO transactions (category_id, amount, type, note, date) VALUES (?, ?, ?, ?, ?)",
        (row["id"], amount, transaction_type, note, date)
    )
    conn.commit()
    conn.close()
    print(f"✅ 已记录: {transaction_type} {category_name} ¥{amount:.2f}")
    return True


def get_monthly_summary(year, month):
    """获取某月的收支汇总"""
    conn = get_connection()
    cursor = conn.cursor()
    
    month_str = f"{year}-{month:02d}"
    
    # 总支出
    cursor.execute("""
        SELECT c.name as category, SUM(t.amount) as total
        FROM transactions t
        JOIN categories c ON t.category_id = c.id
        WHERE t.type = '支出' AND t.date LIKE ?
        GROUP BY c.name
        ORDER BY total DESC
    """, (f"{month_str}%",))
    expense_by_category = cursor.fetchall()
    
    # 总收入
    cursor.execute("""
        SELECT c.name as category, SUM(t.amount) as total
        FROM transactions t
        JOIN categories c ON t.category_id = c.id
        WHERE t.type = '收入' AND t.date LIKE ?
        GROUP BY c.name
        ORDER BY total DESC
    """, (f"{month_str}%",))
    income_by_category = cursor.fetchall()
    
    # 本月总支出和总收入
    cursor.execute("""
        SELECT 
            COALESCE(SUM(CASE WHEN type='支出' THEN amount ELSE 0 END), 0) as total_expense,
            COALESCE(SUM(CASE WHEN type='收入' THEN amount ELSE 0 END), 0) as total_income
        FROM transactions
        WHERE date LIKE ?
    """, (f"{month_str}%",))
    summary = cursor.fetchone()
    
    conn.close()
    return {
        "expenses": [(dict(r)["category"], dict(r)["total"]) for r in expense_by_category],
        "incomes": [(dict(r)["category"], dict(r)["total"]) for r in income_by_category],
        "total_expense": summary["total_expense"],
        "total_income": summary["total_income"],
    }


def get_daily_transactions(limit=20):
    """获取最近的交易记录"""
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute("""
        SELECT t.*, c.name as category_name, c.type as category_type
        FROM transactions t
        JOIN categories c ON t.category_id = c.id
        ORDER BY t.date DESC
        LIMIT ?
    """, (limit,))
    rows = cursor.fetchall()
    conn.close()
    return [dict(r) for r in rows]

"""
charts.py - 统计图表生成模块
"""
import matplotlib.pyplot as plt
import matplotlib
matplotlib.rcParams["font.sans-serif"] = ["SimHei", "Arial Unicode MS", "DejaVu Sans"]
matplotlib.rcParams["axes.unicode_minus"] = False
from finance_db import get_monthly_summary, init_db


def plot_expense_pie(year, month):
    """绘制月度支出饼图"""
    data = get_monthly_summary(year, month)
    
    if not data["expenses"]:
        print(f"⚠️  {year}年{month}月没有支出记录")
        return
    
    categories, amounts = zip(*data["expenses"])
    
    fig, ax = plt.subplots(figsize=(10, 6))
    wedges, texts, autotexts = ax.pie(
        amounts, labels=categories, autopct="%1.1f%%",
        startangle=90, colors=plt.cm.Pastel1(range(len(categories)))
    )
    ax.set_title(f"{year}年{month}月 支出分布", fontsize=16)
    plt.tight_layout()
    plt.savefig(f"expense_{year}_{month:02d}.png", dpi=150)
    print(f"📊 支出饼图已保存为 expense_{year}_{month:02d}.png")
    plt.close()


def plot_income_bar(year, month):
    """绘制月度收入柱状图"""
    data = get_monthly_summary(year, month)
    
    if not data["incomes"]:
        print(f"⚠️  {year}年{month}月没有收入记录")
        return
    
    categories, amounts = zip(*data["incomes"])
    
    fig, ax = plt.subplots(figsize=(10, 5))
    bars = ax.bar(categories, amounts, color=["#4CAF50", "#66BB6A", "#81C784", "#A5D6A7"][:len(categories)])
    
    # 在柱子上标注金额
    for bar, amount in zip(bars, amounts):
        ax.text(bar.get_x() + bar.get_width() / 2, bar.get_height() + 50,
                f"¥{amount:.0f}", ha="center", va="bottom", fontsize=10)
    
    ax.set_title(f"{year}年{month}月 收入明细", fontsize=16)
    ax.set_ylabel("金额（元）")
    plt.tight_layout()
    plt.savefig(f"income_{year}_{month:02d}.png", dpi=150)
    print(f"📊 收入柱状图已保存为 income_{year}_{month:02d}.png")
    plt.close()


def generate_monthly_report(year, month):
    """生成月度统计报告并绘图"""
    data = get_monthly_summary(year, month)
    
    print(f"\n{'='*50}")
    print(f"  {year}年{month}月 财务简报")
    print(f"{'='*50}")
    print(f"  总收入:  ¥{data['total_income']:,.2f}")
    print(f"  总支出:  ¥{data['total_expense']:,.2f}")
    balance = data['total_income'] - data['total_expense']
    print(f"  结余:    ¥{balance:,.2f}")
    print(f"{'='*50}\n")
    
    if data["expenses"]:
        print("  💰 支出明细:")
        for cat, amt in data["expenses"]:
            print(f"     {cat}: ¥{amt:,.2f}")
        print()
    
    if data["incomes"]:
        print("  💵 收入明细:")
        for cat, amt in data["incomes"]:
            print(f"     {cat}: ¥{amt:,.2f}")
        print()
    
    # 生成图表
    if data["expenses"]:
        plot_expense_pie(year, month)
    if data["incomes"]:
        plot_income_bar(year, month)

"""
main.py - 记账本主程序
"""
from finance_db import init_db, add_transaction, get_daily_transactions
from charts import generate_monthly_report


def show_menu():
    """显示主菜单"""
    print("\n" + "="*40)
    print("  📒 我的记账本")
    print("="*40)
    print("  1. 添加支出")
    print("  2. 添加收入")
    print("  3. 最近记录")
    print("  4. 月度报告")
    print("  5. 退出")
    print("="*40)


def add_expense():
    """添加支出"""
    categories = ["餐饮", "交通", "购物", "住房", "娱乐", "医疗", "其他"]
    print("\n  可选分类:", ", ".join(categories))
    name = input("  分类: ").strip()
    
    if name not in categories:
        print("  ❌ 无效分类")
        return
    
    try:
        amount = float(input("  金额: ").strip())
    except ValueError:
        print("  ❌ 请输入有效数字")
        return
    
    note = input("  备注（回车跳过）: ").strip()
    add_transaction(name, amount, "支出", note)


def add_income():
    """添加收入"""
    categories = ["工资", "奖金", "理财收益", "其他收入"]
    print("\n  可选分类:", ", ".join(categories))
    name = input("  分类: ").strip()
    
    if name not in categories:
        print("  ❌ 无效分类")
        return
    
    try:
        amount = float(input("  金额: ").strip())
    except ValueError:
        print("  ❌ 请输入有效数字")
        return
    
    note = input("  备注（回车跳过）: ").strip()
    add_transaction(name, amount, "收入", note)


def show_recent():
    """显示最近交易"""
    transactions = get_daily_transactions(15)
    if not transactions:
        print("  📭 暂无记录")
        return
    
    print("\n  {'日期':<12} {'分类':<8} {'类型':<4} {'金额':>10} {'备注'}")
    print("  " + "-"*60)
    for t in transactions:
        print(f"  {t['date'][:10]:<12} {t['category_name']:<8} "
              f"{t['category_type']:<4} ¥{t['amount']:>9.2f} {t['note'] or ''}")


def main():
    # 初始化数据库
    init_db()
    
    while True:
        show_menu()
        choice = input("  请选择 [1-5]: ").strip()
        
        if choice == "1":
            add_expense()
        elif choice == "2":
            add_income()
        elif choice == "3":
            show_recent()
        elif choice == "4":
            try:
                year = int(input("  年份: "))
                month = int(input("  月份: "))
                generate_monthly_report(year, month)
            except ValueError:
                print("  ❌ 请输入有效数字")
        elif choice == "5":
            print("  👋 再见！")
            break
        else:
            print("  ❌ 无效选项")


if __name__ == "__main__":
    main()

# 安装依赖
pip install matplotlib

# 运行记账本
python main.py