当前位置：首页>python>Python 零基础100天—Day50 综合练习

Python 零基础100天—Day50 综合练习

  • 2026-10-11 06:01:50
Python 零基础100天—Day50 综合练习

🐍 Python Day50：综合练习 — 个人记账本（Flask + SQLite）

🕐 预计用时：3-4 小时 ｜ 🎯 目标：用 Flask + SQLAlchemy + SQLite 构建完整 Web 应用

📖 今日目录

  1. 项目需求分析
  2. 技术选型与架构
  3. 数据库模型设计
  4. Flask 应用搭建
  5. 路由与视图函数
  6. HTML 表单与模板
  7. 添加记录功能
  8. 查询与统计功能
  9. 删除与编辑功能
  10. 完整代码
  11. 今日小结

1. 项目需求分析

构建一个简易的个人记账本 Web 应用。

功能
说明
技术
添加记录
记录金额、类别、备注
HTML 表单 + POST
查看列表
按时间排序显示所有记录
SQLAlchemy 查询
分类统计
按类别统计支出
GROUP BY 聚合
月度报表
某月的收支总览
日期过滤 + 统计
删除记录
删除错误的记录
DELETE 操作
编辑记录
修改已有记录
UPDATE 操作

2. 技术选型与架构

# 项目结构
# 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

3. 数据库模型设计

# 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)

4. Flask 应用搭建

# 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)

5. 路由与视图函数

# 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
        )

6. HTML 表单与模板

<!-- 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. 今日小结

项目知识串联

功能
用到的技术
来源
数据模型
SQLAlchemy ORM
Day48-49
数据库操作
CRUD、聚合查询
Day45-46
Web 框架
Flask 路由、模板
Day51（预告）
HTML 表单
form、input、select
Day67（预告）
日期处理
datetime、date
Day30
数据库
SQLite
Day45-46

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

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

轻松时刻：

请在微信客户端打开

本文来自网友投稿或网络内容，如有侵犯您的权益请联系我们删除，联系邮箱：596563534@qq.com 。

Python itertools 修炼指南

Linux load高负载排查 & SOP

最新文章

随机文章