CS50 Finance索引页SQLite WHERE语法错误排查求助

问题描述 #

我需要实现按股票代码分组汇总所有交易的表格以展示股票投资组合，但始终遭遇SQLite语法错误：RuntimeError: near "WHERE": syntax error。该错误疑似源于transactions_sql查询语句中用户ID的调用方式，同时我不确定transactions表的外键（id）关联是否正确。

相关代码与结构 #

Flask代码 #

@app.route("/")
@login_required
def index():
    """Show portfolio of stocks"""
    transactions_sql = db.execute("SELECT company_symbol, SUM(shares) FROM transactions GROUP BY company_symbol WHERE id IN (?)", session["user_id"])
    index = lookup(transactions_sql.company_symbol)
    value = index["price"] * int(transactions_sql.SUM(shares))
    cash_sql = db.execute("SELECT cash FROM users WHERE id IN (?)", session["user_id"])
    cash_left = float(cash_sql[0]["cash"])
    return render_template("index.html", transactions_sql=transactions_sql, index=index, value=value, cash_left=cash_left)

HTML模板 #

{% extends "layout.html" %}

{% block title %}
Index
{% endblock %}

{% block main %}
<table class="table table-striped">
    <thead>
        <tr>
            <th scope="col">Symbol</th>
            <th scope="col">Name</th>
            <th scope="col">Shares</th>
            <th scope="col">Current price</th>
            <th scope="col">Total value</th>
        </tr>
    </thead>
    <tbody>
        {% for transaction in transactions %}
        <tr>
            <th scope="row">{{ transactions_sql.company_symbol }}</th>
            <td>{{ index["name"] }}</td>
            <td>{{ transactions_sql.SUM(shares) }}</td>
            <td>{{ index["price"] | usd }}</td>
            <td>{{ index["value"]| usd }}</td>
        </tr>
        {% endfor %}
    </tbody>
    <tfoot>
        <tr>
            <td class="border-0 fw-bold text-end" colspan="4">Current cash balance</td>
            <td class="border-0 text-end">{{ cash_left | usd }}</td>
        </tr>
        <tr>
            <td class="border-0 fw-bold text-end" colspan="4">TOTAL VALUE</td>
            <td class="border-0 w-bold text-end">{{ cash_left | usd }}</td>
        </tr>
    </tfoot>
</table>
{% endblock %}

SQLite数据库结构 #

sqlite> .schema
CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, username TEXT NOT NULL, hash TEXT NOT NULL, cash NUMERIC NOT NULL DEFAULT 10000.00);
CREATE TABLE sqlite_sequence(name,seq);
CREATE UNIQUE INDEX username ON users (username);
CREATE TABLE transactions(
    transaction_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    company_symbol TEXT NOT NULL,
    date DATETIME,
    shares NUMERIC NOT NULL,
    price NUMERIC NOT NULL,
    cost NUMERIC NOT NULL,
    id INTEGER,
    FOREIGN KEY (id)
        REFERENCES users (id));

错误分析与修复 #

1. SQL语法错误（核心问题） #

SQL语句顺序错误：WHERE子句必须放在GROUP BY之前，原语句把GROUP BY前置直接导致语法报错。同时需给SUM(shares)设置别名方便后续调用；单用户查询用= ?比IN (?)更简洁合理。

修复后的SQL语句：

SELECT company_symbol, SUM(shares) AS total_shares FROM transactions WHERE id = ? GROUP BY company_symbol

2. 查询结果处理错误 #

db.execute返回的是结果列表，无法直接通过transactions_sql.company_symbol获取字段。需循环遍历每个结果，调用lookup获取股票实时数据，计算单只股票总价值并汇总所有资产价值。

修复后的Flask代码：

@app.route("/")
@login_required
def index():
    """Show portfolio of stocks"""
    # 修正SQL语句，添加别名并调整子句顺序
    transactions = db.execute("SELECT company_symbol, SUM(shares) AS total_shares FROM transactions WHERE id = ? GROUP BY company_symbol", session["user_id"])
    
    portfolio = []
    total_portfolio_value = 0.0
    
    # 遍历每只股票，获取实时数据并计算总价值
    for trans in transactions:
        stock_data = lookup(trans["company_symbol"])
        if stock_data:
            total_value = stock_data["price"] * trans["total_shares"]
            total_portfolio_value += total_value
            portfolio.append({
                "symbol": trans["company_symbol"],
                "name": stock_data["name"],
                "shares": trans["total_shares"],
                "price": stock_data["price"],
                "total_value": total_value
            })
    
    # 获取用户剩余现金
    cash_sql = db.execute("SELECT cash FROM users WHERE id = ?", session["user_id"])
    cash_left = float(cash_sql[0]["cash"])
    total_value = total_portfolio_value + cash_left
    
    return render_template("index.html", portfolio=portfolio, cash_left=cash_left, total_value=total_value)

3. HTML模板错误修复 #

模板循环变量与传入变量不匹配，字段调用方式错误，总价值计算逻辑错误。修复后的模板：

{% extends "layout.html" %}

{% block title %}
Index
{% endblock %}

{% block main %}
<table class="table table-striped">
    <thead>
        <tr>
            <th scope="col">Symbol</th>
            <th scope="col">Name</th>
            <th scope="col">Shares</th>
            <th scope="col">Current price</th>
            <th scope="col">Total value</th>
        </tr>
    </thead>
    <tbody>
        {% for item in portfolio %}
        <tr>
            <th scope="row">{{ item.symbol }}</th>
            <td>{{ item.name }}</td>
            <td>{{ item.shares }}</td>
            <td>{{ item.price | usd }}</td>
            <td>{{ item.total_value | usd }}</td>
        </tr>
        {% endfor %}
    </tbody>
    <tfoot>
        <tr>
            <td class="border-0 fw-bold text-end" colspan="4">Current cash balance</td>
            <td class="border-0 text-end">{{ cash_left | usd }}</td>
        </tr>
        <tr>
            <td class="border-0 fw-bold text-end" colspan="4">TOTAL VALUE</td>
            <td class="border-0 fw-bold text-end">{{ total_value | usd }}</td>
        </tr>
    </tfoot>
</table>
{% endblock %}

4. 外键关联验证 #

transactions表的外键id关联users表的id，结构上是正确的。需确保插入交易记录时，正确设置id字段为当前登录用户的ID，否则查询会返回空结果。

内容的提问来源于stack exchange，提问作者hélène