<jupyter_start><jupyter_text>import python libraries<jupyter_code>import pandas
import configparser
import psycopg2<jupyter_output>/home/ec2-user/anaconda3/envs/python3/lib/python3.6/site-packages/psycopg2/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use "pip install psycopg2-binary" instead. For details see: <http://initd.org/psycopg/docs/install.html#binary-install-from-pypi>.
  """)<jupyter_text>connect to database<jupyter_code>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()<jupyter_output><empty_output><jupyter_text>Q1<jupyter_code>sql_stat = """
        select * from gp17.student

"""
df = pandas.read_sql_query(sql_stat,conn)
df[:]<jupyter_output><empty_output><jupyter_text>Q2<jupyter_code>sql_stat = """
        select gp17.professor.p_name,
            gp17.course.c_name
        from gp17.professor
        inner join gp17.course
        on gp17.professor.p_email = gp17.course.p_email
    
"""
df = pandas.read_sql_query(sql_stat,conn)
df[:]<jupyter_output><empty_output><jupyter_text>Q3<jupyter_code>sql_stat = """
        select count(c_number) as enrolled,
        c_number
        from gp17.enroll_list
        group by c_number
"""
df = pandas.read_sql_query(sql_stat,conn)
df.plot.bar(x='c_number',y='enrolled')<jupyter_output><empty_output><jupyter_text>Q4<jupyter_code>sql_stat = """
        select gp17.professor.p_name,
            count(gp17.course.c_name) as teaching_number
        from gp17.professor
        inner join gp17.course
        on gp17.professor.p_email = gp17.course.p_email
        group by professor.p_name
        order by teaching_number desc
"""
df = pandas.read_sql_query(sql_stat,conn)
df.plot.bar(x='p_name',y='teaching_number')<jupyter_output><empty_output><jupyter_text>Q5<jupyter_code>sql_stat = """
        insert into gp17.professor(p_email,p_name,p_office)
        values ('<EMAIL>','<NAME>','ISAT 206')
"""
df = pandas.read_sql_query("select * from gp17.professor",conn)
df[:]
sql_stat = """
        insert into gp17.course(c_number,c_name,c_room,p_email)
        values('606','IA 606','ISAT 250','<EMAIL>')
"""
df = pandas.read_sql_query("select * from gp17.course",conn)
df[:]<jupyter_output><empty_output><jupyter_text>Q6<jupyter_code>sql_stat = """
        update gp17.course
        set p_email = '<EMAIL>'
        where c_number = '202'
"""
cur.execute(sql_stat)
conn.commit()
df = pandas.read_sql_query("select * from gp17.course",conn)
df[:]
sql_stat = """
        delete from gp17.professor
        where p_email = '<EMAIL>'
"""
cur.execute(sql_stat)
conn.commit()
df = pandas.read_sql_query("select * from gp17.professor",conn)
df[:]
cur.close()
conn.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14037389307969828, 'fraction_numerical': 0.026238110856018366, 'mean_word_length': 4.187074829931973, 'pattern_counts': {'":': 0, '<': 39, '<?xml version=': 0, '>': 39, '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': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '561391', 'license': 'MIT', 'n_tokens_mistral': 1187, 'n_tokens_neox': 1100, 'n_words': 164, 'path': 'Lab4.ipynb', 'repo_name': 'Dukedog23/ia340', 'size': '3081'}