day11：SqlAlchemy ORM

一.外键关联，relationship() 


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
#!/usr/bin/python
#coding=utf-8
__author__ = 'yaobin'
 
'''
使用 ORM/Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect 所有组件对数据进行操作。
根据类创建对象，对象转换成SQL，执行SQL。
'''
 
'''
relationship() 一对多
relationship(backred)|relationship(back_populates) 双向一对多
'''
 
 
from sqlalchemy import Table,Column,Integer,ForeignKey,String,create_engine,and_,or_,func,table
from sqlalchemy.orm import relationship,sessionmaker
from sqlalchemy.ext.declarative import declarative_base
 
Base=declarative_base()
 
engine = create_engine("mysql+pymysql://root:123456@localhost:3306/test2",echo=False)
 
 
Host2Group = Table('host_2_group',Base.metadata,
    Column('host_id',ForeignKey('host.id'),primary_key=True),
    Column('group_id',ForeignKey('group.id'),primary_key=True),
) #两个列不为空，且唯一
 
 
class Host(Base):
    __tablename__ = 'host'
    id = Column(Integer,primary_key=True,autoincrement=True)
    hostname = Column(String(64),unique=True,nullable=False)
    ip_addr = Column(String(128),unique=True,nullable=False)
    port = Column(Integer,default=22)
    group_id = Column(Integer,ForeignKey('group.id'))
 
    #group=relationship("Group")
    group=relationship("Group",backref='host_list') #backref 双向,这里是大写Group，类似反射,
    #group=relationship("Group",back_populates='host_list')   #back_populates 单向的，Group里面也要有host_list
 
    def __repr__(self):
        return "<id=%s,hostname=%s,ip_addr=%s>" %(self.id,
                                                  self.hostname,
                                                  self.ip_addr)
 
class Group(Base):
    __tablename__='group'
    id=Column(Integer,primary_key=True)
    name=Column(String(64),unique=True,nullable=False)
    #host_list=relationship("Host",back_populates="group")  #back_populates 单向的，Host里面也要有group
    def __repr__(self):
        return "<id=%s,name=%s>" %(self.id,self.name,)
 
 
 
#Base.metadata.create_all(engine) #创建所有表结构 # 寻找Base的所有子类，按照子类的结构在数据库中生成对应的数据表信息
 
if __name__ == '__main__':
    SessionCls = sessionmaker(bind=engine) #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
    session = SessionCls() #连接的实例
 
 
 
    # #创建组相关信息
    # g1=Group(name='group1')
    # g2=Group(name='group2')
    # g3=Group(name='group3')
    # g4=Group(name='group4')
    # g5=Group(name='group5')
    #
    # session.add_all([g1,g2,g3,g4])
    # session.add(g5)
 
 
 
    #往host表里面增加数据,
    #all_groups=session.query(Group).filter().all()  #查找全部组
    #print('---all_groups:--->',all_groups)
 
    #把全部组查出来
    query_g1=session.query(Group).filter(Group.name=='group1').first()
    query_g2=session.query(Group).filter(Group.name=='group2').first()
    query_g3=session.query(Group).filter(Group.name=='group3').first()
    query_g4=session.query(Group).filter(Group.name=='group4').first()
    query_g5=session.query(Group).filter(Group.name=='group5').first()
 
    # h1 = Host(hostname='localhost',ip_addr='192.168.1.54',port=10000,group_id=query_g1.id)
    # h2 = Host(hostname='ubuntu',ip_addr='192.168.1.55',port=10001,group_id=query_g2.id)
    h3 = Host(hostname='centos6.5',ip_addr='192.168.1.56',port=10002,group_id=query_g3.id)
    h4 = Host(hostname='centos7.1',ip_addr='192.168.1.57',port=10003,group_id=query_g4.id)
    h5 = Host(hostname='centos7.2',ip_addr='192.168.1.58',port=10004,group_id=query_g5.id)
    h6 = Host(hostname='centos5.8',ip_addr='192.168.1.59',port=10005,group_id=query_g2.id)
    h7 = Host(hostname='centos6.2',ip_addr='192.168.1.60',port=10006,group_id=query_g5.id)
    #session.add_all([h1,h2])
    #session.add_all([h3,h4,h5]) #往host表增加数据
    #session.add_all([h6,h7])
 
    #把全部主机信息查出来
    query_h1=session.query(Host).filter(Host.hostname=='localhost').first()
    query_h2=session.query(Host).filter(Host.hostname=='ubuntu').first()
    query_h3=session.query(Host).filter(Host.hostname=='centos6.5').first()
    query_h4=session.query(Host).filter(Host.hostname=='centos7.1').first()
    query_h5=session.query(Host).filter(Host.hostname=='centos7.2').first()
 
 
    #通过relationship(),打印主机对应的主机组
    print('query_h1---->group_name:',query_h1.group.name)
    print('query_h2---->group_name:',query_h2.group.name)
    print('query_h3---->group_name:',query_h3.group.name)
    print('query_h4---->group_name:',query_h4.group.name)
    print('query_h5---->group_name:',query_h5.group.name)
 
 
    #通过relationship(),打印主机组对应的主机清单
    print('group1---->host_list:',query_g1.host_list)
    print('group2---->host_list:',query_g2.host_list)
    print('group3---->host_list:',query_g3.host_list)
    print('group4---->host_list:',query_g4.host_list)
    print('group5---->host_list:',query_g5.host_list)
 
    session.commit()



二.多对多，通过中间表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
#!/usr/bin/python
#coding=utf-8
__author__ = 'yaobin'
 
'''
使用 ORM/Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect 所有组件对数据进行操作。
根据类创建对象，对象转换成SQL，执行SQL。
'''
 
'''
没有外键关联了，用中间表，实现多对多
'''
 
from sqlalchemy import Table,Column,Integer,ForeignKey,String,create_engine,and_,or_,func,table
from sqlalchemy.orm import relationship,sessionmaker
from sqlalchemy.ext.declarative import declarative_base
 
Base=declarative_base()
 
engine = create_engine("mysql+pymysql://root:123456@localhost:3306/s12day11",echo=False)
 
 
Host2Group = Table('host_2_group',Base.metadata,
    Column('host_id',ForeignKey('host.id'),primary_key=True),
    Column('group_id',ForeignKey('group.id'),primary_key=True),
) #两个列不为空，且唯一
 
 
class Host(Base):
    __tablename__ = 'host'
    id = Column(Integer,primary_key=True,autoincrement=True)
    hostname = Column(String(64),unique=True,nullable=False)
    ip_addr = Column(String(128),unique=True,nullable=False)
    port = Column(Integer,default=22)
    groups=relationship('Group',secondary=Host2Group,backref='host_list')  #backref双向,secondary指定中间表，
 
    #group=relationship("Group",backref='host_list') #backref 双向,这里是大写Group，类似反射,
    #group=relationship("Group",back_populates='host_list')   #back_populates 单向的，Group里面也要有host_list
 
    def __repr__(self):
        return "<id=%s,hostname=%s,ip_addr=%s>" %(self.id,
                                                  self.hostname,
                                                  self.ip_addr)
 
class Group(Base):
    __tablename__='group'
    id=Column(Integer,primary_key=True)
    name=Column(String(64),unique=True,nullable=False)
    #hosts=relationship("Host")
    #host_list=relationship("Host",back_populates="group")  #back_populates 单向的，Host里面也要有group
    def __repr__(self):
        return "<id=%s,name=%s>" %(self.id,self.name,)
 
 
 
#Base.metadata.create_all(engine) #创建所有表结构 # 寻找Base的所有子类，按照子类的结构在数据库中生成对应的数据表信息
 
if __name__ == '__main__':
    SessionCls = sessionmaker(bind=engine) #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
    session = SessionCls() #连接的实例
 
 
 
    #创建组相关信息
    # g1=Group(name='group1')
    # g2=Group(name='group2')
    # g3=Group(name='group3')
    # g4=Group(name='group4')
    # g5=Group(name='group5')
    #
    # session.add_all([g1,g2,g3,g4])
    # session.add(g5)
 
 
 
    # #往host表里面增加数据,
    # all_groups=session.query(Group).filter().all()  #查找全部组
    # print('---all_groups:--->',all_groups)
    # h1 = Host(hostname='localhost',ip_addr='192.168.1.54',port=10000)
    # h2 = Host(hostname='ubuntu',ip_addr='192.168.1.55',port=10001)
    # h3 = Host(hostname='centos6.5',ip_addr='192.168.1.56',port=10002)
    # h4 = Host(hostname='centos7.1',ip_addr='192.168.1.57',port=10003)
    # h5 = Host(hostname='centos7.2',ip_addr='192.168.1.58',port=10004)
    # session.add_all([h1,h2])
    # session.add_all([h3,h4,h5]) #往host表增加数据
    # #顺便生成host对应的group组，其实就是中间表host_2_group会生成一条host_id --> group_id的对应记录
    # h1.groups.append(all_groups[2])
    # h2.groups.append(all_groups[4])
    # h3.groups.append(all_groups[3])
    # h4.groups.append(all_groups[0])
    # h5.groups.append(all_groups[1])
 
 
    #like 匹配
    # obj=session.query(Host).filter(and_(Host.hostname.like('cen%'),Host.port > 20)).all()
    # print('--->',obj)
    # session.commit()
 
 
 
    #修改host信息
    # h_centos6_5_update = session.query(Host).filter(Host.hostname=='centos6.5').update({'ip_addr':'192.168.10.110'}) #修改centos6.5主机信息ip为192.168.10.110
 
 
    #h1=session.query(Host).filter(Host.hostname=='localhost').first() #查找localhost主机信息
    # h2=session.query(Host).filter(Host.hostname=='ubuntu').first() #查找ubuntu主机信息
    # h3=session.query(Host).filter(Host.hostname=='centos6.5').first() #查找centos6.5主机信息
    # h4=session.query(Host).filter(Host.hostname=='centos7.1').first() #查找centos7.1主机信息
    # h5=session.query(Host).filter(Host.hostname=='centos7.2').first() #查找centos7.2主机信息
    #
    #
    # g1=session.query(Group).filter(Group.name=="group1").first() #查询组group1信息
    # g2=session.query(Group).filter(Group.name=="group2").first() #查询组group2信息
    # g3=session.query(Group).filter(Group.name=="group3").first() #查询组group3信息
    # g4=session.query(Group).filter(Group.name=="group4").first() #查询组group4信息
    # g5=session.query(Group).filter(Group.name=="group5").first() #查询组group5信息
    #
    #
    #
    #
    #
 
    # print("[h1--->:]",h1.groups)  #通过中间表找到对应的group_id ,接着打印这个主机对应的group信息
    # print("[h2--->:]",h2.groups)  #通过中间表找到对应的group_id ,接着打印这个主机对应的group信息
    # print("[h3--->:]",h3.groups)  #通过中间表找到对应的group_id ,接着打印这个主机对应的group信息
    # print("[h4--->:]",h4.groups)  #通过中间表找到对应的group_id ,接着打印这个主机对应的group信息
    # print("[h5--->:]",h5.groups)  #通过中间表找到对应的group_id ,接着打印这个主机对应的group信息
    #
    # print("g1--->:",g1.host_list) #通过中间表找到对应的host_id ,接着打印这个组对应的host信息
    # print("g2--->:",g2.host_list) #通过中间表找到对应的host_id ,接着打印这个组对应的host信息
    # print("g3--->:",g3.host_list) #通过中间表找到对应的host_id ,接着打印这个组对应的host信息
    # print("g4--->:",g4.host_list) #通过中间表找到对应的host_id ,接着打印这个组对应的host信息
    # print("g5--->:",g5.host_list) #通过中间表找到对应的host_id ,接着打印这个组对应的host信息







posted on 2016-06-16 20:40  等待唐僧的日子  阅读(175)  评论(0)  编辑  收藏  举报