Smart Cities - SQLite3 database

Introduction

• SQLite3 is a single file database that is easy to install and access.
• It responds to SQL queries.
• Unlike other databases it does not require a database server.
• Because the SQLite3 database exists as a single file on a computer it is easy to copy and backup the database.
• Data kept on the database can be easily shared with students.

Installation of SQLite3 on Raspberry Pi

• In the Terminal enter the command sudo apt install sqlite3

Installing Database Browser software

• In the Terminal enter the command sudo apt install sqlitebrowser

• This will take approximately 30 seconds to install.

• Once the software is installed it will appear in the Raspberry Pi menu under Programming.
• The name of the program is DB Browser for SQLite
• This software gives complete access to the database to view and edit data.

Creating a database and table in SQLite3 using DB Browser for SQLite

• Open the DB Browser for SQLite

• In the main menu, click on New Database
• Navigate to the user home directory /home/pi
• Enter the file name data.db
• Click Save.

• Under Table - Give the database Table the name measurements

• Under Fields - Add field
  • Name id - this is a simple Primary Key. Ensure that the following additional options are selected:
    • Type integer
    • No - Not null (cannot be left empty)
    • PK - Primary Key (unique number for each row entry)
    • AI - Auto increment

• Add fields for V battery voltage and I current

• Click on OK to return to the main menu.
• More fields can be added to the table later by clicking on the Table and then selecting Modify Table from the menu.

• Click on Write Changes to commit these changes (save settings)

• Always remember to Close Database when finished.

Interacting with SQLite Databases using Python

• Python script can create SQLite databases, tables and data entries [1].
• Python allows full interaction with the database using SQL commands.

Creating a SQLite Database using Python

• In the pi user home directory create a new directory named solar_python. We will use this directory for this activity.
• Enter the command nano sqlite_test.py to create a new python application.

• Enter the following python code the nano editor.
• Exit and save changes

• Run the file with the command python3 sqlite_test.py
• The python file will create a new database named SQLite_Python.db

• Verify that the database has been created by listing the contents of the directory.
• You should see the file SQLite_Python.db

• Finally, using the GUI based DB Browser for SQLite you can open the new database file.
• Even though the database exists it wont have any Tables.

Creating a Table and single Data entry in a SQLite Database using Python

• Using nano we will create a new Python script to both create a table and add data to the table.
• Enter the command nano sqlite_test_data.py
• Then enter the following code in nano.
• Exit and Save the file.

• Run the python application with the command python3 sqlite_test_data.py

• Inspect the database using the DB Browser for SQLite.
• You should see that a table named mysql_table has been created.
• And that the table contains Field entries for id, voltage, current, power and state_of_charge.

• Clicking on the Browse Data tab will show the data in each of the fields within the table.

Creating a Table with multiple data entries using Python

• In this example we will add multiple data entries to the table in the database.