<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']
db=config['myaws']['db']
user=config['myaws']['user']
pwd = config['myaws']['pwd']

conn = psycopg2.connect(  host=host,
                       user=user,
                       password = <PASSWORD>,
                       dbname=db)
cur = conn.cursor()
sql_statement = """
                select * from gp18.student
                """
df_student = pandas.read_sql_query(sql_statement,conn)
df_student[:]
sql_statement = """
                select gp18.professor.p_name,
                gp18.course.c_name
                from gp18.professor
                inner join gp18.course
                on gp18.professor.p_email = gp18.course.p_email
                """
df_prof_course = pandas.read_sql_query(sql_statement, conn)
df_prof_course[:]
sql_statement = """
                select count(c_number) as num_enrolled,
                c_number
                from gp18.enroll_list
                group by c_number
                """
df_num_enroll = pandas.read_sql_query(sql_statement, conn)
df_num_enroll.plot.bar(x = 'c_number', y = 'num_enrolled')
prof_courses = """
                select gp18.professor.p_name,
                    count(gp18.course.c_name) as num_taught
                from gp18.professor
                inner join gp18.course
                on gp18.professor.p_email = gp18.course.p_email
                group by gp18.professor.p_name
                order by num_taught desc
                """
df = pandas.read_sql_query(prof_courses, conn)
df.plot.bar(x = 'p_name', y = 'num_taught')
sql_statement = """
                insert into gp18.professor(p_email, p_name, office_number)
                values ('new_email', '<NAME>', '107')
                """
cur.execute(sql_statement)
conn.rollback()
conn.commit()
q5_insert_course_sql = """
            insert into gp18.course(c_number,c_name,c_room,p_email)
            values('7777','Typing101','607','new_email')
            """
cur.execute(q5_insert_course_sql)
conn.commit()
df=pandas.read_sql_query('select * from gp18.course',conn)
df[:]
q6_update_course_sql ="""
                    update gp18.course
                    set p_email = 'new_email'
                    where p_email ='randomemail2'
                    """
cur.execute(q6_update_course_sql)
conn.commit()
q6_delete_prof_sql = """
                    delete from gp18.professor
                    where p_email = 'randomemail2'
                    """
cur.execute(q6_delete_prof_sql)
conn.commit()
df=pandas.read_sql_query('select * from gp18.professor',conn)
df[:]
cur.close()
conn.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11338356655902404, 'fraction_numerical': 0.024040186580552566, 'mean_word_length': 2.1645856980703746, 'pattern_counts': {'":': 0, '<': 7, '<?xml version=': 0, '>': 7, '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': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '594163', 'license': 'MIT', 'n_tokens_mistral': 963, 'n_tokens_neox': 862, 'n_words': 146, 'path': 'Lab4-2.ipynb', 'repo_name': 'DgBrooks3/IA340', 'size': '2780'}