数据库表结构设计:
层次结构:
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
#Column导入
from sqlalchemy import Column,Enum, Integer, String,UniqueConstraint,ForeignKey,Table
from sqlalchemy.orm import sessionmaker,relationship
from sqlalchemy_utils import ChoiceType,PasswordType
Base = declarative_base()#生成orm基类
user_m2m_bindhost = Table('user_m2m_bindhost', Base.metadata,
Column('userprofile_id', Integer, ForeignKey('user_profile.id')),
Column('bindhost_id', Integer, ForeignKey('bind_host.id')),
)
bindhost_m2m_hostgroup=Table('bindhost_m2m_hostgroup', Base.metadata,
Column('bindhost_id',Integer,ForeignKey('bind_host.id')),
Column('hostgroup_id',Integer,ForeignKey('host_group.id'))
)
user_m2m_hostgroup=Table('user_m2m_hostgroup', Base.metadata,
Column('userprofile_id',Integer,ForeignKey('user_profile.id')),
Column('hostgroup_id',Integer,ForeignKey('host_group.id'))
)
class Host(Base):
__tablename__ = 'host'
id =Column(Integer,primary_key=True)
hostname =Column(String(64),unique=True)
ip = Column(String(64),unique=True)
port=Column(Integer,default=22)
# remote_users =relationship('RemoteUser', secondary = host_m2m_remoteuser, backref='hosts')
def __repr__(self):
return self.hostname
class HostGroup(Base):
__tablename__ = 'host_group'
id = Column(Integer, primary_key=True)
name = Column(String(64), unique=True)
bind_hosts = relationship('BindHost',secondary="bindhost_m2m_hostgroup",backref="host_groups")
def __repr__(self):
return self.name
class RemoteUser(Base):
__tablename__ = 'remote_user'
#创建联合唯一auth_type、username、password
__table_args__ = (UniqueConstraint('auth_type', 'username', 'password', name='_user_passwd_uc'),)
AuthTypes = [
('ssh-password', 'SSH/Password'),#第一个是存到数据库的,第二个是显示给我们看的
('ssh-key', 'SSH/KEY'),
]
id = Column(Integer, primary_key=True, autoincrement=True)
auth_type = Column(ChoiceType(AuthTypes))
username = Column(String(64), nullable=False)
password = Column(String(255))
def __repr__(self):
return self.username
class BindHost(Base):
'''
192.168.1.11 web
192.168.1.11 mysql
'''
__tablename__ = "bind_host"
__table_args__ = (UniqueConstraint('host_id','remoteuser_id', name='_host_remoteuser_uc'),)
id = Column(Integer, primary_key=True)
host_id = Column(Integer,ForeignKey('host.id'))
#group_id = Column(Integer,ForeignKey('group.id'))
remoteuser_id = Column(Integer, ForeignKey('remote_user.id'))
host = relationship("Host",backref="bind_hosts")
#host_group = relationship("HostGroup",backref="bind_hosts")
remote_user = relationship("RemoteUser",backref="bind_hosts")
def __repr__(self):
return "<%s -- %s >" %(self.host.ip,
self.remote_user.username
)
class UserProfile(Base):
__tablename__ = 'user_profile'
id = Column(Integer, primary_key=True)
username = Column(String(32), unique=True)
password = Column(String(128))
bindhosts =relationship('BindHost', secondary=user_m2m_bindhost, backref = 'user_profiles')
host_groups = relationship("HostGroup", secondary = user_m2m_hostgroup, backref = "user_profiles")
def __repr__(self):
return self.username
# class AuditLog(Base):
# pass
if __name__ == '__main__':
Base.metadata.create_all() # 创建所有表结构