get nagios configure from nagiosql

get_nagios.py

import simplejson as json
import MySQLdb
from MySQLdb.cursors import DictCursor
import sqlalchemy.pool as pool

qServiceItem = ['id', 'config_name', 'service_description', 'check_command', 'max_check_attempts', 'check_interval', 'retry_interval']

def get_conn():
    c = MySQLdb.connect("localhost", "nagiosql",
                        "db@xingcloud", "XXXXXX",
                        charset='utf8',
                        cursorclass=DictCursor)
    return c

np = pool.QueuePool(get_conn, pool_size=20, max_overflow=5)


def get_conn():
    return np.connect()


def query_sql(sql):
    try:
        c = get_conn().cursor()
        c.execute(sql)
    except (AttributeError, MySQLdb.OperationalError):
        c = get_conn().cursor()
        c.execute(sql)
    return c.fetchall()

def get_hostgroups():
    sql = """SELECT id,hostgroup_name FROM tbl_hostgroup ORDER BY hostgroup_name"""
    return query_sql(sql)

def get_command(command):
    id = command.split('!')[0]
    other = '!'.join(command.split('!')[1:])
    sql = """SELECT command_name FROM tbl_command WHERE id = '%s'""" % id
    result = query_sql(sql)
    if result:
    if other:
            return command, result[0]['command_name'] + '!' + other
        else:
            return command, result[0]['command_name']
    else:
	return command, ''

def get_hosts_in_hg(hg_id):
    sql = """SELECT * FROM tbl_host WHERE id IN (
        SELECT idSlave FROM tbl_lnkHostgroupToHost WHERE idMaster='%s'
        UNION
    SELECT idMaster FROM tbl_lnkHostToHostgroup WHERE idSlave='%s'
        )""" % (hg_id, hg_id)
    return [(s['id'], s['host_name'], s['address']) for s in query_sql(sql)]

def get_service_on_host(id):
    sql = """
    SELECT %s FROM tbl_service WHERE id IN (
        SELECT idMaster FROM tbl_lnkServiceToHost WHERE idSlave = '%s'
        UNION
	SELECT idMaster FROM tbl_lnkServiceToHostgroup WHERE idSlave IN (
            SELECT idMaster FROM tbl_lnkHostgroupToHost WHERE idSlave='%s'
            UNION
            SELECT idSlave FROM tbl_lnkHostToHostgroup WHERE idMaster='%s'
        )) """ % (','.join(qServiceItem), id, id, id)
    #print sql
    servs = query_sql(sql)
    for s in servs:
        c, e = get_command(s['check_command'])
        if e:
            s['real_command'] = e
        else:
            s['real_command'] = 'NULL'
    return servs

def get_all_service_of_hg(id):
    data = {}
    data['id'] = id
    data['hs'] = []
    hs = get_hosts_in_hg(id)
    for h in hs:
        h_service = {}
        hid, host_name, address = h
        h_service['id'] = hid
        h_service['hostname'] = host_name
        h_service['address'] = address
        ss = get_service_on_host(hid)
        h_service['service'] = ss
        h_service['service_num'] = len(ss) + 1
        data['hs'].append(h_service)
    return data

hgs = get_hostgroups()
for hg in hgs:
    hg['detail'] = get_all_service_of_hg(hg['id'])
print json.dumps(hgs, sort_keys=True, indent=4)