SQLite database for sensor data in Raspberry Pi - Mini Project: Build & Apply

SQLite database for sensor data

📖 Scenario: You have a Raspberry Pi connected to a temperature sensor. You want to store the temperature readings in a small database so you can analyze them later.

🎯 Goal: Create a simple SQLite database on the Raspberry Pi to store sensor data. You will create the database, add a configuration for the sensor ID, insert temperature readings, and finally display the stored data.

📋 What You'll Learn

Create an SQLite database connection

Create a table for sensor readings

Store sensor ID in a variable

Insert temperature readings into the database

Retrieve and display all stored readings

💡 Why This Matters

🌍 Real World

Storing sensor data in a database on a Raspberry Pi helps keep track of environmental conditions over time for analysis or alerts.

💼 Career

Many IoT and embedded systems jobs require working with small databases like SQLite to manage sensor or device data efficiently.

1

Create SQLite database and table

Write code to import sqlite3, connect to a database file named sensor_data.db, and create a table called readings with columns id (integer primary key autoincrement), sensor_id (text), temperature (real), and timestamp (text). Use conn as the connection variable and cursor as the cursor variable.

Raspberry Pi

import sqlite3

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

# Create readings table
# Your code here

Need a hint?

Use sqlite3.connect() to connect and cursor.execute() to create the table.

2

Set sensor ID configuration

Create a variable called sensor_id and set it to the string 'sensor_01' to identify the sensor.

Raspberry Pi

import sqlite3

conn = sqlite3.connect('sensor_data.db')
cursor = conn.cursor()
cursor.execute('''CREATE TABLE IF NOT EXISTS readings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    sensor_id TEXT,
    temperature REAL,
    timestamp TEXT
)''')
conn.commit()

# Set sensor_id variable
# Your code here

Need a hint?

Just assign the string 'sensor_01' to the variable sensor_id.

3

Insert a temperature reading

Write code to insert a new reading into the readings table using cursor.execute(). Use the sensor_id variable, temperature value 23.5, and timestamp string '2024-06-01 12:00:00'. Commit the change with conn.commit().

Raspberry Pi

import sqlite3

conn = sqlite3.connect('sensor_data.db')
cursor = conn.cursor()
cursor.execute('''CREATE TABLE IF NOT EXISTS readings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    sensor_id TEXT,
    temperature REAL,
    timestamp TEXT
)''')
conn.commit()

sensor_id = 'sensor_01'

# Insert a temperature reading
# Your code here

Need a hint?

Use parameter substitution with ? placeholders and a tuple of values.

4

Display all stored readings

Write code to select all rows from the readings table using cursor.execute() and fetch all results with cursor.fetchall(). Then print each row on its own line.

Raspberry Pi

import sqlite3

conn = sqlite3.connect('sensor_data.db')
cursor = conn.cursor()
cursor.execute('''CREATE TABLE IF NOT EXISTS readings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    sensor_id TEXT,
    temperature REAL,
    timestamp TEXT
)''')
conn.commit()

sensor_id = 'sensor_01'

cursor.execute('INSERT INTO readings (sensor_id, temperature, timestamp) VALUES (?, ?, ?)', (sensor_id, 23.5, '2024-06-01 12:00:00'))
conn.commit()

# Retrieve and print all readings
# Your code here

Need a hint?

Use cursor.execute('SELECT * FROM readings') and cursor.fetchall(). Then print each row.