<jupyter_start><jupyter_text>import python lib<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 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>Q 3.1 using panda to show all records in student table<jupyter_code>sql_statement = """
select * from gp15.students
"""

cur.execute(sql_statement)
df=pandas.read_sql_query(sql_statement,conn)
df[:]<jupyter_output><empty_output><jupyter_text>Q 3.2<jupyter_code>sql_statement = """
select gp15.professors.p_name, gp15.courses.c_name
from gp15.professors
inner join gp15.courses
on gp15.professors.p_email = gp15.courses.p_email

"""
cur.execute(sql_statement)
df=pandas.read_sql_query(sql_statement,conn)
df[:]<jupyter_output><empty_output><jupyter_text>Q 3.3<jupyter_code>sql_statement = """
select c_number, 
count (c_number) as enrolled
from gp15.enroll_list
group by c_number
order by enrolled desc
"""
cur.execute(sql_statement)
df_price=pandas.read_sql_query(sql_statement,conn)

df_price.plot.bar(x='c_number',y='enrolled')<jupyter_output><empty_output><jupyter_text>Q 3.4<jupyter_code>sql_statement= """
select gp15.courses.c_name, gp15.professors.p_name,
count(gp15.courses.c_name) as numberofcourses
from gp15.professors
inner join gp15.courses
on gp15.professors.p_email = gp15.courses.p_email
group by gp15.professors.p_name, gp15.courses.c_name
"""
cur.execute(sql_statement)
df_price=pandas.read_sql_query(sql_statement,conn)

df_price.plot.bar(x='p_name',y='numberofcourses')<jupyter_output><empty_output><jupyter_text>Q 3.5<jupyter_code>sql_statement= """
insert into gp15.professors(p_email,p_name,p_office)
values('<EMAIL>','profname','2222')

"""
cur.execute(sql_statement)
sql_statement = """
insert into gp15.courses(c_name,c_number,p_email,c_room_number)
values('acourse','anumber','<EMAIL>','234')

"""
cur.execute(sql_statement)
sql_statement= """
select * from gp15.professors
"""
cur.execute(sql_statement)
df=pandas.read_sql_query(sql_statement,conn)
df[:]
sql_statement= """
select * from gp15.courses

"""
cur.execute(sql_statement)
df=pandas.read_sql_query(sql_statement,conn)
df[:]<jupyter_output><empty_output><jupyter_text>Q 3.6<jupyter_code>sql_statement = """
update gp15.courses
set p_email = '<EMAIL>'
where p_email = '<EMAIL>'


"""
cur.execute(sql_statement)
sql_statement = """
select * from gp15.courses

"""
cur.execute(sql_statement)
df=pandas.read_sql_query(sql_statement,conn)
df[:]
cur.close()
conn.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14092906465787822, 'fraction_numerical': 0.025109855618330193, 'mean_word_length': 7.171794871794872, 'pattern_counts': {'":': 0, '<': 38, '<?xml version=': 0, '>': 38, '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': '598659', 'license': 'MIT', 'n_tokens_mistral': 1282, 'n_tokens_neox': 1211, 'n_words': 171, 'path': 'lab4.ipynb', 'repo_name': 'sean12505/ia340sean', 'size': '3201'}