<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>The sql extension is already loaded. To reload it, use:
  %reload_ext sql<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>germplasm = open("Germplasm.tsv", "r")  #load the data
locusgene = open("LocusGene.tsv", "r")
a = 0
counter = 0 #number of genes
#loops
import re
for line in germplasm.readlines():   #loop 1 : reads germplasm and finds AGIcode
    germplasmAGIcode = re.search( r'AT\dG\d+\t AT\dG\d+', line)
    germplasm.seek(a)
    if germplasmAGIcode:
        for line in locusgene.readlines(): # loop 2 : reads locusgene and finds AGIcode  
            locusgeneAGIcode = re.search( r'AT\dG\d+\t', line)
            locusgene.seek(0)
            a = a+1 #in each loop run it is +1 so the reading is in the next line
            if locusgeneAGIcode: #finds if the AGIcode from loop1 is also in loop2
                if germplasmAGIcode.group() == locusgeneAGIcode.group():
                    print(locusgeneAGIcode.group()+ "present in both")
                    counter+=1<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>%load_ext sql
%sql mysql+pymysql://root:root@127.0.0.1:3306/mysql
            
%sql create database Exam_2_database;           
%sql use Exam_2_database;

%sql CREATE TABLE Germplasm(Locus VARCHAR(9) NOT NULL, Germplasm VARCHAR(50) NOT NULL, Phenotype VARCHAR(1500) NOT NULL, Pubmed VARCHAR(50) NOT NULL); 
%sql CREATE TABLE Locus_Gene(Locus VARCHAR(9) NOT NULL, Gene VARCHAR(20) NOT NULL, ProteinLength VARCHAR (20) NOT NULL)

%sql show tables<jupyter_output>The sql extension is already loaded. To reload it, use:
  %reload_ext sql
 * mysql+pymysql://root:***@127.0.0.1:3306/mysql
(pymysql.err.ProgrammingError) (1007, "Can't create database 'Exam_2_database'; database exists")
[SQL: create database Exam_2_database;]
(Background on this error at: http://sqlalche.me/e/f405)
 * mysql+pymysql://root:***@127.0.0.1:3306/mysql
0 rows affected.
 * mysql+pymysql://root:***@127.0.0.1:3306/mysql<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>germplasm = open("Germplasm.tsv", "r")
locus_gene = open("Locus_Gene.tsv", "r")
locus_genelist = []
germplasmlist = []

for line in locus_gene.readlines():
    line = line.rstrip()
    Locus, Gene, ProteinLength = line.split('\t')
    locus_genelist.append((Locus, Gene, ProteinLength)) #I do the list inserting data with .append() command
for line in germplasm.readlines():
    line = line.rstrip()
    Locus, Germplasm, Phenotype, Pubmed = line.split('\t')
    germplasmlist.append((Locus, Germplasm, Phenotype, Pubmed))
locusgene.close()
germplasm.close()

locusgenelist.pop(0)
germplasmlist.pop(0)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10009510223490252, 'fraction_numerical': 0.02615311459819306, 'mean_word_length': 4.31858407079646, 'pattern_counts': {'":': 0, '<': 15, '<?xml version=': 0, '>': 15, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 6, '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': '485131', 'license': 'MIT', 'n_tokens_mistral': 1483, 'n_tokens_neox': 1372, 'n_words': 468, 'path': 'Exam Week 2.ipynb', 'repo_name': 'marinaherrero/Accelerated_Intro_WilkinsonExams', 'size': '4206'}