<jupyter_start><jupyter_text>import lib<jupyter_code>import pandas
import psycopg2
import configparser<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 db<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>q1_sql = """
        select * from gp15.student
"""
df=pandas.read_sql_query(q1_sql,conn)
df[:]<jupyter_output><empty_output><jupyter_text>q2<jupyter_code>q2_sql = """
                    select gp15.professor.p_name, gp15.course.c_name
                    from gp15.professor
                    inner join gp15.course
                    on gp15.professor.p_email=gp15.course.p_email
                    """
df=pandas.read_sql_query(q2_sql,conn)
df[:]<jupyter_output><empty_output><jupyter_text>q3<jupyter_code>q3_sql = """
            select c_number, count(*) as num_enrolled
            from gp15.enroll
            group by c_number
            order by num_enrolled desc
"""
df=pandas.read_sql_query(q3_sql,conn)
df[:]
df.plot.bar(x='c_number', y='num_enrolled')<jupyter_output><empty_output><jupyter_text>q4<jupyter_code>q4_sql = """
            select gp15.professor.p_name, count(*) as num_teaching
            from gp15.professor
            inner join gp15.course
            on gp15.professor.p_email=gp15.course.p_email
            
            group by gp15.professor.p_name
            order by num_teaching desc
"""
df=pandas.read_sql_query(q4_sql,conn)
df[:]
df.plot.bar(x='p_name', y='num_teaching')<jupyter_output><empty_output><jupyter_text>q5<jupyter_code>q5_sql = """
            insert into gp15.professor (p_email, p_name, p_office)
            values ('{}','{}','{}')
            
""".format('<EMAIL>', 'Prof 8', 'ISAT1234')
cur.execute(q5_sql)
conn.commit()
df=pandas.read_sql_query('select * from gp15.professor',conn)
df[:]
q5pt2_sql = """
        insert into gp15.course(c_number, c_name, c_room, p_email)
        values ('{}','{}','{}','{}')
""".format('IA333', 'counterprolif', 'ISAT555', '<EMAIL>')
cur.execute(q5pt2_sql)
conn.commit()
df=pandas.read_sql_query('select * from gp15.course',conn)
df[:]<jupyter_output><empty_output><jupyter_text>q6<jupyter_code>q6_sql = """
            update gp15.course
            set p_email='<EMAIL>'
            where p_email='<EMAIL>'
"""
cur.execute(q6_sql)
conn.commit()
df=pandas.read_sql_query('select * from gp15.course',conn)
df[:]
q6pt2_sql = """
            delete from gp15.professor
            where p_email = '<EMAIL>'
"""
cur.execute(q6pt2_sql)
conn.commit()
df=pandas.read_sql_query('select * from gp15.professor',conn)
df[:]<jupyter_output><empty_output><jupyter_text>close the cursor and connection<jupyter_code>cur.close()
conn.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14667458432304037, 'fraction_numerical': 0.028503562945368172, 'mean_word_length': 3.646896551724138, 'pattern_counts': {'":': 0, '<': 43, '<?xml version=': 0, '>': 43, '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': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '310168', 'license': 'MIT', 'n_tokens_mistral': 1311, 'n_tokens_neox': 1233, 'n_words': 176, 'path': 'Lab4.ipynb', 'repo_name': 'CameronWKerr/IA340', 'size': '3399'}