将别名和不同的子查询从SQLite转换为SQLAlchemy

Question

我正在尝试将SQLite语句转换为用于FASTApi的python SQLAlchemy。我不知道如何将别名为s和p的查询转换为单个prices表。

以下是SQLite查询：

SELECT s.security_id, p.price, MAX(p.price_datetime) price_datetime
FROM (SELECT DISTINCT security_id FROM prices) s
LEFT JOIN prices p ON p.security_id = s.security_id AND p.price_datetime <= '2022-08-10 19:000:00.000000'
GROUP BY s.security_id;

这是我迄今为止的尝试：

# starting attempt so far
select(models.Price.security_id, models.Price.price, func.max(models.Price.price_datetime), models.Price.price_datetime)

Answer 1

首先想知道的是为什么要有这么复杂的查询？选择不同的security_id再次加入，按security_id分组对我来说毫无意义

我已经提出了一个简单得多的版本，在我的测试中也是如此。

SELECT security_id, price, MAX(price_datetime) price_datetime
FROM prices
WHERE price_datetime <= '2022-02-01'
GROUP BY security_id;

这是相当容易翻译成SQLAlchemy。

stmt = (
select(
Price.security_id,
Price.price,
func.max(Price.price_datetime).alias("price_datetime"),
)
.filter(Price.price_datetime <= '2022-02-01')
.group_by(Price.security_id)
)

OP评论后：

SELECT s.id, p.price, MAX(p.price_datetime) AS price_datetime
FROM security AS s
LEFT JOIN prices as p
ON s.id = p.security_id AND p.price_datetime <= '2021-02-01'
GROUP BY s.id;

应该转换为

stmt = (
select(
Security.id,
Price.price,
func.max(Price.price_datetime).label("price_datetime"),
)
.join(
Price,
and_(
Security.id == Price.security_id,
Price.price_datetime <= "2022-01-01",
),
isouter=True,
)
.group_by(Security.id)
)