Exemple #1
0
def create_hot_spot_variants():
    onco_dict = get_oncotree_dict()
    hot_spots = read_snv_hotspot(onco_dict)
    hot_spots.extend(read_indel_hotspot(onco_dict))

    my_db = None
    my_cursor = None
    try:
        my_db = get_local_db_connection()
        my_cursor = my_db.cursor(buffered=True)
        maybe_create_and_select_database(my_cursor, 'OmniSeqKnowledgebase')
        drop_table_if_exists(my_cursor, 'hot_spot_occurrences')
        drop_table_if_exists(my_cursor, 'hot_spot')
        create_hot_spot_table(my_cursor)
        create_hot_spot_occurrences_table(my_cursor)
        for hot_spot in hot_spots:
            graph_id = 'hot_spot_' + hot_spot['name'].replace(' ','_').replace('-','_').replace('*','')
            insert_hotspot(my_cursor, hot_spot, graph_id)
            for occurrence in hot_spot['occurrences']:
                insert_hotspot_occurrence(my_cursor,occurrence,graph_id)
            my_db.commit()
    except mysql.connector.Error as error:
        print("Failed in MySQL: {}".format(error))
    finally:
        if my_db.is_connected():
            my_cursor.close()
def create_go_variant_db():
    print(datetime.datetime.now().strftime("%H:%M:%S"))
    my_db = None
    my_cursor = None
    try:
        my_db = get_local_db_connection()
        my_cursor = my_db.cursor(buffered=True)
        maybe_create_and_select_database(my_cursor, 'OmniSeqKnowledgebase')
        drop_table_if_exists(my_cursor, 'go_variant')
        create_go_variant_table(my_cursor)
        write_go_variants(my_db, my_cursor)
    except mysql.connector.Error as error:
        print("Failed in MySQL: {}".format(error))
    finally:
        if (my_db.is_connected()):
            my_cursor.close()
    print(datetime.datetime.now().strftime("%H:%M:%S"))
def create_clinvar_db():
    my_db = None
    my_cursor = None
    # filename = 'data/xClinVarVariationRelease_2020-05.xml'
    filename = clinvar_is_current()
    try:
        my_db = get_local_db_connection()
        my_cursor = my_db.cursor(buffered=True)
        maybe_create_and_select_database(my_cursor, 'OmniSeqKnowledgebase')
        drop_table_if_exists(my_cursor, 'clinvar')
        create_clinvar_table(my_cursor)

        parse_xml_file(my_db, my_cursor, filename)

    except mysql.connector.Error as error:
        print("Failed in MySQL: {}".format(error))
    finally:
        if my_db.is_connected():
            my_cursor.close()
def write_sql(db_dict, table_name):
    my_db = None
    my_cursor = None
    try:
        my_db = get_local_db_connection()
        my_cursor = my_db.cursor(buffered=True)
        for db_name in sorted(db_dict.keys()):
            maybe_create_and_select_database(my_cursor, db_name)
            #for table_name in sorted(db_dict[db_name].keys()):
            drop_table_if_exists(my_cursor, table_name)
            create_table(my_cursor, table_name, db_name, db_dict)
            load_table(my_cursor, table_name,
                       db_dict[db_name][table_name]['col_order'])
            my_db.commit()
    except mysql.connector.Error as error:
        print("Failed in MySQL: {}".format(error))
    finally:
        if (my_db.is_connected()):
            my_cursor.close()
def main(load_directory, loader_id, id_class):
    print(datetime.datetime.now().strftime("%H:%M:%S"))
    my_db = None
    my_cursor = None
    print('WARNING: skipping JAX download during development')
    #download_jax()
    table_dict = {}
    database_dict = {}
    disease_list = parse_jax()
    disease_list_with_editables = add_editables(disease_list, load_directory,
                                                loader_id, id_class)
    write_load_files(disease_list_with_editables, load_directory)
    table_name = 'jax_diseases'
    db_name = 'OmniSeqKnowledgebase2'
    table_descriptions = config.extract_file(config_directory)
    table_descr = table_descriptions['jax_diseases']
    table_dict[table_name] = table_descr
    database_dict[db_name] = table_dict
    db_dict = database_dict
    try:
        my_db = get_local_db_connection()
        my_cursor = my_db.cursor(buffered=True)
        for db_name in sorted(db_dict.keys()):
            maybe_create_and_select_database(my_cursor, db_name)
            for table_name in sorted(db_dict[db_name].keys()):
                drop_table_if_exists(my_cursor, table_name)
                create_table(my_cursor, table_name, db_name, db_dict)
                load_table(my_cursor, table_name,
                           db_dict[db_name][table_name]['col_order'])
                my_db.commit()
    except mysql.connector.Error as error:
        print("Failed in MySQL: {}".format(error))
    finally:
        if (my_db.is_connected()):
            my_cursor.close()
    print(datetime.datetime.now().strftime("%H:%M:%S"))


#if __name__ == "__main__":
#main(load_directory, loader_id, id_class)
def build_graphql_from_sql():
    print(datetime.datetime.now().strftime("%H:%M:%S"))

    schema_graphql = 'schema.graphql'
    server_write:str = 'localhost'

    my_db = None
    my_cursor = None
    erase_neo4j(schema_graphql, server_write)
    try:
        my_db = get_local_db_connection()
        my_cursor = my_db.cursor(buffered=True)
        maybe_create_and_select_database(my_cursor, 'OmniSeqKnowledgebase')
        populate_from_sql.write_authors(my_cursor, server_write)
        populate_from_sql.write_journals(my_cursor, server_write)
        populate_from_sql.write_references(my_cursor, server_write)

        populate_from_sql.write_internet_references(my_cursor, server_write)
        populate_from_sql.write_users(my_cursor, server_write)

        populate_from_sql.write_editable_statements(my_cursor, server_write)

        populate_from_sql.write_jax_genes(my_cursor, server_write)
        populate_from_sql.write_mygene_genes(my_cursor, server_write)
        populate_from_sql.write_uniprot(my_cursor, server_write)

        populate_from_sql.write_omnigene(my_cursor, server_write)

        populate_from_sql.write_jax_variants(my_cursor, server_write)
        populate_from_sql.write_hot_spot_variants(my_cursor, server_write)
        populate_from_sql.write_clinvar_variants(my_cursor, server_write)
        populate_from_sql.write_go_variants(my_cursor, server_write)
        populate_from_sql.write_ocp_variants(my_cursor, server_write)
    except mysql.connector.Error as error:
        print("Failed in MySQL: {}".format(error))
    finally:
        if (my_db.is_connected()):
            my_cursor.close()
    print(datetime.datetime.now().strftime("%H:%M:%S"))
def extract():
    # server_read: str = '165.227.89.140'
    server_read: str = '161.35.115.213'
    # server_read: str = 'localhost'
    my_db = None
    my_cursor = None
    try:
        # my_db = get_cloud_db_connection()
        my_db = get_local_db_connection()
        my_cursor = my_db.cursor(buffered=True)

        drop_database(my_cursor, 'OmniSeqKnowledgebase2')
        maybe_create_and_select_database(my_cursor, 'OmniSeqKnowledgebase2')

        create_authors_table(my_cursor)
        create_journals_table(my_cursor)
        create_references_table(my_cursor)
        create_author_ref_table(my_cursor)
        create_internet_references_table(my_cursor)
        create_user_table(my_cursor)
        create_editable_statement_table(my_cursor)
        create_es_ref_table(my_cursor)
        create_es_internet_refs_table(my_cursor)
        create_jax_gene_table(my_cursor)
        create_jax_synonym_table(my_cursor)
        create_mygene_info_gene_table(my_cursor)
        create_uniprot_entry_table(my_cursor)
        create_omnigene_table(my_cursor)
        create_jax_variant_table(my_cursor)
        create_clinvar_table(my_cursor)
        create_hot_spot_table(my_cursor)
        create_hot_spot_occurrences_table(my_cursor)
        create_go_variant_table(my_cursor)
        create_ocp_variant_table(my_cursor)

        counter = 0
        print('authors')
        for author in get_current_author_data(server_read):
            counter += 1
            # print(counter,author)
            insert_author(my_cursor, author['surname'],
                          author['first_initial'], author['id'])
            if (counter % 1000 == 0):
                print(counter)
                my_db.commit()

        print('journals')
        for journal in get_current_journal_data(server_read):
            # print(journal)
            insert_journal(my_cursor, journal['name'], journal['id'])
        my_db.commit()

        counter = 0
        print('references')
        for reference in get_current_literature_reference_data(server_read):
            # print(reference)
            insert_reference(my_cursor, reference['PMID'], reference['DOI'],
                             reference['title'], reference['journal']['id'],
                             reference['volume'], reference['first_page'],
                             reference['last_page'],
                             reference['publication_Year'],
                             reference['shortReference'],
                             reference['abstract'], reference['id'])
            for author in reference['authors']:
                author_id = author['id']
                insert_author_ref(my_cursor, author_id, reference['id'])
            counter += 1
            if (counter % 1000 == 0):
                print(counter)
                my_db.commit()
        my_db.commit()

        print('internet_references')
        for internet_reference in get_current_internet_reference_data(
                server_read):
            insert_internet_reference(my_db, my_cursor,
                                      internet_reference['accessed_date'],
                                      internet_reference['web_address'],
                                      internet_reference['shortReference'],
                                      internet_reference['id'])

        print('users')
        for user in get_current_user_data(server_read):
            print(user)
            isAdmin = 'TRUE'
            if user['isAdmin'] == False:
                isAdmin = 'FALSE'
            insert_user(my_db, my_cursor, user['name'], user['password'],
                        isAdmin, user['id'])

        print('EditableStatements')
        for es in get_current_editable_statement_data(server_read):
            deleted = 'TRUE'
            if es['deleted'] == False:
                deleted = 'FALSE'
            insert_editable_statement(my_db, my_cursor, es['field'],
                                      es['statement'], es['edit_date'],
                                      es['editor']['id'], deleted, es['id'])
            for ref in es['references']:
                if ref['__typename'] == 'InternetReference':
                    insert_es_internet_refs(my_db, my_cursor, es['id'],
                                            ref['id'])
                else:
                    insert_es_ref(my_db, my_cursor, es['id'], ref['id'])

        print('JaxGenes')
        for jaxGene in get_current_jax_gene_data(server_read):
            transcript = jaxGene['canonicalTranscript'][0]
            if transcript == 'None':
                transcript = ''
            insert_jax_gene(my_db, my_cursor, jaxGene['name'], transcript,
                            jaxGene['chromosome'], jaxGene['entrezId'],
                            jaxGene['jaxId'], jaxGene['description']['id'],
                            jaxGene['id'])
            for syn in jaxGene['synonyms']:
                insert_jax_synonym(my_db, my_cursor, syn, jaxGene['id'])

        print('mygene_info_gene')
        for myGene in get_current_myGene_info_gene_data(server_read):
            insert_mygene_info_gene(my_db, my_cursor, myGene['name'],
                                    myGene['chromosome'], myGene['strand'],
                                    myGene['start'], myGene['end'],
                                    myGene['entrezId'],
                                    myGene['description']['id'], myGene['id'])

        print('uniprot_entry')
        for prot in get_current_uniprot_entry_data(server_read):
            insert_uniprot_entry(my_db, my_cursor, prot['name'],
                                 prot['accessionNumber'], prot['uniprot_id'],
                                 prot['function']['id'], prot['gene']['id'],
                                 prot['id'])

        print('omnigene')
        for omni in get_current_omnigene_data(server_read):
            # print(omni)
            # def insert_omnigene_entry(my_db,my_cursor,name,panelName,geneDescription_id,oncogenicCategory_Id,synonymsString_Id,myGeneInfo_Id,jaxGene_Id,uniprot_id,graph_id):
            insert_omnigene_entry(my_db, my_cursor, omni['name'],
                                  omni['panelName'],
                                  get_id_helper(omni, 'geneDescription'),
                                  get_id_helper(omni, 'oncogenicCategory'),
                                  get_id_helper(omni, 'synonymsString'),
                                  get_id_helper(omni, 'myGeneInfoGene'),
                                  get_id_helper(omni, 'jaxGene'),
                                  get_id_helper(omni,
                                                'uniprot_entry'), omni['id'])

    except mysql.connector.Error as error:
        print("Failed in MySQL: {}".format(error))
    finally:
        if (my_db.is_connected()):
            my_cursor.close()