<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>df_student=pandas.read_sql_query('select * from gp16.student',conn)
df_student[:]<jupyter_output><empty_output><jupyter_text>q2<jupyter_code>sql_statement2 = """ select gp16.professor.p_name,
                    gp16.course.c_name
                    from gp16.professor
                    inner join gp16.course
                    on gp16.professor.p_email = gp16.course.p_email"""
df_student=pandas.read_sql_query(sql_statement2,conn)
df_student[:]<jupyter_output><empty_output><jupyter_text>q3<jupyter_code>sql_statement3 = """ select c_number,
                    count (c_number) as enrolled
                    from gp16.enroll_list
                   group by c_number
                   """
df_price=pandas.read_sql_query(sql_statement3,conn)

df_price.plot.bar(x='c_number',y='enrolled')<jupyter_output><empty_output><jupyter_text>q4<jupyter_code>sql_statement4 = """ select gp16.professor.p_name,
                   count(gp16.course.c_name) as teaching_number
                    from gp16.professor
                    inner join gp16.course
                    on gp16.professor.p_email = gp16.course.p_email
                    group by professor.p_name
                   """
df_price=pandas.read_sql_query(sql_statement4,conn)

df_price.plot.bar(x='p_name',y='teaching_number')<jupyter_output><empty_output><jupyter_text>q5<jupyter_code>sql_statement5 = """ insert into gp16.professor(p_email, p_name, p_office)
                    values ('{}', '{}', '{}')
                   """.format('<EMAIL>', 'new_pro', 'new_off')
print (sql_statement5)
cur.execute(sql_statement5)
df=pandas.read_sql_query("select * from gp16.professor",conn)

df[:]
sql_statement6 = """ insert into gp16.course (c_number, c_name, c_room, p_email)
                    values ('{}', '{}', '{}', '{}')
                   """.format('new_ia', 'new_course', 'ISAT321', '<EMAIL>')
print (sql_statement6)
cur.execute(sql_statement5)
conn.rollback()
conn.commit()
df=pandas.read_sql_query("select * from gp16.professor",conn)

df[:]
df=pandas.read_sql_query("select * from gp16.course",conn)

df[:]<jupyter_output><empty_output><jupyter_text>q6<jupyter_code>sql_statement7 = """ update gp16.course
                    set p_email = '<EMAIL>'
                    where p_email = '<EMAIL>'
                  """
print (sql_statement7)
cur.execute(sql_statement7)
conn.commit()
df=pandas.read_sql_query("select * from gp16.course",conn)

df[:]
sql_statement8 = """ delete from gp16.professor
                    where p_email = '<EMAIL>'
                  """
print (sql_statement8)
cur.execute(sql_statement8)
conn.commit()
df=pandas.read_sql_query("select * from gp16.professor",conn)

df[:]<jupyter_output><empty_output><jupyter_text>Close cursor and connection<jupyter_code>cur.close()
conn.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13465665236051502, 'fraction_numerical': 0.023605150214592276, 'mean_word_length': 3.4498806682577565, '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': '215311', 'license': 'MIT', 'n_tokens_mistral': 1362, 'n_tokens_neox': 1269, 'n_words': 177, 'path': 'lab4.ipynb', 'repo_name': 'Caleb0929/IA340', 'size': '3757'}