哈佛CS50第9周Finance项目check50登录报错求助

问题背景 #

本地运行登录功能无异常，但使用check50检测时，出现错误提示：

:( logging in as registered user succeeds Cause application raised an exception

登录逻辑基于课程提供的代码，仅自行添加了SQL相关操作，需排查报错原因。

完整代码 #

import os

from cs50 import SQL
from flask import Flask, flash, redirect, render_template, request, session
from flask_session import Session
from werkzeug.security import check_password_hash, generate_password_hash
from datetime import datetime

from helpers import apology, login_required, lookup, usd

# Configure application
app = Flask(__name__)

# Custom filter
app.jinja_env.filters["usd"] = usd

# Configure session to use filesystem (instead of signed cookies)
app.config["SESSION_PERMANENT"] = False
app.config["SESSION_TYPE"] = "filesystem"
Session(app)

# Configure CS50 Library to use SQLite database
db = SQL("sqlite:///finance.db")


@app.after_request
def after_request(response):
    """Ensure responses aren't cached"""
    response.headers["Cache-Control"] = "no-cache, no-store, must-revalidate"
    response.headers["Expires"] = 0
    response.headers["Pragma"] = "no-cache"
    return response


@app.route("/")
@login_required
def index():
    """Show portfolio of stocks"""

    list = db.execute("SELECT stocks FROM stock WHERE username =(SELECT username FROM loged_in)")
    stocks = int(list[0]['stocks'])
    result = lookup('NFLX')
    price = int(result['price'])
    total = price * stocks

    """ , stocks, price, total """
    return render_template("indix.html", stocks=stocks, price=price, total=total)


@app.route("/buy", methods=["GET", "POST"])
@login_required
def buy():
    """Buy shares of stock"""

    if request.method == "POST":
        list = db.execute("SELECT username FROM loged_in")
        name = list[0]['username']
        stock = request.form.get("stock_name")
        result = lookup(stock)
        shares = int(request.form.get("shares"))
        price = int(result['price']*shares)
        list2 = db.execute("SELECT cash FROM users WHERE username = ?", name)
        wallet = int(list2[0]['cash'])


        if not result:
            return apology("sorry this symbol doesnt exist")

        elif shares < 1:
            return apology("please enter a positive number")

        elif price < wallet:
            wallet -= price
            list3 = db.execute("SELECT stocks FROM stock WHERE username = ?", name)
            stocks = int(list3[0]['stocks'])
            newstocks = stocks + shares

            """ return render_template("test.html", name = newstocks) """

            db.execute("UPDATE users SET cash = ? WHERE username = ?", wallet ,name)

            db.execute("UPDATE stock SET stocks = ? WHERE username = ?", newstocks ,name)
        else:
            return apology("sorry you dont have enough money")

        done = (f"you bought {shares} shares from NFLX")

        time = datetime.now()

        now = (f"{time.year}/{time.month}/{time.day} Time={time.hour}:{time.minute}")

        db.execute("INSERT INTO history (username, done, time) VALUES(?, ?, ?)", name, done, now)

        return render_template("thank.html")

    else:
        return render_template("buying.html")

@app.route("/history")
@login_required
def history():
    """Show history of transactions"""

    list = db.execute("SELECT username FROM loged_in")
    name = list[0]['username']

    list2 = db.execute("SELECT done FROM history WHERE username = ?", name)

    list3 = db.execute("SELECT time FROM history WHERE username = ?", name)

    times = []
    names = []

    i = 0
    for n in list2:
        names.append(list2[i]['done'])
        times.append(list3[i]['time'])
        i += 1

    number = len(names)

    return render_template("history.html", name = names, number = number, time = times)


@app.route("/login", methods=["GET", "POST"])
def login():
    """ Log user in """

    # Forget any user_id

    session.clear()

    # User reached route via POST (as by submitting a form via POST)
    if request.method == "POST":

        #DELETE the last log in

        db.execute("DELETE FROM loged_in")

        # Ensure username was submitted
        if not request.form.get("username"):
            return apology("must provide username", 403)

        # Ensure password was submitted
        elif not request.form.get("password"):
            return apology("must provide password", 403)

        # Query database for username
        rows = db.execute("SELECT * FROM users WHERE username = ?", request.form.get("username"))

        # Ensure username exists and password is correct
        if len(rows) != 1 or not check_password_hash(rows[0]["hash"], request.form.get("password")):
            return apology("invalid username and/or password", 403)

        # Remember which user has logged in
        name = request.form.get("username")

        session["user_id"] = rows[0]["id"]
        db.execute("INSERT INTO loged_in (username) VALUES(?)", name)

        # Redirect user to home page
        return redirect("/")

    # User reached route via GET (as by clicking a link or via redirect)
    else:
        return render_template("login.html")

@app.route("/logout")
def logout():
    """Log user out"""

    # Forget any user_id
    session.clear()

    # Redirect user to login form
    return redirect("/")


@app.route("/quote", methods=["GET", "POST"])
@login_required
def quote():
    """Get stock quote."""
    if request.method == "POST":

        qoute = request.form.get("quote-name")
        final = lookup(qoute)
        if final:
            return render_template("quoted.html", final=final)
        else:
            return apology("sorry this symbol doesnt exist")

    else:
        return render_template("quote.html")


@app.route("/register", methods=["GET", "POST"])
def register():
    if request.method == "POST":
        name = request.form.get("username")
        password = request.form.get("password")
        confirm = request.form.get("confirmation")

        if not password or not name or not confirm:
            return apology("please fill all the empty placeholders")

        if password != confirm:
            return apology("your passowrd is not the same")

        list = db.execute("SELECT username FROM users;")
        username = []

        i = 0
        for n in range(len(list)):
            username.append(list[i]['username'])
            i += 1

        for names in username:
            if names == name:
                return apology("sorry this name already exist")

        hash_password = generate_password_hash(password)
        db.execute("INSERT INTO users (username, hash) VALUES(?, ?)", name, hash_password)
        db.execute("INSERT INTO stock (username, stocks) VALUES(?, 0)", name)

        return render_template("login.html")

    else:
        return render_template("register.html")


@app.route("/sell", methods=["GET", "POST"])
@login_required
def sell():
    """Sell shares of stock"""
    if request.method == "POST":
        list = db.execute("SELECT username FROM loged_in")
        name = list[0]['username']
        stock = request.form.get("stock_name_sell")
        result = lookup(stock)
        shares = int(request.form.get("shares_sell"))
        price = int(result['price']*shares)
        list2 = db.execute("SELECT cash FROM users WHERE username = ?", name)
        wallet = int(list2[0]['cash'])

        time = datetime.now()

        now = (f"{time.year }/{time.month}/{time.day} Time={time.hour}:{time.minute}")


        if not result:
            return apology("sorry this symbol doesnt exist")

        elif shares < 1:
            return apology("please enter a positive number")

        else:
            list3 = db.execute("SELECT stocks FROM stock WHERE username = ?", name)
            stocks = int(list3[0]['stocks'])
            if stocks >= shares:
                newstocks = stocks - shares
            else:
                return apology("you dont have enough shares to sell")
            wallet += price

            db.execute("UPDATE users SET cash = ? WHERE username = ?", wallet ,name)

            db.execute("UPDATE stock SET stocks = ? WHERE username = ?", newstocks ,name)

            done = (f"you sold {shares} shares from NFLX")

            db.execute("INSERT INTO history (username, done, time) VALUES(?, ?, ?)", name, done, now)

        return render_template("thank.html")

    else:
        return render_template("sell.html")

报错原因排查 #

1. loged_in表的依赖问题 #

登录时执行DELETE FROM loged_in和INSERT INTO loged_in，但check50测试环境中大概率没有预先创建这个表，直接执行SQL会抛出表不存在的异常。本地环境可能是你手动创建了该表，所以运行正常，但测试环境不具备这个条件。

另外，课程原逻辑已经通过session["user_id"]管理登录状态，loged_in表属于冗余设计，完全可以通过session["user_id"]查询用户信息，不需要额外维护这个表。

2. 首页路由的未处理异常 #

登录后重定向到/路由，代码中直接执行：

list = db.execute("SELECT stocks FROM stock WHERE username =(SELECT username FROM loged_in)")
stocks = int(list[0]['stocks'])

如果loged_in表为空（比如插入失败），list会是空列表，访问list[0]会抛出索引越界异常。此外，lookup('NFLX')如果返回None（比如API请求失败），访问result['price']也会直接崩溃。

3. 测试数据的表结构一致性问题 #

check50会使用预设的测试用户数据，这些用户可能没有在stock表中对应的记录，后续查询stock表时会返回空结果，导致int(list3[0]['stocks'])抛出异常。

修复方案 #

• 移除loged_in表：所有需要获取当前用户信息的地方，通过session["user_id"]查询users表。例如：

  # 代替查询loged_in表
  user = db.execute("SELECT username FROM users WHERE id = ?", session["user_id"])
  name = user[0]['username']
  

• 增加查询结果的非空判断：对所有数据库查询结果做检查，避免索引越界或空指针异常。比如首页路由：

  @app.route("/")
  @login_required
  def index():
      user = db.execute("SELECT username FROM users WHERE id = ?", session["user_id"])
      if not user:
          return apology("用户不存在", 403)
      stocks_data = db.execute("SELECT stocks FROM stock WHERE username = ?", user[0]['username'])
      stocks = int(stocks_data[0]['stocks']) if stocks_data else 0
      
      result = lookup('NFLX')
      if not result:
          return apology("无法获取股票价格", 400)
      price = int(result['price'])
      total = price * stocks
      
      return render_template("index.html", stocks=stocks, price=price, total=total)
  

• 确保表结构初始化：在app启动时自动创建所需的stock、history表，避免测试环境中表不存在的问题。例如添加初始化代码：

  # 初始化数据库表
  db.execute("CREATE TABLE IF NOT EXISTS stock (username TEXT PRIMARY KEY, stocks INTEGER NOT NULL)")
  db.execute("CREATE TABLE IF NOT EXISTS history (id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT NOT NULL, done TEXT NOT NULL, time TEXT NOT NULL)")
  

• 修正模板文件名：首页路由中渲染的是indix.html，应为index.html，避免模板不存在的异常。

内容的提问来源于stack exchange，提问作者AA-SLF