MySQL Database Interface

The MySQL interface allows to using a MySQL (or MariaDB or Percona) server for Data Logging and/or persistence.

Setup

The MySQL interface does (currently) not include automated database setup or database schema migration, so the database needs to be set up manually. Typically, this includes chosing a secure password for the database connection and running the following commands in a mysql console with root privileges:

CREATE DATABASE 'shc';
CREATE USER 'shc'@'localhost' IDENTIFIED BY '<password>';
GRANT SELECT, INSERT, UPDATE ON shc.* TO  'shc'@'localhost';
FLUSH PRIVILEGES;

USE shc;

CREATE TABLE log (
    name VARCHAR(256) NOT NULL,
    ts DATETIME(6) NOT NULL,
    value_int INTEGER,
    value_float FLOAT,
    value_str LONGTEXT,
    KEY name_ts(name, ts)
);

CREATE TABLE `persistence` (
    name VARCHAR(256) NOT NULL,
    ts DATETIME(6) NOT NULL,
    value LONGTEXT,
    UNIQUE KEY name(name)
);

Usage

With the database setup listed above, the database can be used in an SHC project with the following code (or similar):

import shc
import shc.interfaces.mysql

# you may want to load the database password from some configfile to keep it out of your project code
mysql_interface = shc.interfaces.mysql.MySQLConnector(host="localhost", db="shc", user="shc", password="<password>")

# ...

# a variable with logging
my_room_temperature = shc.Variable(float, "my_room_temperature") \
    # example for connecting to the real world:
    #.connect(mqtt_interface.topic_string("temp/my_room"), convert=true) \
    .connect(mysql_interface.variable(float, "my_room_temperature"))

# a variable with persistence
my_room_temperature = shc.Variable(bool, "temperature_control_active") \
    .connect(mysql_interface.persistence_variable(bool, "temperature_control_active"), read=true)

Module Documentation

class shc.interfaces.mysql.MySQLConnector(**kwargs)

Interface for using a MySQL (or MariaDB or Percona) server for logging and/or persistence

A database with the following schema needs to be created manually on the database server:

CREATE TABLE log (
    name VARCHAR(256) NOT NULL,
    ts DATETIME(6) NOT NULL,
    value_int INTEGER,
    value_float FLOAT,
    value_str LONGTEXT,
    KEY name_ts(name, ts)
);

CREATE TABLE `persistence` (
    name VARCHAR(256) NOT NULL,
    ts DATETIME(6) NOT NULL,
    value LONGTEXT,
    UNIQUE KEY name(name)
);

For data logging of all value types that are derived from int (incl. bool), float or str, the respective value_ column is used. This includes Enum types with a value base type in any of those. Otherwise, the value is JSON-encoded, using SHC’s generic JSON encoding/decoding system from the shc.conversion, and stored in the value_str column.

Values of persistence variables are always JSON-encoded for storage.

All timestamps are stored in UTC.

The given constructor keyword arguments are passed to aiomysql.connect() for configuring the connection to the database server:

host: str (default: "localhost")
port: int (default: 3306)
(optional) unix_socket: str
(optional) user: str
password: str (default: "")
db: str – name ot the database
(optional) read_default_file – path to my.cnf file to read all these options from

variable(type_: Type[T], name: str) → MySQLLogVariable[T]

Creates a connectable object with the given value type for logging a time series of the given name in the MySQL database

The returned object is writable, readable and a (subscribable) DataLogVariable.

Parameters:

type – The value type of the returned connectable variable object. It must provide a default JSON serialization.
name – The name of the time series in the database. Its length must not exceed the declared maximum length of the log.name column in the database schema (256 by default). If the same name is requested twice, the same connectable object instance will be returned.

persistence_variable(type_: Type[T], name: str) → MySQLPersistenceVariable[T]

Creates a connectable object with the given value type for persisting (only) the current value of a connected object under the given name in the MySQL database

The returned object is writable, readable.

Parameters:

type – The value type of the returned connectable object. It must provide a default JSON serialization.
name – The name for identifying the value in the database. Its length must not exceed the declared maximum length of the log.persistence column in the database schema (256 by default). If the same name is requested twice, the same connectable object instance will be returned.