1 #!/usr/bin/env python3.5 2 # -*- coding:utf8 -*- 3 # 多对多关联 4 from sqlalchemy import Table, Column, Integer, ForeignKey,String,create_engine,and_,or_,func 5 from sqlalchemy.orm import relationship,sessionmaker 6 from sqlalchemy.ext.declarative import declarative_base 7 Base = declarative_base() #生成一个SqlORM 基类 8 engine = create_engine("mysql+pymysql://python:123456@192.168.1.190:3306/python",echo=True) 9 # 1、创建一个关联表，关联两个对象 组和主机10 Host2Group = Table('host_2_group',Base.metadata,11                     Column('host_id',ForeignKey('hosts.id'),primary_key=True),12                     Column('group_id',ForeignKey('group.id'),primary_key=True),13                     )14 15 class Host(Base):16     __tablename__ = 'hosts'17     id = Column(Integer,primary_key=True,autoincrement=True)18     hostname = Column(String(64),unique=True,nullable=False)19     ip_addr = Column(String(128),unique=True,nullable=False)20     port = Column(Integer,default=22)21     groups = relationship("Group",22                           secondary=Host2Group, # 指定中间表的实例23                           backref ='host_list') # backref参数则对关系提供反向引用的声明。24     def __repr__(self):25          return "
     
      "%\26                (self.id,27                 self.hostname,28                 self.ip_addr)29 class Group(Base):30     __tablename__ ="group"31     id = Column(Integer,primary_key=True)32     name = Column(String(64),unique=True,nullable=False)33     def __repr__(self):34         return "
      
       "%\35                 (self.id,36                 self.name)37 38 39 Base.metadata.create_all(engine) #创建所有表结构40 #41 if __name__ == '__main__':42     SessionCls = sessionmaker(bind=engine) #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例43     session = SessionCls()  # 这才是连接的实例44     # g1 = Group(name = "g1")45     # g2 = Group(name = "g2")46     # g3 = Group(name = "g3")47     # g4 = Group(name = "g4")48     '''49     h1 = Host(hostname='h1',ip_addr='192.168.2.242',port = 2000)50     h2 = Host(hostname='h2',ip_addr='192.168.2.243',port=20000)51     h3 = Host(hostname='h3',ip_addr='192.168.2.244',port=20000)52     session.add_all([h1,h2,h3])53     '''54     # 关联主机55     # 找组56     g1 = session.query(Group).first()57     # groups = session.query(Group).all()58     # h1 = session.query(Host).filter(Host.hostname == "h1").first()59     # 找主机60     h2 = session.query(Host).filter(Host.hostname == "h2").first()61     # h3 = session.query(Host).filter(Host.hostname == "h3").first()62     # # h1.groups = groups63     # # h1.groups.pop()64     # 关联，使一个主机属于多个组65     # h2.groups = groups[1:-1]66     # 查询67     print("======>",h2.groups)68     print("=====>",g1.host_list)69     # update70     # obj = session.query(Host).filter(Host.hostname == "test_localhost").first()71     # print("++>",obj)72     # #obj.hostname = "test_localhost"  # 更改73     # session.delete(obj)74     # objs = session.query(Host).filter(and_(Host.hostname.like("ubun%"),Host.port > 20)).all()75     # print("+++>",objs)76 77 78     session.commit()