从 SQLAlchemy 子查询中选择整个实体

问题描述

如果我在 SQLAlchemy 查询中指定映射类(~= 数据库表)，则返回的行将包含这些类的实例:

If I specify mapped classes (~= database tables) in a SQLAlchemy query, then the returned rows will contain instances of those classes:

q = sess.query(table1, table2, table3.string_column)
q.first()
==> ( <instance of table1>,
      <instance of table2>,
      'string' )

但是，如果我从子查询中进行选择，则返回的行将包含单个列而不是类实例:

However, if I select from a subquery, then the returned rows contain the individual columns rather than the class instances:

q = sess.query(table1, table2, table3.string_column)

q2 = sess.query( q.subquery() )
q2.first()
==> ( col1_of_table1, col2_of_table1, ...,
      col2_of_table2, col2_of_table2, ...,
      'string' )

有没有办法指定我要将子查询中的行保留为映射类的实例?

Is there a way to specify that I want to preserve the row from the subquery as an instance of a mapped class?

如果不加入映射类的新实例，我无法弄清楚如何执行此操作.corresponding_column 方法允许我从子查询中引用特定的列，但我不知道如何从子查询中引用完整的实体.我试过玩 select_from 但它没有给我正确的行为.

I can't figure out how to do this without joining to a new instance of the mapped classes. The corresponding_column method allows me to refer to specific columns from the subquery, but I can't figure out how to refer to complete entities from the subquery. I've tried playing around with select_from but it doesn't give me the right behavior.

有什么建议吗?

推荐答案

返回实体的查询总是需要被告知这些实体——这就是它想要选择"的东西.行的来源是它想要从"选择的内容.两件不同的事情.

the query that returns entities always needs to be told about those entities - thats the things it wants to "select". The source of rows is what it wants to select "from". Two separate things.

如此给出:

q = sess.query(table1, table2, table3.string_column)
q = q.subquery()

一旦你调用了 subquery()，查询的 ORM 特性几乎消失了，它只是一个 SELECT 对象.要从中选择实体，您必须再次命名它们，并使用 select_entity_from(如果您实际上使用的是 0.7，则是当时的 select_from() ；您需要具体说明不是正确的行为):

once you call subquery(), the ORM-ness of the query is mostly gone, it's just a SELECT object. To select entities from that you have to name them out again, and use select_entity_from (which if you are actually on 0.7, is what select_from() was back then; you'd need to be specific about "not the right behavior):

q2 = sess.query(table1, table2, table3.string_column).select_entity_from(q)

这篇关于从 SQLAlchemy 子查询中选择整个实体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！