def get_instances_by_status(status): """Returns a JSON object containing all the data for instances with a status""" try: status = str(status) conn = instdb.get_connection() curs = conn.cursor() curs.execute("""SELECT username, db_name, e_group, category, creation_date, expiry_date, db_type, db_size, no_connections, project, description, version, state, status, master, slave, host FROM dod_instances WHERE status = :status ORDER BY db_name""", {"status": status}) rows = curs.fetchall() cols = [i[0] for i in curs.description] if rows: res = create_json_from_result(rows, cols) # Load the user's data from FIM and join it to the result in Json connF = fimdb.get_connection() cursF = connF.cursor() cursF.execute("""SELECT instance_name, owner_first_name, owner_last_name, owner_login, owner_mail, owner_phone1, owner_phone2, owner_portable_phone, owner_department, owner_group, owner_section FROM fim_ora_ma.db_on_demand""") rowsF = cursF.fetchall() colsF = [i[0] for i in cursF.description] if rowsF: usersJson = create_dict_from_result(rowsF, colsF, "INSTANCE_NAME") for instance in res: if instance["DB_NAME"] in usersJson: instance["USER"] = usersJson[instance["DB_NAME"]] return res return None except DatabaseError as dberr: logging.error("PG Error: %s", dberr.pgerror) logging.error("PG Error lookup: %s", errorcodes.lookup(dberr.pgcode)) return None finally: instdb.end_connection(conn)
def get_instances_by_host(host): """Returns a JSON object containing all the data for a host""" try: host = str(host) conn = instdb.get_connection() curs = conn.cursor() curs.execute("""SELECT username, db_name, e_group, category, creation_date, expiry_date, db_type, db_size, no_connections, project, description, version, state, status, master, slave, host FROM dod_instances WHERE host = :host AND status = 1 ORDER BY db_name""", {"host": host}) rows = curs.fetchall() cols = [i[0] for i in curs.description] if rows: return create_json_from_result(rows, cols) return None except DatabaseError as dberr: logging.error("PG Error: %s", dberr.pgerror) logging.error("PG Error lookup: %s", errorcodes.lookup(dberr.pgcode)) return None finally: instdb.end_connection(conn)
def get_instances_by_host(host): """Returns a JSON object containing all the data for a host""" try: host = str(host) conn = instdb.get_connection() curs = conn.cursor() curs.execute( """SELECT username, db_name, e_group, category, creation_date, expiry_date, db_type, db_size, no_connections, project, description, version, state, status, master, slave, host FROM dod_instances WHERE host = :host AND status = 1 ORDER BY db_name""", {"host": host}) rows = curs.fetchall() cols = [i[0] for i in curs.description] if rows: return create_json_from_result(rows, cols) return None except DatabaseError as dberr: logging.error("PG Error: %s", dberr.pgerror) logging.error("PG Error lookup: %s", errorcodes.lookup(dberr.pgcode)) return None finally: instdb.end_connection(conn)
def get_instances_by_dbname(dbname): """Returns a JSON object containing all the data for a dbname""" connF = None connI = None try: dbname = str(dbname) connI = instdb.get_connection() cursI = connI.cursor() cursI.execute( """SELECT username, db_name, e_group, category, creation_date, expiry_date, db_type, db_size, no_connections, project, description, version, state, status, master, slave, host FROM dod_instances WHERE db_name = :db_name AND status = 1 ORDER BY db_name""", {"db_name": dbname}) rowsI = cursI.fetchall() colsI = [i[0] for i in cursI.description] if rowsI: res = create_json_from_result(rowsI, colsI) # Load the user's data from FIM and join it to the result in Json connF = fimdb.get_connection() cursF = connF.cursor() cursF.execute( """SELECT instance_name, owner_first_name, owner_last_name, owner_login, owner_mail, owner_phone1, owner_phone2, owner_portable_phone, owner_department, owner_group, owner_section FROM fim_ora_ma.db_on_demand WHERE instance_name = :db_name""", {"db_name": dbname}) rowsF = cursF.fetchall() colsF = [i[0] for i in cursF.description] if rowsF: res["USER"] = create_json_from_result(rowsF, colsF) return res return None except DatabaseError as dberr: logging.error("PG Error: %s", dberr.pgerror) logging.error("PG Error lookup: %s", errorcodes.lookup(dberr.pgcode)) return None finally: if connF != None: fimdb.end_connection(connF) if connI != None: instdb.end_connection(connI)
def get_instances_by_status(status): """Returns a JSON object containing all the data for instances with a status""" try: status = str(status) conn = instdb.get_connection() curs = conn.cursor() curs.execute( """SELECT username, db_name, e_group, category, creation_date, expiry_date, db_type, db_size, no_connections, project, description, version, state, status, master, slave, host FROM dod_instances WHERE status = :status ORDER BY db_name""", {"status": status}) rows = curs.fetchall() cols = [i[0] for i in curs.description] if rows: res = create_json_from_result(rows, cols) # Load the user's data from FIM and join it to the result in Json connF = fimdb.get_connection() cursF = connF.cursor() cursF.execute( """SELECT instance_name, owner_first_name, owner_last_name, owner_login, owner_mail, owner_phone1, owner_phone2, owner_portable_phone, owner_department, owner_group, owner_section FROM fim_ora_ma.db_on_demand""") rowsF = cursF.fetchall() colsF = [i[0] for i in cursF.description] if rowsF: usersJson = create_dict_from_result(rowsF, colsF, "INSTANCE_NAME") for instance in res: if instance["DB_NAME"] in usersJson: instance["USER"] = usersJson[instance["DB_NAME"]] return res return None except DatabaseError as dberr: logging.error("PG Error: %s", dberr.pgerror) logging.error("PG Error lookup: %s", errorcodes.lookup(dberr.pgcode)) return None finally: instdb.end_connection(conn)
def get_instances_by_dbname(dbname): """Returns a JSON object containing all the data for a dbname""" connF = None connI = None try: dbname = str(dbname) connI = instdb.get_connection() cursI = connI.cursor() cursI.execute("""SELECT username, db_name, e_group, category, creation_date, expiry_date, db_type, db_size, no_connections, project, description, version, state, status, master, slave, host FROM dod_instances WHERE db_name = :db_name AND status = 1 ORDER BY db_name""", {"db_name": dbname}) rowsI = cursI.fetchall() colsI = [i[0] for i in cursI.description] if rowsI: res = create_json_from_result(rowsI, colsI) # Load the user's data from FIM and join it to the result in Json connF = fimdb.get_connection() cursF = connF.cursor() cursF.execute("""SELECT instance_name, owner_first_name, owner_last_name, owner_login, owner_mail, owner_phone1, owner_phone2, owner_portable_phone, owner_department, owner_group, owner_section FROM fim_ora_ma.db_on_demand WHERE instance_name = :db_name""", {"db_name": dbname}) rowsF = cursF.fetchall() colsF = [i[0] for i in cursF.description] if rowsF: res["USER"] = create_json_from_result(rowsF, colsF) return res return None except DatabaseError as dberr: logging.error("PG Error: %s", dberr.pgerror) logging.error("PG Error lookup: %s", errorcodes.lookup(dberr.pgcode)) return None finally: if connF != None: fimdb.end_connection(connF) if connI != None: instdb.end_connection(connI)