<jupyter_start><jupyter_text>Lab 4  import libs and connect to databases<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>Question 1<jupyter_code>sql_q1 = """
            select * from student
            """
df= pandas.read_sql_query(sql_q1,conn)
df[:]<jupyter_output><empty_output><jupyter_text>Question 2<jupyter_code>sql_q2 = """
        select professor.p_name,
        course.c_name
        from professor
        inner join course
        on professor.p_email = course.p_email
        
        """
df= pandas.read_sql_query(sql_q2,conn)
df[:]<jupyter_output><empty_output><jupyter_text>Question 3<jupyter_code>sql_q3 = """
        select c_number,
        count(c_number) as enrolled
        from 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>Question 4<jupyter_code>sql_q4 = """
        select professor.p_name,
        count(course.c_name) as teaching_number
        from professor
        inner join course
        on professor.p_email = 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>Question 5<jupyter_code>sql_q5_professor = """
                    insert into professor(p_email,p_name,office)
                    values('{}','{}','{}')
                    """.format('<EMAIL>','new_p','new_office')
cur.execute(sql_q5_professor)
conn.commit()
df=pandas.read_sql_query('select * from professor',conn)
df[:]
sql_q5_course = """
                    insert into course(c_number,c_name,room,p_email)
                    values('{}','{}','{}','{}')
                    """.format('ia_new','new_c_name','online','<EMAIL>')
cur.execute(sql_q5_course)
conn.commit()
df=pandas.read_sql_query('select * from course',conn)
df[:]<jupyter_output><empty_output><jupyter_text>Question 6<jupyter_code>sql_q6_course = """
                update course
                set p_email = '{}'
                where p_email = '{}'
                """.format('<EMAIL>','<EMAIL>')
cur.execute(sql_q6_course)
conn.commit()
df=pandas.read_sql_query('select * from course',conn)
df[:]
sql_q6_professor = """
                delete from professor
                where p_email = '{}'
                """.format('<EMAIL>')
cur.execute(sql_q6_professor)
conn.commit()
df=pandas.read_sql_query('select * from 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.1389776357827476, 'fraction_numerical': 0.007987220447284345, 'mean_word_length': 3.04, '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': '178546', 'license': 'MIT', 'n_tokens_mistral': 1079, 'n_tokens_neox': 1037, 'n_words': 148, 'path': 'lab4.ipynb', 'repo_name': 'baileyga/ia340', 'size': '3158'}