Databases MySQL: Integrate SQL in Jupyter Lab

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

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

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)
);

 * 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');

 * mysql+pymysql://user:***@localhost/datab
1 rows affected.
1 rows affected.
1 rows affected.





[]

How to read data

%%sql
SELECT * FROM Pets;

 * mysql+pymysql://user:***@localhost/datab
1 rows affected.

How to delete an entry

%%sql
DELETE FROM Pets WHERE PetID=3;

 * mysql+pymysql://user:***@localhost/datab
1 rows affected.





[]

%%sql
SELECT * FROM Pets;

 * mysql+pymysql://user:***@localhost/datab
2 rows affected.

How to delete a table

%%sql
SHOW TABLES;

 * mysql+pymysql://user:***@localhost/datab
1 rows affected.

%%sql
DROP TABLES Pets;

 * mysql+pymysql://user:***@localhost/datab
0 rows affected.





[]

%%sql
SHOW TABLES;

 * mysql+pymysql://user:***@localhost/datab
0 rows affected.

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