SQLite Database on a Raspberry Pi

Introduction

SQLite is a lightweight, self-contained database engine ideal for embedded systems like the Raspberry Pi. It provides a serverless, zero-configuration database solution that is well-suited for small applications, IoT projects, and data logging.

In this guide, we will explore how to install, configure, and use SQLite on a Raspberry Pi for various applications.

You May also like: What is a Time Series Database?

Why Use SQLite on a Raspberry Pi?

• Lightweight & Fast – Requires minimal resources.
• No Server Required – Runs as a standalone library.
• ACID-Compliant – Ensures data integrity.
• Easy to Use – Uses a simple SQL interface.
• File-Based Storage – Stores data in a single .sqlite file.

Step 1: Installing SQLite on Raspberry Pi

SQLite comes pre-installed on most Raspberry Pi OS distributions. To check if it’s installed, run:

sqlite3 --version

If SQLite is not installed, install it using:

sudo apt update
sudo apt install sqlite3 -y

To install SQLite development libraries (optional for Python or C++ integration):

sudo apt install libsqlite3-dev

Step 2: Creating a SQLite Database

To create a new SQLite database, open the SQLite shell:

sqlite3 mydatabase.db

This creates a new database file named mydatabase.db.

Creating a Table

Inside the SQLite shell, create a table:

CREATE TABLE sensors (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    value REAL,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);

Verify the table:

.tables

Inserting Data

INSERT INTO sensors (name, value) VALUES ('Temperature', 25.6);

Querying Data

SELECT * FROM sensors;

Exit SQLite:

.exit

Step 3: Using SQLite with Python

SQLite integrates seamlessly with Python, making it an excellent choice for Raspberry Pi projects.

Install SQLite Python Library

pip install sqlite3

Python Script to Read & Write Data

import sqlite3

# Connect to database
conn = sqlite3.connect("mydatabase.db")
cursor = conn.cursor()

# Insert data
cursor.execute("INSERT INTO sensors (name, value) VALUES (?, ?)", ("Humidity", 60.5))
conn.commit()

# Retrieve data
cursor.execute("SELECT * FROM sensors")
rows = cursor.fetchall()
for row in rows:
    print(row)

# Close connection
conn.close()

Run the script:

python3 sqlite_script.py

Step 4: Automating Data Logging with Cron Jobs

Creating a Python Data Logger

Create a script logger.py that reads sensor data and logs it to SQLite:

import sqlite3
import random

conn = sqlite3.connect("mydatabase.db")
cursor = conn.cursor()

sensor_value = round(random.uniform(20.0, 30.0), 2)
cursor.execute("INSERT INTO sensors (name, value) VALUES (?, ?)", ("Temperature", sensor_value))
conn.commit()
conn.close()

Scheduling the Script with Cron

Edit the crontab:

crontab -e

Add the following line to log data every minute:

* * * * * /usr/bin/python3 /home/pi/logger.py

Save and exit. This will execute logger.py every minute and store the data in mydatabase.db.

Step 5: Backing Up & Restoring SQLite Data

Backup the Database

sqlite3 mydatabase.db ".backup mydatabase_backup.db"

Restore from Backup

sqlite3 mydatabase.db ".restore mydatabase_backup.db"

Conclusion

SQLite is a powerful and efficient database for Raspberry Pi applications. In this guide, we covered:

• Installing and setting up SQLite
• Creating and managing a database
• Using SQLite with Python
• Automating data logging with cron jobs
• Backing up and restoring data

With these skills, you can build robust IoT projects, data logging applications, and embedded systems using SQLite on Raspberry Pi!