多对多

• 游戏和主机关系：
• 一台server可以属于多个业务线；一个业务线也可以有多台主机（多对多）
• 此时，需要第三张表，来单独存放关系，
• 注意：关系表不用手动去管理，自动会变化（下面例子中，从头到尾都没有操控过关系表）
• 案例：

#!/usr/bin/env python
# -*- encoding:utf-8 -*-


import sqlalchemy
from sqlalchemy import DATE
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

engine = create_engine("mysql+pymysql://root:@127.0.0.1/db01?charset=utf8")
Base = declarative_base()


class Server_Business(Base):
    __tablename__ = 'server_business'
    id = Column(Integer, primary_key=True)
    Server_id = Column(Integer, ForeignKey('server.id'))
    Business_id = Column(Integer, ForeignKey('business.id'))

class Server(Base):
    __tablename__ = 'server'
    id = Column(Integer, primary_key=True)
    name = Column(String(32), nullable=False)
    address = Column(String(32), nullable=False)
    port = Column(Integer, default=22, nullable=False)

    # 'Business' 为类名称，'server_business' 为表名称
    business = relationship('Business', secondary='server_business', backref='server')

class Business(Base):
    __tablename__ = 'business'
    id = Column(Integer, primary_key=True)
    type = Column(String(32), nullable=False)



# Base.metadata.drop_all(bind=engine)
Base.metadata.create_all(bind=engine)


# 插入数据，随便先插入哪个表都行，因为这2张表没有直接的关联关系
Session_cls = sessionmaker(bind=engine)
session = Session_cls()
session.add_all([
    Server(name='node1', address='192.168.1.1', port=22),
    Server(name='node2', address='192.168.1.2', port=22),
    Server(name='node3', address='192.168.1.3', port=22),
    Server(name='node4', address='192.168.1.4', port=22),
    Server(name='node5', address='192.168.1.5', port=22),
    Server(name='node6', address='192.168.1.6', port=22),
])
session.add_all([
    Business(type='youxi'),
    Business(type='yunwei'),
    Business(type='shipin'),
    Business(type='jisuan'),
    Business(type='baobiao'),
])
session.commit()

# 讲 node1 划分给 id 大于 2 的业务线使用 （一台机器属于多个业务线）
server_obj = session.query(Server).filter(Server.name == 'node1').first()
print(server_obj.name, server_obj.port, server_obj.address)
business_obj_lst = session.query(Business).filter(Business.id > 2).all()
server_obj.business = business_obj_lst
session.commit()

# 将 youxi 业务线中，增加 id 大于3 的机器 （一个业务线有多个机器）
business_obj = session.query(Business).filter(Business.type == 'youxi').first()
server_obj_lst = session.query(Server).filter(Server.id > 3).all()
business_obj.server = server_obj_lst
session.commit()

# 查询游戏业务线有哪些主机，这些主机的名称和地址分别为什么
obj_lst = session.query(Business).filter(Business.type == 'youxi').first().server
for obj in obj_lst:
    print(obj.name, obj.address)

# 从游戏业务线中，删除名称为 node4 的主机
business_obj = session.query(Business).filter(Business.type == 'youxi').first()
server_obj = session.query(Server).filter(Server.name == 'node4').first()
business_obj.server.remove(server_obj)

obj_lst2 = session.query(Business).filter(Business.type == 'youxi').first().server
for obj in obj_lst2:
    print(obj.name)  # 此时看到已经没有node4