# 项目结构
# expense-tracker/
# ├── app.py          # Flask 主应用
# ├── models.py       # 数据库模型
# ├── templates/      # HTML 模板
# │   ├── base.html
# │   ├── index.html
# │   ├── add.html
# │   └── stats.html
# └── static/         # 静态文件（CSS）
#     └── style.css

# 依赖安装
# pip install flask sqlalchemy

# models.py
from sqlalchemy import Column, Integer, String, Float, Date, DateTime, create_engine
from sqlalchemy.orm import declarative_base, sessionmaker
from datetime import datetime, date

Base = declarative_base()
engine = create_engine("sqlite:///expense.db", echo=False)
Session = sessionmaker(bind=engine)

class Expense(Base):
    """支出记录"""
    __tablename__ = "expenses"

    id = Column(Integer, primary_key=True, autoincrement=True)
    amount = Column(Float, nullable=False)          # 金额
    category = Column(String(50), nullable=False)    # 类别
    note = Column(String(200))                       # 备注
    date = Column(Date, default=date.today)          # 日期
    type = Column(String(10), default="expense")     # income/expense
    created_at = Column(DateTime, default=datetime.now)

    def __repr__(self):
        return f"<Expense({self.category}: ¥{self.amount})>"

Base.metadata.create_all(engine)

# app.py
from flask import Flask, render_template, request, redirect, url_for
from models import Session, Expense
from datetime import date, datetime
from sqlalchemy import func

app = Flask(__name__)

@app.route("/")
def index():
    """首页：显示所有记录"""
    with Session() as db:
        expenses = db.query(Expense).order_by(Expense.date.desc()).all()

        # 统计
        total_income = db.query(func.sum(Expense.amount)).filter(
            Expense.type == "income"
        ).scalar() or 0
        total_expense = db.query(func.sum(Expense.amount)).filter(
            Expense.type == "expense"
        ).scalar() or 0

        return render_template("index.html",
            expenses=expenses,
            total_income=total_income,
            total_expense=total_expense,
            balance=total_income - total_expense
        )

if __name__ == "__main__":
    app.run(debug=True, port=5000)

# app.py（续）

@app.route("/add", methods=["GET", "POST"])
def add():
    """添加记录"""
    if request.method == "POST":
        amount = float(request.form["amount"])
        category = request.form["category"]
        note = request.form.get("note", "")
        record_date = request.form.get("date", date.today().isoformat())
        record_type = request.form.get("type", "expense")

        with Session() as db:
            expense = Expense(
                amount=amount,
                category=category,
                note=note,
                date=datetime.strptime(record_date, "%Y-%m-%d").date(),
                type=record_type
            )
            db.add(expense)
            db.commit()

        return redirect(url_for("index"))

    return render_template("add.html")

@app.route("/delete/<int:id>")
def delete(id):
    """删除记录"""
    with Session() as db:
        expense = db.query(Expense).get(id)
        if expense:
            db.delete(expense)
            db.commit()
    return redirect(url_for("index"))

@app.route("/edit/<int:id>", methods=["GET", "POST"])
def edit(id):
    """编辑记录"""
    with Session() as db:
        expense = db.query(Expense).get(id)
        if not expense:
            return redirect(url_for("index"))

        if request.method == "POST":
            expense.amount = float(request.form["amount"])
            expense.category = request.form["category"]
            expense.note = request.form.get("note", "")
            expense.date = datetime.strptime(request.form["date"], "%Y-%m-%d").date()
            expense.type = request.form.get("type", "expense")
            db.commit()
            return redirect(url_for("index"))

        return render_template("edit.html", expense=expense)

@app.route("/stats")
def stats():
    """统计页面"""
    with Session() as db:
        # 按类别统计支出
        category_stats = db.query(
            Expense.category,
            func.sum(Expense.amount).label("total"),
            func.count(Expense.id).label("count")
        ).filter(
            Expense.type == "expense"
        ).group_by(Expense.category).order_by(
            func.sum(Expense.amount).desc()
        ).all()

        # 按月统计
        month_stats = db.query(
            func.strftime("%Y-%m", Expense.date).label("month"),
            func.sum(Expense.amount).label("total"),
            Expense.type
        ).group_by(
            func.strftime("%Y-%m", Expense.date),
            Expense.type
        ).order_by("month").all()

        return render_template("stats.html",
            category_stats=category_stats,
            month_stats=month_stats
        )

<!-- templates/base.html -->
<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>{% block title %}个人记账本{% endblock %}</title>
    <style>
        body { font-family: sans-serif; max-width: 800px; margin: 0 auto; padding: 20px; }
        .nav { background: #07c160; padding: 10px 20px; margin-bottom: 20px; border-radius: 8px; }
        .nav a { color: white; text-decoration: none; margin-right: 20px; }
        table { width: 100%; border-collapse: collapse; }
        th { background: #07c160; color: white; padding: 10px; }
        td { padding: 8px; border: 1px solid #ddd; }
        .income { color: green; } .expense { color: red; }
        .btn { padding: 6px 12px; border: none; border-radius: 4px; cursor: pointer; }
        .btn-add { background: #07c160; color: white; }
        .btn-del { background: #ff4d4f; color: white; }
        form label { display: block; margin: 8px 0 4px; }
        form input, form select, form textarea { width: 100%; padding: 8px; box-sizing: border-box; }
    </style>
</head>
<body>
    <div class="nav">
        <a href="/">📋 记录</a>
        <a href="/add">➕ 添加</a>
        <a href="/stats">📊 统计</a>
    </div>
    {% block content %}{% endblock %}
</body>
</html>

<!-- templates/index.html -->
{% extends "base.html" %}
{% block content %}
<h1>💰 个人记账本</h1>

<div style="display:flex;gap:20px;margin:20px 0">
  <div style="flex:1;background:#f0f7f0;padding:16px;border-radius:8px">
    <h3>收入</h3><p class="income">¥{{ "%.2f"|format(total_income) }}</p>
  </div>
  <div style="flex:1;background:#fff2f0;padding:16px;border-radius:8px">
    <h3>支出</h3><p class="expense">¥{{ "%.2f"|format(total_expense) }}</p>
  </div>
  <div style="flex:1;background:#e6f7ff;padding:16px;border-radius:8px">
    <h3>结余</h3><p>¥{{ "%.2f"|format(balance) }}</p>
  </div>
</div>

<a href="/add" class="btn btn-add">➕ 添加记录</a>

<table>
<tr><th>日期</th><th>类别</th><th>金额</th><th>备注</th><th>操作</th></tr>
{% for e in expenses %}
<tr>
  <td>{{ e.date }}</td>
  <td>{{ e.category }}</td>
  <td class="{{ e.type }}">{{ '收入' if e.type == 'income' else '支出' }} ¥{{ "%.2f"|format(e.amount) }}</td>
  <td>{{ e.note or '-' }}</td>
  <td>
    <a href="/edit/{{ e.id }}">✏️</a>
    <a href="/delete/{{ e.id }}" class="btn-del" onclick="return confirm('确定删除？')">🗑️</a>
  </td>
</tr>
{% endfor %}
</table>
{% endblock %}

<!-- templates/add.html -->
{% extends "base.html" %}
{% block title %}添加记录{% endblock %}
{% block content %}
<h1>➕ 添加记录</h1>
<form method="POST">
  <label>类型</label>
  <select name="type">
    <option value="expense">支出</option>
    <option value="income">收入</option>
  </select>

  <label>金额</label>
  <input type="number" name="amount" step="0.01" required>

  <label>类别</label>
  <select name="category">
    <option>餐饮</option><option>交通</option><option>购物</option>
    <option>娱乐</option><option>住房</option><option>医疗</option>
    <option>教育</option><option>工资</option><option>其他</option>
  </select>

  <label>日期</label>
  <input type="date" name="date" value="{{ today }}">

  <label>备注</label>
  <textarea name="note" rows="2"></textarea>

  <button type="submit" class="btn btn-add" style="margin-top:16px;width:100%">保存</button>
</form>
{% endblock %}

7. 添加记录功能

# 已在路由部分实现
# /add 路由处理 GET（显示表单）和 POST（保存数据）

@app.route("/add", methods=["GET", "POST"])
def add():
    if request.method == "POST":
        # 获取表单数据
        amount = float(request.form["amount"])
        category = request.form["category"]
        note = request.form.get("note", "")
        record_date = request.form.get("date", date.today().isoformat())
        record_type = request.form.get("type", "expense")

        # 保存到数据库
        with Session() as db:
            expense = Expense(
                amount=amount, category=category, note=note,
                date=datetime.strptime(record_date, "%Y-%m-%d").date(),
                type=record_type
            )
            db.add(expense)
            db.commit()

        return redirect(url_for("index"))

    return render_template("add.html", today=date.today().isoformat())

8. 查询与统计功能

@app.route("/stats")
def stats():
    with Session() as db:
        # 按类别统计
        category_stats = db.query(
            Expense.category,
            func.sum(Expense.amount).label("total"),
            func.count(Expense.id).label("count")
        ).filter(Expense.type == "expense"
        ).group_by(Expense.category
        ).order_by(func.sum(Expense.amount).desc()).all()

        # 本月统计
        today = date.today()
        month_start = today.replace(day=1)
        month_total = db.query(func.sum(Expense.amount)).filter(
            Expense.date >= month_start,
            Expense.type == "expense"
        ).scalar() or 0

        month_income = db.query(func.sum(Expense.amount)).filter(
            Expense.date >= month_start,
            Expense.type == "income"
        ).scalar() or 0

        return render_template("stats.html",
            category_stats=category_stats,
            month_expense=month_total,
            month_income=month_income,
            month_balance=month_income - month_total
        )

# templates/stats.html
# {% extends "base.html" %}
# {% block content %}
# <h1>📊 统计分析</h1>
# <h2>本月概览</h2>
# <p>收入: ¥{{ month_income }} | 支出: ¥{{ month_expense }} | 结余: ¥{{ month_balance }}</p>
# <h2>分类支出</h2>
# <table>
# <tr><th>类别</th><th>金额</th><th>笔数</th></tr>
# {% for cat, total, count in category_stats %}
# <tr><td>{{ cat }}</td><td>¥{{ "%.2f"|format(total) }}</td><td>{{ count }}</td></tr>
# {% endfor %}
# </table>
# {% endblock %}

9. 完整代码

"""
个人记账本 - 完整 app.py
依赖: pip install flask sqlalchemy
运行: python app.py → http://localhost:5000
"""
from flask import Flask, render_template, request, redirect, url_for
from sqlalchemy import Column, Integer, String, Float, Date, DateTime, func, create_engine
from sqlalchemy.orm import declarative_base, sessionmaker
from datetime import datetime, date

app = Flask(__name__)
Base = declarative_base()
engine = create_engine("sqlite:///expense.db")
Session = sessionmaker(bind=engine)

class Expense(Base):
    __tablename__ = "expenses"
    id = Column(Integer, primary_key=True)
    amount = Column(Float, nullable=False)
    category = Column(String(50), nullable=False)
    note = Column(String(200))
    date = Column(Date, default=date.today)
    type = Column(String(10), default="expense")
    created_at = Column(DateTime, default=datetime.now)

Base.metadata.create_all(engine)

@app.route("/")
def index():
    with Session() as db:
        expenses = db.query(Expense).order_by(Expense.date.desc()).all()
        income = db.query(func.sum(Expense.amount)).filter(Expense.type == "income").scalar() or 0
        expense = db.query(func.sum(Expense.amount)).filter(Expense.type == "expense").scalar() or 0
        return render_template("index.html", expenses=expenses,
            total_income=income, total_expense=expense, balance=income - expense)

@app.route("/add", methods=["GET", "POST"])
def add():
    if request.method == "POST":
        with Session() as db:
            e = Expense(
                amount=float(request.form["amount"]),
                category=request.form["category"],
                note=request.form.get("note", ""),
                date=datetime.strptime(request.form.get("date", date.today().isoformat()), "%Y-%m-%d").date(),
                type=request.form.get("type", "expense")
            )
            db.add(e); db.commit()
        return redirect(url_for("index"))
    return render_template("add.html", today=date.today().isoformat())

@app.route("/delete/<int:id>")
def delete(id):
    with Session() as db:
        e = db.query(Expense).get(id)
        if e: db.delete(e); db.commit()
    return redirect(url_for("index"))

@app.route("/stats")
def stats():
    with Session() as db:
        cat_stats = db.query(Expense.category, func.sum(Expense.amount), func.count(Expense.id)
            ).filter(Expense.type == "expense").group_by(Expense.category
            ).order_by(func.sum(Expense.amount).desc()).all()
        return render_template("stats.html", category_stats=cat_stats)

if __name__ == "__main__":
    app.run(debug=True)

10. 今日小结

项目知识串联

🎯 扩展练习：
1. 添加"预算"功能——设置每月预算，超支时提醒
2. 添加"导入/导出"功能——支持 CSV 格式
3. 添加"图表"功能——用 matplotlib 生成饼图/柱状图
4. 添加"多用户"支持——登录注册 + 每人独立账本

📚 Day50 完成！第二阶段（Python 进阶）全部结束 ✅
🎉 恭喜完成半程！明天进入第三阶段：Web 开发 — Flask 框架入门