<jupyter_start><jupyter_text>Lab 4<jupyter_code>!pip install psycopg2
import pandas
import configparser
import psycopg2
config = configparser.ConfigParser()
config.read('config.ini')
host = config ['myaws']['host']
user = config ['myaws']['user']
pwd= config ['myaws']['pwd']
database = config ['myaws']['database']

conn = psycopg2.connect(
                        host = host,
                        user = user,
                        password = <PASSWORD>,
                        dbname = database

)
cur= conn.cursor()<jupyter_output><empty_output><jupyter_text>Q1<jupyter_code>sql_statement = '''
             select * from student_gp9mb
             '''
df = pandas.read_sql_query(sql_statement,conn)
df[:]<jupyter_output><empty_output><jupyter_text>Q2<jupyter_code>sql_statement = '''
                select  
                cname, count (semail)
                from correlation_gp9mb
                group by cname
                order by count (semail) desc
                limit 1
            '''
# All SQL statements must be displayed as a string, and they can be read by pandas
df = pandas.read_sql_query(sql_statement,conn)
df[:]<jupyter_output><empty_output><jupyter_text>Q3<jupyter_code>sql_statement2 = '''
                select
                *
                from teacher_gp9mb
                inner join course_gp9mb
                on teacher_gp9mb.temail = course_gp9mb.temail
            '''
df = pandas.read_sql_query(sql_statement2,conn)
df[:]<jupyter_output><empty_output><jupyter_text>Q4<jupyter_code>sql_statement3 = '''
                select
                teacher_gp9mb.tname as tname,
                count (course_gp9mb.cname) as course_num
                from teacher_gp9mb
                inner join course_gp9mb
                on teacher_gp9mb.temail = course_gp9mb.temail
                group by teacher_gp9mb.tname
                '''
df = pandas.read_sql_query(sql_statement3,conn)
df[:]<jupyter_output><empty_output><jupyter_text>Q5<jupyter_code>sql_insert_statement = '''
                        insert into teacher_gp9mb(temail,tname,office)
                        values('{}','{}','{}')
                        '''.format ('new_email','new_name','new_office')        # the values are strings, you DO need quotation marks. String means {}
print (sql_insert_statement)
cur.execute(sql_insert_statement)
conn.commit()
df = pandas.read_sql_query('''select * from teacher_gp9mb''',conn)
df[:]<jupyter_output><empty_output><jupyter_text>Q6<jupyter_code>sql_statement4 = '''
                update course_gp9mb
                set temail = '<EMAIL>'
                where temail = '<EMAIL>'
                '''

cur.execute(sql_statement4)
conn.commit()
df = pandas.read_sql_query('''select * from course_gp9mb''',conn)
df[:]
sql_statement5= '''
                delete from teacher_gp9mb
                where temail = '<EMAIL>'
                '''
cur.execute(sql_statement5)
conn.commit()
df = pandas.read_sql_query('''select * from teacher_gp9mb''',conn)
df[:]<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11173184357541899, 'fraction_numerical': 0.01248767663489977, 'mean_word_length': 2.424071991001125, 'pattern_counts': {'":': 0, '<': 33, '<?xml version=': 0, '>': 33, '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': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '42794', 'license': 'MIT', 'n_tokens_mistral': 974, 'n_tokens_neox': 925, 'n_words': 167, 'path': 'Lab4.ipynb', 'repo_name': 'mgbruce/IA241', 'size': '3060'}