IoT - Kubus / Mbed 2 deprecated Kubus

JNP3 16/17

Dependencies:   mbed nRF24L01P

server/databaseconnector.py@36:6686483418a4, 2017-01-17 (annotated)

Committer:
Jan Kopa?ski
Date:
Tue Jan 17 12:00:38 2017 +0100
Revision:
36:6686483418a4
Parent:
35:0f01042765d6
Child:
65:7153b7640953
server json update

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 16:1569130916f1 38
Jan Kopa?ski 16:1569130916f1 39 class DatabaseConnector:
Jan Kopa?ski 16:1569130916f1 40 def __init__(self, database, tables, separator=':'):
Jan Kopa?ski 16:1569130916f1 41 self.tables = tables
Jan Kopa?ski 16:1569130916f1 42 self.separator = separator
Jan Kopa?ski 16:1569130916f1 43 self.conn = connect(database=database, check_same_thread=False)
Jan Kopa?ski 16:1569130916f1 44 self.curr = self.conn.cursor()
Jan Kopa?ski 30:27c08888a838 45 self.mutex = Lock()
Jan Kopa?ski 16:1569130916f1 46
Jan Kopa?ski 16:1569130916f1 47 def write(self, table, data):
Jan Kopa?ski 16:1569130916f1 48 if not data:
Jan Kopa?ski 16:1569130916f1 49 return 'Request message is empty'
Jan Kopa?ski 16:1569130916f1 50 vals = data.split(self.separator)
Jan Kopa?ski 35:0f01042765d6 51 vals += [datetime.now()]
Jan Kopa?ski 16:1569130916f1 52 stmt = ("INSERT INTO %s VALUES (NULL" + (", ?" * len(vals)) + ")") % table
Jan Kopa?ski 30:27c08888a838 53 self.mutex.acquire()
Jan Kopa?ski 16:1569130916f1 54 try:
Jan Kopa?ski 16:1569130916f1 55 self.curr.execute(stmt, vals)
Jan Kopa?ski 16:1569130916f1 56 except Error as e:
Jan Kopa?ski 16:1569130916f1 57 return e.message
Jan Kopa?ski 16:1569130916f1 58 except:
Jan Kopa?ski 16:1569130916f1 59 return 'Undefined exception'
Jan Kopa?ski 16:1569130916f1 60 self.conn.commit()
Jan Kopa?ski 30:27c08888a838 61 self.mutex.release()
Jan Kopa?ski 16:1569130916f1 62 return None
Jan Kopa?ski 16:1569130916f1 63
Jan Kopa?ski 16:1569130916f1 64 def read(self):
Jan Kopa?ski 36:6686483418a4 65 stmt = 'SELECT value, timestamp FROM %s ORDER BY id DESC LIMIT 1'
Jan Kopa?ski 16:1569130916f1 66 json_dict = json_template
Jan Kopa?ski 30:27c08888a838 67 self.mutex.acquire()
Jan Kopa?ski 16:1569130916f1 68 for i, t in enumerate(self.tables):
Jan Kopa?ski 16:1569130916f1 69 try:
Jan Kopa?ski 16:1569130916f1 70 self.curr.execute(stmt % t)
Jan Kopa?ski 30:27c08888a838 71 entry = self.curr.fetchone()
Jan Kopa?ski 30:27c08888a838 72 if entry:
Jan Kopa?ski 30:27c08888a838 73 json_dict['sensors'][i]['value'] = entry[0]
Jan Kopa?ski 36:6686483418a4 74 json_dict['sensors'][i]['timestamp'] = entry[1]
Jan Kopa?ski 16:1569130916f1 75 except Error:
Jan Kopa?ski 16:1569130916f1 76 print 'Database error on read: ' + t
Jan Kopa?ski 30:27c08888a838 77 self.mutex.release()
Jan Kopa?ski 30:27c08888a838 78 return json_dict