<jupyter_start><jupyter_text>Start your mySQL Server from a terminal (if it isn't already running)sudo docker start course-mysql**Don't forget that, if you use sqlMagic, you need to execute the connection lines in your Notebook!**%load_ext sql%config SqlMagic.autocommit=False%sql mysql+pymysql://root:root@127.0.0.1:3306/mysql Create a new Python3 Jupyter Notebook in your Exam Answers folderCommit and push this Notebook to GitHub when you are finished.You must submit your answers to GitHub by 1800h Sept 14.<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>Data FilesGermplasm.tsv and LocusGene.tsv contain the datasets we need for the exam.Our objective is to create a database to contain the data in these files, insert the data into the database, then query the database in a variety of ways.  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>import csv 

germplasmfile = open("Germplasm.tsv", "r")   #loading data
locusgenefile = open("LocusGene.tsv", "r")

germplasmfile.seek(0)  #skip header
locusgenefile.seek(0)

listalocus=[]
i=0

for line1 in germplasmfile.readlines():  #loop1: reads germplasm and finds AGIcode
    listalocus.append(line1.split("\t")[0])

for line2 in locusgenefile.readlines(): #loop2: reads locusgene and finds AGIcode  
    line2 = line2.split("\t")[0]
    if listalocus[i] == line2: #finds if the AGIcode from loop1 is also in loop2
        print("present in both")
    else:
        print("no match")
    
    i=i+1<jupyter_output>match
match
match
match
match
match
match
match
match
match
match
match
match
match
match
match
match
match
match
match
match
match
match
match
match
match
match
match
match
match
match
match
match<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
%sql mysql+pymysql://root:root@127.0.0.1:3306/mysql
            
%sql CREATE DATABASE examdatabase;           
%sql use examdatabase;

%sql CREATE TABLE locus_gene(locus VARCHAR(9) PRIMARY KEY NOT NULL, \
                             gene VARCHAR(20) NOT NULL, protein_length INTEGER NOT NULL);  #locus column as the primary key in both tables (this column has unique values in the tables and links both tables): already 1:1 match
%sql CREATE TABLE germplasm(locus VARCHAR(9) PRIMARY KEY NOT NULL, \
                            germplasm VARCHAR(25) NOT NULL, phenotype VARCHAR(500) NOT NULL, pubmed INTEGER NOT NULL);
#%sql DROP TABLE germplasm_table;
#%sql DROP TABLE locus_gene_table;
#%sql show tables
#%sql describe germplasm_table

%sql describe germplasm
#%sql describe locus_gene<jupyter_output>* mysql+pymysql://root:***@127.0.0.1:3306/mysql
4 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  #importing
import csv 

germplasmfile = open("Germplasm.tsv", "r")
locusgenefile = open("")



connection = pymysql.connect(host="localhost", #connecting database
                             user="root",
                             password="<PASSWORD>",
                             db="examdatabase",
                             charset="utf8mb4", 
                             cursorclass=pymysql.cursors.DictCursor,
                             autocommit = True
    
try:    
    with connection.cursor() as cursor:
        with open ("Germplasm.tsv", newline= "\n") as file: 
            germplasmfile = csv.reader(file, delimiter="\t", quotechar='"')   
            headers= next(germplasmfile) #removing headers so is selected later
            for row in germplasmfile:
                sql = "INSERT INTO germplasmtab(locus, germplasm, phenotype, pubmed) VALUES(%s,%s,%s,%s)" #"%s" is referring to the data in the germplasmfile, one for each column
                cursor.execute(sql, tuple(row)) #selects the data in 'row'
                results = cursor.fetchall() 
                print(results)       
                print("")
        
finally:
connection.close() 
                             
%sql SELECT * FROM germplasm; #comprobation of the table

import pymysql.cursors
import csv


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

connection.autocommit(True)
try:    
    with connection.cursor() as cursor:
        with open ("LocusGene.tsv", newline= "\n") as file:
            locusgenefile = csv.reader(file, delimiter="\t", quotechar='"')
            headers = next(locusgenefile) 
            for row in locusgenefile:
                #print(row)
                sql = "INSERT INTO locusgenetab(locus, gene, proteinlengh) VALUES (%s,%s,%s)" 
                cursor.execute(sql, tuple(row)) 
                results = cursor.fetchall()
                print(results)       
                print("")
        
finally:
connection.close()
%sql SELECT * FROM locus_gene;   #comprobation of the table<jupyter_output>* mysql+pymysql://root:***@127.0.0.1:3306/mysql
32 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 MAA3**3. 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>#4.1 Create a report that shows the full, joined, content of the two database tables (including a header line)

import csv
import io
import pymysql

connection = pymysql.connect(host="localhost",
                             user="root",
                             password="<PASSWORD>",
                             db="examdatabase",
                             charset="utf8mb4", 
                             cursorclass=pymysql.cursors.DictCursor,
                             autocommit = True)

try:
    with connection.cursor() as cursor:
        sql = "select * from germplasm, locus_gene where germplasm.locus = locus_gene.locus"
        cursor.execute(sql)  
        results = cursor.fetchall()
except:
    print("Extraction error")

# Write the report
report_file = open("report_EXAM2.tsv", "w")
report_file.write("Exercise 4.1\n")
writer = csv.DictWriter(report_file, delimiter = '\t', quotechar = '"', fieldnames = results[0].keys())
writer.writeheader()
writer.writerows(results)
report_file.close()
#4.2 Create a joined report that only includes the Genes SKOR and MAA3

try:
    with connection.cursor() as cursor:
        sql = '''select * from germplasm, locus_gene where germplasm.locus = locus_gene.locus and (locus_gene.gene = "SKOR" or locus_gene.gene = "MAA3")'''
        cursor.execute(sql)  
        results = cursor.fetchall()
except:
    print("Extraction error")
    
# Write the report
report_file = open("report_EXAM2.tsv", "a")
report_file.write("\nExercise 4.2\n")
writer = csv.DictWriter(report_file, delimiter = "\t", quotechar = '"', fieldnames = results[0].keys())
writer.writeheader()
writer.writerows(results)
report_file.close()
#4.3 Create a report that counts the number of entries for each Chromosome (AT1Gxxxxxx to AT5Gxxxxxxx)
try:
    with connection.cursor() as cursor:
        sql = '''select locus from germplasm'''
        cursor.execute(sql)  
        results = cursor.fetchall()
except:
    print("Extraction error")
    
report_file = open("report_EXAM2.tsv", "a")
report_file.write("\nExercise 4.3\n")
chromo_list = list() #chromosome numebers
for item in results:
    chromo_list.append(int(item["locus"][2])) #third character-> all loci (chromosome number)
for number in range(1, 6):
    report_file.write(f"Chromosome {number}: {chromo_list.count(number)} genes in the database\n")
report_file.close()
#4.4 Create a report that shows the average protein length for the genes on each Chromosome (AT1Gxxxxxx to AT5Gxxxxxxx)
report_file = open("report_EXAM2.tsv", "a")
report_file.write("\nExercise 4.4\n")
try:
    with connection.cursor() as cursor:
        for number in range(1,6):
            sql = f'''select avg(protein_length) from locus_gene where locus regexp '^..{number}' '''
            cursor.execute(sql)  
            results = cursor.fetchall() 
            report_file.write(f"Average protein length in chromosome {number}: {list(results[0].values())[0]}\n")
except:
    print("Extraction error")
    connection.close()
report_file.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09938018699443218, 'fraction_numerical': 0.015232692509717408, 'mean_word_length': 3.0545144804088586, 'pattern_counts': {'":': 0, '<': 21, '<?xml version=': 0, '>': 22, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 5, 'substrings_counts': 4, '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': '178110', 'license': 'MIT', 'n_tokens_mistral': 3007, 'n_tokens_neox': 2802, 'n_words': 921, 'path': 'Exam Week 2_ ANSWERS.ipynb', 'repo_name': 'marinaherrero/Accelerated_Intro_WilkinsonExams', 'size': '9501'}