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.

Authors

For more information contact Adam Simankowicz or Edmond Lascaris

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.

Python instructions for SQLite

Here is a list of useful commands in python for the SQLite database [2].

• import sqlite3 - imports the sqlite3 module in the python program. Classes and methods are defined in this sqlite3 module to allow communication with the SQLite database.

• connect() method of the connector class with the database name. To establish a connection to SQLite, you need to pass the database name you want to connect. If you specify the database file name that already presents on the disk, it will connect to it. But if a SQLite database file doesn’t exist, SQLite creates a new database for you. The method returns the SQLite Connection Object if the connection is successful. Examples of the implementation of the method are below.
  • con = sqlite3.connect('data.db')
  • sqliteConnection = = sqlite3.connect('SQLite_Python.db')

• cursor() method of a connection class to create a cursor object to execute SQLite command/queries from Python. A cursor object is the object that interacts with the tables and fields within the database. Think of it as an invisible hand.
  • c = con.cursor()
  • cursor = sqliteConnection.cursor()

• execute() methods run the SQL query and return the result.
  • c.execute(insert_query, insert_data)
  • cursor.execute(sqlite_Query)

• SQLite Query - SQL queries are the commands that are sent to the database. This is a whole topic in itself. You can find tutorials here at w3schools [3]

• fetchall() - cursor.fetchall() will read the query result.

• cursor.close()' and connection.close() - Msethod to close the cursor and SQLite connections. All connections to a database need to be closed to leave the database in a safe state and to allow other users to access the database.

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

• The code is reproduced below:

import sqlite3

try:
    sqliteConnection = sqlite3.connect('SQLite_Python.db')
    cursor = sqliteConnection.cursor()
    print("Database created and Successfully Connected to SQLite")

    sqlite_select_Query = "select sqlite_version();"
    cursor.execute(sqlite_select_Query)
    record = cursor.fetchall()
    print("SQLite Database Version is: ", record)
    cursor.close()

except sqlite3.Error as error:
    print("Error while connecting to sqlite", error)
finally:
    if sqliteConnection:
        sqliteConnection.close()
        print("The SQLite connection is closed")

• 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.

• The python code is available below.

import sqlite3

try:
    sqliteConnection = sqlite3.connect('SQLite_Python.db')
    cursor = sqliteConnection.cursor()
    print("Successfully Connected to SQLite")

    sqlite_insert_query = """INSERT INTO mysql_table
                          (id, voltage, current, power, state_of_charge) 
                           VALUES 
                          (1,'13567','2376','198',86)"""
    cursor.execute("""CREATE TABLE IF NOT EXISTS mysql_table
    (id INTEGER PRIMARY KEY,
    voltage INTEGER,
    current INTEGER,
    power INTEGER,
    state_of_charge INTEGER);""")
    count = cursor.execute(sqlite_insert_query)
    sqliteConnection.commit()
    print("Record inserted successfully into mysql_table table ", cursor.rowcount)
    cursor.close()

except sqlite3.Error as error:
    print("Failed to insert data into sqlite table", error)
finally:
    if sqliteConnection:
        sqliteConnection.close()
        print("The SQLite connection is closed")

• 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.

Inserting data into Table using Python

• There are several ways that data can be inserted into database. Here are some examples.
  • connection.execute("INSERT INTO entries VALUES ('This is some test content', '01-01-2020');")

• onnection.execute("INSERT INTO entries VALUES (?, ?);", ("This is some test content", "01-01-2020"))