<jupyter_start><jupyter_text>Problem 1 first trial<jupyter_code>germ = open('../Accelerated_Intro_WilkinsonExams/Germplasm.tsv','r')

AGI = open('AGI.txt', 'w')

for line1 in germ:
    matchObj1 = re.search(r'AT\wG\w{5}', line1)
    if matchObj1:
        AGI.write(str(matchObj1)

AGI.close()
  
locus = open('../Accelerated_Intro_WilkinsonExams/LocusGene.tsv', 'r')
AGI = open('AGI.txt', 'a') 
                  
for line2 in locus:
    matchObj2 = re.search(r'AT\wG\w{5}', line2)
    if matchObj2:
        AGI.write(str(matchObj2))
germ.close()
locus.close()
AGI.close()
                  
AGI = open('AGI.txt', 'r')
AGI.seek(0)
print(AGI.readlines())<jupyter_output><empty_output><jupyter_text>Problem 1 second trial. It is an UGLY solution, but it is better than nothing.<jupyter_code>import re
germ = open('../Accelerated_Intro_WilkinsonExams/Germplasm.tsv','r')
locus = open('../Accelerated_Intro_WilkinsonExams/LocusGene.tsv', 'r')
    
for line in germ.readlines():
    matchObj = re.search(r'AT\wG\w{5}', line)
    if matchObj:
        for line in locus.readlines():
            matchObj = re.search(r'AT\wG\w{5}', line)
            print(matchObj)
    else: 
        print('No match')
    
print('*****')

for line in locus.readlines():
    matchObj = re.search(r'AT\wG\w+', line, re.I)
    if matchObj:
        print(matchObj)
    else: 
        print('No match')
        
germ.close()
locus.close()
#Design and create the database
%sql drop database exam;
%sql create database exam;
%sql show databases;
%sql use exam

%sql CREATE TABLE germplasm (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, locus VARCHAR(10) NOT NULL, germplasm VARCHAR(15) NOT NULL, phenotype VARCHAR(500) NOT NULL, pubmed INTEGER NOT NULL)
%sql CREATE TABLE locus (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, locus VARCHAR(10) NOT NULL, gene VARCHAR(10) NOT NULL, protein_length INTEGER NOT NULL)
%sql desc germplasm
import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='root',
                             password='<PASSWORD>',
                             db='exam',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

germ = open('../Accelerated_Intro_WilkinsonExams/Germplasm.tsv','r')
locus = open('../Accelerated_Intro_WilkinsonExams/LocusGene.tsv', 'r')

try:
    with connection.cursor() as cursor:
        for line in germ.readlines()[1:]:
            locus,germplasm,phenotype,pubmed = line.split('\t','\t')
            #print(locus, germplasm, phenotype, pubmed)
            sql = "INSERT INTO germplasm (locus, germplasm,phenotype, pubmed) VALUES ('%s', '%s', '%s', '%d')" % (locus, germplasm,phenotype, pubmed)
            cursor.execute(sql)
        sql = "SELECT * from germplasm"
        cursor.execute(sql)
        results = cursor.fetchall()
        print(results)
        
        for line in locus.readlines()[1:]:
            locus,gene,protein_length = line.split('\t','\t')
            sql = "INSERT INTO locus (locus, gene, protein_length) VALUES ('%s', '%s', '%d')" % (locus, gene, protein_length)
            cursor.execute(sql)
        sql = "SELECT * from locus"
        cursor.execute(sql)
        results = cursor.fetchall()
        print(results)

finally:
    connection.close()
    
germ.close()
locus.close()<jupyter_output><empty_output><jupyter_text>Create a report that shows the full, joined, content of the two database tables (including a header line)<jupyter_code>import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='root',
                             password='<PASSWORD>',
                             db='exam',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)
report1 = open('reportfile.txt','w')
report1.write("%s\t%s\t%s\t%s\t%s\t%s\t" % (locus, gene, protein_length, germplasm,phenotype, pubmed))
report1.close()

report1 = open('reportfile.txt','a')
try:
    with connection.cursor() as cursor:
        sql = "SELECT * FROM germplasm, locus WHERE germplasm.locus=locus.locus"
        cursor.execute(sql)
        results = cursor.fetchall()
        report1.write(results)
        
finally:
    connection.close() 

report1 = open('reportfile.txt','r')
print(report1.readlines())<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1353908537959, 'fraction_numerical': 0.009236314485244425, 'mean_word_length': 2.7563451776649748, 'pattern_counts': {'":': 0, '<': 15, '<?xml version=': 0, '>': 15, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, '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': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '164829', 'license': 'MIT', 'n_tokens_mistral': 1543, 'n_tokens_neox': 1468, 'n_words': 323, 'path': 'My answers for exam #2.ipynb', 'repo_name': 'carol13jz/CompBio_Exams', 'size': '4427'}