<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>import re
germplasm = open ('Germplasm.tsv', 'r')
genelocus = open ('LocusGene.tsv', 'r')

germplasm.seek(0)

 
for line in germplasm.readlines():

    matchObj = re.search(r'AT\dG\d+', line)

    if at:

        name1 = print(matchObj(1))

    else:

        pass

germplasm.close()

for line in genelocus.readlines():

    matchObj = re.search(r'AT\dG\d+', line)

    if at:

        name2 = print(matchObj(1))

    else:

        pass

genelocus.close()

if name1==name2:

            print ("same")

         else:

            print (“not found”)<jupyter_output>Connection info needed in SQLAlchemy format, example:
               postgresql://username:password@hostname/dbname
               or an existing connection: dict_keys([])
No module named 'pymysql'
Connection info needed in SQLAlchemy format, example:
               postgresql://username:password@hostname/dbname
               or an existing connection: dict_keys([])<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 relationshipyou may use either sqlMagic or pymysql to build the database<jupyter_code>%sql create database germplasm;
%sql use  germplasm;
%sql show tables;

%sql CREATE TABLE germplasm(id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, Locus VARCHAR(10) NOT NULL, Germplasm VARCHAR(10) NOT NULL, Phenotype VARCHAR(100) NOT NULL, Pubmed INTEGER NOT NULL);
%sql DESCRIBE germplasm;
%sql CREATE TABLE locusgene (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, Locus VARCHAR(10) NOT NULL, Gene VARCHAR(10) NOT NULL, ProteinLenght INTEGER NOT NULL);
%sql DESCRIBE locusgene;


%sql show tables;<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>import pymysql.cursors
connection = pymysql.connect(host='localhost',
                             user='root',
                             password='<PASSWORD>',
                             db='germplasm',
                             charset='utf8mb4',  
                             cursorclass=pymysql.cursors.DictCursor,
                             autocommit=True )
germplasm = open ("Germplasm.tsv", "r")
try:
    with connection.cursor() as cursor:
        germplasm.seek(0)
        discard = germplasm.readline()
        for line in germplasm.readlines():
            line = line.strip() 
            if line:
                Locus, Germplasm, Phenotype, Pubmed = line.split("\t")
                sql = "INSERT INTO germplasm (Locus, Germplasm, Phenotype, Pubmed) VALUES
   ('%s','%s','%s','%s')" % (Locus, Germplasm, Phenotype, Pubmed)
                cursor.execute(sql)
           else:
             continue
      
        germplasm.close()

finally:
    connection.close()<jupyter_output><empty_output><jupyter_text>Problem 4: Create reports, written to a file¶Create a report that shows the full, joined, content of the two database tables (including a header line)Create a joined report that only includes the Genes SKOR and MAA3Create a report that counts the number of entries for each Chromosome (AT1Gxxxxxx to AT5Gxxxxxxx)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>%sql SELECT * FROM locusgene, germplasm WHERE \
     locusgene.id = germplasm.id;<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08425994563874475, 'fraction_numerical': 0.008401284902396838, 'mean_word_length': 3.088888888888889, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 17, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, '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': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '76817', 'license': 'MIT', 'n_tokens_mistral': 1267, 'n_tokens_neox': 1176, 'n_words': 420, 'path': 'Exam_2.ipynb', 'repo_name': 'anasierra1/Accelerated_Intro_WilkinsonExams', 'size': '4041'}