#SQLite installing and test

<div class="text-success lead">
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.
</div>

<a href="http://www.sqlite.org" target="_blank">
<img src="./sqlite_logo.gif">
</a>

#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:

<pre class="minicom">
debarm:~# apt-get update
debarm:~# apt-get install sqlite3
</pre>

##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**: 

<pre class="prettyprint">
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");
</pre>

Then type: 

<pre class="minicom">
debarm:~# sqlite3 events.sqlite < create.sql
</pre>

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

<pre class="prettyprint">
create table events(timestamp text, description text);
</pre>

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

<pre class="prettyprint">
insert into events values(datetime("now"),"First test event");
insert into events values(datetime("now"),"Second test event");
</pre>

which insert two dummy records into the table **events**.

To read the DB launch **sqlite**:

<pre class="minicom">
debarm:~# sqlite3 events.sqlite                                                 
SQLite version 3.7.3                                                            
Enter ".help" for instructions                                                  
Enter SQL statements terminated with a ";"                                      
sqlite>
</pre>

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

<pre class="minicom">
sqlite> select * from events;                                                   
</pre>

the result shuld be this:

<pre class="minicom">
2013-05-16 11:46:09|First test event                                            
2013-05-16 11:46:09|Second test event                                           
</pre>

Return to the Linux command line by typing:

<pre class="minicom">
sqlite> .quit                                                                   
debarm:~#
</pre>

#Code examples

##Doing a SQL SELECT in Python 

Python supports SQLite by the module [sqlite3](http://docs.python.org/2/library/sqlite3.html). The following example are
available on the [code example playground](/playground).

This is the code to make the same select done previously using sqlite3: @pg='python/sqlite/select.py'

<pre class="minicom">
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')
</pre>

To obtain a well formatted output use this example: @pg='python/sqlite/select2.py'

<pre class="minicom">
debarm:~# python select2.py                                                     
16/05/2013 13:31:00 [First test event]
16/05/2013 13:31:00 [Second test event]
</pre>

* [SQLite Date And Time Functions](http://www.sqlite.org/lang_datefunc.html)
* [DB-API 2.0 interface for SQLite databases](http://docs.python.org/2/library/sqlite3.html)

##Doing a SQL INSERT in Python 

This example logs each push button pressing on [Daisy-5 pushbutton module](/DAISY-5) 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...](/gpio)).

* @pg='python/sqlite/presslog.py'

Use the @pg='python/sqlite/select.py' example to read the logs saved.

<pre class="minicom">
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]
</pre>

@include='bio_tanzilli'