Sqlalichemy：在父属性中加载相关对象ID(Sqlalichemy : Loading related objects ids in parent property)

Sqlalichemy：在父属性中加载相关对象ID(Sqlalichemy : Loading related objects ids in parent property) python

使用以下表格和映射

class A: def __init__(self): self.id = None self.b_ids = {} self.b_s = {} class B: def __init__ (self): self.id = None self.a_id = None self.a = None a = Table('t_a', meta, Column('id',Integer, autoincrement=True, primary_key=True), ) b = Table('t_b', meta, Column('id',Integer, autoincrement=True, primary_key=True), Column('a_id', Integer, ForeignKey('t_a.id')), ) mapper(A, a) mapper(B, b, properties={'a' : relationship(A, backref="b_s")})

当我加载'A'时，我可以在'b_s'属性中获得相关的'B'对象。 但我想要的是A.b_ids属性中相关B的ID列表。 有没有办法做到这一点？

我试过了 ：

mapper(A, a, properties={'b_ids' : column_property(select( [b.c.id], a.c.id==b.c.a_id)) })

但它给出错误：'ProgrammingError：（ProgrammingError）由用作表达式的子查询返回的多行

With the following tables and mappings

class A: def __init__(self): self.id = None self.b_ids = {} self.b_s = {} class B: def __init__ (self): self.id = None self.a_id = None self.a = None a = Table('t_a', meta, Column('id',Integer, autoincrement=True, primary_key=True), ) b = Table('t_b', meta, Column('id',Integer, autoincrement=True, primary_key=True), Column('a_id', Integer, ForeignKey('t_a.id')), ) mapper(A, a) mapper(B, b, properties={'a' : relationship(A, backref="b_s")})

When I load 'A' I can get the related 'B' objects in the 'b_s' property. But What I want is a list of ids of related Bs in the A.b_ids property. Is there a way to do this?

I tried :

mapper(A, a, properties={'b_ids' : column_property(select( [b.c.id], a.c.id==b.c.a_id)) })

But its giving error: 'ProgrammingError: (ProgrammingError) more than one row returned by a subquery used as an expression'

最满意答案

column_property（）用于向SELECT语句添加另一列，例如：

SELECT a.x, a.y, (SELECT b.id FROM b where a.id=b.a_id) AS b_id FROM a

在SQL中，这是SELECT语句的columns子句中的子查询，并且它需要为每行返回一行/列 - 在SQLAlchemy中，我们将其称为“标量选择”。

在这种情况下，您正在寻找一个列表，因此没有嵌入到主列子句中。 这就是数据库为此返回错误的原因。 您的映射上已经有一个“b ID”列表，格式为“b_s”。 你只想从中提取“id”，这可以通过关联代理很容易地完成：

from sqlalchemy.ext.associationproxy import association_proxy class A(object): # ... other things b_ids = association_proxy('b_s', 'id')

当你访问“a.b_ids”时，它将查看“b_s”中的每个条目并提取“id”属性，返回一个与“b_s”集合类型兼容的集合，在本例中是一个Python列表。

column_property() is used to add another column to the SELECT statement, such as this:

SELECT a.x, a.y, (SELECT b.id FROM b where a.id=b.a_id) AS b_id FROM a

In SQL, that's a subquery in the columns clause of the SELECT statement, and it needs to return exactly one row/column for each row - in SQLAlchemy we call this a "scalar select".

In this case you're looking for a list, so that's not embedded into the main columns clause there. That's why the database returns an error for that. Your mapping already has a list of "b ids" on it, in the form of "b_s". You just want to extract the "id" from that, and this can be done pretty easily with the association proxy:

from sqlalchemy.ext.associationproxy import association_proxy class A(object): # ... other things b_ids = association_proxy('b_s', 'id')

When you access "a.b_ids", it will look at each entry within "b_s" and extract the "id" attribute, returning a collection that's compatible with the type of collection of "b_s", which in this case is a Python list.