<jupyter_start><jupyter_text>Before anything: Start your mySQL Server from terminal<jupyter_code>%load_ext sql
#%config SqlMagic.autocommit=False
%sql mysql+pymysql://root:root@127.0.0.1:3306/mysql<jupyter_output><empty_output><jupyter_text>Problem 1:  ControlsWrite a Python script that proves that the lines of data in Germplasm.tsv, and LocusGene are in the same sequence, based on the AGI Locus Code (ATxGxxxxxx).  (hint: This will help you decide how to load the data into the database)<jupyter_code># 1. Open both files en reading mode

germfile = open("Germplasm.tsv", "r")
genefile = open("LocusGene.tsv", "r")
#print(germfile.read()) #If I want, I can check that files have been opened correctly
#print(genefile.read())
germfile.seek(0) # I want to be sure that my files are read since the beginning
genefile.seek(0)

# 2. Use regular expressions for finding all the times that AGI Locus Codes appear in both files by 're.findall'
#I am going to generate two lists with AGI Codes in the same appearance order that in tsv files

import re  # this brings the python regular expression object into your program
match_germ = re.findall( r'AT\dG\d{5}', germfile.read(), re.I) #re.I makes re.findall case insensitive
match_gene = re.findall( r'AT\dG\d{5}', genefile.read(), re.I)
#print(match_germ)
#print(match_gene)

# 3. Check that both files have AGI Codes in the same lines

if match_germ == match_gene:
    print("All our data is in the same lines!! We are very tidy!!")
    
else: 
    print("Sorry! Our data is a disaster...")

# 4. Close files

germfile.close()
genefile.close()<jupyter_output><empty_output><jupyter_text>Problem 2:  Design and create the database.  * It should have two tables - one for each of the two data files.* The two tables should be linked in a 1:1 relationship* you may use either sqlMagic or pymysql to build the database<jupyter_code># 1. I create a new database by sqlMagic and I check that it exists.

%sql create database exam2
%sql show databases
# 2. I create 1st table by sqlMagic and I check its features.
%sql use exam2 #I have to select the database
%sql drop table germplasm
%sql CREATE TABLE germplasm(id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, Locus VARCHAR(20) NOT NULL, germplasm VARCHAR(50) NOT NULL, phenotype VARCHAR(500) NOT NULL, pubmed VARCHAR(20) NOT NULL);
%sql DESCRIBE germplasm
#3 I create 2nd table by sqlMagic and I check its features.
%sql use exam2 #I have to select the database
%sql drop table gene
%sql CREATE TABLE gene(id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, Locus VARCHAR(20) NOT NULL, Gene VARCHAR(15) NOT NULL, ProteinLength VARCHAR(20) NOT NULL);
%sql DESCRIBE gene
#Both tables are connected by the 'Locus' field. This guarantees the 1:1 relationship.<jupyter_output><empty_output><jupyter_text>Problem 3: Fill the databaseUsing pymysql, create a Python script that reads the data from these files, and fills the database.  There are a variety of strategies to accomplish this.  I will give all strategies equal credit - do whichever one you are most confident with.<jupyter_code># 1. I add information to the table 'gene' by pymysql from LocusGene.tsv

import csv # I have to import specific package into the program for reading csv files

import pymysql.cursors # I have to import specific package into the program for using pymysql

connection = pymysql.connect(host='localhost', #With this information I basically provide to pymysql the location of my database.
                             user='root',
                             password='<PASSWORD>',
                             db='exam2',
                             charset='utf8mb4',  # note utf8... this is important for unusual characters!
                             cursorclass=pymysql.cursors.DictCursor)

connection.autocommit = False # As I am going to commit manually, I put this on False

try:
    with connection.cursor() as cursor:
        with open("LocusGene.tsv") as tsvfile:
            geneinfo = csv.DictReader(tsvfile, delimiter="\t") 
            #'DictReader has special features for reading files: 'delimiter' indicates the separator character between fields \
            # In this case, the separator character is 'tab'
            for row in geneinfo:
                sql = """INSERT INTO gene (Locus, Gene, ProteinLength) \
                VALUES ('{}', '{}', '{}')""".format(row["Locus"],row["Gene"],row["ProteinLength"])
                #this command inserts the different data of each header field in the table and makes it row-to-row.
                cursor.execute(sql) #this command execute the previous order

    connection.commit() # I commit manually
        
finally:
    print("")
    connection.close() #I close pymysql. This is necessary for its good running

%sql SELECT * FROM gene; # I check the table after the insertion
# 2. I add information to the table 'germplasm' by pymysql from Germplasm.tsv

import csv # I have to import specific package into the program for reading csv files

import pymysql.cursors # I have to import specific package into the program for using pymysql

connection = pymysql.connect(host='localhost', #With this information I basically provide to pymysql the location of my database.
                             user='root',
                             password='<PASSWORD>',
                             db='exam2',
                             charset='utf8mb4',  # note utf8... this is important for unusual characters!
                             cursorclass=pymysql.cursors.DictCursor)

connection.autocommit = False # As I am going to commit manually, I put this on False

try:
    with connection.cursor() as cursor:
        with open("Germplasm.tsv") as tsvfile:
            geneinfo = csv.DictReader(tsvfile, delimiter="\t") 
            #'DictReader has special features for reading: 'delimiter' indicates the separator character between fields \ 
            # In this case is 'tab'
            for row in geneinfo:
                sql = """INSERT INTO germplasm (Locus, germplasm, phenotype, pubmed) \
                VALUES ('{}', '{}', '{}', '{}')""".format(row["Locus"],row["germplasm"],row["phenotype"],row["pubmed"])
                #this command inserts the different data of each field in the table and makes it row-to-row.
                cursor.execute(sql) #this command execute the previous order

    connection.commit() # I commit manually
        
finally:
    print("")
    connection.close() #I close pymysql. This is necessary.

%sql SELECT * FROM germplasm; # I check the table after the insertion<jupyter_output><empty_output><jupyter_text>Problem 4: Create reports, written to a file1. Create a report that shows the full, joined, content of the two database tables (including a header line)2. Create a joined report that only includes the Genes SKOR and MAA33. Create a report that counts the number of entries for each Chromosome (AT1Gxxxxxx to AT5Gxxxxxxx)4. Create a report that shows the average protein length for the genes on each Chromosome (AT1Gxxxxxx to AT5Gxxxxxxx)When creating reports 2 and 3, remember the "Don't Repeat Yourself" rule! All reports should be written to **the same file**.  You may name the file anything you wish. **Problem 4.1**<jupyter_code># 1. I generate a unique table with all the data and I link the data to a variable.

mother_table = %sql SELECT * FROM gene, germplasm WHERE gene.Locus = germplasm.Locus

# 2. I write the clean content of the generated variable in a csv file.

import csv
import io

with open('report_exam2.csv', 'w', newline='') as csvfile:
    reportwriter = csv.writer(csvfile, delimiter="\t", quotechar='"')
    reportwriter.writerow(["1st Report"]) # I write the title of the report
    reportwriter.writerow(("Locus", "Gene", "ProteinLength", "Germplasm", "Phenotype", "Pubmed"))  # This line writes the header
    for row in mother_table: #This command line iterates along the table for filling all the rows
        reportwriter.writerow([row["Locus"],row["Gene"],row["ProteinLength"],row["germplasm"],row["phenotype"],row["pubmed"]])
csvfile.close

#3. I check the content of the report. I think that the operation has been completed succesfully!
csvfile = io.open("report_exam2.csv")
print(csvfile.read())
csvfile.close()<jupyter_output><empty_output><jupyter_text>**Problem 4.2**<jupyter_code># 1. I generate a unique table with all the data and I link the data to a variable.

mother_table = %sql SELECT * FROM gene, germplasm WHERE gene.Locus = germplasm.Locus

# 2. I append the required content in the previous csv file.

import csv
import io

with open('report_exam2.csv', 'a', newline='') as csvfile: #The'a' argument indicates that the written information will be appended.
    reportwriter = csv.writer(csvfile, delimiter="\t", quotechar='"')
    reportwriter.writerow([""]) # I write an 'empty' line
    reportwriter.writerow(["2nd Report"])
    reportwriter.writerow(("Locus", "Gene", "ProteinLength", "Germplasm", "Phenotype", "Pubmed"))  # This line writes the header
    for row in mother_table: #This command line iterates along all the rows of the table
        if row["Gene"] == "SKOR" or row["Gene"] == "MAA3": # This clause makes that only SKOR and MAA3 files can be added
            reportwriter.writerow([row["Locus"],row["Gene"],row["ProteinLength"],row["germplasm"],row["phenotype"],row["pubmed"]])
        else:
            reportwriter.writerow(["This is TOP SECRET information"]) # In this case, I have wanted to play a little with the else clause \
            # However, this is not necessary for the good running of the if clause.
            
csvfile.close

# 3. I check the content of the report. I think that the operation has been completed succesfully!
csvfile = io.open("report_exam2.csv")
print(csvfile.read())
csvfile.close()<jupyter_output><empty_output><jupyter_text>**Problem 4.3**<jupyter_code># 1. I open LocusGene.tsv file in reading mode.

genefile = open("LocusGene.tsv", "r")

# 2. I use regular expressions for finding all the times that AGI Locus Codes appear in LocusGene.tsv by're.findall'. \
# Besides, I am going to generate five variables, one for each chromosome.

import re  # this brings the python regular expression object into your program 

genefile.seek(0) # I want to be sure that my files are read since the beginning 

match_chr1_genes = re.findall( r'AT1G\d{5}', genefile.read(), re.I) # The third character indicates the number of the chromosome. This character is the unique thing that changes in each "harvest". 
#re.findall generates a list that is associated to the variable match_chr1_genes
chr1_counts = len(match_chr1_genes) # Since match_chr1_genes is a list, I can measure the length of the list and link it to variable chr1_counts.

genefile.seek(0) #I have to add this line before each "harvest". If not, harvest will begin since the bottom of the file and harvest will be vain

match_chr2_genes = re.findall( r'AT2G\d{5}', genefile.read(), re.I) #re.I makes re.findall case insensitive
chr2_counts = len(match_chr2_genes)

genefile.seek(0)

match_chr3_genes = re.findall( r'AT3G\d{5}', genefile.read(), re.I) #re.I makes re.findall case insensitive
chr3_counts = len(match_chr3_genes)

genefile.seek(0)

match_chr4_genes = re.findall( r'AT4G\d{5}', genefile.read(), re.I) #re.I makes re.findall case insensitive
chr4_counts = len(match_chr4_genes)

genefile.seek(0)

match_chr5_genes = re.findall( r'AT5G\d{5}', genefile.read(), re.I) #re.I makes re.findall case insensitive
chr5_counts = len(match_chr5_genes)

# 3. I append to the file report.exam2.csv the 3rd report with the information of the five previous variables

report = open("report_exam2.csv", "a")
report.write("\n3rd Report\nThe 1st chromosome has " + str(chr1_counts) + " genes" \
             + ".\nThe 2nd chromosome has " + str(chr2_counts) + " genes" \
             + ".\nThe 3rd chromosome has " + str(chr3_counts) + " genes" \
             + ".\nThe 4th chromosome has " + str(chr4_counts) + " genes" \
             + ".\nThe 5th chromosome has " + str(chr5_counts) + " genes")
report.close()

#4. I check the content of the file for look at the correct appending.

checkcontent = open("report_exam2.csv", "r")
print(checkcontent.read())
checkcontent.close()
#Appendix to Problem 4.3

#I know that the 2nd step of the previous code is repeating. This can be solved writing a function, like:

def gene_counts(chr_number):
    genefile.seek(0)
    match_chr_genes = re.findall(r'AT'chr_number'G\d{5}', genefile.read(), re.I) #re.I makes re.findall case insensitive
    chr_counts = len(match_chr_genes)
    return chr_counts

#With this function, I could write the report of this way:

report = open("report_exam2.csv", "a")
report.write("\n3rd Report\nThe 1st chromosome has " + str(gene_counts(1)) + " genes" \
             + ".\nThe 2nd chromosome has " + str(gene_counts(2)) + " genes" \
             + ".\nThe 3rd chromosome has " + str(gene_counts(3)) + " genes" \
             + ".\nThe 4th chromosome has " + str(gene_counts(4)) + " genes" \
             + ".\nThe 5th chromosome has " + str(gene_counts(5)) + " genes")
report.close()

#This strategy could be much more friendly and efficient. However, the problem is that it doesn't work. 
#I would like to know if it is possible to add a variable to a regular expression, and how it is possible.<jupyter_output><empty_output><jupyter_text>**Problem 4.4**<jupyter_code># 1. I will obtain the data (the protein sizes) from LocusGene.tsv.

import csv
import re

with open('LocusGene.tsv') as csvfile: #I open the file
    gene_info = list(csv.DictReader(csvfile, delimiter="\t")) # I transform the info of the tsv file in a list
    sum_prot_chr1 = 0 # I initialize all the variables that I am going to iterate. This is important if you run the code more of one times.
    sum_prot_chr2 = 0
    sum_prot_chr3 = 0
    sum_prot_chr4 = 0
    sum_prot_chr5 = 0
    for row in gene_info: # I iterate for each row of the table (that has been transformed into a list)
        for AGI_Code in match_chr1_genes: # I iterate for each one of my five lists of genes/chromosome (generated in the previous problem)
            if row['Locus'] == AGI_Code:
                sum_prot_chr1 += int(row['ProteinLength']) #For each match, I sum the size of the protein associated to the match
                #Finally, I obtain the sum of all the proteins associated to the 1st chromosome
        for AGI_Code in match_chr2_genes:
            if row['Locus'] == AGI_Code:
                sum_prot_chr2 += int(row['ProteinLength'])
        for AGI_Code in match_chr3_genes:
            if row['Locus'] == AGI_Code:
                sum_prot_chr3 += int(row['ProteinLength'])
        for AGI_Code in match_chr4_genes:
            if row['Locus'] == AGI_Code:
                sum_prot_chr4 += int(row['ProteinLength'])
        for AGI_Code in match_chr5_genes:
            if row['Locus'] == AGI_Code:
                sum_prot_chr5 += int(row['ProteinLength'])
    avg_prot_chr1 = sum_prot_chr1 / len(match_chr1_genes) # I divide the total sum of aminoacids associated to each one \
    # of the 5 chromosomes between the total number of genes belonging to each chromosome
    avg_prot_chr2 = sum_prot_chr2 / len(match_chr2_genes)
    avg_prot_chr3 = sum_prot_chr3 / len(match_chr3_genes)
    avg_prot_chr4 = sum_prot_chr4 / len(match_chr4_genes)
    avg_prot_chr5 = sum_prot_chr5 / len(match_chr5_genes)

# 2. I write the report in report_exam2 with the results.

report = open("report_exam2.csv", "a")
report.write("\n\n4st Report\nThe average lenght of the proteins of the 1st chromosome is of " + str(avg_prot_chr1) + " aminoacids" \
             + ".\nThe average lenght of the proteins of the 2nd chromosome is of " + str(avg_prot_chr2) + " aminoacids" \
             + ".\nThe average lenght of the proteins of the 2nd chromosome is of " + str(avg_prot_chr3) + " aminoacids" \
             + ".\nThe average lenght of the proteins of the 2nd chromosome is of " + str(avg_prot_chr4) + " aminoacids" \
             + ".\nThe average lenght of the proteins of the 2nd chromosome is of " + str(avg_prot_chr5) + " aminoacids")
report.close()
    
# 3. I check the content of the file for look at the correct appending.

checkcontent = open("report_exam2.csv", "r")
print(checkcontent.read())
checkcontent.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09575053500458576, 'fraction_numerical': 0.013145826964231121, 'mean_word_length': 3.533259423503326, 'pattern_counts': {'":': 1, '<': 35, '<?xml version=': 0, '>': 35, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 1, 'substrings_counts': 2, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '645497', 'license': 'MIT', 'n_tokens_mistral': 5251, 'n_tokens_neox': 4928, 'n_words': 1888, 'path': 'Exam_2_Answers.ipynb', 'repo_name': 'cesalga/Accelerated_Intro_WilkinsonExams', 'size': '16343'}