| Previous Post | Top | Next Post |
TOC
This was originally written and created around 2013 and may require to be updated. (2021)
SQLite
SQLite is a lightweight relational database management system suitable for embedding into the application program by linking the SQLite library. Structured Query Language (SQL) can be used to manage the content of the database.
SQL basics
You can interactively input SQL commands to the command line after starting
sqlite3 as:
$ sqlite3 test.db
SQLite version 3.7.14 2012-09-03 15:42:36
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
Read references:
- SQLite (upstream)
- SQL Exercises (Wikibooks)
- SQL Dialects Reference (Wikibooks)
- SQLite Tutorial (Mike Chirico, 2007)
- SQLite tutorial (Jan Bodnar, 2009)
- SQLite Python tutorial (Jan Bodnar, 2011)
Alternatively, the GUI tool sqlitebrowser may be used, too.
Phonebook
Let’s make a batch file of SQL commands to create a phonebook database.
SQL batch file sql-1.sql:
.explain ON
.header ON
-- Generate a SQL data table: phone
create table phone(
name string NOT NULL,
phone string NOT NULL);
insert into phone values('Napoleon Bonaparte', '+33-(0)1.01.01.01.01');
insert into phone values('George Washington', '+1-800-START-USA');
insert into phone values('John Adams', '+1-800-LOVE-BEER');
insert into phone values('Thomas Jefferson', '+1-800-4-FREEDOM');
-- SQLite parameter set and data dump
.width 8 20 20
.show
.schema
.dump phone
-- Select all phone table contents and display
select rowid,* from phone;
Here, “NOT NULL” is specified to avoid empty data.
Let’s create a SQL database file private.db containing a phonebook data as
a phone table using sqlite3. For readability, I added “-echo” to echo
SQL command to the console.
$ rm -f private.db # Initialize :-)
$ cat sql-1.sql | sqlite3 -echo private.db
.explain ON
.header ON
create table phone(
name string NOT NULL,
phone string NOT NULL);
insert into phone values('Napoleon Bonaparte', '+33-(0)1.01.01.01.01');
insert into phone values('George Washington', '+1-800-START-USA');
insert into phone values('John Adams', '+1-800-LOVE-BEER');
insert into phone values('Thomas Jefferson', '+1-800-4-FREEDOM');
.width 8 20 20
.show
echo: on
explain: on
headers: on
mode: explain
nullvalue: ""
output: stdout
separator: "|"
stats: off
width: 8 20 20 4 4 13 2 13
.schema
CREATE TABLE phone(
name string NOT NULL,
phone string NOT NULL);
.dump phone
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE phone(
name string NOT NULL,
phone string NOT NULL);
INSERT INTO "phone" VALUES('Napoleon Bonaparte','+33-(0)1.01.01.01.01');
INSERT INTO "phone" VALUES('George Washington','+1-800-START-USA');
INSERT INTO "phone" VALUES('John Adams','+1-800-LOVE-BEER');
INSERT INTO "phone" VALUES('Thomas Jefferson','+1-800-4-FREEDOM');
COMMIT;
select rowid,* from phone;
rowid name phone
-------- -------------------- --------------------
1 Napoleon Bonaparte +33-(0)1.01.01.01.01
2 George Washington +1-800-START-USA
3 John Adams +1-800-LOVE-BEER
4 Thomas Jefferson +1-800-4-FREEDOM
$ ls -l private.db
-rw-r--r-- 1 osamu osamu 2048 Aug 17 23:41 private.db
Let’s add, update, delete data in this phone table be creating a batch file
sql-2.sql and executing sqlite3 with it.
$ cat sql-2.sql
.explain ON
.header ON
-- Add/remove entries in a SQL data table
insert into phone values('Tokugawa Ieyasu', '+81-(0)1-SHOUGUN1');
update phone set phone = '+1-800-BEER-BEER' where name = 'John Adams';
delete from phone where name like 'George%';
-- Select all phone table contents and display
.width 8 20 20
select rowid,* from phone;
$ cat sql-2.sql | sqlite3 -echo private.db
.explain ON
.header ON
insert into phone values('Tokugawa Ieyasu', '+81-(0)1-SHOUGUN1');
update phone set phone = '+1-800-BEER-BEER' where name = 'John Adams';
delete from phone where name like 'George%';
.width 8 20 20
select rowid,* from phone;
rowid name phone
-------- -------------------- --------------------
1 Napoleon Bonaparte +33-(0)1.01.01.01.01
3 John Adams +1-800-BEER-BEER
4 Thomas Jefferson +1-800-4-FREEDOM
5 Tokugawa Ieyasu +81-(0)1-SHOUGUN1
SQL wildcard chracters used for LIKE matching
| Wildcard | Description |
|---|---|
% |
A substitute for zero or more characters |
_ |
A substitute for exactly one character |
SQL + Python
You can manupilate SQL database via Python using the pysqlite binding. For example:
Python code example to add “Abraham Lincoln” to the phonebook.
#!/usr/bin/python3
# -*- coding: utf-8 -*-
import sqlite3 as lite
import sys
con = lite.connect('private.db')
with con:
c = con.cursor()
c.execute("""insert into phone values(
'Abraham Lincoln', '+1-800-FREEDOM-1');""")
c.execute("""select rowid,* from phone""")
for row in c.fetchall():
print ("%s : Name = %s, Phone = %s" % (row[0], row[1], row[2]))
con.commit()
con.close()
Let’s execute it.
$ ./sql-3.py
1 : Name = Napoleon Bonaparte, Phone = +33-(0)1.01.01.01.01
3 : Name = John Adams, Phone = +1-800-BEER-BEER
4 : Name = Thomas Jefferson, Phone = +1-800-4-FREEDOM
5 : Name = Tokugawa Ieyasu, Phone = +81-(0)1-SHOUGUN1
6 : Name = Abraham Lincoln, Phone = +1-800-FREEDOM-1
NULL data
SQL command behavior for NULL data is complicated. See how SQLite behaves:
$ cat sql-4.sql | sqlite3 -echo private.db
.explain ON
.header ON
create table t1(a int, b int, c int);
insert into t1 values(1,0,0);
insert into t1 values(2,0,1);
insert into t1 values(3,1,0);
insert into t1 values(4,1,1);
insert into t1 values(5,null,0);
insert into t1 values(6,null,1);
insert into t1 values(7,null,null);
insert into t1 values(null,null,null);
insert into t1 values(null,null,null);
.schema
CREATE TABLE phone(
name string NOT NULL,
phone string NOT NULL);
CREATE TABLE t1(a int, b int, c int);
.width 8 8 8 40
select rowid,* from t1;
rowid a b c
-------- -------- -------- ----------------------------------------
1 1 0 0
2 2 0 1
3 3 1 0
4 4 1 1
5 5 0
6 6 1
7 7
8
9
.width 4 8 60
select rowid,a, case when b<>0 then 1 else 0 end from t1;
rowi a case when b<>0 then 1 else 0 end
---- -------- ------------------------------------------------------------
1 1 0
2 2 0
3 3 1
4 4 1
5 5 0
6 6 0
7 7 0
8 0
9 0
select rowid,a+10, case when not b<>0 then 1 else 0 end from t1;
rowi a+10 case when not b<>0 then 1 else 0 end
---- -------- ------------------------------------------------------------
1 11 1
2 12 1
3 13 0
4 14 0
5 15 0
6 16 0
7 17 0
8 0
9 0
select rowid,a+20, case when b<>0 and c<>0 then 1 else 0 end from t1;
rowi a+20 case when b<>0 and c<>0 then 1 else 0 end
---- -------- ------------------------------------------------------------
1 21 0
2 22 0
3 23 0
4 24 1
5 25 0
6 26 0
7 27 0
8 0
9 0
select rowid,a+30, case when not (b<>0 and c<>0) then 1 else 0 end from t1;
rowi a+30 case when not (b<>0 and c<>0) then 1 else 0 end
---- -------- ------------------------------------------------------------
1 31 1
2 32 1
3 33 1
4 34 0
5 35 1
6 36 0
7 37 0
8 0
9 0
select rowid,a+40, case when b<>0 or c<>0 then 1 else 0 end from t1;
rowi a+40 case when b<>0 or c<>0 then 1 else 0 end
---- -------- ------------------------------------------------------------
1 41 0
2 42 1
3 43 1
4 44 1
5 45 0
6 46 1
7 47 0
8 0
9 0
select rowid,a+50, case when not (b<>0 or c<>0) then 1 else 0 end from t1;
rowi a+50 case when not (b<>0 or c<>0) then 1 else 0 end
---- -------- ------------------------------------------------------------
1 51 1
2 52 0
3 53 0
4 54 0
5 55 0
6 56 0
7 57 0
8 0
9 0
select rowid,a+60, case b when c then 1 else 0 end from t1;
rowi a+60 case b when c then 1 else 0 end
---- -------- ------------------------------------------------------------
1 61 1
2 62 0
3 63 0
4 64 1
5 65 0
6 66 0
7 67 0
8 0
9 0
select rowid,a+70, case c when b then 1 else 0 end from t1;
rowi a+70 case c when b then 1 else 0 end
---- -------- ------------------------------------------------------------
1 71 1
2 72 0
3 73 0
4 74 1
5 75 0
6 76 0
7 77 0
8 0
9 0
select rowid,a+80, b*0 from t1;
rowi a+80 b*0
---- -------- ------------------------------------------------------------
1 81 0
2 82 0
3 83 0
4 84 0
5 85
6 86
7 87
8
9
select rowid,a+90, b*c from t1;
rowi a+90 b*c
---- -------- ------------------------------------------------------------
1 91 0
2 92 0
3 93 0
4 94 1
5 95
6 96
7 97
8
9
select rowid,a+100, b+c from t1;
rowi a+100 b+c
---- -------- ------------------------------------------------------------
1 101 0
2 102 1
3 103 1
4 104 2
5 105
6 106
7 107
8
9
select rowid,a+110 from t1 where b<10;
rowi a+110
---- --------
1 111
2 112
3 113
4 114
select rowid,+120 from t1 where not b>10;
rowi +120
---- --------
1 120
2 120
3 120
4 120
select rowid,a+130 from t1 where b<10 OR c=1;
rowi a+130
---- --------
1 131
2 132
3 133
4 134
6 136
select rowid,a+140 from t1 where b<10 AND c=1;
rowi a+140
---- --------
2 142
4 144
select rowid,a+150 from t1 where not (b<10 AND c=1);
rowi a+150
---- --------
1 151
3 153
5 155
select rowid,a+160 from t1 where not (c=1 AND b<10);
rowi a+160
---- --------
1 161
3 163
5 165
select rowid,a+170 from t1 where a isnull;
rowi a+170
---- --------
8
9
select rowid,a+180 from t1 where b isnull;
rowi a+180
---- --------
5 185
6 186
7 187
8
9
select rowid,a+190 from t1 where c isnull;
rowi a+190
---- --------
7 197
8
9
.width 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8
select count(*), count(b), sum(b), avg(b), min(b), max(b) from t1;
count(*) count(b) sum(b) avg(b) min(b) max(b)
-------- -------- -------- -------- -------- --------
9 4 2 0.5 0 1
select distinct b from t1;
b
--------
0
1
select b from t1 union select b from t1;
b
--------
0
1
create table t2(a int, b int unique);
insert into t2 values(1,1);
insert into t2 values(2,null);
insert into t2 values(3,null);
Error: near line 56: column b is not unique
insert into t2 values(4,1);
.schema
CREATE TABLE phone(
name string NOT NULL,
phone string NOT NULL);
CREATE TABLE t1(a int, b int, c int);
CREATE TABLE t2(a int, b int unique);
select rowid,* from t2;
rowid a b
-------- -------- --------
1 1 1
2 2
3 3
drop table t1;
drop table t2;
.schema
CREATE TABLE phone(
name string NOT NULL,
phone string NOT NULL);
TIP: The autogenerated “rowid” is a 64-bit signed integer key that uniquely
identifies the row within its table.
| Previous Post | Top | Next Post |