SQLite database for sensor data in Raspberry Pi

Introduction

We use an SQLite database to save sensor readings so we can keep track of data over time. It helps us organize and find sensor information easily.

You want to store temperature readings from a sensor every minute.

You need to keep a history of humidity levels to analyze later.

You want to save light sensor data to check patterns during the day.

You want to log sensor data on a Raspberry Pi without needing a big database server.

Syntax

Raspberry Pi

import sqlite3

# Connect to database (creates file if not exists)
conn = sqlite3.connect('sensor_data.db')
cursor = conn.cursor()

# Create table for sensor readings
cursor.execute('''
CREATE TABLE IF NOT EXISTS readings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    sensor_name TEXT NOT NULL,
    value REAL NOT NULL,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
)
''')

# Insert a sensor reading
cursor.execute('INSERT INTO readings (sensor_name, value) VALUES (?, ?)', ('temperature', 23.5))
conn.commit()

# Query data
cursor.execute('SELECT * FROM readings')
rows = cursor.fetchall()

# Close connection
conn.close()

Use sqlite3.connect() to open or create the database file.

Use cursor.execute() to run SQL commands like creating tables or inserting data.

Examples

This creates a table named readings if it does not exist yet.

Raspberry Pi

cursor.execute('CREATE TABLE IF NOT EXISTS readings (id INTEGER PRIMARY KEY, sensor_name TEXT, value REAL, timestamp DATETIME)')

This adds a new humidity reading with value 45.2.

Raspberry Pi

cursor.execute('INSERT INTO readings (sensor_name, value) VALUES (?, ?)', ('humidity', 45.2))

This gets all temperature readings from the database.

Raspberry Pi

cursor.execute('SELECT * FROM readings WHERE sensor_name = ?', ('temperature',))
rows = cursor.fetchall()

Sample Program

This program creates a database and table, inserts three temperature readings with a pause between them, then prints all stored readings with their timestamps.

Raspberry Pi

import sqlite3
import time

# Connect to SQLite database file
conn = sqlite3.connect('sensor_data.db')
cursor = conn.cursor()

# Create table if not exists
cursor.execute('''
CREATE TABLE IF NOT EXISTS readings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    sensor_name TEXT NOT NULL,
    value REAL NOT NULL,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
)
''')

# Simulate inserting 3 temperature readings
for temp in [22.4, 22.8, 23.1]:
    cursor.execute('INSERT INTO readings (sensor_name, value) VALUES (?, ?)', ('temperature', temp))
    conn.commit()
    time.sleep(1)  # wait 1 second to get different timestamps

# Retrieve and print all readings
cursor.execute('SELECT id, sensor_name, value, timestamp FROM readings')
rows = cursor.fetchall()
for row in rows:
    print(f'ID: {row[0]}, Sensor: {row[1]}, Value: {row[2]}, Time: {row[3]}')

# Close connection
conn.close()

Important Notes

SQLite stores the database in a single file, making it easy to move or backup.

Always call conn.commit() after inserting or changing data to save it.

Use DEFAULT CURRENT_TIMESTAMP to automatically save the time when data is added.

Summary

SQLite helps save sensor data on Raspberry Pi without extra setup.

Create tables to organize sensor readings with columns for name, value, and time.

Insert and query data easily using Python's sqlite3 module.