SQLite Python Example

Taken from here.

Create an Empty Database

Note upfront: If you want to get the full example execute the database dump below which will also give you the correct timestamps for some of the examples, like here: selecting on times/dates.

This creates a database /tmp/test_db.db (taken from the org manual)

drop table if exists greeting;
create table greeting(
    one varchar(10) not null,
    two varchar(10),
    lang char(2) not null,
    country char(2),
    primary key (lang, country)
);

-- give some content
insert into greeting values('Good', 'day!', 'en', 'UK');
insert into greeting values('G''day', '', 'en', 'AU');
insert into greeting values('Guten', 'Tag!', 'de', 'DE');
insert into greeting values('Dobrý', 'den!', 'cz', 'CZ');
insert into greeting values('Bonjour', null, 'fr', null);

Check whether it worked

-- select * from greeting;
select '|', g.lang, '|', g.one, g.two, '|' from greeting as g;

en	Good day!
en	G’day
de	Guten Tag!
cz	Dobrý den!
fr	Bonjour

Alternatively via Commandline

sqlite3 /tmp/test_db.db

There enter .tables and .exit

Further Adding Data from Org Mode

one	two	lang	country
Goede	dag!	nl	NL
God	dag!	dk	DK
God	dag!	se	SE

.mode csv greeting
-- for modes see
--   http://www.tutorialspoint.com/sqlite/sqlite_commands.htm
.import $orgtable greeting
select one, count(*) from greeting group by one;

one	count(*)
Bonjour	1
Dobrý	1
G’day	1
God	2
Goede	1
Good	1
Guten	1

A Little More Detail

Taken from here.

Connect more Databases / In-Memory Databases

-- add a in-memory db
attach database ':memory:' as mem_db;
.database

seq name file

— --------------- ----------------------------------------------------------

0 main /tmp/test_db.db

2 mem_db

Add Triggers on Changes

drop trigger if exists car_update_trg;
create trigger car_update_trg after update on cars
begin
  update cars set last_update = datetime('NOW') where rowid = new.rowid;
end;

-- Change one entry to use the trigger
update cars set price = 10000 where rowid = 5;
select * from cars where rowid < 6;

1	Audi	de	10000	2017-03-25 19:57:17
2	Mercedes	de	57127	2017-03-25 19:35:52
3	Skoda	cz	9000	2017-03-25 19:35:52
4	Volvo	se	29000	2017-03-25 19:35:52
5	Bentley	en	10000	2017-03-25 21:41:56

Further Datetime Functions

Allow for proper formatting:

.print '', '+ day-of-week / week-of-year'
select c.Name, strftime('%d-%m-%Y %w %W', c.last_update)
    from cars as c where rowid < 3;

	+ day-of-week / week-of-year
Audi	25-03-2017 6 12
Mercedes	25-03-2017 6 12

Comparing Times, Selecting on Dates

We can use the time stamps for selecting data as follows, see also here: http://www.sqlite.org/lang_datefunc.html

-- select all entries changed between 19:45 and 20:00
-- Note that these times are UTC
select * from cars where last_update between '2017-03-25 19:45:00' and '2017-03-25 22:00:00';

Id	Name	Language	Price	last_update
1	Audi	de	10000	2017-03-25 19:57:17
5	Bentley	en	10000	2017-03-25 21:41:56

Performance Information about Queries

Use explain as in

explain query plan select * from cars;
explain select c.Name, strftime('%d-%m-%Y %w %W', c.last_update)
    from cars as c where rowid < 3;

0,0,0,"SCAN TABLE cars"
0,Init,0,13,0,"",00,
1,OpenRead,0,2,0,5,00,
2,Rewind,0,11,0,"",00,
3,Integer,3,1,0,"",00,
4,Rowid,0,2,0,"",00,
5,Ge,1,11,2,"",53,
6,Column,0,1,3,"",00,
7,Column,0,4,6,"",00,
8,Function,1,5,4,strftime(-1),02,
9,ResultRow,3,2,0,"",00,
10,Next,0,4,0,"",00,
11,Close,0,0,0,"",00,
12,Halt,0,0,0,"",00,
13,Transaction,0,0,87,0,01,
14,TableLock,0,2,0,Cars,00,
15,String8,0,5,0,"%d-%m-%Y %w %W",00,
16,Goto,0,1,0,"",00,

Dump the Whole Database

.dump

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
drop table if exists greeting;
CREATE TABLE greeting(
    one varchar(10) not null,
    two varchar(10),
    lang char(2) not null,
    country char(2),
    primary key (lang, country)
);
INSERT INTO "greeting" VALUES('Good','day!','en','UK');
INSERT INTO "greeting" VALUES('G''day','','en','AU');
INSERT INTO "greeting" VALUES('Guten','Tag!','de','DE');
INSERT INTO "greeting" VALUES('Dobrý','den!','cz','CZ');
INSERT INTO "greeting" VALUES('Bonjour',NULL,'fr',NULL);
INSERT INTO "greeting" VALUES('Goede','dag!','nl','NL');
INSERT INTO "greeting" VALUES('God','dag!','dk','DK');
INSERT INTO "greeting" VALUES('God','dag!','se','SE');
drop table if exists cars;
CREATE TABLE Cars(
      Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
      Name TEXT,
      Language CHAR(2),
      Price INT,
      last_update dateime default current_timestamp
    );
INSERT INTO "Cars" VALUES(1,'Audi','de',10000,'2017-03-25 19:57:17');
INSERT INTO "Cars" VALUES(2,'Mercedes','de',57127,'2017-03-25 19:35:52');
INSERT INTO "Cars" VALUES(3,'Skoda','cz',9000,'2017-03-25 19:35:52');
INSERT INTO "Cars" VALUES(4,'Volvo','se',29000,'2017-03-25 19:35:52');
INSERT INTO "Cars" VALUES(5,'Bentley','en',10000,'2017-03-25 21:41:56');
INSERT INTO "Cars" VALUES(6,'Citroën','fr',21000,'2017-03-25 22:33:09');
INSERT INTO "Cars" VALUES(7,'Hummer','en',41400,'2017-03-25 19:35:52');
INSERT INTO "Cars" VALUES(9,'Volkswagen','de',21600,'2017-03-25 19:35:52');
DELETE FROM sqlite_sequence;
INSERT INTO "sqlite_sequence" VALUES('Cars',9);
CREATE TRIGGER car_update_trg after update on cars
begin
  update cars set last_update = datetime('NOW') where rowid = new.rowid;
end;
COMMIT;

Add Further Data via Python

import sqlite3

dbase = "/tmp/test_db.db"


def create_data(con):
    """Create some data in the db"""
    cur = con.cursor()
    # Begin with empty table
    cur.execute("DROP TABLE IF EXISTS Cars")

    cur.execute("""CREATE TABLE Cars(
      Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
      Name TEXT,
      Language CHAR(2),
      Price INT,
      last_update dateime default current_timestamp
    )""")

    # https://www.xkcd.com/327/
    sql_injection_attack = (
        "; ".join([
            "'Bobby Tables', 'XK', 0)",
            "DROP TABLE Greeting",
            "INSERT INTO Cars VALUES('https://www.xkcd.com/327'"]), 'CD',
        327)

    cars = [
        ('Audi', 'de', 52642),
        ('Mercedes', 'de', 57127),
        ('Skoda', 'cz', 9000),
        ('Volvo', 'se', 29000),
        ('Bentley', 'en', 350000),
        # use some proper unicode characters
        (u'Citro\u00ebn', 'fr', 21000),
        ('Hummer', 'en', 41400),
        sql_injection_attack,
        ('Volkswagen', 'de', 21600)]

    cur.executemany('''
        INSERT INTO cars(Name, Language, Price) VALUES(?,?,?)''', cars)

with sqlite3.connect(dbase) as connection:
    create_data(connection)


with sqlite3.connect(dbase) as connection:
    cursor = connection.cursor()
    # Try SQL injection ... Gives:
    # sqlite3.Warning: You can only execute one statement at a time.
    # cursor.execute("INSERT INTO Cars(Name, Language, Price) VALUES({0}, {1}, {2})".format(
    #     *sql_injection_attack))

    # Therefore we might as well delete the entry
    name_to_delete = "DROP TABLE"
    # Approach I: Do search in application
    cursor.execute(
        "select Id, Name from cars")
    idx_to_delete = [row[0] for row in cursor
                     if name_to_delete in row[1]]
    # Approach II: Do search on database
    # (Faster but maybe not database independent)
    # See here for commands:
    #     [[http://www.sqlite.org/lang_corefunc.html]]
    cursor.execute(
        # Note: This is case insensitive
        "select Id FROM cars WHERE Name LIKE ?",
        ("%{0}%".format(name_to_delete), ))
    idx_to_delete = [row[0] for row in cursor]

    # Delete the entries
    for idx in idx_to_delete:
        cursor.execute(
            "Delete from cars where id = ?", (int(idx), ))

Access via Python

The full documentation is here and here for py3.

import sqlite3

dbase = "/tmp/test_db.db"

with sqlite3.connect(
        # This is possible in python3 only
        # "file:{0}?mode=ro".format(dbase), uri=True) as connection:
        "{0}".format(dbase)) as connection:
    cursor = connection.cursor()

    cursor.execute("""
        select c.name, c.price, g.country from cars as c
        inner join greeting as g on c.language = g.lang
        where g.country not null")

# Print the results

print("|Car | Price | Country |\n|---|")
print("\n".join((
    "|{0}|".format("|".join(map(str, item)))
    for item in cursor.fetchall())))

Car	Price	Country
Audi	10000	DE
Mercedes	57127	DE
Skoda	9000	CZ
Volvo	29000	SE
Bentley	10000	AU
Bentley	10000	UK
Hummer	41400	AU
Hummer	41400	UK
Volkswagen	21600	DE

Check in SQL

You can now sqlite3 /tmp/test_db.db and

See also online for the possible sql commands.

.mode csv greeting
SELECT * FROM Greeting;

one	two	lang	country
Good	day!	en	UK
G’day		en	AU
Guten	Tag!	de	DE
Dobrý	den!	cz	CZ
Bonjour		fr
Goede	dag!	nl	NL
God	dag!	dk	DK
God	dag!	se	SE

.mode csv cars
SELECT * FROM Cars;
.print '', '', '', '', '(Has trigger attached)'

Id	Name	Language	Price	last_update
1	Audi	de	10000	2017-03-25 19:57:17
2	Mercedes	de	57127	2017-03-25 19:35:52
3	Skoda	cz	9000	2017-03-25 19:35:52
4	Volvo	se	29000	2017-03-25 19:35:52
5	Bentley	en	10000	2017-03-25 21:41:56
6	Citroën	fr	21000	2017-03-25 22:33:09
7	Hummer	en	41400	2017-03-25 19:35:52
9	Volkswagen	de	21600	2017-03-25 19:35:52
				(Has trigger attached)

And to get some info about the tables

cid	name	type	notnull	dflt_value	pk
0	Id	INTEGER	1		1
1	Name	TEXT	0		0
2	Language	CHAR(2)	0		0
3	Price	INT	0		0
4	last_update	dateime	0	current_timestamp	0

pragma table_info(greeting);

cid	name	type	notnull	dflt_value	pk
0	one	varchar(10)	1		0
1	two	varchar(10)	0		0
2	lang	char(2)	1		1
3	country	char(2)	0		2

-- show the main db schema
.schema sqlite_master

CREATE TABLE sqlite_master (
  type text,
  name text,
  tbl_name text,
  rootpage integer,
  sql text
);

Literature and References

SQLite

Core Functions

http://www.sqlite.org/lang_corefunc.html

Modes for the output

http://www.tutorialspoint.com/sqlite/sqlite_commands.htm

Turtle Example

Set up the database

test -f ${db} && rm ${db}
sqlite3 ${db} < "./turtle_example.sql"

Show all tables:

.tables

with open(csv, "r") as fhn:
    pass