SQL with Jupyter Notebook Tutorial

4 / 61

Connecting to MySQL through Jupyter Notebook

To connect to MySQL database, the first thing we need to do is load the extension. Run the following command in the Jupyter notebook:

%load_ext sql

Then, we have to do is initialize a connection to database:

%sql mysql://sqoopuser:NHkkP876rp@cxln2.c.thelab-240901.internal/retail_db

Basically, the format of connection URL is : mysql://username:password@host/databasename.

So by writing %sql mysql://sqoopuser:NHkkP876rp@cxln2.c.thelab-240901.internal/retail_db, we are establishing connection to the MySQL database named retail_db. The username is sqoopuser and the password is NHkkP876rp. the host is cxln2.c.thelab-240901.internal.

Please note that the mysql username and password are different from your lab credentials which can be seen along with the Jupyter console.

Now, we can run any SQL query just like mentioned above. The only condition is that, there should be %%sql in every cell where we want to run the SQL query.

For example, to find out about tables, you can run:

show tables

Let us create a table with your lab username. For example, if your lab username is alex2345, then we want to create a table named test_alex2345.


Create a table with your username:

drop table if exists test_<<your lab username>>;
create table test_<<your lab username>>(name varchar(10));

Delete any existing rows:

delete from test_<<your lab username>>;

Insert new rows:

insert into test_<<your lab username>> values("John");

Select the data:

select * from test_<<your lab username>>;

You just created a table, deleted the data, inserted the data into the table and selected the data from the table. Awesome!

Get Hint

Answer is not availble for this assesment

Note - Having trouble with the assessment engine? Follow the steps listed here

Loading comments...