<jupyter_start><jupyter_text>**Exam 2 AIDA PINACHO** ..<jupyter_code>%load_ext sql
#%config SqlMagic.autocommit=False
%sql mysql+pymysql://root:root@127.0.0.1:3306/mysql<jupyter_output>The sql extension is already loaded. To reload it, use:
  %reload_ext sql<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>#First we need to import the files we are going to use and then open them and read their lines

import re

locus_file = open ("LocusGene.tsv", "r")
germ_file = open ("Germplasm.tsv", "r")

locus_file = locus_file.readlines()
germ_file = germ_file.readlines() 

#we can now check if the size of both files is the same

locus_size = len(locus_file)
germ_size = len(germ_file)

if locus_size == germ_size:
    print("both files have the same size")
else:
    print("the files don't have the same size")
    

#Now, we know that both files have the same size but we need to know if the AGI Locus code in both of them match. For this, we can create a loop in which it takes all the AGI LOcus Code from both files and copy them into lists in which we can check if the match or not.

locus = []
germ = []

for i in range (0,locus_size):
    matchObj = re.search(r'AT\dG\d+', locus_file[i])
    matchObj_2 = re.search(r'AT\dG\d+', germ_file[i])
    if matchObj:
        Locus_AGI = matchObj.group()
        locus.append(Locus_AGI) 
        
        Germ_AGI = matchObj_2.group()
        germ.append(Germ_AGI)
        
    else:
        print("Done")

if locus == germ:
    print("Both files are the same")
else:
    print ("The files are not the same")
    
print (locus)
print ("")
print(germ)<jupyter_output>both files have the same size
Done
Both files are the same
['AT1G01040', 'AT1G01060', 'AT1G01140', 'AT1G01220', 'AT2G03720', 'AT2G03800', 'AT2G04240', 'AT2G05210', 'AT3G02130', 'AT3G02140', 'AT3G02230', 'AT3G02260', 'AT3G02310', 'AT3G02680', 'AT3G02850', 'AT3G02870', 'AT3G03260', 'AT4G14790', 'AT4G15210', 'AT4G15560', 'AT4G15570', 'AT4G15802', 'AT4G15880', 'AT4G16420', 'AT4G16480', 'AT5G10480', 'AT5G10510', 'AT5G11110', 'AT5G11260', 'AT5G11510', 'AT5G12200', 'AT5G13290']

['AT1G01040', 'AT1G01060', 'AT1G01140', 'AT1G01220', 'AT2G03720', 'AT2G03800', 'AT2G04240', 'AT2G05210', 'AT3G02130', 'AT3G02140', 'AT3G02230', 'AT3G02260', 'AT3G02310', 'AT3G02680', 'AT3G02850', 'AT3G02870', 'AT3G03260', 'AT4G14790', 'AT4G15210', 'AT4G15560', 'AT4G15570', 'AT4G15802', 'AT4G15880', 'AT4G16420', 'AT4G16480', 'AT5G10480', 'AT5G10510', 'AT5G11110', 'AT5G11260', 'AT5G11510', 'AT5G12200', 'AT5G13290']<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>%load_ext sql
#%config SqlMagic.autocommit=False
%sql mysql+pymysql://root:root@127.0.0.1:3306/mysql

%sql CREATE DATABASE Exam_2;
%sql show databases;
%sql use Exam_2;
%sql show tables;
#%sql drop table locus_table;
#%sql drop table germ_table;
#In case we need to start from the beginning

%sql CREATE TABLE locus_table (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, locus VARCHAR(20) NOT NULL, gene VARCHAR(30) NOT NULL, Protein_length INTEGER NOT NULL);
%sql CREATE TABLE germ_table (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, locus VARCHAR(20) NOT NULL, germplasm VARCHAR(30) NOT NULL, phenotype VARCHAR(500) NOT NULL, pubmed_link INTEGER NOT NULL);

#As we want the two tables linked 1:1, we will use the same PRIMARY KEY as the conection being it the same for both of the tables

%sql DESCRIBE locus_table;
%sql DESCRIBE germ_table;
%sql SELECT * from locus_table;
%sql SELECT * from germ_table<jupyter_output>* mysql+pymysql://root:***@127.0.0.1:3306/mysql
0 rows affected.<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>import pymysql.cursors

connection = pymysql.connect(host='localhost',
                             user='root',
                             password='<PASSWORD>',
                             db='Exam_2',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

connection.autocommit(True)

try:
    with connection.cursor() as cursor:
        for i in range (1,len(locus_file)):
            locus_filled = locus_file[i].split('\t')
            
            locus = locus_filled [0]
            gene = locus_filled[1]
            Protein_length = locus_filled[2]
            
            sql = "INSERT INTO locus_table(locus, gene, Protein_length) values ('{}', '{}', '{}')".format(locus, gene, Protein_length)
            cursor.execute(sql)
            
            
            germ_filled = germ_file[i].split('\t')
            #in this table we begin with [1] because both tables have the column "locus" and it is the same in both
            germplasm = germ_filled [1]
            phenotype = germ_filled [2]
            pubmed_link = germ_filled [3]
            
            sql = "INSERT INTO germ_table(locus, germplasm, phenotype, pubmed_link) values ('{}', '{}', '{}', '{}')".format(locus, germplasm, phenotype, pubmed_link)
            cursor.execute(sql)
            
            connection.commit()
            
finally:
    print("Everything correct")
    connection.close()

    
#In this exercise, in order to read the files and fill the tables created, we can use pymysql to extract the data from the files using "for"and then tell the program to fill the tables with the data from the files using "try-with" giving the names of the columns and the format we need to fill


# in order to see if the tables are well filled
%sql SELECT * from locus_table
%sql SELECT * from germ_table<jupyter_output>* mysql+pymysql://root:***@127.0.0.1:3306/mysql
128 rows affected.<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.<jupyter_code>import pymysql.cursors
import csv

connection = pymysql.connect(host='localhost',
                             user='root',
                             password='<PASSWORD>',
                             db='Exam_2',
                             charset='utf8mb4',  
                             cursorclass=pymysql.cursors.DictCursor)

connection.autocommit(True)

try:
    with connection.cursor() as cursor:
        sql = "SELECT * FROM germ_table, locus_table WHERE germ_table.locus = locus_table.locus"
        cursor.execute(sql)
        results = cursor.fetchall()
        with open ('AIDA_FINAL_REPORT.csv', 'w') as csvfile:
            answerwriter = csv.writer(csvfile, delimiter="\t", quotechar='"')
            answerwriter.writerow(["Problem 1"])
            answerwriter.writerow(["Locus", "Germplasm", "Phenotype", "pubmed_link", "Gene", "Protein_length"])
            for line in results:
                answerwriter.writerow([line['locus'], line['germplasm'], line['phenotype'], line['pubmed_link'], line['gene'], line['Protein_length']])

    connection.commit() 
        
finally:
    print("Problem 1 done")
    connection.close()
    

l = open('AIDA_FINAL_REPORT.csv', 'r') ## In order to see the content of the file every time we add some information. 
for line in l.readlines():
    print(line)
    
#in this case we follow the same estructure as in problem one but using WHERE + indexing the names of the genes we are interested in 

import pymysql.cursors
import csv

connection = pymysql.connect(host='localhost',
                             user='root',
                             password='<PASSWORD>',
                             db='Exam_2',
                             charset='utf8mb4',  
                             cursorclass=pymysql.cursors.DictCursor)

connection.autocommit(True)


try:
    with connection.cursor() as cursor:
        sql = "SELECT * FROM germ_table, locus_table WHERE (gene = 'SKOR' OR gene = 'MAA3') AND germ_table.locus = locus_table.locus"
        cursor.execute(sql)
        results = cursor.fetchall()
        with open('AIDA_FINAL_REPORT.csv', 'w') as csvfile:
            answerwriter = csv.writer(csvfile, delimiter="\t", quotechar='"')
            answerwriter.writerow(["Problem 2"])
            answerwriter.writerow(["Locus", "Germplasm", "Phenotype", "pubmed_link", "Gene", "Protein_ength"])
            for line in results:
                answerwriter.writerow([line['locus'], line['germplasm'], line['phenotype'], line['pubmed_link'], line['gene'], line['Protein_length']])
                
        connection.commit() 
        
finally:
    print("Problem 2 done")
    connection.close()
    

l = open('AIDA_FINAL_REPORT.csv', 'r')
for line in l.readlines():
    print(line)
#In this problem we need to count the number of entries on each chromosome. we will follow the same estructure but using SELECT COUNT and using regular expresion in order to query for the chromosomes. 
# we can set i = 1 at the beginning and tell the program to catch all the answers while i <= 5 until i =1. In this way we are cerating a loop that reads the table looking for each chromosome and recording each time it finds it. 
import csv
import pymysql.cursors

i = 1

connection = pymysql.connect(host='localhost',
                             user='root',
                             password='<PASSWORD>',
                             db='Exam_2',
                             charset='utf8mb4',  
                             cursorclass=pymysql.cursors.DictCursor)

connection.autocommit(True)

try:
    with connection.cursor() as cursor:
        
        with open('AIDA_FINAL_REPORT.csv', 'w', newline='') as csvfile:
            answerwriter = csv.writer(csvfile, delimiter="\t", quotechar='"')
            answerwriter.writerow(["Problem 3"])
            answerwriter.writerow(["Number of entries for each chromosome"])
            while i <= 5:
                sql = "SELECT COUNT(locus) FROM germ_table WHERE locus REGEXP (%s)"
                data = ("^AT"+str(i)+"G.+") 
                cursor.execute(sql, data)
                results = cursor.fetchall()
                for line in results:
                    answerwriter.writerow(["AT"+str(i)+"G", line['COUNT(locus)']])
                i += 1
                
        connection.commit() 
        
finally:
    print("problem 3 done")
    connection.close()


l = open('AIDA_FINAL_REPORT.csv', 'r')
for line in l.readlines():
    print(line)
# In the same way, we can repeat this structure of code in order to show the average protein length for the genes on each Chromosome
import csv
import pymysql.cursors

i = 1

connection = pymysql.connect(host='localhost',
                             user='root',
                             password='<PASSWORD>',
                             db='Exam_2',
                             charset='utf8mb4',  
                             cursorclass=pymysql.cursors.DictCursor)

connection.autocommit(True)

try:
    with connection.cursor() as cursor:
        
        with open('AIDA_FINAL_REPORT.csv', 'w', newline='') as csvfile:
            answerwriter = csv.writer(csvfile, delimiter="\t", quotechar='"')
            answerwriter.writerow(["Problem 4"])
            answerwriter.writerow(["Average protein lenght for each chromosome"])
            while i <= 5:
                sql = "SELECT ROUND(AVG(Protein_length)) FROM locus_table WHERE locus REGEXP (%s)"
                data = ("^AT"+str(i)+"G.+") 
                cursor.execute(sql, data)
                results = cursor.fetchall()
                for line in results:
                    answerwriter.writerow(["AT"+str(i)+"G", line['ROUND(AVG(Protein_length))']])
                i += 1
                
        connection.commit() 
        
finally:
    print("problem 4 done")
    connection.close()
    
    

l = open('AIDA_FINAL_REPORT.csv', 'r')
for line in l.readlines():
    print(line)<jupyter_output>problem 4 done
Problem 4

Average protein lenght for each chromosome

AT1G	259

AT2G	216

AT3G	252

AT4G	278

AT5G	271
{'fraction_non_alphanumeric': 0.10779230828754933, 'fraction_numerical': 0.04078000464288478, 'mean_word_length': 2.7591623036649215, 'pattern_counts': {'":': 0, '<': 24, '<?xml version=': 0, '>': 21, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 4, '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': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '665316', 'license': 'MIT', 'n_tokens_mistral': 4419, 'n_tokens_neox': 3961, 'n_words': 1250, 'path': 'Exam2_Answers.ipynb', 'repo_name': 'Aidapinacho/AIDA_IntroToBioinformatics', 'size': '12893'}