SQLite database for sensor data in Raspberry Pi - Step-by-Step Execution

Concept Flow - SQLite database for sensor data

Start Program

↓

Connect to SQLite DB

↓

Create Table if not exists

↓

Read Sensor Data

↓

Insert Data into Table

↓

Commit Transaction

↓

Close Connection

↓

End Program

The program connects to a SQLite database, creates a table if needed, reads sensor data, inserts it into the table, commits changes, and closes the connection.

Execution Sample

Raspberry Pi

import sqlite3
conn = sqlite3.connect('sensor.db')
cursor = conn.cursor()
cursor.execute('''CREATE TABLE IF NOT EXISTS readings (id INTEGER PRIMARY KEY, temperature REAL, humidity REAL)''')
cursor.execute('INSERT INTO readings (temperature, humidity) VALUES (?, ?)', (23.5, 60.2))
conn.commit()
conn.close()

This code creates a database and table, inserts one sensor reading, saves it, and closes the database.

Execution Table

Step	Action	SQL Command / Operation	Result / State
1	Connect to DB	sqlite3.connect('sensor.db')	Database file 'sensor.db' opened or created
2	Create cursor	conn.cursor()	Cursor object created for SQL commands
3	Create table if not exists	CREATE TABLE IF NOT EXISTS readings (id INTEGER PRIMARY KEY, temperature REAL, humidity REAL)	Table 'readings' ready in DB
4	Insert sensor data	INSERT INTO readings (temperature, humidity) VALUES (23.5, 60.2)	One row inserted with temperature=23.5, humidity=60.2
5	Commit transaction	conn.commit()	Changes saved to database
6	Close connection	conn.close()	Database connection closed

💡 Program ends after closing the database connection

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5	After Step 6
conn	None	Connection object	Connection object	Connection object	Connection object	Connection object	Closed
cursor	None	None	Cursor object	Cursor object	Cursor object	Cursor object	None

Key Moments - 3 Insights

Why do we need to commit after inserting data?

Committing saves changes permanently in the database. Without commit, inserted data won't be stored, as shown in step 5 of the execution_table.

What happens if the table already exists when we run CREATE TABLE?

Using 'IF NOT EXISTS' prevents errors by only creating the table if it doesn't exist, as seen in step 3.

Why close the database connection at the end?

Closing frees resources and ensures data integrity, as shown in step 6.

Visual Quiz - 3 Questions

Look at the execution_table, what is the state of 'conn' after step 6?

A Connection open

B Connection closed

C Cursor object

D None

💡 Hint

Check variable_tracker row for 'conn' after step 6

At which step is the sensor data actually saved permanently in the database?

A Step 4 - Insert sensor data

B Step 3 - Create table

C Step 5 - Commit transaction

D Step 6 - Close connection

💡 Hint

Look at execution_table step 5 description about commit

If we remove 'IF NOT EXISTS' from the CREATE TABLE command, what could happen?

A Program will fail if table exists

B Data will be lost

C Table will be created anyway without issues

D Database connection will close early

💡 Hint

Refer to key_moments about table creation errors

Concept Snapshot

SQLite database for sensor data:
- Connect with sqlite3.connect('file.db')
- Create table with CREATE TABLE IF NOT EXISTS
- Insert data with INSERT INTO
- Commit changes with conn.commit()
- Close connection with conn.close()
- Always commit to save data permanently

Full Transcript

This example shows how to use SQLite on a Raspberry Pi to store sensor data. First, the program connects to a database file or creates it if missing. Then it creates a table for readings if it doesn't exist yet. Next, it inserts one sensor reading with temperature and humidity values. After inserting, it commits the transaction to save the data permanently. Finally, it closes the database connection to free resources. Key points include always committing after inserts and using 'IF NOT EXISTS' to avoid errors when creating tables. Closing the connection is important for data integrity.