def check_if_node_exists(driver, node_property, value): """ Queries the graph database and checks if a node with a specific property and property value already exists. :param driver: neo4j driver, which provides the connection to the neo4j graph database. :type driver: neo4j driver :param str node_property: property of the node. :param value: property value. :type value: str, int, float or bool :return: Pandas dataframe with user identifier if User with node_property and value already exists, \ if User does not exist, returns and empty dataframe. """ query_name = 'check_node' try: user_creation_cypher = get_user_creation_queries() query = user_creation_cypher[query_name]['query'].replace( 'PROPERTY', node_property) for q in query.split(';')[0:-1]: if '$' in q: result = connector.getCursorData(driver, q + ';', parameters={'value': value}) else: result = connector.getCursorData(driver, q + ';') except Exception as err: exc_type, exc_obj, exc_tb = sys.exc_info() fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1] logger.error( "Reading query {}: {}, file: {},line: {}, error: {}".format( query_name, sys.exc_info(), fname, exc_tb.tb_lineno, err)) return result
def get_subjects_enrolled_in_project(driver, projectId): """ Extracts the number of subjects included in a given project. :param driver: neo4j driver, which provides the connection to the neo4j graph database. :type driver: neo4j driver :param str projectId: external project identifier (from the graph database). :return: Number of subjects. :rtype: Numpy ndarray """ query_name = 'extract_enrolled_subjects' query = '' try: data_upload_cypher = get_data_upload_queries() query = data_upload_cypher[query_name]['query'] for q in query.split(';')[0:-1]: if '$' in q: result = connector.getCursorData(driver, q+';', parameters={'external_id': str(projectId)}) else: result = connector.getCursorData(driver, q+';') except Exception as err: exc_type, exc_obj, exc_tb = sys.exc_info() fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1] logger.error("Error: {}. Getting new subjects enrolled in project: Query name ({}) - Query ({}), error info: {}, file: {},line: {}".format(err, query_name, query, sys.exc_info(), fname, exc_tb.tb_lineno)) return result.values
def create_db_user(driver, data): """ Creates and assigns role to new graph database user, if user not in list of local users. :param driver: neo4j driver, which provides the connection to the neo4j graph database. :type driver: neo4j driver :param Series data: pandas Series with required user information (see set_arguments()). """ query_name_add = 'create_db_user' query_name_role = 'add_role_to_db_user' query_list_db_users = 'list_db_users' try: cypher = get_user_creation_queries() db_query = cypher[query_name_add]['query'] + cypher[query_name_role][ 'query'] db_users = connector.getCursorData( driver, cypher[query_list_db_users]['query'], {}) if data['username'] not in db_users['username'].to_list( ) or db_users.empty: for q in db_query.split(';')[0:-1]: result = connector.getCursorData(driver, q + ';', parameters=data.to_dict()) logger.info("New user created: {}. Result: {}".format( data['username'], result)) except Exception as err: exc_type, exc_obj, exc_tb = sys.exc_info() fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1] logger.error( "Reading query {}: {}, file: {},line: {}, error: {}".format( query_name_add, sys.exc_info(), fname, exc_tb.tb_lineno, err))
def get_db_stats_data(): """ Retrieves all the stats data from the graph database and returns them as a dictionary. :return: Dictionary of dataframes. """ query_names = ['unique_projects', 'get_db_stats', 'get_db_store_size', 'get_db_transactions', 'get_db_kernel'] df_names = ['projects', 'meta_stats', 'store_size', 'transactions', 'kernel_monitor'] dfs = {} cypher = get_query() driver = connector.getGraphDatabaseConnectionConfiguration() if driver is not None: for i, j in zip(df_names, query_names): query = cypher[j]['query'] try: data = connector.getCursorData(driver, query) if i == 'store_size': data = data.T data['size'] = [size_converter(i) for i in data[0]] dfs[i] = data.to_json(orient='records') except Exception: pass return dfs
def get_new_project_identifier(driver, projectId): """ Queries the database for the last project external identifier and returns a new sequential identifier. :param driver: neo4j driver, which provides the connection to the neo4j graph database. :type driver: neo4j driver :param str projectId: internal project identifier (CPxxxxxxxxxxxx). :return: Project external identifier. :rtype: str """ query_name = 'increment_project_id' try: project_creation_cypher = get_project_creation_queries() query = project_creation_cypher[query_name]['query'] last_project, new_id = connector.getCursorData(driver, query).values[0] if last_project is None and new_id is None: external_identifier = 'P0000001' else: length = len(last_project.split('P')[-1]) new_length = len(str(new_id)) external_identifier = 'P' + '0' * (length - new_length) + str(new_id) except Exception as err: external_identifier = None exc_type, exc_obj, exc_tb = sys.exc_info() fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1] logger.error("Reading query {}: {}, file: {},line: {}, err: {}".format( query_name, sys.exc_info(), fname, exc_tb.tb_lineno, err)) return external_identifier
def get_subject_number_in_project(driver, projectId): """ Extracts the number of subjects included in a given project. :param driver: neo4j driver, which provides the connection to the neo4j graph database. :type driver: neo4j driver :param str projectId: external project identifier (from the graph database). :return: Integer with the number of subjects. """ query_name = 'subject_number' try: cypher = get_project_creation_queries() query = cypher[query_name]['query'] result = connector.getCursorData(driver, query, parameters={ 'external_id': projectId }).values[0][0] except Exception as err: exc_type, exc_obj, exc_tb = sys.exc_info() fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1] logger.error( "Error: {}. Reading query {}: {}, file: {},line: {}".format( err, query_name, sys.exc_info(), fname, exc_tb.tb_lineno)) return result
def query_data(self): data = {} try: queries_path = os.path.join(cwd, self.queries_file) project_cypher = query_utils.read_queries(queries_path) driver = connector.getGraphDatabaseConnectionConfiguration() replace = [("PROJECTID", self.identifier)] for query_name in project_cypher: title = query_name.lower().replace('_', ' ') query = project_cypher[query_name]['query'] query_type = project_cypher[query_name]['query_type'] for r, by in replace: query = query.replace(r, by) if query_type == "pre": data[title] = connector.getCursorData(driver, query) except Exception as err: exc_type, exc_obj, exc_tb = sys.exc_info() fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1] logger.error( "Reading queries from file {}: {}, file: {},line: {}, error: {}" .format(queries_path, sys.exc_info(), fname, exc_tb.tb_lineno, err)) return data
def get_mapping_analytical_samples(project_id): from ckg.graphdb_connector import connector driver = connector.getGraphDatabaseConnectionConfiguration() mapping = {} query = "MATCH (p:Project)-[:HAS_ENROLLED]-(:Subject)-[:BELONGS_TO_SUBJECT]-()-[:SPLITTED_INTO]-(a:Analytical_sample) WHERE p.id='{}' RETURN a.external_id, a.id".format( project_id) mapping = connector.getCursorData(driver, query) if not mapping.empty: mapping = mapping.set_index("a.external_id").to_dict( orient='dict')["a.id"] return mapping
def getMappingFromDatabase(id_list, node, attribute_from='id', attribute_to='name'): id_list = ["'{}'".format(i) for i in id_list] driver = connector.getGraphDatabaseConnectionConfiguration() mapping_query = "MATCH (n:{}) WHERE n.{} IN [{}] RETURN n.{} AS from, n.{} AS to" mapping = connector.getCursorData( driver, mapping_query.format(node, attribute_from, ','.join(id_list), attribute_from, attribute_to)) if not mapping.empty: mapping = dict(zip(mapping['from'], mapping['to'])) return mapping
def map_node_name_to_id(driver, node, value): identifier = None query_name = 'map_node_name' cwd = os.path.dirname(os.path.abspath(__file__)) queries_path = "queries.yml" cypher = read_queries(os.path.join(cwd, queries_path)) query = cypher[query_name]['query'].replace('NODE', node) result = connector.getCursorData(driver, query, parameters={'name': str(value).lower()}) if result is not None and not result.empty: identifier = result.values[0][0] return identifier
def check_external_ids_in_db(driver, projectId): """ """ query_name = 'check_external_ids' query = '' result = pd.DataFrame() try: data_upload_cypher = get_data_upload_queries() query = data_upload_cypher[query_name]['query'] result = connector.getCursorData(driver, query, parameters={'external_id': str(projectId)}) except Exception as err: exc_type, exc_obj, exc_tb = sys.exc_info() fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1] logger.error("Error: {}. Checking if external identifiers exist in the database: Query name ({}) - Query ({}), error info: {}, file: {},line: {}".format(err, query_name, query, sys.exc_info(), fname, exc_tb.tb_lineno)) return result
def get_sdrf(self): sdrf_df = pd.DataFrame() try: driver = connector.getGraphDatabaseConnectionConfiguration() query_path = os.path.join(cwd, self.queries_file) project_cypher = query_utils.read_queries(query_path) query = query_utils.get_query(project_cypher, query_id="project_sdrf") df = connector.getCursorData( driver, query.replace("PROJECTID", self.identifier)) sdrf_df = builder_utils.convert_ckg_to_sdrf(df) except Exception as err: exc_type, exc_obj, exc_tb = sys.exc_info() fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1] logger.error( "Error: {}. Reading queries from file {}: {}, file: {},line: {}" .format(err, query_path, sys.exc_info(), fname, exc_tb.tb_lineno)) return sdrf_df
def get_new_analytical_sample_identifier(driver): """ Queries the database for the last analytical sample internal identifier and returns a new sequential identifier. :param driver: neo4j driver, which provides the connection to the neo4j graph database. :return: Analytical sample identifier. """ query_name = 'increment_analytical_sample_id' query = '' try: cypher = get_data_upload_queries() query = cypher[query_name]['query'] identifier = connector.getCursorData(driver, query).values[0][0] except Exception as err: identifier = None exc_type, exc_obj, exc_tb = sys.exc_info() fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1] logger.error("Error: {}. Getting new analytical sample identifiers: Query name ({}) - Query ({}), error info: {}, file: {},line: {}".format(err, query_name, query, sys.exc_info(), fname, exc_tb.tb_lineno)) return identifier
def get_new_user_identifier(driver): """ Queries the database for the last user identifier and returns a new sequential identifier. :param driver: neo4j driver, which provides the connection to the neo4j graph database. :type driver: neo4j driver :return: User identifier. :rtype: str """ query_name = 'increment_user_id' try: user_creation_cypher = get_user_creation_queries() query = user_creation_cypher[query_name]['query'] user_identifier = connector.getCursorData(driver, query).values[0][0] except Exception as err: exc_type, exc_obj, exc_tb = sys.exc_info() fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1] logger.error( "Reading query {}: {}, file: {},line: {}, error: {}".format( query, sys.exc_info(), fname, exc_tb.tb_lineno, err)) return user_identifier
def convert_ckg_clinical_to_sdrf(df): out_mapping = {'tissue':'characteristics[organism part]', 'disease': 'characteristics[disease]', 'grouping1': 'characteristics[phenotype]', 'analytical_sample': 'comment[data file]', 'subject': 'characteristics[individual]', 'biological_sample': 'source name'} cols = [] for c in df.columns: matches = re.search(r'(\d+)', c) if matches: cols.append(c) driver = connector.getGraphDatabaseConnectionConfiguration() query = '''MATCH (ef:Experimental_factor)-[r:MAPS_TO]-(c:Clinical_variable) WHERE c.name+' ('+c.id+')' IN {} RETURN c.name+' ('+c.id+')' AS from, "characteristic["+ef.name+"]" AS to, LABELS(c)''' mapping = connector.getCursorData(driver, query.format(cols)) mapping = dict(zip(mapping['from'], mapping['to'])) mapping.update(out_mapping) df = df.rename(mapping, axis=1) return df
def convert_sdrf_to_ckg(df): in_mapping = {'organism part': 'tissue', 'disease': 'disease', 'phenotype': 'grouping1', 'data file': 'analytical_sample external_id', 'individual':'subject external_id', 'source name':'biological_sample external_id'} cols = {} for c in df.columns: matches = re.search(r'\[(.+)\]', c) if matches: cols[c] = matches.group(1) driver = connector.getGraphDatabaseConnectionConfiguration() query = '''MATCH (ef:Experimental_factor)-[r:MAPS_TO]-(c:Clinical_variable) WHERE ef.name IN {} RETURN ef.name AS from, c.name+' ('+c.id+')' AS to, LABELS(c)''' mapping = connector.getCursorData(driver, query.format(list(cols.values()))) mapping = dict(zip(mapping['from'], mapping['to'])) mapping.update(in_mapping) df = df.rename(cols, axis=1).rename(mapping, axis=1) return df
def send_query(self, query): driver = connector.getGraphDatabaseConnectionConfiguration() data = connector.getCursorData(driver, query) return data