Alias names

[dukbilt]

ASSIGNING NAME TO A 1-WIRE SENSOR ID

Hello everyone,

I have a 1-wire temperature sensor network running off a Raspberry Pi. The temperature from the sensors is added to a SQL database.

It works well, but I would like to assign more meaningful names to the sensors than their 1-wire ID number (like 'Wall' instead of '28BC7CEF04000344' for instance). As the sensors don't change location, I don't want to make any changes to the sensor logging and database side at all.

What I do want is to have a single, easily to read/modify text file that holds the 'position' information (such as "Back Porch", "Bedroom 1", "Bedroom 2", "Front Porch", "Kitchen") along with the sensor IDs for sensors that relate to those positions. This text file needs to be able to be amended easily so I easily add a new 'position' (say "Underfloor" or "Air-conditioner Inlet") as well as new sensors (ie, say sensor '28BC7CEF04000344' in the "Air-conditioner Inlet" fails then I replace it with sensor '28BC7CEF04000345', update the file, and the data display will continue to show me the "Air-conditioner Inlet" temperatures regardless of sensors being replaced.


The code that records the data is:

#! /usr/bin/python

# NAME:     logger.py
# DATE:     24 Apr 17

#####################################################################################################################################################
# PURPOSE: Records information from DS18B20 1-wire Temperature Sensors into an SQLite database.
#
#          NOTE 1: This program runs once - the intention is for to be executed as required (for example, through 'Cron' under Linux).
#
#          NOTE 2: No prior knowledge about what is on the bus is required - the entire bus is read, and the temperature data of all sensors
#                     is recorded, so sensors can be added and removed as required. The database is updated using:
#                        A. the time the bus was read for the Primary Key (ie the row number);
#                        B. the ID of the sensor for the field (ie the column name);
#                        C. the sensor temperature is stored as a value in that field at that time (ie the value of that column at that row).
#                     The database therefore consists of the temperatures for all sensors that were read at a given time.
#
#          NOTE 3: Primarily designed for multiple DS18B20 1-wire sensors connected to a Raspberry Pi via a DS2482-800 'I2C to 1-wire Bus Master'.
#                     The DS2482-800 connects to the I2C bus of the Raspberry Pi.
#
# OPERATION:    1. Connect to SQLite database (refer to 'DATABASE STRUCTURE' further below)
#               2. Create the table (unless it already exists)
#               3. Get current time ('Epoch Time')
#               4. Read 1-wire bus
#               5. Add data to the database using 'Epoch Time' as the Primary Key
#               6. Commit database changes
#               7. Close the database connection.
#
# DEPENDANCIES: 1-wire devices on local bus
#               External Time - This program uses 'time()' to produce the SQLite Primary Key called 'Epoch Time'. As the Raspberry Pi has no on-board
#                  non-volatile clock, re-booting the Pi will cause 'time()' to re-set, resulting in the database being over-written or current data
#                  being written in before data acquired in a previous boot. To prevent this from occurring, either use a non-volatile clock or
#                  synchronise with an external time source before executing. 
#               Python version - Written in Python 2 and therefore may not work under Python 3
#               SQLite database
#
# REFERENCES:   1-wire: https://www.maximintegrated.com/en/app-notes/index.mvp/id/1796
#               DS18B20 1-wire Temperature Sensors: https://www.maximintegrated.com/en/products/analog/sensors-and-sensor-interface/DS18B20.html
#               DS2482-800 'I2C to 1-wire Bus Master': https://www.maximintegrated.com/en/products/interface/controllers-expanders/DS2482-800.html
#               Python 'ow' file access: http://owfs.sourceforge.net/owpython.html
#               SQLite: https://www.techonthenet.com/sqlite/
#               Using CRON: https://help.ubuntu.com/community/CronHowto

####### 
# DATABASE STRUCTURE
# Name - "Temperature.db"
# COLUMN NAME:        'epoch' NUMERIC   '1st sensor id' TEXT  '2nd sensor id' TEXT    '3rd sensor id' TEXT    ...    'nth sensor id' TEXT    
# COLUMN CONTENT:       Epoch Time        1st Temp              2nd Temp                3rd Temp              ...      nth Temp
#
#####################################################################################################################################################


# USER DEFINITIONS
database_name = '/var/www/Temperature.sqlite3'
table_name = 'Log'
master_column = 'epoch'
master_col_type = 'NUMERIC PRIMARY KEY'

import ow
import time
import sqlite3

# Connect to the database
conn = sqlite3.connect(database_name)
cur = conn.cursor()

# Create the table if it doesn't already exist
cur.execute("CREATE TABLE IF NOT EXISTS {tn} ({col} {ct})".format(tn=table_name, col=master_column, ct=master_col_type))

# Get the current Epoch Time (used as the the SQLite table's Primary Key)
now = int(time.time())

# Get data from 1-wire sensors (sensor.type, sensor.address, sensor.temperature are all "string" types)
ow.init('localhost:4304')
mysensors = ow.Sensor("/uncached").sensorList( )
for sensor in mysensors[:]:

    # Create row using time
    try:
        cur.execute("INSERT INTO {tn} ({col}) VALUES ({data})".format(tn=table_name, col=master_column, data=now))
    except:
        a=1 # does nothing but allows the 'try...catch' arrangement to work
        
    # do we have a column name that matches the sensor?
    try:
        cur.execute("ALTER TABLE {tn} ADD COLUMN '{sensor}' NUMERIC".format(tn=table_name, sensor=sensor.address))
    except:
        a=1 # does nothing but allows the 'try...catch' arrangement to work

    # Add the temperature (to 1 decimal place) under the appropriate sensor's column
    cur.execute("UPDATE Log SET '{sensor}'={temp} WHERE epoch={time}".format(sensor=sensor.address, temp=round(float(sensor.temperature),1), time=now))

# commit the changes
conn.commit()

#close the connection
conn.close()

The code that displays the data in graphical format is:

#! /usr/bin/python

# Plot temperature data stored in SQLite database
#
# Specifically designed for Raspberry Pi running a headless version of Raspbian called Minibian.
# Output graph is intended to be be used in a web-page.

import sqlite3
import time

import matplotlib.dates as mdate

import matplotlib as mpl
mpl.use('Agg') # Force matplotlib to not use any Xwindows backend
import matplotlib.pyplot as plt

database_name = '/var/www/Temperature.sqlite3'
#database_name = './Temperature.sqlite3'

conn = sqlite3.connect(database_name)
cur = conn.cursor()

# Get table name from database
cur.execute("SELECT name FROM sqlite_master WHERE type='table'")
table_data = cur.fetchall()
table_name = table_data[0][0]

# Get information about the table
cur.execute('PRAGMA TABLE_INFO({})'.format(table_name))
table_info = cur.fetchall()
numberOfColumns = len(table_info) # Get number of columns

# Retrieve the data from the table
cur.execute("SELECT * FROM {tn}".format(tn=table_name))
table_data = cur.fetchall()
numberOfRows = len(table_data) # Get number of rows

##    PLOT
# Prepare plot
fig = plt.figure()
ax = fig.add_subplot(111)

# Set x axis
x_raw=zip(*table_data)[0]
x=mdate.epoch2num(x_raw)

for datasets in range(1, numberOfColumns):
    ax.plot(x, zip(*table_data)[datasets], label=table_info[datasets][1])

# Set x limits
plt.xlim(min(x), max(x))
plt.legend(loc=3)#bbox_to_anchor=(1.05, 1), loc=2, borderaxespad=0.)

# Show the grid
ax.grid(True)

# Choose your xtick format string
date_fmt = '%d-%m-%y'

# Use a DateFormatter to set the data to the correct format.
date_formatter = mdate.DateFormatter(date_fmt)
ax.xaxis.set_major_formatter(date_formatter)

# Sets the tick labels diagonal so they fit easier.
fig.autofmt_xdate()

fig.savefig('/var/www/html/temp1.svg', format='svg')
#fig.savefig('./temp3.svg', format='svg')

conn.close()

A sample of the output graph is (SVG file - sorry!):

<?xml version="1.0" encoding="utf-8" standalone="no"?>
<!DOCTYPE svg PUBLIC "-//W3C//DTD SVG 1.1//EN"
"http://www.w3.org/Graphics/SVG/1.1/DTD/svg11.dtd">
<!-- Created with matplotlib (http://matplotlib.org/) -->
<svg height="432pt" version="1.1" viewBox="0 0 576 432" width="576pt" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink">
<defs>
<style type="text/css">
*{stroke-linecap:butt;stroke-linejoin:round;}
</style>
</defs>
<g id="figure_1">
<g id="patch_1">
<path d="
M0 432
L576 432
L576 0
L0 0
z
" style="fill:#ffffff;"/>
</g>
<g id="axes_1">
<g id="patch_2">
<path d="
M72 345.6
L518.4 345.6
L518.4 43.2
L72 43.2
z
" style="fill:#ffffff;"/>
</g>
<g id="line2d_1">
<path clip-path="url(#pcb2bf987bf)" d="
M72 249.504
L72.0029 249.504
L72.1144 256.224
L72.1315 254.208
L72.1344 254.208
L72.1887 252.864
L72.2087 254.208
L72.2087 254.208
L72.2802 259.584
L72.3288 256.896
L72.3374 256.896
L72.3431 256.224
L72.3802 256.896
L72.3802 256.896
L72.486 258.912
L72.4889 257.568
L72.6404 236.736
L72.6433 237.408
L72.6461 236.736
L72.6461 236.736
L72.7005 227.328
L72.7719 228.672
L72.9435 243.456
L72.9521 243.456
L72.9549 242.784
L72.9578 243.456
L72.9578 243.456
L73.1122 254.208
L73.135 252.192
L73.1693 254.208
L73.1693 254.208
L73.278 258.24
L73.2894 257.568
L73.3037 256.896
L73.3094 257.568
L73.3094 257.568
L73.3294 259.584
L73.3494 257.568
L73.3494 257.568
L73.481 252.192
L73.4895 252.192
L73.4981 252.864
L73.5038 252.192
L73.5038 252.192
L73.5953 248.16
L73.6268 248.832
L73.6325 248.832
L73.7697 255.552
L73.7897 255.552
L73.9127 258.912
L73.9355 258.912
L73.9556 258.24
L73.9584 258.912
L73.9584 258.912
L74.0756 260.928
L74.0871 260.928
L74.1128 262.272
L74.1357 260.928
L74.1357 260.928
L74.2672 255.552
L74.2701 255.552
L74.4044 234.048
L74.4159 238.752
L74.4387 237.408
L74.4387 237.408
L74.4387 237.408
L74.5588 248.832
L74.6903 254.208
L74.696 254.208
L74.8276 259.584
L74.839 259.584
L74.9591 265.632
L74.9677 264.288
L74.9705 264.288
L75.1592 245.472
L75.1649 245.472
L75.2507 239.424
L75.2793 244.128
L75.3793 256.224
L75.4051 255.552
L75.4165 255.552
L75.4737 256.224
L75.4851 255.552
L75.4851 255.552
L75.5938 254.88
L75.5995 255.552
L75.6138 254.88
L75.6138 254.88
L75.6195 254.208
L75.6252 254.88
L75.6252 254.88
L75.7396 256.224
L75.751 256.224
L75.8311 258.24
L75.8482 256.224
L75.8482 256.224
L75.994 244.128
L76.0026 245.472
L76.0055 245.472
L76.0369 248.832
L76.0655 246.144
L76.0655 246.144
L76.0769 242.112
L76.0912 246.144
L76.0912 246.144
L76.1198 248.16
L76.1742 246.144
L76.1742 246.144
L76.1999 244.128
L76.2542 246.144
L76.2542 246.144
L76.3772 249.504
L76.3943 249.504
L76.4515 252.192
L76.4886 249.504
L76.4886 249.504
L76.5658 247.488
L76.5973 249.504
L76.603 249.504
L76.6116 250.176
L76.6173 249.504
L76.6173 249.504
L76.7317 247.488
L76.7345 248.16
L76.746 247.488
L76.746 247.488
L76.8689 235.392
L76.8775 236.736
L76.8803 237.408
L76.8803 237.408
L76.8803 237.408
L76.886 236.064
L76.8889 237.408
L76.8889 237.408
L77.0547 250.848
L77.0662 250.848
L77.1805 252.192
L77.1977 252.192
L77.312 254.208
L77.3149 253.536
L77.3378 254.208
L77.3378 254.208
L77.4607 257.568
L77.4722 257.568
L77.6037 249.504
L77.6065 250.176
L77.6094 251.52
L77.6151 250.848
L77.6151 250.848
L77.6866 248.16
L77.7295 248.832
L77.8352 252.864
L77.8524 252.192
L77.8581 252.192
L77.9896 257.568
L77.9982 257.568
L78.124 261.6
L78.1612 261.6
L78.2155 264.288
L78.2755 262.944
L78.407 257.568
L78.4099 258.24
L78.4128 257.568
L78.4128 257.568
L78.5328 252.192
L78.5671 252.192
L78.6529 256.224
L78.6901 255.552
L78.6987 255.552
L78.8044 258.912
L78.8159 257.568
L78.8216 257.568
L78.9503 262.272
L78.956 262.272
L79.0332 263.616
L79.0675 262.944
L79.0703 262.944
L79.1933 256.896
L79.2018 257.568
L79.2104 257.568
L79.2133 258.912
L79.2333 257.568
L79.2333 257.568
L79.3762 249.504
L79.4906 250.848
L79.5306 250.848
L79.5649 249.504
L79.5764 250.848
L79.5764 250.848
L79.6965 254.208
L79.7593 253.536
L79.7736 254.208
L79.7736 254.208
L79.8966 258.24
L79.8994 257.568
L79.8994 257.568
L79.8994 257.568
L79.908 259.584
L79.9595 257.568
L79.9595 257.568
L80.0967 244.8
L80.0996 244.128
L80.0996 244.128
L80.0996 244.128
L80.1024 245.472
L80.111 244.128
L80.111 244.128
L80.1539 241.44
L80.2053 244.128
L80.2053 244.128
L80.3483 252.192
L80.4484 252.192
L80.5656 254.208
L80.5999 254.208
L80.7143 255.552
L80.7171 255.552
L80.7514 256.224
L80.7571 255.552
L80.7571 255.552
L80.9573 232.032
L80.963 232.704
L80.9659 232.704
L81.003 230.688
L81.0259 232.704
L81.0259 232.704
L81.1974 247.488
L81.2032 247.488
L81.3289 255.552
L81.3375 255.552
L81.4633 259.584
L81.4719 258.912
L81.4919 259.584
L81.4919 259.584
L81.5319 262.272
L81.5605 259.584
L81.5605 259.584
L81.7778 231.36
L81.7835 232.704
L81.7864 233.376
L81.7864 233.376
L81.7864 233.376
L81.8064 229.344
L81.8493 232.704
L81.8493 232.704
L81.9922 248.16
L82.0008 248.16
L82.018 247.488
L82.0237 248.16
L82.0237 248.16
L82.1752 258.24
L82.1781 257.568
L82.1781 257.568
L82.1781 257.568
L82.2781 262.944
L82.2982 262.272
L82.3096 262.944
L82.3353 262.272
L82.3353 262.272
L82.5354 240.768
L82.5412 240.768
L82.5497 242.784
L82.5526 242.112
L82.5526 242.112
L82.6355 232.704
L82.6698 234.72
L82.8414 249.504

[micseydel]

I'm not sure what exactly you're looking for in an answer here. That said, if I were to do what you've described you want, I'd just have a JSON file essentially containing a dict which maps the IDs to the human readable alias. As another hint, I'd likely use dict's get method rather than regular indexing or checking ahead of time so that un-aliased IDs show in the right way.