SQLite installing and test

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 code example playground.

This is the code to make the same select done previously using sqlite3: select.py

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')

To obtain a well formatted output use this example: select2.py

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

Doing a SQL INSERT in Python

This example logs each push button pressing on Daisy-5 pushbutton module P1 and P2 keys. The code can be changed to be used with any GPIO line using the class Pin istead of the class Daisy5 (Read this article for more info...).

Use the select.py 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]

Author:
Sergio Tanzilli - tanzilli@acmesystems.it
http://www.tanzilli.com - https://github.com/tanzilli