Preparation of Jupyter Notebook

In the previous post we saw how to install MySQL and prepare the external programs needed to use SQL in our Jupyter Notebook.

Now let’s load the module and establish the connection and let’s start

%load_ext sql
Python
%sql mysql+pymysql://user:password@localhost/datab
Python

How to create a table

Imagine that we want to build a database to identify pets. Every pet has an ID, a category (dog, cat, bird) and a name

%%sql 
CREATE TABLE Pets (
    PetID int NOT NULL AUTO_INCREMENT,
    Name varchar(50) NOT NULL,
    Category varchar(50) NOT NULL,
    PRIMARY KEY (PetID)
);
SQL
 * mysql+pymysql://user:***@localhost/datab
0 rows affected.





[]

How to add an entry

%%sql 
INSERT into Pets(Name, Category) values ('Luna', 'Dog');
INSERT into Pets(Name, Category) values ('Silvester', 'Cat');
INSERT into Pets(Name, Category) values ('Twetee', 'Bird');
SQL
 * mysql+pymysql://user:***@localhost/datab
1 rows affected.
1 rows affected.
1 rows affected.





[]

How to read data

%%sql
SELECT * FROM Pets;
SQL
 * mysql+pymysql://user:***@localhost/datab
1 rows affected.
PetID Name Category
1 Luna Dog

How to delete an entry

%%sql
DELETE FROM Pets WHERE PetID=3;
SQL
 * mysql+pymysql://user:***@localhost/datab
1 rows affected.





[]

%%sql
SELECT * FROM Pets;
SQL
 * mysql+pymysql://user:***@localhost/datab
2 rows affected.
PetID Name Category
1 Luna Dog
2 Silvester Cat

How to delete a table

%%sql
SHOW TABLES;
SQL
 * mysql+pymysql://user:***@localhost/datab
1 rows affected.
Tables_in_datab
Pets 
%%sql
DROP TABLES Pets;
SQL
 * mysql+pymysql://user:***@localhost/datab
0 rows affected.





[]

%%sql
SHOW TABLES;
SQL
 * mysql+pymysql://user:***@localhost/datab
0 rows affected.
Tables_in_datab

In this post we saw the basics operations of SQL, for more information you can go to SQLTutorial