1 #!/usr/bin/env python 2 #coding:utf8 3 4 from sqlalchemy import create_engine,and_,or_,func,Table 5 from sqlalchemy.ext.declarative import declarative_base 6 from sqlalchemy import Column, Integer, String,ForeignKey 7 from sqlalchemy.orm import sessionmaker,relationship 8 9 10 Base=declarative_base() 11 engine=create_engine("mysql+mysqldb://root@localhost:3306/xym",echo=False)#echo=True可以打印详细的原生SQL 12 13 14 15 16 17 User2Group = Table('user2group_uc',Base.metadata, #多个用户对应多个组，同时多个组对应多个用户，多对多 18 Column('users_id',ForeignKey('users.id'),primary_key=True), 19 Column('groups_id',ForeignKey('groups.id'),primary_key=True) 20 ) 21 22 Host2Group = Table('host2group_uc',Base.metadata, #多个主机对应多个组，同时多个组对应多个主机，多对多 23 Column('hosts_id',ForeignKey('hosts.id'),primary_key=True), 24 Column('groups_id',ForeignKey('groups.id'),primary_key=True) 25 ) 26 27 28 class Host(Base): 29 __tablename__ = 'hosts' 30 id = Column(Integer,primary_key=True,autoincrement=True) 31 hostname = Column(String(64),unique=True,nullable=False) 32 ip_addr = Column(String(128),unique=True,nullable=False) 33 port = Column(Integer,default=22) 34 group = relationship('Group', #一个主机对应多个组，一个组对应多个主机，多对多,group表可以通过host_list字段找到group对应的主机，host表能通过group这个字段找到这个主机对应的group 35 secondary=Host2Group, 36 backref='host_list') #反向关联，从Group表往这里关联host_list字段,groups.host_list就能获取到group的所有主机 37 def __repr__(self): 38 return "<hostname=%s, ip_addr=%s>" %(#如果没有定义repr,那么all返回的是所有的groups记录对应的sqlalchemy封装的表结构 39 self.hostname, 40 self.ip_addr) 41 42 43 class Group(Base): 44 __tablename__ = 'groups' 45 id = Column(Integer,primary_key=True) 46 name = Column(String(64),unique=True,nullable=False) 47 def __repr__(self): 48 return "<name=%s>" %(self.name) 49 50 51 52 class User(Base): 53 __tablename__ = 'users' 54 id = Column(Integer,primary_key=True) 55 username = Column(String(64),unique=True,nullable=False) 56 password = Column(String(255),nullable=False) 57 group = relationship('Group', 58 secondary=User2Group, 59 backref='user_list') 60 def __repr__(self): 61 return "<username=%s,password=%s>" %(self.username,self.password) 62 63 Base.metadata.create_all(engine)#创建所有表结构 64 SessionCls=sessionmaker(bind=engine)#创建于数据库的会话session class,这里返回的是session的class 65 session=SessionCls() 66 67 68 def create_database(): 69 70 #########添加组######## 71 dba_group=Group(name='dba') 72 sys_group=Group(name='system') 73 session.add_all([dba_group,sys_group]) 74 session.commit() 75 ##################添加主机##################### 76 nginx_server=Host(hostname='nginx_server',ip_addr='192.168.20.136') 77 lvs_server=Host(hostname='lvs_server',ip_addr='192.168.20.137') 78 mysql_server=Host(hostname='mysql_server',ip_addr='192.168.20.138') 79 redis_server=Host(hostname='redis_server',ip_addr='192.168.20.139',port=2211) 80 session.add_all([nginx_server,lvs_server,mysql_server,redis_server]) 81 session.commit() 82 ####################添加用户################### 83 user1=User(username='root',password='123456') 84 user2=User(username='abc',password='123') 85 session.add_all([user1,user2]) 86 session.commit() 87 ################Host2Group关联################ 88 sys_groups=session.query(Group).filter(Group.name=='system').all() 89 dba_groups=session.query(Group).filter(Group.name=='dba').all() 90 nginx_server.group=sys_groups 91 lvs_server.group=sys_groups 92 mysql_server.group=dba_groups 93 redis_server.group=dba_groups 94 session.commit() 95 #################User2Group################# 96 user1.group=sys_groups 97 user2.group=dba_groups 98 session.commit() 99 100 101 102 def Auth(username,password): 103 #查询用户 104 result=session.query(User).filter(User.username==username,User.password==password).all() 105 if result: 106 print '[%s]:登录成功'%username 107 #获取用户ID 108 cuser_id=session.query(User).filter(User.username==username).first().id 109 #获取组ID 110 group_id = session.query(User2Group).filter(User2Group.c.users_id==cuser_id).first().groups_id #如果不是通过class创建的表，需要.c才能使用后面字段名 111 #获取组名 112 group_name=session.query(Group).filter(Group.id==group_id).first() 113 print "[--{:^10}--]".format(group_name.name) 114 for name in group_name.host_list: 115 print name.hostname,name.ip_addr 116 return True 117 else: 118 print '登录失败' 119 return False
