<jupyter_start><jupyter_text>Harvard CS109b, Spring 2018 Lecture 21 Installation and Setup Install MySQL1. Download free MySQL Community Edition [https://dev.mysql.com/downloads/](https://dev.mysql.com/downloads/)2. Start MySQL. On Mac, use System Preferences, MySQL3. Install Jupyter connector [http://bigdatazone.blogspot.com/2017/05/run-sql-from-within-jupyter-notebook.html](http://bigdatazone.blogspot.com/2017/05/run-sql-from-within-jupyter-notebook.html)The iPython - SQL connector: [https://github.com/catherinedevlin/ipython-sql](https://github.com/catherinedevlin/ipython-sql)<jupyter_code># Load the Jupyter Extension
%load_ext sql
try:
    import pymysql
    pymysql.install_as_MySQLdb()
except ImportError:
    pass
# Connect to the database
# TODO: substitute your MySQL password here
%sql mysql://root:0=^b-jLRm)2W\<QPIW#J@localhost<jupyter_output>/anaconda/lib/python3.6/site-packages/pymysql/cursors.py:165: Warning: (1287, "'@@tx_isolation' is deprecated and will be removed in a future release. Please use '@@transaction_isolation' instead")
  result = self._query(query)<jupyter_text>Create a database<jupyter_code># Create the database
%sql create database school
# Connect to the new database
# TODO: enter your password here
%sql mysql://root:0=^b-jLRm)2W\<QPIW#J@localhost/school
%sql CREATE TABLE student(ID int NOT NULL,name varchar(50),email varchar(50));
%sql ALTER TABLE student ADD PRIMARY KEY (ID);
%sql DESC student
%sql INSERT into student values(12345678, "Test User", "<EMAIL>")

%sql INSERT into student values(23456789, "<NAME>", "<EMAIL>")
%sql select * from student
%sql CREATE TABLE classroom(ID int NOT NULL, name varchar(50), location varchar(20));
%sql insert into classroom values (123,"CS109B", "NWB103");
%sql insert into classroom values (234, "AM207", "MDG125")
%sql select * from classroom
%%sql 
create table registration( 
                               student_id int NOT NULL, 
                               class_id int NOT NULL, 
                               FOREIGN KEY (student_id) REFERENCES student(ID));
%sql desc registration
%sql insert into registration values (12345678, 123)
%sql select * from registration;
%%sql 
select s.name "Student", c.name "Course" 
from student s, registration r, classroom c 
where c.id = r.class_id and s.id = r.student_id
%%sql 
SELECT s.name as Student, c.name as Course
FROM student s 
    INNER JOIN registration r ON s.id = r.student_id 
    INNER JOIN classroom c ON r.class_id  = c.id 
ORDER BY s.name<jupyter_output>mysql://root:***@localhost
 * mysql://root:***@localhost/school
1 rows affected.<jupyter_text>Use Database in Python  MySQL Library<jupyter_code>import mysql.connector
# TODO: substitute your MySQL password here
cnx = mysql.connector.connect(user='root',password='<PASSWORD>', host='localhost',database='school')
query = ("SELECT s.name as Student, c.name as Course FROM student s " +
            "INNER JOIN registration r ON s.id = r.student_id " + 
            "INNER JOIN classroom c ON r.class_id  = c.id "+ 
            "ORDER BY s.name")
query
cursor = cnx.cursor()
cursor.execute((query))
for (name, course) in cursor:
    print (name, course)<jupyter_output>Test User CS109B<jupyter_text>From Pandas<jupyter_code>import pandas as pd
df = pd.read_sql(query, con=cnx)
len(df)
enrollment = df.sort_values(['Student','Course']).groupby('Student')
enrollment.head(5)<jupyter_output><empty_output><jupyter_text>To Reset Everything<jupyter_code># Note that dropping the database may hang because of the Pandas and MySQL Library connections.
# You may need to restart your Jupyter kernel for this to have effect.
%sql drop database school
%sql show databases;
%sql create database school<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup