如何使用SQL Alchemy查询联接? [英] How to query with joins using sql alchemy?

问题描述

我正在尝试将SqlAlchemy与mysql作为后端一起使用.以下是我的表架构(使用SQLAlchemy为ORM定义):

I am trying to use SqlAlchemy with mysql as backend.The following are my table schema (defined for ORM using SQLAlchemy):

class ListItem(Base):
""""""
__tablename__ = "listitem"

ListItemID = Column(Integer, primary_key=True)
ListItemTypeID = Column(Integer, ForeignKey("ListItemType.ListItemTypeID"))
ModelID = Column(Integer, ForeignKey("Model.ModelID"))
RefCode = Column(String(25))

def __init__(self, ListItemTypeID, ModelID, RefCode):
    self.ListItemTypeID= ListItemTypeID
    self.ModelID= ModelID
    self.RefCode= RefCode

class Model(Base):
""""""
__tablename__ = "model"

ModelID= Column(Integer, primary_key=True)
Name = Column(String(255))  

def __init__(self, Name):
    self.Name= Name

我不包括(ListItemType)之类的其他引用表的类映射器.

I am not including the class mappers for the other reference tables like (ListItemType).

我想知道如何查询将"ListItem"表连接到"Model"表和"ListItemType"表.

I would like to know how to query joining the "ListItem" table to the "Model" table and "ListItemType" table.

与SQL等效的方法应该是这种方式:

An SQL equivalent of the same should be this way:

select listitem.ListItemID, model.Name, listitemtype.Name from listitemrequest
join listitemtype on listitemrequest.ListItemTypeID = listitemtype.ListItemID
join model on listitemrequest.ModelID = Model.ModelID

我对使用sqlalchemy相当陌生.感谢您的任何事先帮助.

I am fairly new with using sqlalchemy. Thanks for any help in advance.

推荐答案

如果这些列已经具有外键关系，则应该可以使用以下内容.

If the columns already have a foreign key relationship the following should work.

在联接上阅读 docs .

result = session.query(listitemrequest).
         join(listitemtype).
         join(model).
         with_entities([listitem.c.ListItemID, mode.c.name,listitemtype.c.Name]).
         all()

这篇关于如何使用SQL Alchemy查询联接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！