<jupyter_start><jupyter_text>Exam 2 <NAME>  Problem 1: Controls I have created a function that can check if the number of lines of both files are the same and, in that case, if the order of the entries regarding the AGI Codes is the same.<jupyter_code>def lines_control(file_1_lines, file_2_lines):
    """" This function compares the lines
    number and order of the given files."""
    
    same = False
    
    if len(file_1_lines) == len (file_2_lines):
        print ("Both files have the same number of lines.")
        same = True
    else:
        print("The given files have not the same number of lines.")
        
    if same == True:
        
        dif = 0

        for line in range(1, len(file_1_lines)):

            line_G = file_1_lines[line].split("\t")[0]
            line_L = file_2_lines[line].split("\t")[0]

            if line_G != line_L:
                dif = 1
                break

        if dif == 0:
            print("All lines of both files are in the same sequence (order)!")
        else:
            print("The lines of both files are not in the same sequence (order).")
        
germplasm_file = open("Germplasm.tsv", "r")
locusgene_file = open("LocusGene.tsv", "r")

germplasm_lines = germplasm_file.readlines()
locusgene_lines = locusgene_file.readlines()

lines_control(germplasm_lines, locusgene_lines)<jupyter_output>Both files have the same number of lines.
All lines of both files are in the same sequence (order)!<jupyter_text>Problem 2: Design and create the database<jupyter_code>%load_ext sql
%sql mysql+pymysql://root:root@127.0.0.1:3306/mysql
#%sql drop database exam2_DB
%sql create database exam2_DB;
import pymysql.cursors

#Connection is established

connection = pymysql.connect(host='localhost',
                             user='root',
                             password='<PASSWORD>',
                             db='exam2_DB',
                             charset='utf8mb4', 
                             cursorclass=pymysql.cursors.DictCursor)
def create_function(query_list):
    """This function creates the tables following the
     queries in the given list"""
    
    for query in query_list:
        
        try:
            with connection.cursor() as cursor:

                sql = query
                cursor.execute(sql)

        except:   
            print("Oops! An error ocurred. Try again.")


query_1 = "create table Germplasm(AGI_code VARCHAR(20) NOT NULL PRIMARY KEY, Germplasm VARCHAR(50) NOT NULL, Phenotype VARCHAR(1000) NOT NULL, PUBMED INT NOT NULL)"
query_2 = "create table LocusGene(AGI_code  VARCHAR(20) NOT NULL PRIMARY KEY REFERENCES Germplasm(AGI_code), Gene VARCHAR(20) NOT NULL, Protein_Length INT NOT NULL)"

# Due to the 1:1 relationship between tables, AGI_code of LocusGene table references to the Germplasm one

query_list = [query_1, query_2]

create_function(query_list)<jupyter_output><empty_output><jupyter_text>Problem 3: Fill the database<jupyter_code>def insert_function(file_1_lines, file_2_lines, query1, query2):
    """This function fills the tables folowing the specified queries.
    It obtains the information from the given files."""
    
    for line in range(1, len(file_1_lines)):
    
        AGI_code, germplasm, phenotype, pubmed = file_1_lines[line].split("\t")    
        AGI_code, gene, ProtLen = file_2_lines[line].split("\t")
        
        #Note that AGI_code variable contains exactly the same information in both cases

        try:
            with connection.cursor() as cursor:

                sql = query1.format(AGI_code, germplasm, phenotype, pubmed)
                cursor.execute(sql)
                sql = query2.format(AGI_code, gene, ProtLen)
                cursor.execute(sql)

        except:
            print("Oops! An error ocurred. Try again.")
            
        
query1 = """INSERT INTO Germplasm(AGI_code, Germplasm, Phenotype, PUBMED)
            VALUES ("{}","{}","{}", {})"""
query2 = """INSERT INTO LocusGene(AGI_code, Gene, Protein_Length) VALUES ("{}","{}", {})"""

insert_function(germplasm_lines, locusgene_lines, query1, query2)
germplasm_file.close()
locusgene_file.close()<jupyter_output><empty_output><jupyter_text>Problem 4: Create reports, written to a file<jupyter_code>def reports_function(reports_file, write_append, report_number, query): 
    """ This function executes the specified query in order to get some information from the data base, 
    writting it to a indicated file."""
    
    reports_file = open(reports_file, write_append)
    reports_file.write("Report {}\n\n".format(report_number))
    try:
        with connection.cursor() as cursor:

            sql = query
            cursor.execute(sql)
            results = cursor.fetchall()
            header = results[0].keys()
            reports_file.write('\t'.join([str(x) for x in header]))
            reports_file.write("\n")
            for row in results:
                reports_file.write('\t'.join([str(x) for x in row.values()]))
                reports_file.write("\n")
            reports_file.write("\n\n")
    except:
        print("Oops! An error ocurred. Try again.")


    reports_file.close()

query1 = """SELECT * FROM Germplasm, LocusGene 
        WHERE Germplasm.AGI_code = LocusGene.AGI_code;"""

reports_function("Reports_file.tsv", "w", 1, query1)
query2 = """SELECT * FROM Germplasm, LocusGene 
        WHERE (LocusGene.gene = "SKOR" OR LocusGene.gene = "MAA3")
        AND Germplasm.AGI_code = LocusGene.AGI_code;"""

reports_function("Reports_file.tsv", "a", 2, query2)
query3 = """SELECT SUBSTRING(Germplasm.AGI_code, 3,1) AS 'Chromosomes', count(*) AS 'Number of entries' FROM Germplasm 
        GROUP BY SUBSTRING(Germplasm.AGI_code, 3,1);"""

reports_function("Reports_file.tsv", "a", 3, query3)
query4 = """SELECT SUBSTRING(LocusGene.AGI_code, 3,1) AS 'Chromosomes', AVG(Protein_Length) AS 'Protein_length_mean' FROM LocusGene 
        GROUP BY SUBSTRING(LocusGene.AGI_code, 3,1);"""

reports_function("Reports_file.tsv", "a", 4, query4)
connection.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10777851021753461, 'fraction_numerical': 0.014337508239947264, 'mean_word_length': 2.8903846153846153, 'pattern_counts': {'":': 0, '<': 18, '<?xml version=': 0, '>': 18, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 1, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '62902', 'license': 'MIT', 'n_tokens_mistral': 1983, 'n_tokens_neox': 1859, 'n_words': 524, 'path': 'Exam_2_answers_Andrea_Escolar.ipynb', 'repo_name': 'Andrea290799/Accelerated_Intro_WilkinsonExams', 'size': '6075'}