1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
|
from time import time
from datetime import datetime
from typing import Tuple, List
from .clickhouse import get_clickhouse
from ..api.types import TemperatureSensorLocation
def get_temperature_table(sensor: TemperatureSensorLocation) -> str:
if sensor == TemperatureSensorLocation.DIANA:
return 'temp_diana'
elif sensor == TemperatureSensorLocation.STREET:
return 'temp_street'
elif sensor == TemperatureSensorLocation.BIG_HOUSE_1:
return 'temp'
elif sensor == TemperatureSensorLocation.BIG_HOUSE_2:
return 'temp_roof'
elif sensor == TemperatureSensorLocation.BIG_HOUSE_ROOM:
return 'temp_room'
elif sensor == TemperatureSensorLocation.SPB1:
return 'temp_spb1'
class SensorsDatabase:
def __init__(self):
self.db = get_clickhouse('home')
def add_temperature(self,
home_id: int,
client_time: int,
sensor: TemperatureSensorLocation,
temp: int,
rh: int):
table = get_temperature_table(sensor)
sql = """INSERT INTO """ + table + """ (
ClientTime,
ReceivedTime,
HomeID,
Temperature,
RelativeHumidity
) VALUES"""
self.db.execute(sql, [[
client_time,
int(time()),
home_id,
temp,
rh
]])
def get_temperature_recordings(self,
sensor: TemperatureSensorLocation,
time_range: Tuple[datetime, datetime],
home_id=1) -> List[tuple]:
table = get_temperature_table(sensor)
sql = f"""SELECT ClientTime, Temperature, RelativeHumidity
FROM {table}
WHERE ClientTime >= %(from)s AND ClientTime <= %(to)s
ORDER BY ClientTime"""
dt_from, dt_to = time_range
data = self.db.execute(sql, {
'from': dt_from,
'to': dt_to
})
return [(date, temp/100, humidity/100) for date, temp, humidity in data]
|
