How to learn SQL (Structured Query Language)
using a jupyter notebook ?

Benjamin H.G. Marchant

Summary:

• Run SQL in a jupyter notebook
• SQL basic commands
  • Create a table
  • Drop a table
  • Insert new row into a table
  • Select all rows from a table
  • Add a primary key into a table
  • Joining tables
  • Create a table
• Use an existing database
• Links

Run SQL in a jupyter notebook

conda install pip

pip install ipython-sql

SQL basic commands

%load_ext sql

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

%sql sqlite://

Create a table

%%sql   

CREATE TABLE CUSTOMER(age int, 
                      firstname varchar(50), 
                      lastname varchar(50));  

 * sqlite://
   sqlite:///chinook.db
(sqlite3.OperationalError) table CUSTOMER already exists
[SQL: CREATE TABLE CUSTOMER(age int, 
                      firstname varchar(50), 
                      lastname varchar(50));]
(Background on this error at: http://sqlalche.me/e/14/e3q8)

Drop a table

%%sql

DROP TABLE CUSTOMER;

 * sqlite://
   sqlite:///chinook.db
Done.

[]

Insert new row into a table

%%sql   

CREATE TABLE CUSTOMER(age int, 
                      firstname varchar(50), 
                      lastname varchar(50)); 

 * sqlite://
   sqlite:///chinook.db
Done.

[]

%%sql 

INSERT INTO CUSTOMER VALUES(25,'Tom','Mitchell');  

 * sqlite://
   sqlite:///chinook.db
1 rows affected.

[]

Note: if you get the error message "ResourceClosedError: This result object does not return rows. It has been closed automatically.", a solution is to add SET NOCOUNT ON;

Fix error: "ResourceClosedError: This result object does not return rows. It has been closed automatically." %%sql

SET NOCOUNT ON;

The issue is that the UPDATE statement is returning a row count, which is a scalar value, and the rows returned by the SELECT statement are "stuck" behind the row count where pyodbc cannot "see" them (without additional machinations).

It is considered a best practice to ensure that our stored procedures always start with a SET NOCOUNT ON; statement to suppress the returning of row count values from DML statements (UPDATE, DELETE, etc.) and allow the stored procedure to just return the rows from the SELECT statement.

source: https://stackoverflow.com/questions/53819199/pandas-sqlalchemy-pyodbc-result-object-does-not-return-rows-from-stored-proc-wh

%%sql 

INSERT INTO CUSTOMER VALUES(42,'John','Doe');  

SET NOCOUNT ON;

 * sqlite://
   sqlite:///chinook.db
1 rows affected.
(sqlite3.OperationalError) near "SET": syntax error
[SQL: SET NOCOUNT ON;]
(Background on this error at: http://sqlalche.me/e/14/e3q8)

Select all rows from a table

%sql SELECT * from CUSTOMER;  

 * sqlite://
   sqlite:///chinook.db
Done.

age	firstname	lastname
25	Tom	Mitchell
42	John	Doe

Add a primary key into a table

%%sql

DROP TABLE CUSTOMER; 

CREATE TABLE CUSTOMER(ID int NULL PRIMARY KEY,
                      age int, 
                      firstname varchar(50), 
                      lastname varchar(50)); 

 * sqlite://
   sqlite:///chinook.db
Done.
Done.

[]

%%sql 

INSERT INTO CUSTOMER VALUES(1,25,'Tom','Mitchell');  
INSERT INTO CUSTOMER VALUES(2,42,'John','Doe');  
INSERT INTO CUSTOMER VALUES(3,25,'Tom','Mitchell');  

 * sqlite://
   sqlite:///chinook.db
1 rows affected.
1 rows affected.
1 rows affected.

[]

%sql SELECT * from CUSTOMER;  

 * sqlite://
   sqlite:///chinook.db
Done.

ID	age	firstname	lastname
1	25	Tom	Mitchell
2	42	John	Doe
3	25	Tom	Mitchell

Add autoincrement

%%sql

DROP TABLE CUSTOMER; 

CREATE TABLE CUSTOMER(ID INTEGER NULL PRIMARY KEY AUTOINCREMENT,
                      age int, 
                      firstname varchar(50), 
                      lastname varchar(50)); 

 * sqlite://
   sqlite:///chinook.db
Done.
Done.

[]

Is there an auto increment in sqlite?

%%sql

INSERT INTO CUSTOMER (age, firstname, lastname) VALUES (25,'Tom','Mitchell');
INSERT INTO CUSTOMER (age, firstname, lastname) VALUES (42,'John','Doe');
INSERT INTO CUSTOMER (age, firstname, lastname) VALUES (25,'Tom','Mitchell');

 * sqlite://
   sqlite:///chinook.db
1 rows affected.
1 rows affected.
1 rows affected.

[]

%sql SELECT * from CUSTOMER;  

 * sqlite://
   sqlite:///chinook.db
Done.

ID	age	firstname	lastname
1	25	Tom	Mitchell
2	42	John	Doe
3	25	Tom	Mitchell

Update

%%sql

UPDATE CUSTOMER SET age = 27 WHERE ID=1;

SELECT * from CUSTOMER; 

 * sqlite://
   sqlite:///chinook.db
1 rows affected.
Done.

ID	age	firstname	lastname
1	27	Tom	Mitchell
2	42	John	Doe
3	25	Tom	Mitchell

Like operator

%%sql

SELECT *
FROM CUSTOMER
WHERE lastname LIKE 'M%';

 * sqlite://
   sqlite:///chinook.db
Done.

ID	age	firstname	lastname
1	27	Tom	Mitchell
3	25	Tom	Mitchell

Joining tables

%%sql

DROP TABLE CUSTOMER; 
DROP TABLE PRODUCT; 

CREATE TABLE CUSTOMER(CUSTOMER_ID INTEGER NULL PRIMARY KEY AUTOINCREMENT,
                      age int, 
                      firstname varchar(50), 
                      lastname varchar(50)); 

CREATE TABLE PRODUCT(PRODUCT_ID INTEGER NULL PRIMARY KEY AUTOINCREMENT,
                      name varchar(50)); 

INSERT INTO CUSTOMER (age, firstname, lastname) VALUES (25,'James','Smith');
INSERT INTO CUSTOMER (age, firstname, lastname) VALUES (42,'Robert','Williams');
INSERT INTO CUSTOMER (age, firstname, lastname) VALUES (36,'Mary','Miller');
INSERT INTO CUSTOMER (age, firstname, lastname) VALUES (52,'Patricia','Smith');

INSERT INTO PRODUCT (name) VALUES ('Tomato');
INSERT INTO PRODUCT (name) VALUES ('Orange');
INSERT INTO PRODUCT (name) VALUES ('Potato');

 * sqlite://
   sqlite:///chinook.db
Done.
Done.
Done.
Done.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.

[]

%%sql

SELECT * from CUSTOMER;   

 * sqlite://
   sqlite:///chinook.db
Done.

CUSTOMER_ID	age	firstname	lastname
1	25	James	Smith
2	42	Robert	Williams
3	36	Mary	Miller
4	52	Patricia	Smith

%%sql

SELECT * from PRODUCT;

 * sqlite://
   sqlite:///chinook.db
Done.

PRODUCT_ID	name
1	Tomato
2	Orange
3	Potato

%%sql

DROP TABLE PRODUCT_ORDER; 

CREATE TABLE PRODUCT_ORDER(ORDER_ID INTEGER NULL PRIMARY KEY AUTOINCREMENT,
                           price int, 
                           order_date DATE,
                           CUSTOMER_ID INTEGER,
                           PRODUCT_ID INTEGER,
                           FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER(CUSTOMER_ID)
                           FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT(PRODUCT_ID)
                           );

INSERT INTO PRODUCT_ORDER (price,order_date,CUSTOMER_ID,PRODUCT_ID) VALUES (4,'2021-05-07',1,1);
INSERT INTO PRODUCT_ORDER (price,order_date,CUSTOMER_ID,PRODUCT_ID) VALUES (3,'2021-05-07',1,3);
INSERT INTO PRODUCT_ORDER (price,order_date,CUSTOMER_ID,PRODUCT_ID) VALUES (3,'2021-05-07',2,3);
INSERT INTO PRODUCT_ORDER (price,order_date,CUSTOMER_ID,PRODUCT_ID) VALUES (4,'2021-05-04',3,1);
INSERT INTO PRODUCT_ORDER (price,order_date,CUSTOMER_ID,PRODUCT_ID) VALUES (4,'2021-05-04',3,1);

SELECT * from PRODUCT_ORDER;

 * sqlite://
   sqlite:///chinook.db
Done.
Done.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
Done.

ORDER_ID	price	order_date	CUSTOMER_ID	PRODUCT_ID
1	4	2021-05-07	1	1
2	3	2021-05-07	1	3
3	3	2021-05-07	2	3
4	4	2021-05-04	3	1
5	4	2021-05-04	3	1

%%sql

SELECT * FROM CUSTOMER INNER JOIN PRODUCT_ORDER ON CUSTOMER.CUSTOMER_ID = PRODUCT_ORDER.CUSTOMER_ID ;

 * sqlite://
   sqlite:///chinook.db
Done.

CUSTOMER_ID	age	firstname	lastname	ORDER_ID	price	order_date	CUSTOMER_ID_1	PRODUCT_ID
1	25	James	Smith	1	4	2021-05-07	1	1
1	25	James	Smith	2	3	2021-05-07	1	3
2	42	Robert	Williams	3	3	2021-05-07	2	3
3	36	Mary	Miller	4	4	2021-05-04	3	1
3	36	Mary	Miller	5	4	2021-05-04	3	1

%%sql

SELECT * FROM 
( SELECT * FROM 
CUSTOMER 
INNER JOIN 
PRODUCT_ORDER 
ON CUSTOMER.CUSTOMER_ID = PRODUCT_ORDER.CUSTOMER_ID) a
INNER JOIN 
PRODUCT
ON PRODUCT.PRODUCT_ID = a.PRODUCT_ID
;

 * sqlite://
   sqlite:///chinook.db
Done.

CUSTOMER_ID	age	firstname	lastname	ORDER_ID	price	order_date	CUSTOMER_ID:1	PRODUCT_ID	PRODUCT_ID_1	name
1	25	James	Smith	1	4	2021-05-07	1	1	1	Tomato
1	25	James	Smith	2	3	2021-05-07	1	3	3	Potato
2	42	Robert	Williams	3	3	2021-05-07	2	3	3	Potato
3	36	Mary	Miller	4	4	2021-05-04	3	1	1	Tomato
3	36	Mary	Miller	5	4	2021-05-04	3	1	1	Tomato

Use an existing database

Installing SQLAlchemy and connecting to database

public sql database

http://2016.padjo.org/tutorials/sqlite-data-starterpacks/ https://www.sqlitetutorial.net/sqlite-sample-database/

import sqlalchemy

%sql sqlite:///chinook.db

sqlalchemy sqlite get all tables in database

https://stackoverflow.com/questions/6473925/sqlalchemy-getting-a-list-of-tables

engine = sqlalchemy.create_engine('sqlite:///chinook.db')

print(engine.table_names())

['albums', 'artists', 'customers', 'employees', 'genres', 'invoice_items', 'invoices', 'media_types', 'playlist_track', 'playlists', 'sqlite_sequence', 'sqlite_stat1', 'tracks', 'users']

/Users/bmarcha1/opt/anaconda3/envs/worklab2/lib/python3.6/site-packages/ipykernel_launcher.py:3: SADeprecationWarning: The Engine.table_names() method is deprecated and will be removed in a future release.  Please refer to Inspector.get_table_names(). (deprecated since: 1.4)
  This is separate from the ipykernel package so we can avoid doing imports until

%sql SELECT * from employees;  

   sqlite://
 * sqlite:///chinook.db
Done.

EmployeeId	LastName	FirstName	Title	ReportsTo	BirthDate	HireDate	Address	City	State	Country	PostalCode	Phone	Fax	Email
1	Adams	Andrew	General Manager	None	1962-02-18 00:00:00	2002-08-14 00:00:00	11120 Jasper Ave NW	Edmonton	AB	Canada	T5K 2N1	+1 (780) 428-9482	+1 (780) 428-3457	andrew@chinookcorp.com
2	Edwards	Nancy	Sales Manager	1	1958-12-08 00:00:00	2002-05-01 00:00:00	825 8 Ave SW	Calgary	AB	Canada	T2P 2T3	+1 (403) 262-3443	+1 (403) 262-3322	nancy@chinookcorp.com
3	Peacock	Jane	Sales Support Agent	2	1973-08-29 00:00:00	2002-04-01 00:00:00	1111 6 Ave SW	Calgary	AB	Canada	T2P 5M5	+1 (403) 262-3443	+1 (403) 262-6712	jane@chinookcorp.com
4	Park	Margaret	Sales Support Agent	2	1947-09-19 00:00:00	2003-05-03 00:00:00	683 10 Street SW	Calgary	AB	Canada	T2P 5G3	+1 (403) 263-4423	+1 (403) 263-4289	margaret@chinookcorp.com
5	Johnson	Steve	Sales Support Agent	2	1965-03-03 00:00:00	2003-10-17 00:00:00	7727B 41 Ave	Calgary	AB	Canada	T3B 1Y7	1 (780) 836-9987	1 (780) 836-9543	steve@chinookcorp.com
6	Mitchell	Michael	IT Manager	1	1973-07-01 00:00:00	2003-10-17 00:00:00	5827 Bowness Road NW	Calgary	AB	Canada	T3B 0C5	+1 (403) 246-9887	+1 (403) 246-9899	michael@chinookcorp.com
7	King	Robert	IT Staff	6	1970-05-29 00:00:00	2004-01-02 00:00:00	590 Columbia Boulevard West	Lethbridge	AB	Canada	T1K 5N8	+1 (403) 456-9986	+1 (403) 456-8485	robert@chinookcorp.com
8	Callahan	Laura	IT Staff	6	1968-01-09 00:00:00	2004-03-04 00:00:00	923 7 ST NW	Lethbridge	AB	Canada	T1H 1Y8	+1 (403) 467-3351	+1 (403) 467-8772	laura@chinookcorp.com

Links

• w3schools
• codecademy
• Here’s How to Run SQL in Jupyter Notebooks
• SQL Interface within JupyterLab
• ipython-sql 0.4.0
• Microsoft SQL Server
• Jupyter Magics with SQL
• Using Pip to install packages to Anaconda Environment
• Learn Jupyter Notebooks for SQL Server
• Installing SQLAlchemy and connecting to database
• Facebook Data Science Interview Question and Solution in SQL - Friend Acceptance Rate
• SQLite vs MySQL – What’s the Difference