def ping_database():
"""
Performs a simple query against the database to confirm connection is up
"""
try:
# Get a DB connection and cursor
conn = connect.get_connection()
cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
# Define simple select query against the settings table
sql = "SELECT * " \
"FROM settings;"
logger = logging.getLogger(__name__)
logger.debug(f'Start: {sql}')
# Execute the query
cur.execute(sql)
rows = cur.fetchall()
logger.debug(f'End: {sql}')
# Providing we get some results from the query, return True
if rows:
return True
# Catch any exceptions and represent as an error, return False
except Exception as e:
logger.error("Error performing database status check", exc_info=True)
return False
def execute_isolated_postgres_statement(sql):
# conn = connection.get_connection('postgres', 'postgres', '', 'dev.ods.cis.spine2.ncrs.nhs.uk', 5432)
conn = connection.get_connection('postgres', 'postgres', '', 'localhost',
5432)
conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
cursor.execute(sql)
conn.close()
def search_organisation(search_text, offset=0, limit=1000, ):
logger = logging.getLogger(__name__)
if limit > 1000:
limit = 1000
# Get a database connection
conn = connect.get_connection()
# Use the RealDictCursor to return data as a python dictionary type
cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
try:
search_term = str.format("%{0}%",
search_text)
sql = "SELECT * " \
"FROM organisations " \
"WHERE name LIKE UPPER(%s) " \
"AND status = 'Active' " \
"ORDER BY name OFFSET %s LIMIT %s;"
data = (search_term, offset, limit)
logger.debug("Query: {sql}".format(sql=sql))
cur.execute(sql, data)
rows = cur.fetchall()
logger.debug("Number of rows retrieved: {row_count}".format(row_count=rows))
# Raise an exception if the organisation record is not found
if not rows:
raise Exception("Record Not Found")
result = []
for row in rows:
link_self_href = str.format('{0}/organisations/{1}',
app.config['APP_HOSTNAME'],
row['odscode'])
item = {
'odsCode': row['odscode'],
'name': row['name'],
'recordClass': row['record_class'],
'links': [{
'rel': 'self',
'href': link_self_href
}]
}
result.append(item)
return result
except Exception as e:
logger.error(e)
def kill_db_connections(target_db_name):
kill_connection_sql = '''SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = '{}'
AND pid <> pg_backend_pid();'''.format(
target_db_name)
# conn = connection.get_connection('postgres', 'postgres', '', 'dev.ods.cis.spine2.ncrs.nhs.uk', 5432)
conn = connection.get_connection('postgres', 'postgres', '', 'localhost',
5432)
cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
cursor.execute(kill_connection_sql)
conn.close()
def get_role_types():
logger = logging.getLogger(__name__)
conn = connect.get_connection()
cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
cur.execute("SELECT displayname, id "
"FROM codesystems "
"WHERE name = 'OrganisationRole' "
"ORDER BY displayname;")
rows = cur.fetchall()
result = []
for row in rows:
role_code = row['id']
role_display_name = row['displayname']
link_self_href = str.format('{0}/role-types/{1}',
app.config['APP_HOSTNAME'],
role_code)
link_search_primary_role_code_href = str.format('{0}/organisations?primaryRoleCode={1}',
app.config['APP_HOSTNAME'],
role_code)
link_search_role_code_href = str.format('{0}/organisations?roleCode={1}',
app.config['APP_HOSTNAME'],
role_code)
result_data = {
'name': role_display_name,
'code': role_code,
'links': [{
'rel': 'self',
'href': link_self_href
}, {
'rel': 'organisations.searchByRoleCode',
'href': link_search_role_code_href
}]
}
if not feature.is_active('SuppressPrimaryRoleSearchLink'):
result_data['links'].append({
'rel': 'organisations.searchByPrimaryRoleCode',
'href': link_search_primary_role_code_href
})
result.append(result_data)
logger.debug("Returning: {result}".format(result=result))
return result
def get_org_list(offset=0, limit=20, recordclass='both',
primary_role_code_list=None, role_code_list=None,
query=None, postcode=None, active=True, last_updated_since=None,
legally_active=None):
"""Retrieves a list of organisations
Parameters
----------
q = search term
offset = the record from which to start
limit = the maximum number of records to return
recordclass = the type of record to return (HSCSite, HSCOrg, Both)
primary_role_code = filter organisations to only those where this is their primary role code
role_code = filter organisations to only those a role with this code
postcode = filter organisations to those with a match on the postcode
active = filter organisations by their status (active / inactive)
last_changed_since = filter organisations by their lastUpdated date
legally_active = filter organisations to exclude those with legal end date prior to now
Returns
-------
List of organisations filtered by provided parameters
"""
logger = logging.getLogger(__name__)
if int(limit) > 1000:
limit = 1000
conn = connect.get_connection()
cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
# TODO: Sort out this slightly dodgy code for filtering by record class.
# Statement below doesn't look like it's actually using the filtered value...
record_class_param = '%' if recordclass == 'both' else recordclass
# Start the select statement with the field list and from clause
sql = "SELECT odscode, name, record_class, status, post_code " \
"FROM organisations " \
"WHERE TRUE "
sql_count = "SELECT COUNT(*) FROM organisations WHERE TRUE "
data = ()
# If a record_class parameter was specified, add that to the statement
if recordclass:
logger.debug('record_class parameter was provided')
sql = str.format("{0} {1}",
sql,
"AND record_class LIKE %s ")
sql_count = str.format("{0} {1}",
sql_count,
"AND record_class LIKE %s ")
data = (recordclass,)
# If a query parameter was specified, add that to the statement
if query:
logger.debug("q parameter was provided")
sql = str.format("{0} {1}",
sql,
"AND name LIKE UPPER(%s) ")
sql_count = str.format("{0} {1}",
sql_count,
"AND name LIKE UPPER(%s) ")
search_query = str.format("%{0}%",
query)
data = data + (search_query,)
# If a postcode parameter was specified, add that to the statement
if postcode:
logger.debug("postcode parameter was provided")
new_clause = "AND post_code LIKE UPPER(%s) "
sql = "{sql} {new_sql}".format(
sql=sql, new_sql=new_clause)
sql_count = "{sql} {new_sql}".format(
sql=sql_count, new_sql=new_clause)
postcode_query = str.format("%{0}%",
postcode)
data = data + (postcode_query,)
# If the active parameter was specified, add that to the statement
if active:
logger.debug("active parameter was provided")
if active in (True, 1, '1', 'True', 'true', 'TRUE', 'yes', 'Yes', 'YES'):
active_value = 'Active'
new_clause = "AND status = %s "
else:
active_value = 'Inactive'
new_clause = "AND status = %s "
sql = "{sql} {new_sql}".format(
sql=sql, new_sql=new_clause)
sql_count = "{sql} {new_sql}".format(
sql=sql_count, new_sql=new_clause)
data = data + (active_value,)
# If the last_changed_since parameter was specified, add that to the statement
if last_updated_since:
logger.debug("last_changed_since parameter was provided")
new_clause = "AND last_changed > %s "
sql = "{sql} {new_sql}".format(
sql=sql, new_sql=new_clause)
sql_count = "{sql} {new_sql}".format(
sql=sql_count, new_sql=new_clause)
data = data + (last_updated_since,)
# If the legally_active parameter was specified, check for true or false, and append correct clause to query
if legally_active:
# If value for legallyActive parameter is any of the below list (True), filter the query to include only
# organisations that have a legal_end_date in the future, or do not have one
if legally_active in (True, 1, '1', 'True', 'true', 'TRUE', 'yes', 'Yes', 'YES'):
logger.debug("legally_active parameter was True")
new_clause = "AND (legal_end_date > now() or legal_end_date ISNULL) "
sql = "{sql} {new_sql}".format(
sql=sql, new_sql=new_clause)
sql_count = "{sql} {new_sql}".format(
sql=sql_count, new_sql=new_clause)
# If value for legallyActive parameter is any of the below list (False), filter the query to include only
# organisations that have a legal_end_date in the past and are therefore not legally active today
elif legally_active in (False, 0, '0', 'False', 'false', 'FALSE', 'no', 'No', 'NO'):
logger.debug("legally_active parameter was False")
new_clause = "AND legal_end_date < now() "
sql = "{sql} {new_sql}".format(
sql=sql, new_sql=new_clause)
sql_count = "{sql} {new_sql}".format(
sql=sql_count, new_sql=new_clause)
# If value for legallyActive parameter doesn't match the True or False list, ignore it
else:
logger.debug("legally_active parameter value not recognised - ignored")
# If a role_code parameter was specified, add that to the statement
if role_code_list:
logger.debug('role_code parameter was provided')
sql = str.format("{0} {1}",
sql,
"AND odscode in "
"(SELECT org_odscode "
"FROM roles "
"WHERE status = 'Active' "
"AND code = ANY(%s)) ")
sql_count = str.format("{0} {1}",
sql_count,
"AND odscode in "
"(SELECT org_odscode "
"FROM roles "
"WHERE status = 'Active' "
"AND UPPER(code) = ANY(%s)) ")
data = data + (role_code_list,)
# Or if a primary_role_code parameter was specified, add that to the statement
elif primary_role_code_list:
logger.debug('primary_role_code parameter was provided')
sql = str.format("{0} {1}",
sql,
"AND odscode IN "
"(SELECT org_odscode "
"FROM roles "
"WHERE primary_role = TRUE "
"AND status = 'Active' "
"AND code = ANY(%s)) ")
sql_count = str.format("{0} {1}",
sql_count,
"AND odscode IN "
"(SELECT org_odscode "
"FROM roles "
"WHERE primary_role = TRUE "
"AND status = 'Active' "
"AND code = ANY(%s)) ")
data = data + (primary_role_code_list,)
# Quickly get total number of query results before applying offset and limit
cur.execute(sql_count, data)
count = cur.fetchone()['count']
# Lastly, add the offset and limit clauses to the main select statement
sql = str.format("{0} {1}",
sql,
"ORDER BY name "
"OFFSET %s "
"LIMIT %s;")
data = data + (offset, limit)
logger.debug(sql)
# Execute the main query
cur.execute(sql, data)
rows = cur.fetchall()
logger.debug(str.format("{0} results", len(rows)))
result = []
for row in rows:
link_self_href = str.format('{0}/organisations/{1}',
app.config['APP_HOSTNAME'],
row['odscode'])
item = {
'postCode': row['post_code'],
'odsCode': row['odscode'],
'name': row['name'],
'recordClass': row['record_class'],
'status': row['status'],
'links': [{
'rel': 'self',
'href': link_self_href
}]
}
result.append(item)
# Return both the paged results and the count of total results
return result, count
def get_organisation_by_odscode(odscode):
logger = logging.getLogger(__name__)
# Get a database connection
conn = connect.get_connection()
# Use the RealDictCursor to return data as a python dictionary type
cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
# Try and retrieve the organisation record for the provided ODS code
try:
sql = "SELECT * " \
"FROM organisations " \
"WHERE odscode = UPPER(%s) " \
"LIMIT 1;"
data = (odscode,)
cur.execute(sql, data)
row_org = cur.fetchone()
logger.debug(str.format('requestId="{1}"|Organisation Record:{0}',
row_org, g.request_id))
# Raise an exception if the organisation record is not found
if row_org is None:
raise Exception(str.format('requestId="{0}"|Record Not Found', g.request_id))
row_org = remove_none_values_from_dictionary(row_org)
# Get the organisation ref from the retrieved record
organisation_odscode = row_org['odscode']
# Retrieve the roles for the organisation
try:
sql = "SELECT r.code, csr.displayname, r.unique_id, r.status, " \
"r.operational_start_date, r.operational_end_date, r.legal_start_date, " \
"r.legal_end_date, r.primary_role " \
"FROM roles r " \
"LEFT JOIN codesystems csr on r.code = csr.id " \
"WHERE r.org_odscode = UPPER(%s) " \
"AND csr.name = 'OrganisationRole';"
data = (organisation_odscode,)
cur.execute(sql, data)
rows_roles = cur.fetchall()
except Exception:
raise
# Retrieve the relationships for the organisation
try:
sql = "SELECT rs.code, csr.displayname, rs.unique_id, rs.target_odscode, rs.status, " \
"rs.operational_start_date, rs.operational_end_date, rs.legal_start_date, " \
"rs.legal_end_date, o.name " \
"FROM relationships rs " \
"LEFT JOIN codesystems csr on rs.code = csr.id " \
"LEFT JOIN organisations o on rs.target_odscode = o.odscode " \
"WHERE rs.org_odscode = UPPER(%s);"
data = (organisation_odscode,)
cur.execute(sql, data)
rows_relationships = cur.fetchall()
except Exception:
raise
# Retrieve the addresses for the organisation
try:
sql = "SELECT address_line1, " \
"address_line2, " \
"address_line3, " \
"town, county, " \
"post_code, " \
"country " \
"FROM addresses a " \
"WHERE a.org_odscode = UPPER(%s);"
data = (organisation_odscode,)
cur.execute(sql, data)
rows_addresses = cur.fetchall()
logger.debug("Addresses: %s" % rows_addresses)
except Exception:
raise
# Retrieve the successors / predecessors for the organisation
try:
sql = "SELECT type, target_odscode as targetOdsCode, " \
"o.name as targetName, " \
"target_primary_role_code as targetPrimaryRoleCode, " \
"unique_id as uniqueId " \
"FROM successors s " \
"LEFT JOIN organisations o on s.target_odscode = o.odscode " \
"WHERE s.org_odscode = UPPER(%s);"
data = (organisation_odscode,)
cur.execute(sql, data)
rows_successors = cur.fetchall()
logger.debug(str.format('requestId="{0}"|Successors: {1}',
g.request_id,
rows_successors))
except Exception:
raise
# Create an object from the returned organisation record to hold the data to be returned
result_data = row_org
# Add the retrieved relationships data to the object
relationships = []
for relationship in rows_relationships:
relationship = remove_none_values_from_dictionary(relationship)
link_target_href = str.format('{0}/organisations/{1}',
app.config['APP_HOSTNAME'],
relationship['target_odscode'])
relationship['uniqueId'] = int(relationship.pop('unique_id'))
relationship['relatedOdsCode'] = relationship.pop('target_odscode')
relationship['relatedOrganisationName'] = relationship.pop('name')
relationship['description'] = relationship.pop('displayname')
relationship['status'] = relationship.pop('status')
try:
relationship['operationalStartDate'] = relationship.pop('operational_start_date').isoformat()
except:
pass
try:
relationship['legalEndDate'] = relationship.pop('legal_end_date').isoformat()
except:
pass
try:
relationship['legalStartDate'] = relationship.pop('legal_start_date').isoformat()
except:
pass
try:
relationship['operationalEndDate'] = relationship.pop('operational_end_date').isoformat()
except:
pass
relationship['links'] = [{
'rel': 'related-organisation',
'href': link_target_href
}]
relationships.append(relationship)
result_data['relationships'] = relationships
# Add the retrieved roles data to the object
roles = []
for role in rows_roles:
role = remove_none_values_from_dictionary(role)
link_role_href = str.format('{0}/role-types/{1}',
app.config['APP_HOSTNAME'],
role['code'])
role['code'] = role.pop('code')
role['description'] = role.pop('displayname')
role['primaryRole'] = role.pop('primary_role')
try:
role['status'] = role.pop('status')
except:
pass
try:
role['uniqueId'] = int(role.pop('unique_id'))
except:
pass
try:
role['operationalStartDate'] = role.pop('operational_start_date').isoformat()
except Exception:
pass
try:
role['legalEndDate'] = role.pop('legal_end_date').isoformat()
except Exception:
pass
try:
role['legalStartDate'] = role.pop('legal_start_date').isoformat()
except Exception:
pass
try:
role['operationalEndDate'] = role.pop('operational_end_date').isoformat()
except Exception:
pass
role['links'] = [{
'rel': 'role-type',
'href': link_role_href
}]
roles.append(role)
result_data['roles'] = roles
# Add the addresses to the object
addresses = []
for address in rows_addresses:
address = remove_none_values_from_dictionary(address)
address_lines = []
try:
address_lines.append(address.pop('address_line1'))
except:
pass
try:
address_lines.append(address.pop('address_line2'))
except:
pass
try:
address_lines.append(address.pop('address_line3'))
except:
pass
if len(address_lines) > 0:
address['addressLines'] = address_lines
try:
address['postCode'] = address.pop('post_code')
except:
pass
addresses.append(address)
result_data['addresses'] = addresses
# Add the successors to the object
successors = []
for successor in rows_successors:
link_successor_href = str.format('{0}/organisations/{1}',
app.config['APP_HOSTNAME'],
successor['targetodscode'])
successor = remove_none_values_from_dictionary(successor)
successor['targetOdsCode'] = successor.pop('targetodscode')
successor['targetPrimaryRoleCode'] = successor.pop('targetprimaryrolecode')
successor['targetName'] = successor.pop('targetname')
successor['uniqueId'] = successor.pop('uniqueid')
successor['links'] = [{
'rel': str.lower(successor['type']),
'href': link_successor_href
}]
successors.append(successor)
result_data['successors'] = successors
# Tidy up the field names etc. in the organisation dictionary before it's returned
result_data['odsCode'] = result_data.pop('odscode')
result_data['lastChangeDate'] = result_data.pop('last_changed')
result_data['refOnly'] = bool(result_data.pop('ref_only'))
result_data['recordClass'] = result_data.pop('record_class')
result_data.pop('post_code')
result_data.pop('ref')
link_self_href = str.format('{0}/organisations/{1}',
app.config['APP_HOSTNAME'],
result_data['odsCode'])
result_data['links'] = [
{'rel': 'self',
'href': link_self_href
}
]
try:
result_data['operationalStartDate'] = result_data.pop('operational_start_date').isoformat()
except:
pass
try:
result_data['legalEndDate'] = result_data.pop('legal_end_date').isoformat()
except:
pass
try:
result_data['legalStartDate'] = result_data.pop('legal_start_date').isoformat()
except:
pass
try:
result_data['operationalEndDate'] = result_data.pop('operational_end_date').isoformat()
except:
pass
return result_data
except psycopg2.DatabaseError as e:
logger.error(str.format("Error {0}", e))
except Exception as e:
logger.error(e)