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 "<id =%s,hostname=%s,ip_addr =%s>"%\
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 "<group_id =%s,group_name=%s>"%\
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 = groups
63 # # h1.groups.pop()
64 # 关联，使一个主机属于多个组
65 # h2.groups = groups[1:-1]
66 # 查询
67 print("======>",h2.groups)
68 print("=====>",g1.host_list)
69 # update
70 # 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()
相关sqlalchem参考文件请点击这里>>>>>
