How to query with joins using sql alchemy?

问题:

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

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

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

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

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

回答1:

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

Read the docs on joins.

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