As Discussed in the post Using an ORM Tool for SQLite , we used the Peewee ORM to act as an intermediate layer between our SQLite database and our Python code.
Model.py
When starting off with SQLite you would create,access and edit your database as shown in the SQLite Database post. With Peewee this process is made a lot easier. A ‘model.py’ file was created. The code from within this file is shown and explained below;
from peewee import * # Imports all the libraries from the Peewee db = SqliteDatabase('UPVSite.db')# Creates a database(db) called UPVSite.db ''' Standard convention would be to have a Plural as the DB name and singular for the class name(s)'''<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span> class Turbine_Weather(Model): time = DateTimeField() incoming_data = TextField() class Meta: database = db
The above code is the start of the ‘model.py‘ script. Apart from what is already commented within the code, it is creating a Model type class named ‘Turbine_Weather‘ that contains the characteristics time and incoming_data, that are declared as a DateTime field and Text field respectively. DateTimeField() and TextField() are two out of a long list of built in field type specifiers within Peewee, a full list can be found at Peewee Field Types . The class named Meta is declaring that the Turbine_Weather Model will have Peewee’s database characteristics which will be important for use with Flask and Flask-Admin later on.
class SensorAccess(object): # Initialises access to the database def __init__(self): ''' Self stops confusion between SQLite syntax that is similar to Python syntax ''' db.connect() # Connects to database db.create_tables([Turbine_Weather], safe=True) # Checks if table is made # Function to add data readings to db table def add_reading(self,time, incoming_data): Turbine_Weather.create(time = time, incoming_data = incoming_data ) # Function to return data correlating to a specific time passed to in def get_(self, time ): data_at_time = Turbine_Weather.get(Turbine_Weather.time == time) return data_at_time # This is a function to return all rows of data def get_readings(self): return Turbine_Weather.select() # Function to return the latest data readings up to a certain limit in # descending order def get_recent_readings (self , limit = 30): return Turbine_Weather.select() \ .order_by(Turbine_Weather.time.desc()) \ .limit(limit) # Closes the connection to the database def close(self): db.close()
The above code is the final part of the ‘model.py‘ script. Here an Object type class is being made named ‘SensorAccess’. This class contains all functions that will relate to the sensor and related databases, although in this case we only have one db associated with the sensor object. All database query’s are made as functions e.g. get_recent_readings(self, limit = 30).
SerialRead.py
In our case to use Peewee to add the data coming in through the antennas connected to the serial port of the PI a second .py script was needed, we named it as ‘SerialRead.py‘. This script is very similar to the code that was implemented in the Saving Data to a .CSV post. It updates on that by using the functionality within the ‘model.py‘ to add the incoming data sent from the STM32L4 chip to the UPVSite database.
import sqlite3 import time import datetime import serial import string import model print ("Starting... ") ser_in = 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) print ("Connecting... ") data = model.SensorAccess() try: while True : si = ser_in.readline().strip() # si = Serial in<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span> read_time = datetime.datetime.now()#reads in current time into read_time print(si) data.add_reading(read_time, si)# Adds the current time and incoming data time.sleep(300)# Sleeps for 5 minutes before updating its readings
In the code above the data is read in as usual. The major difference is the ‘data.add_reading(read_time, si)’ that uses the ‘add_reading(self, time)’ function within our ‘model.py‘ to add read in data to the database.