Last time we did a few basic operations with SQLite in Raspberry Pi. We learned how to set up SQLite3, create the first database file and fill it with data. Using SQL commands, we were able to select data and print it on the terminal screen. But eventually, you will face more complex queries or store data automatically so other routines could read it.
For instance, you will build a simple project that would read data from the digital temperature sensor. One of the great ways of storing data into a database is to use a python script. Anyway, you are probably going to use it for GPIO operations. So let us learn how to store simple data to SQLite database using python.
Installing Python3 into Raspbian
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 the console:
python3
if you write python scripts and want them automatically to be run with Python3 you should include the first line as follows:
#!/usr/bin/env python3
That’s all we need to start without example.
For the following example, we are going to use the same data table that we created in the last post:
create table temperature (id integer, tempfloat, datetext);
You could create new if you missed that with the previous statement.
Writing Python script
OK, let’s stick to python. Since we are working from the terminal screen we need to create a python file by using nano editor:
sudo nano pydb.py
For now, run this command while in the 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
To make it executable, we need to chmod the file:
sudo chmod +x pydb.py
Then we can run a program without the need to type python3:
./pydb.py
This is a fundamental approach that is enough for small data logging applications. For more complex tasks, there are several more tricks while working with sqlite3 and Python3.
SQL queries with Python on Raspberry Pi
One of such would be parameterized 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 the 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 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 using it in the best way.