Welcome to another tutorial on Raspberry Pi. In this tutorial, we will see how to use the SQLite Database on Raspberry Pi. Apart from the installation and a few basic commands, in the end, we shall look at a simple project to log sensor data into an SQLite database.

Contents

• What is SQLite?
• Installing SQLite on Raspberry Pi
• SQLite Command Line Interface (CLI)
  • SQLite Create Table
  • Insert Values
  • Access Database Values
  • Delete Table
• Logging Sensor Data
  • Components Required
  • Circuit Diagram
  • Creating the database
  • Python Code
• Conclusion

What is SQLite?

SQLite is a popular relational database management system (RDBMS) contained in a C library. What sets SQLite apart from other SQL databases is that it is not a client-server database engine. It does not have a separate server process. Rather, it is a lightweight and embedded SQL database engine. It is a popular choice for IoT devices and embedded systems because it can read and write to ordinary disk files. A complete SQLite database with tables, views, definitions, and the data itself is stored in a single cross-platform disk file. Owing to its server-less design, it is efficient and required no configuration (zero-conf). This makes it suitable to use on a Raspberry Pi.

Installing SQLite on Raspberry Pi

The installation process of SQLite on Raspberry Pi is very simple. First, update and upgrade the packages in your Raspberry Pi. Open a terminal window and type the following commands:

sudo apt-get update
sudo apt-get upgrade

Now, type the following command, press Y and continue with the installation:

sudo apt-get install sqlite3

That’s it. SQLite Database is installed in your Raspberry Pi.

SQLite Command Line Interface (CLI)

Now that we have seen how to install SQLite on Raspberry Pi, let us look at a few basic commands.

To launch the SQLite CLI, type the following command:

sqlite3 myfirstdatabase.db

We have created a database file called “myfirstdatabase.db“. We shall now look at how to create tables and insert a few values.

SQLite Create Table

To create a table in the database that we created above, type the following command in the CLI shown above:

sqlite> BEGIN;
sqlite> CREATE TABLE details(id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT, Age NUMERIC, Date DATE, Time TIME, Country TEXT);
sqlite> COMMIT;

The important point to note is that every command is terminated with a semicolon (;). Using the above commands, we create a table called “details” with six columns that contain the details of, say, someone who logged into the system. We have to COMMIT any changes else it won’t be reflected in the table.

To see all the tables created till now, type:

sqlite> .tables
details

It shows the new table that we created. Using the fullschema command, we can see the parameters of the table:

sqlite> .fullschema
CREATE TABLE details(id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT, Age NUMERIC, Date DATE, Time TIME, Country TEXT);

Insert Values

To insert values in the table we created above, continue in the same CLI:

sqlite> BEGIN;
sqlite> INSERT INTO details(Name, Age, Country, Date, Time) VALUES("John Smith", 21, "USA", date('now'), time('now'));
sqlite> COMMIT;

We have manually entered values in the table.

Access Database Values

To view the contents of the table that we created, type the following command:

sqlite> SELECT * FROM details;
1|John Smith|21|2020-06-11|14:04:50|USA

We can manually enter more values using the INSERT command as shown above.

Delete Table

To delete a particular table, type the following:

sqlite> DROP TABLE details;

This will delete the table that we created.

To exit the CLI, type:

sqlite> .exit

We have looked at the basic commands to create a table in a database in SQLite. Let us use this project in a that logs a temperature sensor values into the database.

Logging Sensor Data

In this project, we will log the DHT11 sensor data into a database “temperature.db”.

Components Required

• Raspberry Pi ( I have used a Raspberry Pi 4 Model B)
• DHT11 Temperature and Humidity Sensor
• Jumper Wires
• USB C Power Cable (MicroUSB if you have a Pi 3)

Circuit Diagram

Make the sensor connections as shown above. To install the DHT11 sensor library for Raspberry Pi, check out my earlier post. We use pin GPIO.21 (BCM numbering for Adafruit Library). To retrieve data from the sensor, check out the link for my earlier post above.

Creating the database

For this project, let us create a new directory in the Documents folder:

cd Documents
mkdir TemperatureDB
cd TemperatureDB

Within the TemperatureDB folder, open a terminal and type the following:

sqlite3 temperature.db

Let us create the table in this database.

sqlite> BEGIN;
sqlite> CREATE TABLE dhtsensor(id INTEGER PRIMARY KEY AUTOINCREMENT, Temperature NUMERIC, Humidity NUMERIC, Date DATE, Time TIME);
sqlite> COMMIT;
sqlite> .exit

We have created the database file within the TemperatureDB directory. Now it is time to see the python code to log sensor data onto this database.

Python Code

Python 3 comes with the sqlite3 package. There is no need to separately install it. The code is very simple and straightforward. Create a new file with the nano editor “dhtreading.py” within the TemperatureDB folder:

sudo nano dhtreading.py

Now paste the code into the file:

import sys
import Adafruit_DHT
import time
import RPi.GPIO as GPIO
import sqlite3
from time import sleep
GPIO.setmode(GPIO.BOARD)
GPIO.setwarnings(False)

conn = sqlite3.connect('temperature.db')
c = conn.cursor()

while True:
        humidity, temperature = Adafruit_DHT.read_retry(11, 21) # read temperature and humidity values
        date=time.strftime("%Y-%m-%d ")     # current date
        t=time.strftime("%H:%M:%S")         # current time
        c.execute("INSERT INTO dhtsensor(temperature,humidity,Date,Time) VALUES(?,?,?,?)", (temperature,humidity,date,t))
        conn.commit() # commit all changes to database
        sleep(60)  # write values to the table every 60 seconds

We connect to the database file within the folder. The cursor function ensures that we add a new row to the database everytime we use the execute function. Commit all changes to the database else the values will not be reflected in the table. The sleep function pauses the program for 60 seconds and then writes the new temperature and humidity readings into the table.

To check the data in the table, we use the command already shown above. Stop the program and then run the following commands:

sqlite3 temperature.db
sqlite> SELECT * FROM dhtsensor;

The sensor data is shown in the image below:

We can see that data is logged into the database every minute. With this data, we can check out how the temperature and humidity vary throughout the day. We can retrieve data from this database and use graphs to plot the data.

Conclusion

This tutorial showed you how to install SQLite Database on Raspberry Pi. We learned a few basic commands to create and insert values into a table. We also saw how we can log sensor data to the table using Python.

With this knowledge, you can create more powerful database applications. You can create login systems that authenticate only those users present in the database. We can create more advanced home automation systems that log any abnormal data into the database. These are just a few simple ideas. SQLite is a powerful database engine and we can make some amazing projects by using it with Raspberry Pi.

Hope this tutorial was fun and informative. Happy learning!