SQLite - Basics

• SQL - Tutorial
• SQL As Understood By SQLite(官網)
• SQLite - Tutorial

Load ipython-sql

ipython-sql:

• 是jupyter notebook的extension，用來擴充jupyter對SQL的支援
• 其底層是使用SQLAlchemy

%load_ext sql

Create Database

The concept of creating or dropping a database is not meant for an embedded database engine like SQLite.

If you want to create DB in SQLite, just from the command line: sqlite3 databasefilename.

For example: sqlite3 test.db

Create DB in Jupyter

Because ipython-sql is based on SQLAlchemy, we can create and connect DB as follow:

%sql sqlite:///test.db

!ls -al

total 32
drwxr-xr-x 3 kaka kaka  4096  5月 14 10:16 .
drwxr-xr-x 7 kaka kaka  4096  5月 14 09:49 ..
drwxr-xr-x 2 kaka kaka  4096  5月 14 09:52 .ipynb_checkpoints
-rw-r--r-- 1 kaka kaka   422  5月 14 09:25 README.md
-rw-r--r-- 1 kaka kaka 15267  5月 14 10:16 sqlite-basics.ipynb
-rw-r--r-- 1 kaka kaka     0  5月 14 10:16 test.db

SQLite Version

%sql SELECT sqlite_version() AS 'SQLite Version';

* sqlite:///test.db
Done.

| SQLite Version |
| 3.31.1 |

Create Table

• SQLite - Data Type
• SQLite - AUTOINCREMENT

%%sql

CREATE TABLE persons(
    person_id INTEGER PRIMARY KEY AUTOINCREMENT,
    firstname VARCHAR(255),
    lastname  VARCHAR(255),
    age       INTEGER,
    height    REAL,
    weight    REAL,
    city      VARCHAR(255)
);

 * sqlite:///test.db
Done.
[]

CRUD for Data

• C: Create
• R: Read
• U: Update
• D: Delete

Create Data: SQL INSERT INTO

%%sql
INSERT INTO persons
VALUES (10, 'kaka','Lin', 28, 175, 70, 'Taipei');

INSERT INTO persons (firstname, lastname, age, height, weight, city)
VALUES ('kiwi','Li', 30, 173, 70, 'Taipei');

 * sqlite:///test.db
1 rows affected.
1 rows affected.
[]

Read Data: SQL SELECT

%%sql

SELECT * FROM persons;

 * sqlite:///test.db
Done.

Update Data: SQL UPDATE

%%sql

UPDATE persons
SET    weight = 68
WHERE  firstname = 'kaka';
-- or WHERE person_id = 1;

 * sqlite:///test.db
1 rows affected.
0 rows affected.
[]

%%sql

SELECT * FROM persons;

 * sqlite:///test.db
Done.

Delete Data: SQL DELETE

Before we delete data, we first add the data that we want to delete.

%%sql

INSERT INTO persons
VALUES (3, 'albert','Lin', 28, 180, 70, 'Taipei');

 * sqlite:///test.db
1 rows affected.
[]

%%sql

SELECT * FROM persons;

 * sqlite:///test.db
Done.

%%sql

DELETE FROM persons
WHERE person_id = 3;

 * sqlite:///test.db
1 rows affected.
[]

%%sql

SELECT * FROM persons;

 * sqlite:///test.db
Done.

SQL WHERE

%%sql

INSERT INTO persons (firstname, lastname, age, height, weight, city)
VALUES ('Albert', 'Lin', 28, 160, 70, 'Taipei'),
       ('Andy', 'Wei', 24, 175, 72, 'Teipei'),
       ('kevin', 'Wang', 30, 174, 63, 'San Francisco'),
       ('kevin', 'Wei', 27, 178, 65, 'Taipei'),
       ('David', 'Kang', 26, 175, 65, 'Washington'),
       ('Matt', 'Wang', 26, 172, 72, 'Taipei'),
       ('kaka-ideal', 'Lin', 28, 178, 70, 'Janpan');

 * sqlite:///test.db
7 rows affected.
[]

%%sql

SELECT * FROM persons

 * sqlite:///test.db
Done.

%%sql

SELECT *
FROM   persons
WHERE  age = 28;

 * sqlite:///test.db
Done.

SQL AND, OR and NOT

AND

%%sql

SELECT *
FROM   persons
WHERE  age = 28
AND    height > 170;

 * sqlite:///test.db
Done.

OR

%%sql

SELECT *
FROM   persons
WHERE  age = 28
OR     height > 170;

 * sqlite:///test.db
Done.

SQL IN Operator

The IN operator allows you to specify multiple values in a WHERE clause.

%%sql

SELECT *
FROM   persons
WHERE  age = 28 OR age = 26;

 * sqlite:///test.db
Done.

%%sql

SELECT *
FROM   persons
WHERE age IN (26, 28);

 * sqlite:///test.db
Done.

NOT

%%sql

SELECT *
FROM   persons
/* WHERE NOT age = 28; */
WHERE age != 28;

 * sqlite:///test.db
Done.

SQL ORDER BY

SELECT column1, column2, ...
    FROM table_name
    ORDER BY column1, column2, ... ASC|DESC;

• Default: ASC

%%sql

SELECT *
FROM   persons
ORDER BY age;

 * sqlite:///test.db
Done.

SQL LIKE Operator

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

There are two wildcards often used in conjunction with the LIKE operator:

• % : The percent sign represents zero, one, or multiple characters
• _ : The underscore represents a single character

%%sql

SELECT *
FROM   persons
WHERE city LIKE '%pei%';

 * sqlite:///test.db
Done.

SQLite - Functions

• SQLite Core Function

Modify Table: SQL ALTER TABLE

• Add a new column in an existing table

  ALTER TABLE table_name ADD column_name datatype;
  

%%sql

ALTER TABLE persons
ADD height_meters REAL;

UPDATE persons
SET height_meters = round(height / 100, 2);

SELECT * FROM persons;

 * sqlite:///test.db
Done.
9 rows affected.
Done.

Drop Table

%%sql

DROP TABLE persons;

 * sqlite:///test.db
Done.
[]

Drop Database

If we want to drop DB in SQLite, just delete the file.

!rm -rf test.db
!ls -al

total 32
drwxr-xr-x 3 kaka kaka  4096  5月 14 10:16 .
drwxr-xr-x 7 kaka kaka  4096  5月 14 09:49 ..
drwxr-xr-x 2 kaka kaka  4096  5月 14 09:52 .ipynb_checkpoints
-rw-r--r-- 1 kaka kaka   422  5月 14 09:25 README.md
-rw-r--r-- 1 kaka kaka 15267  5月 14 10:16 sqlite-basics.ipynb