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.