SQLite Database

The previous post regarding CSV is useful for local monitoring but in order for it be accessed online the data must be added to an SQLite database. SQL (Structured Query Language) is a programming language which can be understood by a database, it is similar to a CSV in which it stores data in columns and rows but can be accessed more easily by a server. The package sqlite3 must be installed before use.

SQL Basics

The above screen capture is showing basic commands of sqlite3 command line interface. To create a .db file (Database file), the following command is used:

sqlite3 fileName.db

Luckily, SQL is very easy to read and completing tasks that are normally complex in other languages are far easier in SQL due to it being designed with server maintenance in mind, searching the database for a keyword is far faster than say a for loop iterating through the entirety of the data. All commands end with a semicolon, a basic command:

CREATE TABLE – the name of the table follows this command followed by the column details e.g. CREATE TABLE NewTable (Foo REAL, Bar INTEGER);

The data types are also very easy to interpret with int being INTEGER and float being REAL. Contrary to most languages the data type succeeds the variable name which may be confusing.

Information on SQLite

Although it makes no difference to the function of the code, it is common practice to include the SQL commands/data types (e.g. CREATE, REAL) as upper case, this way when the SQL is included in a Python script it is easily distinguishable. In our server setup, SQL and Python will be used alongside each other as well as the code being shared between two.

import sqlite3
import time
import serial
import string

print ("Starting... ")
serialData = serial.Serial(port = '/dev/ttyS0', # ttyS0 is for the RPi 3
                           baudrate = 19200,
                           parity = serial.PARITY_NONE,
                           stopbits = serial.STOPBITS_ONE,
                           bytesize = serial.EIGHTBITS,
                           timeout = 100)

my_Db = sqlite3.connect('SQL_DB.db')
cursor_Obj = my_Db.cursor()
'''
when selecting stuff in a query, multiple rows are returned so the cursor
remembers where in the query you are
'''

# Storing data from port to database
while True:
  windSpeed, date = t
  reading_time = int(time.time())
  windSpeed = serialData # serial variable read from COM port
  print('Date: {0} windSpeed = {1:0.2f}%'format(date, windSpeed))
  cursor_Obj.execute('INSERT INTO Table1 VALUES (?, ?)',
                    (reading_time, '{0} Wind Speed: ', windSpeed)# finish
  cursor_Obj.commit() # must commit after each
  time.sleep(1) # reads once a second<span 				

The above code has similar aims to the previous CSV file but with the inclusion of SQL code instead of simply sending the data to a spreadsheet. The use of SQL allows the data to easily managed and transferred. For our project, we are using a particular engine of SQL and that's SQLite, an open source engine which is the most used out of all.

To view the SQLite database in GUI form we used the SQL Studio, available for Windows and Mac for free and is far easier to use than the command line, it also shows the data in a far clearer view.

We added Date along with the two types of information successfully transferred wirelessly from the L4 chip (the MCU we’re using).