具有自定义 SQL 延迟查询集的 Python 异步 SQL ORM

发布于 2025-01-15 06:11:32 字数 862 浏览 2 评论 0原文

我通常使用 Python ORM，如 Tortoise ORM 或 Ormar ORM（因为它们是异步的，与 FastAPI 集成良好，具有非常好的性能（对于 Tortoise ORM），可以使用单个 Pydantic/类型提示模型（对于 Ormar）。当然也出于习惯。）

但是，我现在有一个用例，似乎我无法使用 Tortoise ORM 或 Ormar ORM：我想在现有查询集上延迟应用自定义 SQL 查询（延迟，我的意思是两个连续的查询只需要稍后执行一次）。

例如，当前简化的 Tortoise ORM 惰性查询逻辑：

async def main() -> list:
    await connect_db()
    query_1 = Users.all().only("id", "name", "age")
    query_2 = query_1.order_by("-age")
    return await query_2

我需要应用一个查询（例如 query_3），但使用自定义 SQL，所以我会得到类似的结果：

async def main() -> list:
    await connect_db()
    query_1 = Users.all().only("id", "name", "age")
    query_2 = query_1.order_by("-age")
    query_3 = query_2.custom_query("SELECT id, name, age FROM users WHERE age > 18;")
    return await query_3

我可以使用 ORM 做类似的事情吗？哪一个以及如何？谢谢！

原文

I generally use Python ORMs like Tortoise ORM or Ormar ORM (because they're async, integrate well with FastAPI, have very good performances (for Tortoise ORM), can use single Pydantic/type-hinted models (for Ormar). And of course also from habit.)

However, I have now a use case it seems I cannot do with Tortoise ORM neither Ormar ORM:
I would like to do lazily apply custom SQL queries on existing querysets (by lazily, I mean the two consecutive queries need to be executed only once, later).

e.g. current simplified Tortoise ORM lazy query logic:

async def main() -> list:
    await connect_db()
    query_1 = Users.all().only("id", "name", "age")
    query_2 = query_1.order_by("-age")
    return await query_2

I would need to apply one query (say query_3) but with custom SQL, so I would have something like that:

async def main() -> list:
    await connect_db()
    query_1 = Users.all().only("id", "name", "age")
    query_2 = query_1.order_by("-age")
    query_3 = query_2.custom_query("SELECT id, name, age FROM users WHERE age > 18;")
    return await query_3

Can I do something like that with an ORM? Which one and how? Thanks!

分享到QQ

分享到微博