SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world.

The SQLite command-line shell

Before writing any lines of code it is very useful, to become familiar with the basic DB operations, to install and try the SQLite command-line sheel. This tool allows you to create a DB, create tables inside it, insert rows and run SQL queries. It is available as a precompiled binaries package from the Debarm repository and can be installed very quicky by typing:

debarm:~# apt-get update
debarm:~# apt-get install sqlite3

Create a simple DB

Let's create now a simple DB called mydb.sqlite with a table called events where to log when a pushbutton is pressed.

We'll create two columns called:

• timestamp where to save the event date and time in the format YYYY-MM-DD HH:MM:SS
• description where to write the event dscriscription like as "Button xxx pressed"

To do that these three queries in a file called create.sql:

create table events(timestamp text, description text);
insert into events values(datetime("now"),"First test event");
insert into events values(datetime("now"),"Second test event");

Then type:

debarm:~# sqlite3 events.sqlite < create.sql

This command creates a file called events.sqlite which is our database and use the text file create.sql to do three SQL queries.

create table events(timestamp text, description text);

which creates the table events with the two fields timestamp and description then:

insert into events values(datetime("now"),"First test event");
insert into events values(datetime("now"),"Second test event");

which insert two dummy records into the table events.

To read the DB launch sqlite:

debarm:~# sqlite3 events.sqlite                                                 
SQLite version 3.7.3                                                            
Enter ".help" for instructions                                                  
Enter SQL statements terminated with a ";"                                      
sqlite>

then and prompt type the SQL query SELECT to read the records just inserted::

sqlite> select * from events;                                                   

the result shuld be this:

2013-05-16 11:46:09|First test event                                            
2013-05-16 11:46:09|Second test event                                           

Return to the Linux command line by typing:

sqlite> .quit                                                                   
debarm:~#

Code examples

Doing a SQL SELECT in Python

Python supports SQLite by the module sqlite3. The following example are available on the CD://Debian/playground/python/sqlite.

import time
import sqlite3
 
connection = sqlite3.connect('events.sqlite')
cursor = connection.cursor()
cursor.execute('SELECT * FROM events;')

for row in cursor:
	print row

connection.commit()
connection.close()

debarm:~# python select.py                                                      
(u'2013-05-16 13:31:00', u'First test event')                                   
(u'2013-05-16 13:31:00', u'Second test event')

import time
import sqlite3
 
connection = sqlite3.connect('events.sqlite')
cursor = connection.cursor()
cursor.execute("SELECT strftime('%d/%m/%Y %H:%M:%S',timestamp),description FROM events;")

for row in cursor:
	print "%s [%s]" % (row[0],row[1])

connection.commit()
connection.close()

debarm:~# python select2.py                                                     
16/05/2013 13:31:00 [First test event]
16/05/2013 13:31:00 [Second test event]

• SQLite Date And Time Functions
• DB-API 2.0 interface for SQLite databases

Doing a SQL INSERT in Python

This example logs each push button pressing on P1 keys. The code can be changed to be used with any GPIO line using the class Pin istead of the P1.

import ablib
import time
import sqlite3

#Callback function for pushbutton P1 
def P1_pressed():
	connection = sqlite3.connect('events.sqlite')
	cursor = connection.cursor()
	print "P1 pressed"	
	cursor.execute('insert into events values(datetime("now"),"P1 pressed");')
	connection.commit()
	connection.close()

#Callback function for pushbutton P2 
def P2_pressed():
	connection = sqlite3.connect('events.sqlite')
	cursor = connection.cursor()
	print "P2 pressed"	
	cursor.execute('insert into events values(datetime("now"),"P2 pressed");')
	connection.commit()
	connection.close()

P1 = ablib.Daisy5('D11','P1')
P1.set_edge("rising",P1_pressed)

P2 = ablib.Daisy5('D11','P2')
P2.set_edge("rising",P2_pressed)

#Forever loop
while True:
	time.sleep(10)

import time
import sqlite3
 
connection = sqlite3.connect('events.sqlite')
cursor = connection.cursor()
cursor.execute('SELECT * FROM events;')

for row in cursor:
	print row

connection.commit()
connection.close()

example to read the logs saved.

debarm:~# python select2.py                                                     
16/05/2013 13:31:00 [First test event]
16/05/2013 13:31:00 [Second test event]
16/05/2013 13:34:02 [P1 pressed]
16/05/2013 13:34:02 [P1 pressed]
16/05/2013 13:34:03 [P2 pressed]
16/05/2013 13:34:04 [P1 pressed]
16/05/2013 13:34:05 [P1 pressed]
16/05/2013 13:34:06 [P2 pressed]
16/05/2013 13:34:12 [P2 pressed]
16/05/2013 13:34:15 [P1 pressed]
16/05/2013 13:34:19 [P2 pressed]
16/05/2013 13:34:19 [P2 pressed]
16/05/2013 13:34:19 [P1 pressed]
16/05/2013 13:34:19 [P2 pressed]