继续上一篇SQLAlchemy的学习之旅。

多对多表的创建

表Host和表HostUser通过表HostToHostUser关联在一起

from sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index,Tablefrom sqlalchemy.orm import sessionmaker, relationshipengine = create_engine("mysql+pymysql://yli:yli@sydnagios:3306/mydb", max_overflow=5)Base = declarative_base()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'))class Host(Base): # metaclass,Host.table对象    __tablename__ = 'host'    nid = Column(Integer, primary_key=True,autoincrement=True)    hostname = Column(String(32))    port = Column(String(32))    ip = Column(String(32))    # host_user = relationship('HostUser', secondary=HostToHostUser, backref='h')    host_user = relationship('HostUser', secondary=HostToHostUser.__table__, backref='h')class HostUser(Base):    __tablename__ = 'host_user'    nid = Column(Integer, primary_key=True,autoincrement=True)    username = Column(String(32))def init_db():    Base.metadata.create_all(engine)## def drop_db():#     Base.metadata.drop_all(engine)init_db()Session = sessionmaker(bind=engine)session = Session()session.add_all([    Host(hostname='c1',port='22',ip='1.1.1.1'),    Host(hostname='c2',port='22',ip='1.1.1.2'),    Host(hostname='c3',port='22',ip='1.1.1.3'),    Host(hostname='c4',port='22',ip='1.1.1.4'),    Host(hostname='c5',port='22',ip='1.1.1.5'),])session.commit()session.add_all([    HostUser(username='root'),    HostUser(username='db'),    HostUser(username='nb'),    HostUser(username='sb'),])session.commit()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),])session.commit()

结果如下

例1. 获取主机1的所有用户,原理和1对多的一样，通过relationship快速定位到对应的表

from sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index,Tablefrom sqlalchemy.orm import sessionmaker, relationshipengine = create_engine("mysql+pymysql://yli:yli@sydnagios:3306/mydb", max_overflow=5)Base = declarative_base()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')    class Host(Base): # metaclass,Host.table对象    __tablename__ = 'host'    nid = Column(Integer, primary_key=True,autoincrement=True)    hostname = Column(String(32))    port = Column(String(32))    ip = Column(String(32))class HostUser(Base):    __tablename__ = 'host_user'    nid = Column(Integer, primary_key=True,autoincrement=True)    username = Column(String(32))    #def init_db():#    Base.metadata.create_all(engine)## def drop_db():#     Base.metadata.drop_all(engine)#init_db()Session = sessionmaker(bind=engine)session = Session()host_obj = session.query(Host).filter(Host.hostname=='c1').first()print(host_obj.nid)print(host_obj.hostname)# 第三表对应的对象（逆向查询）print(host_obj.h)# 循环获取的第三表对应的对象for item in host_obj.h:    print(item.host_user,item.host_user.nid,item.host_user.username)    --------------"C:\Program Files\Python3\python.exe" "C:/Users/yli/Documents/Tencent Files/38144205/FileRecv/s13课上代码/s13课上代码/s13day13课上代码/s13day13_课上代码/ORM—de,p.py"1c1[<__main__.HostToHostUser object at 0x000002678C0A3CC0>, <__main__.HostToHostUser object at 0x000002678C0B53C8>, <__main__.HostToHostUser object at 0x000002678C0B5438>]<__main__.HostUser object at 0x000002678C0B5748> 1 root<__main__.HostUser object at 0x000002678C0B5908> 2 db<__main__.HostUser object at 0x000002678C0B5AC8> 3 nbProcess finished with exit code 0

例2，另外一种方式关联多表可以在group表上面

from sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index,Tablefrom sqlalchemy.orm import sessionmaker, relationshipengine = create_engine("mysql+pymysql://yli:yli@sydnagios:3306/mydb", max_overflow=5)Base = declarative_base()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')class Host(Base): # metaclass,Host.table对象    __tablename__ = 'host'    nid = Column(Integer, primary_key=True,autoincrement=True)    hostname = Column(String(32))    port = Column(String(32))    ip = Column(String(32))        #这次是在Host表上做关联，指定通过HostToHostUser表给HostUser关联起来    host_user = relationship('HostUser', secondary=HostToHostUser.__table__, backref='h')class HostUser(Base):    __tablename__ = 'host_user'    nid = Column(Integer, primary_key=True,autoincrement=True)    username = Column(String(32))    # host = relationship("Host", backref='h')    # host_user = relationship("HostUser", backref='u')def init_db():    Base.metadata.create_all(engine)#def drop_db():    Base.metadata.drop_all(engine)# init_db()# # drop_db()Session = sessionmaker(bind=engine)session = Session()host_obj = session.query(Host).filter(Host.hostname == 'c1').first()print(host_obj.host_user)for item in host_obj.host_user:    print(item.username)        ------------[<__main__.HostUser object at 0x000001D422BCEBA8>, <__main__.HostUser object at 0x000001D422BCE550>, <__main__.HostUser object at 0x000001D422BCE630>]rootdbnb

上面两张方式，个人觉得第一种更容易理解，第二种写代码更省事