多对多表结构创建

from sqlalchemy import create_engine

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index

from sqlalchemy.orm import sessionmaker, relationship

engine = create_engine("mysql+pymysql://root:root@192.168.100.201:3306/web", max_overflow=5)

Base = declarative_base(engine)

Session = sessionmaker(bind=engine)

dbSession = Session()

# 多对多

class HostToHostUser(Base):

    __tablename__ = 'host_to_hostuser'

    nid = Column(Integer, primary_key=True, autoincrement=True)

    host_id = Column(Integer, ForeignKey('host.nid'))

    hostuser_id = Column(Integer, ForeignKey('hostuser.nid'))

    # 多对多操作

    host = relationship('Host', backref='h')

    hostuser = relationship('HostUser', backref='hu')

class Host(Base):

    __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.__table__, backref='h')

class HostUser(Base):

    __tablename__ = 'hostuser'

    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)

# 向表内插入数据

# ##################################################################

# dbSession.add_all([

#     Host(hostname='lala1', port='22', ip='1.1.1.1'),

#     Host(hostname='lala2', port='22', ip='1.1.1.2'),

#     Host(hostname='lala3', port='22', ip='1.1.1.3'),

#     HostToHostUser(host_id=1, hostuser_id=1),

#     HostToHostUser(host_id=1, hostuser_id=2),

#     HostToHostUser(host_id=1, hostuser_id=3),

# ])

# dbSession.commit()

##################################################################

查询数据

# 首先查询hostname 为 lala1 的 host

host_obj = dbSession.query(Host).filter(Host.hostname=='lala1').first()

print host_obj

# 通过反向查询，查到hostuser 的对象

for item in host_obj.h:

    print (item.hostuser.username)

最后总结：

多对多表结构，只需在主表上创建到子表的虚拟关系：

host_user = relationship('HostUser', secondary=HostToHostUser.__table__, backref='h')

然后在中间表中，关联两张表的外键，再通过relationship + backref参数创建虚拟关系，实现正反向查询