Working with SQLite in Raspberry Pi using Python 3

Last time we did few basic operations with SQLite in Raspberry Pi. We learned how to set up SQLite3, create first database file and fill with data. Using SQL commands we were able to select data and print in terminal screen. But eventually you are going to face with more complex queries or will need to store data automatically so it could be read by other routines and so on.

raspi_sqlite3_python3

For instance you are going to build a simple project that would read data from digital temperature sensor. One of great ways of storing data in to database is to use python script. Anyway you are probably gonna use it for GPIO operations. So lets learn how to store simple data to sqlite database using python. I am going to use Python3. Raspberry Pi comes with Python 2.7 built in so we will need to install Python3. To do so we run:

sudo apt-get install python3

after setup completes you can run python3 on console:

python3

if you write python scripts and want them automatically to be run with Python3 you should include first line as follows:

#!/usr/bin/env python3

That’s all wee need to start with out example.

For following example we are going to use same data table that we created in last post:

create table temperature (id integer, tempfloat, datetext);

You can create new if you missed that with previous statement.

OK lets stick to python. Since we are working from terminal screen we need to create a python file by using nano editor:

sudo nano pydb.py

For now run this command while in same directory where my.db file is.

Te\hen we write python code as follows:

#!/usr/bin/env python3
import sqlite3
#connect to database file
dbconnect = sqlite3.connect("my.db");
#If we want to access columns by name we need to set
#row_factory to sqlite3.Row class
dbconnect.row_factory = sqlite3.Row;
#now we create a cursor to work with db
cursor = dbconnect.cursor();
#execute insetr statement
cursor.execute('''insert into temperature values (4, 2.1, '2013-10-09')''');
dbconnect.commit();
#execute simple select statement
cursor.execute('SELECT * FROM temperature');
#print data
for row in cursor:
    print(row['id'],row['temp'],row['date'] );
#close the connection
dbconnect.close();

Save the script and exit to console. Execute it by typing:

python3 pydb.py

python_sqlite_output

To make it executable, we need to chmod the file:

sudo chmod +x pydb.py

Then we can run program without need to type python3:

./pydb.py

This is very basic approach that is really enough for small data logging applications. For more complex tasks there are several more tricks while working with sqlite3 and Python3.

One of such would be parametrized queries with placeholders. This allows to pass python variables to sql statement like this:

cursor.execute('''insert into temperature values (?, ?, ?)''', (id, temp, date))
dbconnect.commit();

Now we can write a bit different script where we fill database with some dummy data using for loop:

#!/usr/bin/env python3
import sqlite3
#some initial data
id = 4;
temperature = 0.0;
date = '2014-01-05';
#connect to database file
dbconnect = sqlite3.connect("my.db");
#If we want to access columns by name we need to set
#row_factory to sqlite3.Row class
dbconnect.row_factory = sqlite3.Row;
#now we create a cursor to work with db
cursor = dbconnect.cursor();
for i in range(10):
    #execute insert statement
    id += 1;
    temperature += 1.1;
    cursor.execute('''insert into temperature values (?, ?, ?)''',
    (id, temperature, date));
dbconnect.commit();
#execute simple select statement
cursor.execute('SELECT * FROM temperature');
#print data
for row in cursor:
    print(row['id'],row['temp'],row['date'] );
#close the connection
dbconnect.close();

And here is an output:

python_sqlite3_pametrized_query_output

These are very basics to get started. If you decide to use SQLite in your next project you will find even more tricks on how to use it in best way.

Leave a Reply

Your email address will not be published. Required fields are marked *