<jupyter_start><jupyter_text>Lab 4<jupyter_code>## import libs and connect to database
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()<jupyter_output><empty_output><jupyter_text>Q1<jupyter_code>sql_q1 = 'select * from gp28.student'
df= pandas.read_sql_query(sql_q1,conn)
df[:]<jupyter_output><empty_output><jupyter_text>Q2<jupyter_code>sql_q2 = """
        select gp28.professor.p_name,
        gp28.course.c_name
        from gp28.professor
        inner join gp28.course
        on gp28.professor.p_email = gp28.course.p_email
        
        """
df= pandas.read_sql_query(sql_q2,conn)
df[:]<jupyter_output><empty_output><jupyter_text>Q3<jupyter_code>sql_q3 = """
        select c_number,
        count(c_number) as enrolled
        from gp28.enroll_list
        group by c_number
        order by enrolled desc
        """
df= pandas.read_sql_query(sql_q3,conn)
df.plot.bar(y='enrolled',x='c_number')<jupyter_output><empty_output><jupyter_text>Q4<jupyter_code>sql_q4 = """
        select gp28.professor.p_name,
        count(gp28.course.c_name) as teaching_number
        from gp28.professor
        inner join gp28.course
        on gp28.professor.p_email = gp28.course.p_email
        group by professor.p_name
        order by teaching_number desc
        """
df= pandas.read_sql_query(sql_q4,conn)
df.plot.bar(y='teaching_number',x='p_name')<jupyter_output><empty_output><jupyter_text>Q5<jupyter_code>sql_q5_professor = """
                    insert into gp28.professor(p_email,p_name,office)
                    values('{}','{}','{}')
                    """.format('<EMAIL>','new_p','new_office')
cur.execute(sql_q5_professor)
cur.execute('ROLLBACK')
conn.commit()
df=pandas.read_sql_query('select * from gp28.professor',conn)
df[:]
sql_q5_course = """
                    insert into gp28.course(c_number,c_name,room_num,p_email)
                    values('{}','{}','{}')
                    """.format('ia_new','new_c_name','online','<EMAIL>')
cur.execute('ROLLBACK')
conn.commit()
df=pandas.read_sql_query('select * from gp28.course',conn)
df[:]<jupyter_output><empty_output><jupyter_text>Q6<jupyter_code>sql_q6_course = """
                update gp28.course
                set p_email = '{}'
                where p_email = '{}'
                """.format('<EMAIL>','<EMAIL>')
cur.execute(sql_q6_course)
cur.execute('ROLLBACK')
#cur.execute('ROLLBACK')
conn.commit()
df=pandas.read_sql_query('select * from gp28.course',conn)
df[:]
sql_q6_professor = """
                delete from gp28.professor
                where p_email = '{}'
                """.format('<EMAIL>')
cur.execute(sql_q6_professor)
conn.commit()
df=pandas.read_sql_query('select * from gp28.professor',conn)
df[:]<jupyter_output><empty_output><jupyter_text>close connection<jupyter_code>cur.close()
conn.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1446676970633694, 'fraction_numerical': 0.021020092735703245, 'mean_word_length': 3.1808785529715764, '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': '58519', 'license': 'MIT', 'n_tokens_mistral': 1224, 'n_tokens_neox': 1137, 'n_words': 147, 'path': 'Lab4.ipynb', 'repo_name': 'Stormer2701/ia340', 'size': '3266'}