资产管理(一)
建立数据库
drop database link;
create database if not exists link default charset utf8mb4 collate utf8mb4_general_ci;
grant all privileges on link.* to link@'localhost' identified by '@link';
flush privileges
set foreign_key_checks=0;
set foreign_key_checks=1;
数据表
# 机房 机柜 机架 主机
from . import db
class Location(db.Model):
__tablename__ = 'location'
uuid = db.Column(db.String(40), primary_key=True)
name = db.Column(db.String(20))
id = db.Column(db.String(20), unique=True)
description = db.Column(db.Text)
cabinets = db.relationship('Cabinet', backref='location')
class Cabinet(db.Model):
__tablename__ = 'cabinet'
uuid = db.Column(db.String(40), primary_key=True)
id = db.Column(db.String(20))
capacity = db.Column(db.Integer, default=42)
description = db.Column(db.Text)
location_uuid = db.Column(db.String(40), db.ForeignKey('location.uuid', onupdate='CASCADE', ondelete='SET NULL'))
racks = db.relationship('Rack', backref='cabinet')
class Rack(db.Model):
__tablename__ = 'rack'
uuid = db.Column(db.String(40), primary_key=True)
id = db.Column(db.Integer)
cabinet_uuid = db.Column(db.String(40), db.ForeignKey('cabinet.uuid', onupdate='CASCADE', ondelete='SET NULL'))
host_uuid = db.Column(db.String(40), db.ForeignKey('host.uuid', onupdate='CASCADE', ondelete='SET NULL'))
class Host(db.Model):
__tablename__ = 'host'
uuid = db.Column(db.String(40), primary_key=True)
name = db.Column(db.String(20), unique=True, index=True)
size = db.Column(db.Integer, nullable=True, default=1)
category = db.Column(db.String(20), nullable=True, default='host')
description = db.Column(db.Text)
racks = db.relationship('Rack', backref='host')
手工建立模拟数据
from app import db
from app.models import *
from sqlalchemy import func, desc
import uuid, random
db.create_all()
#建立location
l_bj = Location(uuid=uuid.uuid4(), name='北京', id='bj', description='北京')
l_sh = Location(uuid=uuid.uuid4(), name='上海', id='sh', description='上海')
db.session.add_all([l_bj, l_sh])
db.session.commit()
#建立cabinet
c_bj = [Cabinet(uuid=uuid.uuid4(), id='bj0'+str(id), capacity=random.choice([42]), description='bj0'+str(id)) for id in xrange(1, 8)]
c_sh = [Cabinet(uuid=uuid.uuid4(), id='sh0'+str(id), capacity=random.choice([42]), description='sh0'+str(id)) for id in xrange(1, 4)]
db.session.add_all(c_bj)
db.session.add_all(c_sh)
db.session.commit()
#建立rack，并关联对应的cabinet
for id in ['bj01','bj02', 'bj03', 'bj04', 'bj05', 'bj06', 'bj07', 'sh01', 'sh02', 'sh03']:
cabinet = Cabinet.query.filter(Cabinet.id==id).first()
racks = [Rack(uuid=uuid.uuid4(), id=i, cabinet=cabinet) for i in xrange(1, cabinet.capacity+1)]
db.session.add_all(racks)
db.session.commit()
#建立host
for i in xrange(11, 81):
name = random.choice(['bj01','bj02', 'bj03', 'bj04', 'bj05', 'bj06', 'bj07'])
host = Host(uuid=uuid.uuid4(), name=name+'-'+str(i), size=random.choice([1,2]), category='host', description=name+'-'+str(i))
db.session.add(host)
for i in xrange(11, 21):
name = random.choice(['sh01','sh02', 'sh03'])
host = Host(uuid=uuid.uuid4(), name=name+'-'+str(i), size=random.choice([1,2]), category='host', description=name+'-'+str(i))
db.session.add(host)
db.session.commit()
#关联cabinet和location
for id in ['bj', 'sh']:
location = Location.query.filter(Location.id==id).first()
for cabinet in Cabinet.query.filter(Cabinet.id.like('%'+id+'%')):
cabinet.location = location
db.session.commit()
#关联host和rack
interval = 1
for id in ['bj01','bj02', 'bj03', 'bj04', 'bj05', 'bj06', 'bj07']:
start = 0
racks = Rack.query.join(Cabinet).filter(Rack.cabinet_uuid==Cabinet.uuid).filter(Cabinet.id==id).order_by(Rack.id)
for host in Host.query.filter(Host.name.like('%'+id+'%')).order_by(Host.name):
end = start + host.size
host.racks = racks[start:end]
start = end + interval
db.session.commit()
posted on
2015-10-19 11:57
北京涛子 阅读(
...) 评论(
)
编辑
收藏
转载于:https://www.cnblogs.com/liujitao79/p/4891477.html
