def sync_cmdb(self): """ 将RDS信息入库 :return: """ rds_list = self.get_rds_info() if not rds_list: ins_log.read_log('error', 'Not Fount rds info...') return False with DBContext('w') as session: for rds in rds_list: ins_log.read_log('info', 'RDS信息:{}'.format(rds)) db_code = rds.get('db_code') exist_rds = session.query(DB).filter(DB.db_code == db_code).first() if exist_rds: session.query(DB).filter(DB.db_code == db_code).update({ DB.idc: self.idc, DB.db_class: rds.get('db_class'), DB.db_host: rds.get('db_host'), DB.db_port: rds.get('db_port'), DB.db_disk: rds.get('db_disk'), DB.db_region: rds.get('db_region'), DB.db_type: rds.get('db_type'), DB.db_version: rds.get('db_version'), DB.state: rds.get('state'), DB.db_env: 'Null', DB.db_instance_id: rds.get('db_instance_id'), DB.tag: rds.get('tag'),DB.db_multiaz: rds.get('db_multiaz'),DB.Iops: rds.get('Iops')}) else: new_db = DB(idc=self.idc, db_code=db_code, db_class=rds.get('db_class'), db_host=rds.get('db_host'), db_port=rds.get('db_port'), db_user=rds.get('db_user'), db_disk=rds.get('db_disk'), db_region=rds.get('db_region'), db_type=rds.get('db_type'), db_version=rds.get('db_version'), state=rds.get('state'), db_env='Null', db_instance_id=rds.get('db_instance_id'),tag=rds.get('tag'), db_multiaz=rds.get('db_multiaz'),Iops=rds.get('Iops')) session.add(new_db) session.commit()
def sync_cmdb(self): """ 入库 :return: """ redis_info_list = self.get_redis_all() if not redis_info_list: return False with DBContext('w') as session: for data in redis_info_list: for redis in data: ins_log.read_log('info', 'redis信息:{}'.format(redis)) db_code = redis.get('db_code') exist_redis = session.query(DB).filter(DB.db_code == db_code).first() if exist_redis: session.query(DB).filter(DB.db_code == db_code).update({ DB.idc: self.idc, DB.db_class: redis.get('db_class'), DB.db_host: redis.get('db_host'), DB.db_port: redis.get('db_port'), DB.db_user: redis.get('db_user'), DB.db_disk: redis.get('db_disk'), DB.db_region: redis.get('db_region'), DB.db_type: redis.get('db_type'), DB.db_version: redis.get('db_version'), DB.state: redis.get('state'), DB.db_mark: redis.get('db_mark'), DB.db_public_ip: redis.get('db_public_ip'), DB.db_instance_id: redis.get('db_instance_id')}) else: new_db = DB(idc=self.idc, db_code=db_code, db_class=redis.get('db_class'), db_host=redis.get('db_host'), db_port=redis.get('db_port'), db_user=redis.get('db_user'), db_region=redis.get('db_region'), db_type=redis.get('db_type'), db_disk=redis.get('db_disk'), db_version=redis.get('db_version'), state=redis.get('state'), db_public_ip=redis.get('db_public_ip'), db_instance_id=redis.get('db_instance_id')) session.add(new_db) session.commit()
def sync_cmdb(self): """ 入库 :return: """ redis_info_list = self.get_region_redis() memcached_info_list = self.get_region_memcached() if not redis_info_list and not memcached_info_list: return False cache_list = [] cache_list.extend(redis_info_list) cache_list.extend(memcached_info_list) if not cache_list: return False with DBContext('w') as session: for i in cache_list: ins_log.read_log('info', 'Cache info:{}'.format(i)) db_code = i.get('db_code') exist_redis = session.query(DB).filter( DB.db_code == db_code).first() if exist_redis: session.query(DB).filter(DB.db_code == db_code).update({ DB.idc: self.idc, DB.db_class: i.get('db_class'), DB.db_host: i.get('db_host'), DB.db_port: i.get('db_port'), DB.db_region: i.get('db_region'), DB.db_type: i.get('db_type'), DB.db_version: i.get('db_version'), DB.db_mark: i.get('db_mark'), DB.state: i.get('state') }) else: new_db = DB(idc=self.idc, db_code=db_code, db_class=i.get('db_class'), db_host=i.get('db_host'), db_port=i.get('db_port'), db_mark=i.get('db_mark'), db_user=i.get('db_user'), db_region=i.get('db_region'), db_type=i.get('db_type'), db_version=i.get('db_version'), state=i.get('state'), db_detail=i.get('db_detail')) session.add(new_db) session.commit()
def test_connection(self): self.db = DB(DB_PATH) self.assertIsNotNone(self.db, msg='Impossible to create db "test_db"') self.assertIsNotNone(self.db.get_con(), msg='Impossible to connect to db "test_db"') self.db.close() self.assertIsNone(self.db.conn, msg='Impossible to disconnect to db "test_db"')
def sync_cmdb(self): """ 写入CMDB :return: """ db_list = self.get_udb_all() if not db_list: ins_log.read_log('info', 'Not fount db info...') return False with DBContext('w') as session: for data in db_list: for rds in data: ins_log.read_log('info', '资产信息:{}'.format(rds)) try: db_user = rds['user'] except KeyError: db_user = '******' db_name = rds.get('db_name') exist_rds = session.query(DB).filter( DB.db_code == db_name).first() if exist_rds: session.query(DB).filter(DB.db_code == db_name).update( { DB.idc: self.idc, DB.db_host: rds.get('db_host'), DB.db_port: rds.get('db_port'), DB.db_user: db_user, DB.db_disk: rds.get('db_disk'), DB.db_region: rds.get('db_region'), DB.db_type: rds.get('db_type'), DB.db_version: rds.get('db_version'), DB.state: rds.get('instance_state'), DB.db_mark: rds.get('db_role'), DB.db_instance_id: rds.get('db_instance_id') }) else: new_db = DB(idc=self.idc, db_code=db_name, db_host=rds.get('db_host'), db_port=rds.get('db_port'), db_user=db_user, db_disk=rds.get('db_disk'), db_region=rds.get('db_region'), db_type=rds.get('db_type'), db_mark=rds.get('db_role'), db_version=rds.get('db_version'), state=rds.get('instance_state'), db_instance_id=rds.get('db_instance_id')) session.add(new_db) session.commit()
def post(self, *args, **kwargs): data = json.loads(self.request.body.decode("utf-8")) nickname = self.get_current_nickname() db_code = data.get('db_code', None) db_host = data.get('db_host', None) db_port = data.get('db_port', 3306) db_user = data.get('db_user', None) db_pwd = data.get('db_pwd', None) db_env = data.get('db_env', None) proxy_host = data.get('proxy_host', None) db_type = data.get('db_type', 'mysql') db_version = data.get('db_version', None) db_mark = data.get('db_mark', '写') tag_list = data.get('tag_list', []) db_detail = data.get('db_detail', None) db_instance = data.get('db_instance', '') if not db_code or not db_host or not db_port or not db_user: return self.write(dict(code=-1, msg='关键参数不能为空')) with DBContext('r') as session: exist_id = session.query(DB.id).filter(DB.db_code == db_code, DB.db_host == db_host, DB.db_port == db_port, DB.db_user == db_user, DB.db_env == db_env, DB.proxy_host == proxy_host, DB.db_type == db_type, db_version == db_version, DB.db_mark == db_mark).first() if exist_id: return self.write(dict(code=-2, msg='不要重复记录')) # 加密密码 db_pwd = encrypt(db_pwd) with DBContext('w', None, True) as session: new_db = DB(db_code=db_code, db_host=db_host, db_port=db_port, db_user=db_user, db_pwd=db_pwd, db_env=db_env, proxy_host=proxy_host, db_type=db_type, db_version=db_version, db_mark=db_mark, db_detail=db_detail, db_instance=db_instance) session.add(new_db) all_tags = session.query(Tag.id).filter(Tag.tag_name.in_(tag_list)).order_by(Tag.id).all() if all_tags: for tag_id in all_tags: session.add(DBTag(db_id=new_db.id, tag_id=tag_id[0])) # 记录,记录错误也不要影响用户正常添加 try: with DBContext('w', None, True) as session: new_record = AssetOperationalAudit(username=nickname, request_object='数据库', request_host=db_host, request_method='新增', modify_data=data) session.add(new_record) except Exception as err: ins_log.read_log('error', 'operational_audit error:{err}'.format(err=err)) return self.write(dict(code=0, msg='添加成功'))
def post(self, *args, **kwargs): data = json.loads(self.request.body.decode("utf-8")) nickname = self.get_current_nickname() # 列表包str格式,str空格切割 if not data: return self.write(dict(code=-1, msg='不能为空')) # 记录下操作,即使报错也不影响程序 try: with DBContext('w', None, True) as session: new_record = AssetOperationalAudit(username=nickname, request_object='数据库', request_method='批量添加', modify_data=data) session.add(new_record) except Exception as err: ins_log.read_log('error', 'operational_audit error:{err}'.format(err=err)) # 判断下格式长度 with DBContext('w', None, True) as session: for i in data: if i: db_info = i.split(' ') ins_log.read_log('info', 'MultiDB:{db_info}'.format(db_info=db_info)) if len(db_info) != 6: return self.write(dict(code=-2, msg='格式错误')) db_type = db_info[0] db_name = db_info[1] db_host = db_info[2] db_port = db_info[3] db_user = db_info[4] db_passwd = db_info[5] if not type(db_port) is int and int(db_port) >= 65535: return self.write(dict(code=-1, msg="端口格式不正确")) exist_id = session.query(DB.id).filter(DB.db_code == db_name).first() exist_ip = session.query(DB.id).filter(DB.db_host == db_host).first() if exist_id or exist_ip: return self.write( dict(code=-2, msg='IP:{address}/Hostname:{hostname}已经存在,不要重复记录'.format(address=db_host, hostname=db_name))) new_db = DB(db_code=db_name, db_host=db_host, db_port=db_port, db_user=db_user, db_type=db_type, db_pwd=db_passwd, state='Handle') session.add(new_db) session.commit() return self.write(dict(code=0, msg='批量添加成功'))
async def set(self, guild, value): self.__data[guild.id] = value lock = asyncio.Lock() async with lock: db = DB() query = f""" INSERT INTO {self.table} (guild_id, value) VALUES (?, ?) ON CONFLICT (guild_id) DO UPDATE SET value=?; """ db.cursor.execute(query, (guild.id, value, value)) db.commit() db.close()
def post(self, *args, **kwargs): data = json.loads(self.request.body.decode("utf-8")) db_code = data.get('db_code', None) db_host = data.get('db_host', None) db_port = data.get('db_port', 3306) db_user = data.get('db_user', None) db_pwd = data.get('db_pwd', None) db_env = data.get('db_env', None) proxy_host = data.get('proxy_host', None) db_type = data.get('db_type', 'mysql') db_version = data.get('db_version', None) db_mark = data.get('db_mark', '写') tag_list = data.get('tag_list', []) db_detail = data.get('db_detail', None) if not db_code or not db_host or not db_port or not db_user or not db_pwd or not db_env or not db_detail: return self.write(dict(code=-1, msg='关键参数不能为空')) with DBContext('r') as session: exist_id = session.query( DB.id).filter(DB.db_code == db_code, DB.db_host == db_host, DB.db_port == db_port, DB.db_user == db_user, DB.db_env == db_env, DB.proxy_host == proxy_host, DB.db_type == db_type, db_version == db_version, DB.db_mark == db_mark).first() if exist_id: return self.write(dict(code=-2, msg='不要重复记录')) with DBContext('w', None, True) as session: new_db = DB(db_code=db_code, db_host=db_host, db_port=db_port, db_user=db_user, db_pwd=db_pwd, db_env=db_env, proxy_host=proxy_host, db_type=db_type, db_version=db_version, db_mark=db_mark, db_detail=db_detail) session.add(new_db) all_tags = session.query(Tag.id).filter( Tag.tag_name.in_(tag_list)).order_by(Tag.id).all() if all_tags: for tag_id in all_tags: session.add(DBTag(db_id=new_db.id, tag_id=tag_id[0])) return self.write(dict(code=0, msg='添加成功'))
def sync_cmdb(self): """ 将我们拿到的数据入库 :return: """ rds_info_list = self.get_rds_info() if not rds_info_list: ins_log.read_log('error', '[Error]: Not get redis info...') return False with DBContext('w') as session: for rds in rds_info_list: ins_log.read_log('info', 'RDS信息:{}'.format(rds)) db_code = rds.get('db_code') try: db_user = rds['user'] except KeyError: # 没有从接口看到阿里云的User db_user = '******' exist_rds = session.query(DB).filter(DB.db_code == db_code).first() if exist_rds: session.query(DB).filter(DB.db_code == db_code).update({ DB.idc: self.idc, DB.db_class: rds.get('db_class'), DB.db_host: rds.get('db_host'), DB.db_port: rds.get('db_port'), DB.db_user: db_user, DB.db_disk: rds.get('db_disk'), DB.db_region: rds.get('db_region'), DB.db_type: rds.get('db_type'), DB.db_version: rds.get('db_version'), DB.state: rds.get('state'), DB.db_mark: rds.get('db_mark'), DB.db_instance_id: rds.get('db_instance_id'), DB.db_detail: rds.get('db_detail')}) else: new_db = DB(idc=self.idc, db_code=db_code, db_class=rds.get('db_class'), db_host=rds.get('db_host'), db_port=rds.get('db_port'), db_user=db_user, db_disk=rds.get('db_disk'), db_region=rds.get('db_region'), db_type=rds.get('db_type'), db_mark=rds.get('db_mark'), db_version=rds.get('db_version'), state=rds.get('state'), db_instance_id=rds.get('db_instance_id'), db_detail=rds.get('db_detail')) session.add(new_db) session.commit()
def sync_cmdb(self): """ 返回结果写入CMDB :return: """ db_list = self.get_db_info() if not db_list: ins_log.read_log('error', 'Not fount db info') return False with DBContext('w') as session: for db in db_list: idc = db.get('idc') db_instance_id = db.get('db_instance_id', 'Null') db_code = db.get('db_code', 'Null') db_host = db.get('db_host', 'Null') db_public_ip = db.get('db_public_ip', 'Null') db_port = db.get('db_port', 'Null') db_region = db.get('db_region', 'Null') db_version = db.get('db_version', 'Null') db_mark = db.get('db_mark', 'Null') state = db.get('state', 'Null') db_type = db.get('db_type', 'Null') exist_rds = session.query(DB).filter( DB.db_code == db_code).first() if exist_rds: session.query(DB).filter(DB.db_code == db_code).update({ DB.idc: idc, DB.db_host: db_host, DB.db_type: db_type, DB.db_port: db_port, DB.db_region: db_region, DB.db_version: db_version, DB.state: state, DB.db_mark: db_mark, DB.db_instance_id: db_instance_id, DB.db_public_ip: db_public_ip }) else: new_db = DB(idc=idc, db_code=db_code, db_host=db_host, db_public_ip=db_public_ip, db_port=db_port, db_region=db_region, db_version=db_version, db_mark=db_mark, state=state, db_type=db_type) session.add(new_db) session.commit()
from flask import Flask, jsonify, abort, make_response from models.db import DB my_app = Flask(__name__) my_db = DB() # set up routing & controllers from controllers import users_controller, groups_controller my_app.register_blueprint(users_controller.users_blueprint) my_app.register_blueprint(groups_controller.groups_blueprint) # set up error handling def json_error_description(error): return make_response(jsonify({'error': error.description}), error.code) for error in list(range(400, 420)) + list(range(500, 506)): my_app.error_handler_spec[None][error] = json_error_description
import json from models.db import DB db = DB() db.cursor.execute( 'CREATE TABLE Prefix (guild_id INTEGER UNIQUE, value TEXT NOT NULL);') with open('prefixes.json') as f: prefixes = json.load(f) for guild_id, prefix in prefixes.items(): db.cursor.execute('INSERT INTO Prefix VALUES (?, ?);', (guild_id, prefix)) db.cursor.execute( 'CREATE TABLE Language (guild_id INTEGER UNIQUE, value TEXT NOT NULL);') with open('languages.json') as f: languages = json.load(f) for guild_id, lang in languages.items(): db.cursor.execute('INSERT INTO Language VALUES (?, ?);', (guild_id, lang)) db.cursor.execute( 'CREATE TABLE Subscription ' '(channel_id INTEGER UNIQUE, guild_id INTEGER, guild TEXT, channel TEXT, pc BOOLEAN, switch BOOLEAN);' ) with open('subscriptions.json') as f: subscriptions = json.load(f) for subscription_id, s in subscriptions.items(): db.cursor.execute( 'INSERT INTO Subscription VALUES (?, ?, ?, ?, ?, ?);', (s['channel_id'], s['channel_name'], s['guild_id'], s['guild_name'], s.get('pc', False), s.get('switch', False)))
def load(self): db = DB() result = db.cursor.execute(f'SELECT * FROM `{self.table}`;') self.__data = {guild_id: value for guild_id, value in result.fetchall()} db.close()