<jupyter_start><jupyter_text>Lab 4  import python labs<jupyter_code>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 gp1.student

        """
df=pandas.read_sql_query(sql_q1,conn)
df[:]<jupyter_output><empty_output><jupyter_text>q2<jupyter_code>sql_q2 = """
        select gp1.professor.p_name,
        gp1.course.c_name
        from gp1.professor
        inner join gp1.course
        on gp1.professor.p_email = gp1.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_num,
        count(c_num) as num_enroll
        from gp1.enroll_list
        group by c_num
        """
df=pandas.read_sql_query(sql_q3,conn)
df[:]
df.plot.bar(x='c_num',y='num_enroll')<jupyter_output><empty_output><jupyter_text>q4<jupyter_code>sql_q4 = """
        select gp1.professor.p_name,
        count(gp1.course.c_name) as num_course
        from gp1.professor
        inner join gp1.course
        on gp1.professor.p_email = gp1.course.p_email
        group by gp1.professor.p_name
        """
df=pandas.read_sql_query(sql_q4,conn)
df[:]
df.plot.bar(x='p_name',y='num_course')<jupyter_output><empty_output><jupyter_text>q5<jupyter_code>q5_insert_professor_sql = """
            insert into gp1.professor(p_email,p_name,p_office)
            values('{}','{}','{}')
            """.format('new_lab4_email','new_p_lab4_name','new_lab4_o')
print(q5_insert_professor_sql)
cur.execute(q5_insert_professor_sql)
conn.commit()
df=pandas.read_sql_query('select * from gp1.professor',conn)
df[:]
q5_insert_course_sql = """
            insert into gp1.course(c_num,c_name,c_room,p_email)
            values('{}','{}','{}','{}')
            """.format('new_lab4_c','new_lab4_n','new_lab4_r','new_lab4_email')
print(q5_insert_course_sql)
cur.execute(q5_insert_course_sql)
conn.commit()
df=pandas.read_sql_query('select * from gp1.course',conn)
df[:]<jupyter_output><empty_output><jupyter_text>q6<jupyter_code>q6_update_course_sql ="""
                    update gp1.course
                    set p_email = '{}'
                    where p_email ='{}'
                    """.format('new_lab4_email','<EMAIL>')
cur.execute(q6_update_course_sql)
conn.commit()
df=pandas.read_sql_query('select * from gp1.course',conn)
df[:]
q6_delete_professor_sql = """
                            delete from gp1.professor
                            where p_email='{}'
                            """.format('<EMAIL>')
cur.execute(q6_delete_professor_sql)
conn.commit()
df=pandas.read_sql_query('select * from gp1.professor',conn)
df[:]
cur.close()
conn.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14692556634304207, 'fraction_numerical': 0.018770226537216828, 'mean_word_length': 3.4410919540229883, 'pattern_counts': {'":': 0, '<': 32, '<?xml version=': 0, '>': 32, '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': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '223681', 'license': 'MIT', 'n_tokens_mistral': 1214, 'n_tokens_neox': 1161, 'n_words': 129, 'path': 'Lab 4.ipynb', 'repo_name': 'tylrgry/IA340', 'size': '3089'}