IoT - Kubus / Mbed 2 deprecated Kubus

JNP3 16/17

Dependencies:   mbed nRF24L01P

server/databaseconnector.py@35:0f01042765d6, 2017-01-17 (annotated)

Committer:
Jan Kopa?ski
Date:
Tue Jan 17 11:50:16 2017 +0100
Revision:
35:0f01042765d6
Parent:
30:27c08888a838
Child:
36:6686483418a4
server timestamp

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 16:1569130916f1 10 'value': None
Jan Kopa?ski 16:1569130916f1 11 },
Jan Kopa?ski 16:1569130916f1 12 {
Jan Kopa?ski 16:1569130916f1 13 'name': 'Motion at the entrance',
Jan Kopa?ski 16:1569130916f1 14 'type': 'bool',
Jan Kopa?ski 16:1569130916f1 15 'value': None
Jan Kopa?ski 16:1569130916f1 16 },
Jan Kopa?ski 16:1569130916f1 17 {
Jan Kopa?ski 16:1569130916f1 18 'name': 'Distance at the entrance',
Jan Kopa?ski 16:1569130916f1 19 'type': 'number',
Jan Kopa?ski 16:1569130916f1 20 'range': [0, 90],
Jan Kopa?ski 16:1569130916f1 21 'unit': 'cm',
Jan Kopa?ski 16:1569130916f1 22 'value': None
Jan Kopa?ski 16:1569130916f1 23 },
Jan Kopa?ski 16:1569130916f1 24 {
Jan Kopa?ski 16:1569130916f1 25 'name': 'Volume inside',
Jan Kopa?ski 16:1569130916f1 26 'type': 'number',
Jan Kopa?ski 16:1569130916f1 27 'range': [-30, 30],
Jan Kopa?ski 16:1569130916f1 28 'unit': 'dB',
Jan Kopa?ski 16:1569130916f1 29 'value': None
Jan Kopa?ski 16:1569130916f1 30 }
Jan Kopa?ski 16:1569130916f1 31 ]
Jan Kopa?ski 16:1569130916f1 32 }
Jan Kopa?ski 16:1569130916f1 33
Jan Kopa?ski 16:1569130916f1 34
Jan Kopa?ski 16:1569130916f1 35 class DatabaseConnector:
Jan Kopa?ski 16:1569130916f1 36 def __init__(self, database, tables, separator=':'):
Jan Kopa?ski 16:1569130916f1 37 self.tables = tables
Jan Kopa?ski 16:1569130916f1 38 self.separator = separator
Jan Kopa?ski 16:1569130916f1 39 self.conn = connect(database=database, check_same_thread=False)
Jan Kopa?ski 16:1569130916f1 40 self.curr = self.conn.cursor()
Jan Kopa?ski 30:27c08888a838 41 self.mutex = Lock()
Jan Kopa?ski 16:1569130916f1 42
Jan Kopa?ski 16:1569130916f1 43 def write(self, table, data):
Jan Kopa?ski 16:1569130916f1 44 if not data:
Jan Kopa?ski 16:1569130916f1 45 return 'Request message is empty'
Jan Kopa?ski 16:1569130916f1 46 vals = data.split(self.separator)
Jan Kopa?ski 35:0f01042765d6 47 vals += [datetime.now()]
Jan Kopa?ski 16:1569130916f1 48 stmt = ("INSERT INTO %s VALUES (NULL" + (", ?" * len(vals)) + ")") % table
Jan Kopa?ski 30:27c08888a838 49 self.mutex.acquire()
Jan Kopa?ski 16:1569130916f1 50 try:
Jan Kopa?ski 16:1569130916f1 51 self.curr.execute(stmt, vals)
Jan Kopa?ski 16:1569130916f1 52 except Error as e:
Jan Kopa?ski 16:1569130916f1 53 return e.message
Jan Kopa?ski 16:1569130916f1 54 except:
Jan Kopa?ski 16:1569130916f1 55 return 'Undefined exception'
Jan Kopa?ski 16:1569130916f1 56 self.conn.commit()
Jan Kopa?ski 30:27c08888a838 57 self.mutex.release()
Jan Kopa?ski 16:1569130916f1 58 return None
Jan Kopa?ski 16:1569130916f1 59
Jan Kopa?ski 16:1569130916f1 60 def read(self):
Jan Kopa?ski 16:1569130916f1 61 stmt = 'SELECT value FROM %s ORDER BY id DESC LIMIT 1'
Jan Kopa?ski 16:1569130916f1 62 json_dict = json_template
Jan Kopa?ski 30:27c08888a838 63 self.mutex.acquire()
Jan Kopa?ski 16:1569130916f1 64 for i, t in enumerate(self.tables):
Jan Kopa?ski 16:1569130916f1 65 try:
Jan Kopa?ski 16:1569130916f1 66 self.curr.execute(stmt % t)
Jan Kopa?ski 30:27c08888a838 67 entry = self.curr.fetchone()
Jan Kopa?ski 30:27c08888a838 68 if entry:
Jan Kopa?ski 30:27c08888a838 69 json_dict['sensors'][i]['value'] = entry[0]
Jan Kopa?ski 16:1569130916f1 70 except Error:
Jan Kopa?ski 16:1569130916f1 71 print 'Database error on read: ' + t
Jan Kopa?ski 30:27c08888a838 72 self.mutex.release()
Jan Kopa?ski 30:27c08888a838 73 return json_dict