IoT - Kubus / Mbed 2 deprecated Kubus

JNP3 16/17

Dependencies:   mbed nRF24L01P

server/databaseconnector.py@68:e88f3fd2e9cf, 2017-01-23 (annotated)

Committer:
pannaanna
Date:
Mon Jan 23 14:18:23 2017 +0100
Revision:
68:e88f3fd2e9cf
Parent:
65:7153b7640953 
port changed

Who changed what in which revision?

UserRevisionLine numberNew contents of line
Jan Kopa?ski 16:1569130916f1 1 from sqlite3 import connect, Error
Jan Kopa?ski 30:27c08888a838 2 from threading import Lock
Jan Kopa?ski 35:0f01042765d6 3 from datetime import datetime
Jan Kopa?ski 16:1569130916f1 4
Jan Kopa?ski 16:1569130916f1 5 json_template = {
Jan Kopa?ski 16:1569130916f1 6 'sensors': [
Jan Kopa?ski 16:1569130916f1 7 {
Jan Kopa?ski 16:1569130916f1 8 'name': 'Motion at the stairway',
Jan Kopa?ski 16:1569130916f1 9 'type': 'bool',
Jan Kopa?ski 36:6686483418a4 10 'value': None,
Jan Kopa?ski 36:6686483418a4 11 'timestamp': None
Jan Kopa?ski 16:1569130916f1 12 },
Jan Kopa?ski 16:1569130916f1 13 {
Jan Kopa?ski 16:1569130916f1 14 'name': 'Motion at the entrance',
Jan Kopa?ski 16:1569130916f1 15 'type': 'bool',
Jan Kopa?ski 36:6686483418a4 16 'value': None,
Jan Kopa?ski 36:6686483418a4 17 'timestamp': None
Jan Kopa?ski 16:1569130916f1 18 },
Jan Kopa?ski 16:1569130916f1 19 {
Jan Kopa?ski 16:1569130916f1 20 'name': 'Distance at the entrance',
Jan Kopa?ski 16:1569130916f1 21 'type': 'number',
Jan Kopa?ski 16:1569130916f1 22 'range': [0, 90],
Jan Kopa?ski 16:1569130916f1 23 'unit': 'cm',
Jan Kopa?ski 36:6686483418a4 24 'value': None,
Jan Kopa?ski 36:6686483418a4 25 'timestamp': None
Jan Kopa?ski 16:1569130916f1 26 },
Jan Kopa?ski 16:1569130916f1 27 {
Jan Kopa?ski 16:1569130916f1 28 'name': 'Volume inside',
Jan Kopa?ski 16:1569130916f1 29 'type': 'number',
Jan Kopa?ski 16:1569130916f1 30 'range': [-30, 30],
Jan Kopa?ski 16:1569130916f1 31 'unit': 'dB',
Jan Kopa?ski 36:6686483418a4 32 'value': None,
Jan Kopa?ski 36:6686483418a4 33 'timestamp': None
Jan Kopa?ski 16:1569130916f1 34 }
Jan Kopa?ski 16:1569130916f1 35 ]
Jan Kopa?ski 16:1569130916f1 36 }
Jan Kopa?ski 16:1569130916f1 37
Jan Kopa?ski 65:7153b7640953 38 funs = {
Jan Kopa?ski 65:7153b7640953 39 'pir1': lambda x: x,
Jan Kopa?ski 65:7153b7640953 40 'pir2': lambda x: x,
Jan Kopa?ski 65:7153b7640953 41 'distance': lambda x: 18 / (3.3 * x - 0.4),
Jan Kopa?ski 65:7153b7640953 42 'volume': lambda x: x,
Jan Kopa?ski 65:7153b7640953 43 }
Jan Kopa?ski 16:1569130916f1 44
Jan Kopa?ski 16:1569130916f1 45 class DatabaseConnector:
Jan Kopa?ski 16:1569130916f1 46 def __init__(self, database, tables, separator=':'):
Jan Kopa?ski 16:1569130916f1 47 self.tables = tables
Jan Kopa?ski 16:1569130916f1 48 self.separator = separator
Jan Kopa?ski 16:1569130916f1 49 self.conn = connect(database=database, check_same_thread=False)
Jan Kopa?ski 16:1569130916f1 50 self.curr = self.conn.cursor()
Jan Kopa?ski 30:27c08888a838 51 self.mutex = Lock()
Jan Kopa?ski 16:1569130916f1 52
Jan Kopa?ski 16:1569130916f1 53 def write(self, table, data):
Jan Kopa?ski 16:1569130916f1 54 if not data:
Jan Kopa?ski 16:1569130916f1 55 return 'Request message is empty'
Jan Kopa?ski 16:1569130916f1 56 vals = data.split(self.separator)
Jan Kopa?ski 35:0f01042765d6 57 vals += [datetime.now()]
Jan Kopa?ski 16:1569130916f1 58 stmt = ("INSERT INTO %s VALUES (NULL" + (", ?" * len(vals)) + ")") % table
Jan Kopa?ski 30:27c08888a838 59 self.mutex.acquire()
Jan Kopa?ski 16:1569130916f1 60 try:
Jan Kopa?ski 16:1569130916f1 61 self.curr.execute(stmt, vals)
Jan Kopa?ski 16:1569130916f1 62 except Error as e:
Jan Kopa?ski 16:1569130916f1 63 return e.message
Jan Kopa?ski 16:1569130916f1 64 except:
Jan Kopa?ski 16:1569130916f1 65 return 'Undefined exception'
Jan Kopa?ski 16:1569130916f1 66 self.conn.commit()
Jan Kopa?ski 30:27c08888a838 67 self.mutex.release()
Jan Kopa?ski 16:1569130916f1 68 return None
Jan Kopa?ski 16:1569130916f1 69
Jan Kopa?ski 16:1569130916f1 70 def read(self):
Jan Kopa?ski 36:6686483418a4 71 stmt = 'SELECT value, timestamp FROM %s ORDER BY id DESC LIMIT 1'
Jan Kopa?ski 16:1569130916f1 72 json_dict = json_template
Jan Kopa?ski 30:27c08888a838 73 self.mutex.acquire()
Jan Kopa?ski 16:1569130916f1 74 for i, t in enumerate(self.tables):
Jan Kopa?ski 16:1569130916f1 75 try:
Jan Kopa?ski 16:1569130916f1 76 self.curr.execute(stmt % t)
Jan Kopa?ski 30:27c08888a838 77 entry = self.curr.fetchone()
Jan Kopa?ski 30:27c08888a838 78 if entry:
Jan Kopa?ski 65:7153b7640953 79 json_dict['sensors'][i]['value'] = funs[t](entry[0])
Jan Kopa?ski 36:6686483418a4 80 json_dict['sensors'][i]['timestamp'] = entry[1]
Jan Kopa?ski 16:1569130916f1 81 except Error:
Jan Kopa?ski 16:1569130916f1 82 print 'Database error on read: ' + t
Jan Kopa?ski 30:27c08888a838 83 self.mutex.release()
Jan Kopa?ski 30:27c08888a838 84 return json_dict