<jupyter_start><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>#Problem 1 solution
germplasm = open("Germplasm.tsv", "r")
locusgene = open("LocusGene.tsv", "r")

a = 0
counter = 0 #variable that counts the number of genes
import re
for line in germplasm.readlines():
    germplasmAGIcode = re.search( r'AT\dG\d+\t', line)
    germplasm.seek(a)
    if germplasmAGIcode:
        for line in locusgene.readlines():
            locusgeneAGIcode = re.search( r'AT\dG\d+\t', line)
            locusgene.seek(0)
            a = a+1
            if locusgeneAGIcode:
                if germplasmAGIcode.group() == locusgeneAGIcode.group():
                    print(locusgeneAGIcode.group()+"is in both files")
                    counter+=1
                
germplasm.close()                    
locusgene.close()                
print ("There are "+str(counter)+" genes")<jupyter_output><empty_output><jupyter_text>**Problem 1 explanation**For the problem number 1, I have used two loops for. The first one, read the file germplasm and looks for an AGIcode; after that, begin the second loop for that looks for AGI code in locusgene file and with an if command try to search the same AGIcode extracted from germplasm file.When the command if finish, the output prints the AGI code of the gene + "is in both files" to indicate that the gene is in germplasm and locusgene files.When the if command finishes, the code comeback to the first for loop, but begins the reading in the next line thanks to .seek() command. I used a variable called "a" that rise in 1 in each loop and, on this way, I get that the germplasm file reading begins a line after.At the end, with a variable called counter, I count the number of genes there are in common.  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>#Problem 2 solution
%load_ext sql
#%config SqlMagic.autocommit=False
%sql mysql+pymysql://root:root@127.0.0.1:3306/mysql
#%sql drop database Germplasm_Locusgene;
            
%sql create database Germplasm_Locusgene;           

%sql use Germplasm_Locusgene;
%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 LocusGene(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
2 rows affected.
 * mysql+pymysql://root:***@127.0.0.1:3306/mysql
1 rows affected.
 * mysql+pymysql://root:***@127.0.0.1:3306/mysql
0 rows affected.
 * mysql+pymysql://root:***@127.0.0.1:3306/mysql
0 rows affected.
 * mysql+pymysql://root:***@127.0.0.1:3306/mysql
0 rows affected.
 * mysql+pymysql://root:***@127.0.0.1:3306/mysql
2 rows affected.<jupyter_text>**Problem 2 explanation**Using command in mysql(%sql) create database, I have created a database called Germplasm_Locusgene. After that, I have selected the database using a command called use and finally, I have created two tables inside the database Germplasm_Locusgene.The first table is called Germplasm and have four columns(Locus, Germplasm, Phenotype and Pubmed), the second table is called LocusGene and have three columns(Locus, Gene and ProteinLength).As both tables coincide in number and genes, the gene to gene relationship between tables is a 1:1 ratio, and I don't have to insert and auto primary key id.  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>#Problem 3 solution
germplasm = open("Germplasm.tsv", "r")
locusgene = open("LocusGene.tsv", "r")
locusgenelist = []
germplasmlist = []

for line in locusgene.readlines():
    line = line.rstrip()
    Locus, Gene, ProteinLength = line.split('\t')
    locusgenelist.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)

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

#connection.autocommit = False
try:
    with connection.cursor() as cursor:
            sql = """INSERT INTO LocusGene (Locus, Gene, ProteinLength)
            VALUES (%s,%s,%s)"""
            val = locusgenelist  
            cursor.executemany(sql,val) #execute sql and insert into %s the data contained in locusgenelist
           
            connection.commit()
            
            mql = """INSERT INTO Germplasm (Locus, Germplasm, Phenotype, Pubmed)
            VALUES (%s,%s,%s,%s)"""
            cal = germplasmlist
            cursor.executemany(mql, cal)
            connection.commit()
            
finally:
    print("")
connection.close()
%sql SELECT * FROM Germplasm;
%sql SELECT * FROM LocusGene;

%sql SELECT * FROM Germplasm;<jupyter_output><empty_output><jupyter_text>**Problem 3 explanation**At first, I opened and read the tsv files using the command open(file, 'r'(read). After that I created two empty lists called locusgenelist and germplasmlist to fill them with two for loops. The for loop use the command readline to read the lines in order and fill the lists in order using the command .append. The file is a tsv file and for this reason I have used the command line.split(\t) to shows the code how the elements in a line in the files are separated.Once I filled the lists, I close the files and remove the header line of each list using the command .pop(0) that remove the first element of the list (I have removed the header line).After that, using Python SQL library I filled the tables created in MySQL in problem 2 with the lists (locusgenelist and germplasmlist). To do it I filled the tables with command cursor.executemany and execute the sql line to insert data and the list to fill the values (%s).Finally, I have closed the connection and assure that the tables are filled correctly usig the command %sql SELECT * FROM.As we can see in the tables, the 32 genes are correctly inserted in the tables.  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>#Problem 4.1 solution
%sql SELECT * FROM LocusGene, Germplasm WHERE \
    LocusGene.Locus = Germplasm.Locus;<jupyter_output>* mysql+pymysql://root:***@127.0.0.1:3306/mysql
32 rows affected.<jupyter_text>**Problem 4.1 explanation**To create a report that joined the Germplasm table and the LocusGene table, I have used the command SELECT * FROM LocusGene, Germplasm (to select both tables) WHERE LocusGene.Locus = Germplasm.Locus (to say to the code that the row Locus in LocusGene table is the same row that Locus row in Germplasm table).And on this way, I get an output that join both tables and ordering data according to the Locus.I didn't use id primary key because if I had used that, in the output would have had two id and two locus (one per table), but the tables have the same genes I can order tables in a relationship 1:1 using locus.<jupyter_code>#Problem 4.2 solution
%sql SELECT * FROM LocusGene, Germplasm WHERE LocusGene.Locus = Germplasm.Locus AND (Gene = ('MAA3') OR Gene=('SKOR'));<jupyter_output>* mysql+pymysql://root:***@127.0.0.1:3306/mysql
2 rows affected.<jupyter_text>**Problem 4.2 explanation**As I did in the problem 4.1, I used SELECT * FROM command and I put that LocusGene.Locus = Germplasm.Locus but I have shown only MAA3 and SKOR genes.<jupyter_code>#Problem 4.3 solution
import pymysql.cursors

connection = pymysql.connect(host='localhost',
                             user='root',
                             password='<PASSWORD>',
                             db='Germplasm_Locusgene',
                             charset='utf8mb4',  # note utf8... this is important for unusual characters!
                             cursorclass=pymysql.cursors.DictCursor)
#connection.autocommit = False  # note that it is possible to delay putting changes into the database!
#Report = open("Reports.tsv", 'w')
try:
    with connection.cursor() as cursor:
        for chromosome in range(1,6):
            chr_name= "chromosome "+str(chromosome)
            regex_var = "AT"+str(chromosome)+"G.+"
            sql= "SELECT COUNT(*) AS %s FROM LocusGene, Germplasm WHERE \
                  LocusGene.Locus = Germplasm.Locus AND \
                 LocusGene.Locus REGEXP %s;" 
            val= (chr_name, regex_var) #variable var contains data to insert in sql 
            cursor.execute(sql, val) #execute sql and insert val data into %s.
            print(cursor.fetchone())
            #report.write(str(cursor.fetchone()))
           
           
                         
finally:
    print("")
    connection.close()
#report.close()

#checkcontent = open("Reports.tsv", "r")
#print(checkcontent.read())
#checkcontent.close()<jupyter_output>{'chromosome 1': 4}
{'chromosome 2': 4}
{'chromosome 3': 9}
{'chromosome 4': 8}
{'chromosome 5': 7}<jupyter_text>**Problem 4.3 explanation**At first, I connected with the SQL library in python and use the database Germplasm_Locusgene.With a for loop in range (1,6), I selected the chromosomos from 1 to 5. In the loop, I have created two variables; the first one to name the chromosome and the second one to write the regular expression of the AGIcode per each chromosome.After that and using the command count in SQL I counted the number of genes there are in each chromosome.<jupyter_code>#Problem 4.4 solution
import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='root',
                             password='<PASSWORD>',
                             db='Germplasm_Locusgene',
                             charset='utf8mb4',  # note utf8... this is important for unusual characters!
                             cursorclass=pymysql.cursors.DictCursor)
#connection.autocommit = False  # note that it is possible to delay putting changes into the database!

try:
    with connection.cursor() as cursor:
        # Read a single record
        for chromosome in range(1,6):
            chr_name= "chromosome "+str(chromosome)
            regex_var = "AT"+str(chromosome)+"G.+"
            sql= "SELECT AVG(LocusGene.ProteinLength) FROM LocusGene, Germplasm WHERE \
             LocusGene.Locus = Germplasm.Locus AND \
             LocusGene.Locus REGEXP %s;"
            val=(regex_var)
            cursor.execute(sql, val)
            print(cursor.fetchone())
            
        
finally:
    print("")
    connection.close()<jupyter_output>{'AVG(LocusGene.ProteinLength)': 258.75}
{'AVG(LocusGene.ProteinLength)': 215.5}
{'AVG(LocusGene.ProteinLength)': 252.0}
{'AVG(LocusGene.ProteinLength)': 277.5}
{'AVG(LocusGene.ProteinLength)': 271.2857142857143}
{'fraction_non_alphanumeric': 0.08881471833279062, 'fraction_numerical': 0.01872354281992836, 'mean_word_length': 3.234746639089969, 'pattern_counts': {'":': 0, '<': 27, '<?xml version=': 0, '>': 27, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 9, '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': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '555651', 'license': 'MIT', 'n_tokens_mistral': 3842, 'n_tokens_neox': 3532, 'n_words': 1393, 'path': 'Exam_2_Answers.ipynb', 'repo_name': 'AdrGlezOV/Accelerated_Intro_WilkinsonExams', 'size': '12266'}