提示在数据库浏览器SQLite中有效，但在代码中无效

Question

我正试图根据价格数据筛选股票列表。老实说，我真的不知道自己在做什么，所以任何帮助都是非常感激的。我会开门见山的。基本上，这个提示

select * from (
select symbol, name, stock_id, max(close), date
from stock_price join stock on stock.id = stock_price.stock_id
group by stock_id
order by symbol
) where date = '2021-04-01'

在数据库浏览器(SQLite(中运行良好。在应用程序中，我希望它能起到过滤器的作用；New Closing High"：

import sqlite3, config
from fastapi import FastAPI, Request
from fastapi.templating import Jinja2Templates
from datetime import date
app = FastAPI()
templates = Jinja2Templates(directory="templates")
@app.get("/")
def index(request: Request):
stock_filter = request.query_params.get('filter', False)
connection = sqlite3.connect(config.DB_FILE)
connection.row_factory = sqlite3.Row
cursor = connection.cursor()
if stock_filter == 'new_closing_highs':
cursor.execute("""
select * from (
select symbol, name, stock_id, max(close), date
from stock_price join stock on stock.id = stock_price.stock_id
group by stock_id
order by symbol
) where date = ?
""", (date.today().isoformat(),))
else: 
cursor.execute("""
SELECT id, symbol, name FROM stock order by symbol
""")
rows = cursor.fetchall()
return templates.TemplateResponse("index.html", {"request": request, "stocks": rows})
@app.get("/stock/{symbol}")
def index(request: Request, symbol):
connection = sqlite3.connect(config.DB_FILE)
connection.row_factory = sqlite3.Row
cursor = connection.cursor()
cursor.execute("""
SELECT id, symbol, name FROM stock WHERE symbol = ?
""", (symbol,))
row = cursor.fetchall()
cursor.execute("""
SELECT *FROM stock_price WHERE stock_ID = ? ORDER BY date DESC
""", (row['id'],))
prices = cursor.fetchall()
return templates.TemplateResponse("stock_detail.html", {"request": request, "stock": row, "bars": prices})

如果代码中没有定义过滤器，它只会跳回所有股票，但它显示了一个空表，我不知道为什么。用uvicorn运行这个。

到目前为止，该数据库有两个表：股票，包含Alpaca API提供的所有可交易的活跃资产：

id  symbol  name                                     Exchange
65  AAA     AAF First Priority CLO Bond ETF          ARCA
66  AAAU    Goldman Sachs Physical Gold ETF Shares   ARCA 

库存价格：

id stock_id    date      open    high    low     close  volume
1   65      2020-10-02  24.9127 24.925  24.9127  24.92  2944
2   65      2020-10-05  24.91   24.94   24.91    24.92  29000
3   65      2020-10-06  24.89   24.91   24.89    24.91  4019
4   65      2020-10-07  24.9017 24.91   24.9     24.91  3800
5   65      2020-10-08  24.9    24.905  24.9     24.9   1534
6   65      2020-10-09  24.88   24.91   24.88    24.89  16273

如果我在这里没有提供足够的信息，请告诉我。非常感谢花时间

Answer 1

我认为您的查询实际上不起作用
也许它对您所拥有的数据和特定日期'2021-04-01'来说是巧合
如果您想在特定日期获得每只股票的最高价格，您应该加入表格，按股票分组并汇总：

SELECT s.symbol, s.name, s.id, MAX(p.close) max_price, p.date
FROM stock s INNER JOIN stock_price p
ON p.stock_id = s.id
WHERE p.date = ?
GROUP BY s.symbol, s.name, s.id

Answer 2

我的坏！我还没有今天的数据！我以为我做到了，但剧本没有按计划运行。应该仔细检查一下。很抱歉我浪费了你的时间