#取主机名为c1的服务器ID号host_obj = session.query(Host).filter(Host.hostname == ‘c1‘).first()#通过获取到的服务器ID号去对应的关系表中找到用户ID，得到一个列表，里面是元祖host_2_host_user = session.query(HostToHostUser.host_user_id).filter(HostToHostUser.host_id == host_obj.nid).all()# print (host_2_host_user)   #[(1,), (2,), (3,)]#处理元祖ret = zip(*host_2_host_user)# print (list(ret)[0])users = session.query(HostUser.username).filter(HostUser.nid.in_(list(ret)[0])).all()print (users)
执行结果：

[(‘root‘,), (‘db‘,), (‘nb‘,)]

#coding:utf-8from sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Indexfrom sqlalchemy.orm import sessionmaker, relationshipfrom sqlalchemy import create_engineengine = create_engine("mysql+pymysql://root:Passw0rd!@192.168.2.101/beadata", max_overflow=5)Base = declarative_base()class Host(Base):    __tablename__ = ‘host‘    nid = Column(Integer,primary_key=True,autoincrement=True)    hostname = Column(String(32))class HostUser(Base):    __tablename__ = ‘host_user‘    nid = Column(Integer,primary_key=True,autoincrement=True)    username = Column(String(32))class HostToHostUser(Base):    __tablename__ = ‘host_to_host_user‘    nid = Column(Integer,primary_key=True,autoincrement=True)    host_id = Column(Integer,ForeignKey(‘host.nid‘))    host_user_id = Column(Integer,ForeignKey(‘host_user.nid‘))    host = relationship(‘Host‘,backref = ‘h‘)    host_user = relationship(‘HostUser‘,backref = ‘u‘)Session = sessionmaker(bind=engine)session = Session()# session.add_all([#     Host(hostname=‘c1‘),#     Host(hostname=‘c2‘),#     Host(hostname=‘c3‘),#     Host(hostname=‘c4‘),#     Host(hostname=‘c5‘),# ])## session.add_all([#     HostUser(username=‘root‘),#     HostUser(username=‘db‘),#     HostUser(username=‘nb‘),#     HostUser(username=‘sb‘),# ])## session.add_all([#     HostToHostUser(host_id=‘1‘,host_user_id=‘1‘),#     HostToHostUser(host_id=‘1‘,host_user_id=‘2‘),#     HostToHostUser(host_id=‘1‘,host_user_id=‘3‘),#     HostToHostUser(host_id=‘2‘,host_user_id=‘2‘),#     HostToHostUser(host_id=‘2‘,host_user_id=‘4‘),#     HostToHostUser(host_id=‘2‘,host_user_id=‘3‘),# ])host_obj = session.query(Host).filter(Host.hostname == ‘c1‘).first()print (host_obj.nid)print (host_obj.hostname)print (host_obj.h)
执行结果：

1c1[<__main__.HostToHostUser object at 0x0000000003FA3208>, <__main__.HostToHostUser object at 0x0000000003FA3278>, <__main__.HostToHostUser object at 0x0000000003FA32E8>]

for item in host_obj.h:    print (item.host_user,item.host_user.username)

执行结果：

1c1[<__main__.HostToHostUser object at 0x0000000003FA2208>, <__main__.HostToHostUser object at 0x0000000003FA2278>, <__main__.HostToHostUser object at 0x0000000003FA22E8>]<__main__.HostUser object at 0x0000000003FA28D0> root<__main__.HostUser object at 0x0000000003FA2A90> db<__main__.HostUser object at 0x0000000003FA2C50> nb