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.

Practice

1. What is the main purpose of using SQLite on a Raspberry Pi for sensor data?

easy

A. To send sensor data directly to the cloud

B. To control the sensor hardware directly

C. To store sensor readings locally without needing a separate database server

D. To display sensor data on a web page automatically

Solution

1. Step 1: Understand SQLite's role on Raspberry Pi

   SQLite is a lightweight database that runs locally without needing a separate server.

2. Step 2: Connect SQLite to sensor data storage

   It stores sensor readings on the Raspberry Pi itself, making data management easy and offline.

3. Final Answer:

   To store sensor readings locally without needing a separate database server -> Option C

4. Quick Check:

   SQLite stores data locally = B [OK]

Hint: SQLite stores data locally on Pi, no extra server needed [OK]

Common Mistakes:

• Confusing SQLite with cloud services
• Thinking SQLite controls hardware
• Assuming SQLite automatically displays data

2. Which of the following is the correct Python code snippet to create a table named sensors with columns id (integer primary key), name (text), and value (real)?

easy

A. cursor.execute('CREATE TABLE sensors (id INTEGER PRIMARY KEY, name TEXT, value REAL)')

B. cursor.execute('CREATE sensors TABLE (id INT, name STRING, value FLOAT)')

C. cursor.execute('MAKE TABLE sensors (id PRIMARY KEY, name TEXT, value REAL)')

D. cursor.execute('CREATE TABLE sensors id INTEGER, name TEXT, value REAL')

Solution

1. Step 1: Check SQL syntax for table creation

   The correct SQL command is CREATE TABLE followed by table name and column definitions in parentheses.

2. Step 2: Match Python sqlite3 syntax

   Python uses cursor.execute() with a valid SQL string. cursor.execute('CREATE TABLE sensors (id INTEGER PRIMARY KEY, name TEXT, value REAL)') matches correct SQL and Python syntax.

3. Final Answer:

   cursor.execute('CREATE TABLE sensors (id INTEGER PRIMARY KEY, name TEXT, value REAL)') -> Option A

4. Quick Check:

   Correct SQL CREATE TABLE syntax = D [OK]

Hint: Use 'CREATE TABLE table_name (columns)' inside cursor.execute() [OK]

Common Mistakes:

• Using wrong SQL keywords like MAKE TABLE
• Incorrect column types like STRING instead of TEXT
• Missing parentheses around columns

3. Given this Python code snippet using sqlite3 on Raspberry Pi:

import sqlite3
conn = sqlite3.connect('data.db')
cursor = conn.cursor()
cursor.execute('CREATE TABLE IF NOT EXISTS sensors (name TEXT, value REAL)')
cursor.execute("INSERT INTO sensors VALUES ('temp', 23.5)")
cursor.execute('SELECT * FROM sensors')
rows = cursor.fetchall()
print(rows)

What will be the output?

medium

A. [('temp', 23.5)]

B. []

C. SyntaxError

D. None

Solution

1. Step 1: Understand table creation and insertion

   The table 'sensors' is created if not exists, then a row ('temp', 23.5) is inserted.

2. Step 2: Fetch all rows and print

   The SELECT query fetches all rows, which includes the inserted row, so print outputs [('temp', 23.5)].

3. Final Answer:

   [('temp', 23.5)] -> Option A

4. Quick Check:

   Inserted row fetched = [('temp', 23.5)] [OK]

Hint: Inserted data appears in fetchall() output as list of tuples [OK]

Common Mistakes:

• Forgetting to insert data before SELECT
• Expecting empty list if table exists
• Confusing fetchall() output format

4. This Python code tries to insert sensor data but causes an error:

cursor.execute('CREATE TABLE sensors (name TEXT, value REAL)')
cursor.execute('INSERT INTO sensors (name, value) VALUES (?, ?)', ('humidity'))
conn.commit()

What is the error and how to fix it?

medium

A. Error: SQL syntax error; fix by removing parentheses in VALUES

B. Error: Missing second value in tuple; fix by adding a second value like ('humidity', 45.0)

C. Error: Table sensors does not exist; fix by creating table first

D. Error: commit() missing; fix by adding conn.commit()

Solution

1. Step 1: Identify parameter mismatch in INSERT

   The VALUES clause expects two values, but only one ('humidity') is given as a tuple missing a second element.

2. Step 2: Fix by providing both values

   Provide a tuple with two elements, e.g., ('humidity', 45.0), to match the two columns.

3. Final Answer:

   Error: Missing second value in tuple; fix by adding a second value like ('humidity', 45.0) -> Option B

4. Quick Check:

   Parameter count matches columns = C [OK]

Hint: Tuple for parameters must match number of placeholders [OK]

Common Mistakes:

• Passing single string instead of tuple with two values
• Ignoring commit() after insert
• Wrong SQL syntax in INSERT statement

5. You want to store sensor readings with a timestamp in SQLite on Raspberry Pi. Which table schema and insertion code correctly store sensor name, value, and current time using Python's sqlite3?

hard

A. CREATE TABLE readings (name TEXT, value REAL, time TEXT); cursor.execute("INSERT INTO readings VALUES ('temp', 22.1, 'CURRENT_TIMESTAMP')")

B. CREATE TABLE readings (name TEXT, value REAL, time INTEGER); cursor.execute("INSERT INTO readings VALUES ('temp', 22.1, CURRENT_TIMESTAMP)")

C. CREATE TABLE readings (name TEXT, value REAL, time INTEGER); cursor.execute("INSERT INTO readings VALUES ('temp', 22.1, datetime('now'))")

D. CREATE TABLE readings (name TEXT, value REAL, time TEXT); cursor.execute("INSERT INTO readings VALUES ('temp', 22.1, datetime('now', 'localtime'))")

Solution

1. Step 1: Choose correct table schema for timestamp

   Use TEXT type for time column to store datetime strings, which SQLite supports well.

2. Step 2: Insert current local time correctly

   Use SQLite function datetime('now', 'localtime') to get current local timestamp as text.

3. Step 3: Match insertion code with schema

   CREATE TABLE readings (name TEXT, value REAL, time TEXT); cursor.execute("INSERT INTO readings VALUES ('temp', 22.1, datetime('now', 'localtime'))") uses TEXT time column and inserts with datetime('now', 'localtime'), which is correct.

4. Final Answer:

   CREATE TABLE readings (name TEXT, value REAL, time TEXT); cursor.execute("INSERT INTO readings VALUES ('temp', 22.1, datetime('now', 'localtime'))") -> Option D

5. Quick Check:

   Local timestamp stored as TEXT = A [OK]

Hint: Use datetime('now', 'localtime') for current local time in SQLite [OK]

Common Mistakes:

• Using CURRENT_TIMESTAMP without quotes in Python
• Storing time as INTEGER without conversion
• Ignoring localtime adjustment for timestamps