Untitled

print('Importing libraries...')

import config
from datetime import datetime
import numpy as np
import mariadb
import pandas as pd
import serial
import sys

print(f'Connecting to {config.SERIAL_PORT}...')

try:
	ser = serial.Serial(config.SERIAL_PORT, baudrate=config.SERIAL_BAUDRATE, timeout=config.SERIAL_TIMEOUT)
	ser.flush()
except serial.serialutil.SerialException as e:
	print(f'Error connecting to serial: {e}')
	sys.exit(1)

print(f'Connecting to {config.DB_HOST}...')

try:
	conn = mariadb.connect(
		user=config.DB_USER,
		password=config.DB_PASSWORD,
		host=config.DB_HOST,
		port=config.DB_PORT,
		)
	cursor = conn.cursor()
except mariadb.Error as e:
	print(f'Error connecting to MariaDB Platform: {e}')
	sys.exit(1)

print(f'Checking database: {config.DB_NAME}...')

try:
	if config.DB_CLEAR:
		cursor.execute(f'DROP DATABASE {config.DB_NAME}')
	cursor.execute(f'CREATE DATABASE IF NOT EXISTS {config.DB_NAME}')
	cursor.execute(f'USE {config.DB_NAME}')
except mariadb.Error as e:
	print(f'Error: {e}')


print(f'Checking tables...')
try:
	# Sessions
	cursor.execute("""
		CREATE TABLE IF NOT EXISTS sessions (
			id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
			start_time DATETIME,
			last_update DATETIME
		)""")
	# Measurements
	cursor.execute("""
		CREATE TABLE IF NOT EXISTS measurements (
			id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
			session_id INT NOT NULL, 
			measure_time DATETIME, 
			sensor VARCHAR(16), 
			value INT,
			volts FLOAT,
			temperature FLOAT,
			CONSTRAINT `fk_session_id`
			    FOREIGN KEY (session_id) REFERENCES sessions (id)
			    ON DELETE CASCADE
			    ON UPDATE RESTRICT
		)""")
except mariadb.Error as e:
	print(f'Error: {e}')

# Session ID.
print(f'Creating session...')
try:
	start_time = datetime.now()
	params = [
		(start_time),
	]
	cursor.execute('INSERT INTO sessions (start_time) VALUES (?)', params)
	session_id = cursor.lastrowid
except mariadb.Error as e:
	print(f'Error: {e}')
print('Session ID:', session_id)


# Measurements

def empty_measurement_dict():
	d = dict(
		measure_times = [],
		values = [],
		volts = [],
		temperatures =[],
	)
	return d


data = empty_measurement_dict()

average_counter = 0

while True:

	if ser.in_waiting <= 0:
		continue

	line = ser.readline().decode('utf-8').rstrip()
	line = line.split(', ')
	if len(line) != 3:
		print(f"Error separating values: {line}")
		continue
	try:
		value, volts, temperature = int(line[0]), float(line[1]), float(line[2])
	except:
		print(f"Error converting temperature: {line}")
		continue

	now = datetime.now()
	data['measure_times'].append(now)
	data['values'].append(value)
	data['volts'].append(volts)
	data['temperatures'].append(temperature)
	
	average_counter += 1

	if average_counter >= config.N_MEASUREMENT_AVERAGE:

		df = pd.DataFrame(data)
		measure_time = df['measure_times'].mean().to_pydatetime()
		sensor = 'TMP36'
		value = df['values'].mean()
		volts = df['volts'].mean()
		temperature = df['temperatures'].mean()
		
		average_counter = 0
		data = empty_measurement_dict()

		params = (session_id, measure_time, sensor, value, volts, temperature)

		cursor.execute('INSERT INTO measurements (session_id, measure_time, sensor, value, volts, temperature) VALUES (?, ?, ?, ?, ?, ?)', params)
		cursor.execute('UPDATE sessions SET last_update=%s WHERE id=%s', (measure_time, session_id))
		conn.commit()