Python SQLAlchemy之多對多

標籤：

1.建立三張表host,host_user,host_to_host_user

host:

nid	hostname
1	c1
2	c2
3	c3
4	c4
5	c5

host_user:

nid	username
1	root
2	db
3	nb
4	sb

 

host_to_host_user:

nid	host_id	host_user_id
1	1	1
2	1	2
3	1	3
4	2	2
5	2	4
6	2	3

第一個問題：我們需要查詢c1伺服器裡面的使用者都有哪些？

如果使用傳統的方法，我們使用下面的語句來實現功能：

#取主機名稱為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‘,)]

使用新方式來實現，在建表的步驟裡面我們加入了新的relationship:

#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:[email protected]/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>]

host_obj.h擷取到三個對象，然後迴圈這三個對象，可以得到這三個對象中的host_to_host_user表中的host_user.nid,host_user.host_id,host_user.host_user_id:

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

 

Python SQLAlchemy之多對多