SQLAlchemy: 부분적으로 지정된 관계 (SQLAlchemy: partially specified relationship)

문제 설명

SQLAlchemy: 부분적으로 지정된 관계 (SQLAlchemy: partially specified relationship)

Legacy SQL 스키마에 존재하는 관계를 SQLAlchemy로 설명하려고 합니다. 스키마를 다음과 같이 번역했습니다.

class Lang(Base):
    __tablename__ = "tblLang"
    id = Column("ID", Integer, primary_key=True)
    # ...

class ItemName(Base):
    __tablename__ = "tblItemName"
    # ItemName has a composite primary key
    id = Column("ID", Integer, primary_key=True)
    lang_id = Column("Lang", Integer, ForeignKey("Lang.id"), primary_key=True)
    name = Column("Name", String)

class Item(Base):
    __tablename__ = "tblItem"
    id = Column("id", Integer, primary_key=True)
    inventory = Column("Inventory", Integer)
    # item_name_id specifies only part of the table
    # relationship
    item_name_id = Column("ItemNameID", Integer, ForeignKey("tblItemName.ID"))
    # lang_id should come from outside the database:
    # a user preference stored in a configuration file
    # or a session cookie, etc.
    item_name = relationship(???)

즉, 알려진 언어 집합이 있습니다. 각 항목에 대해 하나 이상의 언어로 된 항목 이름이 있을 수 있지만 선택해야 하는 lang_id는 외부 매개변수에 의해 제공됩니다.

다음을 수행할 수 있기를 바랍니다. 쿼리 실행 전에 이 값이 설정된 쿼리 개체를 만듭니다.

제 질문은 다음과 같습니다. 위의 코드에서 ??? 대신 무엇을 넣어야 합니까? 이러한 쿼리를 생성할 수 있으며 실행 시간 전에 lang_id를 어떻게 지정해야 하나요?

편집:

Specifying Alternate Join Conditions for the documentation.

relationship(
    ItemName,
    uselist=False,
    primaryjoin=f"and_(Item.item_name_id==ItemName.id, ItemName.lang_id=={LANG_ID})",
)

As to the when when the desired lang_id become available to your application, you could simply define (or redefine) the relationship long after the model definition and completely outside of the Item model mapping:

# ....
# now define/assign the relationship once the LANG_ID is known

LANG_NAME = "English"
LANG_ID = session.query(Lang).filter(Lang.name == LANG_NAME).one().id
print(LANG_ID, LANG_NAME)

Item.item_name = relationship(
    ItemName,
    uselist=False,
    primaryjoin=f"and_(Item.item_name_id==ItemName.id, ItemName.lang_id=={LANG_ID})",
)

However, if the language is configured per user (logged in), you might need to use even later binding, or storing the Language setting in the session context, for example.

In any event, you could then construct the desired query as below:

q = (
    session
    .query(ItemName.name, Item.inventory)
    .select_from(Item)
    .join(ItemName, Item.item_name)
)

for res in q:
    print(res)

방법 2:

This will work for your sample query with your code as is, without defining the relationship:

res = session.query(
    ItemName.name,
    Item.inventory,
).select_from(Item).outerjoin(ItemName).filter(
    ItemName.lang_id == 2
)

(because only the foreign key is needed here, which you've defined)

One thing you could do with a relationship is:

from sqlalchemy.orm import aliased
from sqlalchemy.sql import func


class Item(Base):
    ...
    item_name = relationship('ItemName')

    @classmethod
    def get_lang_total_inventory_dict(cls):
        """Returns {lang_id: total_inventory}"""
        item_name = aliased(cls.item_name)

        res = session.query(
            item_name.lang_id,
            func.sum(cls.inventory),
        ).select_from(cls).join(item_name).group_by(
            item_name.lang_id
        )

        return dict(res)

Notice how ??? was replaced with 'ItemName' rather than ItemName.
This allows to use ItemName in the query without directly referring to its class.
Which prevents circular imports, a real pain when each ORM sits in its own file.

There are other things you can do with relationships as well.

방법 3:

WARNING: this is a workaround and might not be optimal or even correct

I wasn't able to find the magic incantation that will make relationship do what I want. Instead I can write a workaround:

from sqlalchemy.ext.hybrid import hybrid_property, hybrid_method


class Item(Base):
    __tablename__ = "tblItem"
    id = Column("id", Integer, primary_key=True)
    inventory = Column("Inventory", Integer)

    @hybrid_method
    def item_name(self, lang):
        return ItemName.query.filter(
            ItemName.id == self.id,
            ItemName.lang_id == lang
        )

    @hybrid_property
    def item_names(self):
        return ItemName.query.filter(ItemName.id == self.id)

I then can use it like:

item_names = Item.get(123).item_names.all()
item_klingon_name = Item.get(123).item(KLINGON_LANG_ID).one()

I am fully aware this is not a proper solution, and it does not do eager loading. I am not even sure if I use @hybrid_method and @hybrid_property correctly in this context.

I post this answer in the hope it can benefit some use‑cases, but will gladly accept other solutions, preferably ones that use relationship.

(by Chen Levy、van、EliadL、Chen Levy)

참조 문서

1. SQLAlchemy: partially specified relationship (CC BY‑SA 2.5/3.0/4.0)