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
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
|
import requests
import time
import pymysql
import pymysql.cursors
from json.decoder import JSONDecodeError
from retrying import retry
# Connect to the database
connection = pymysql.connect(
host='',
user='',
password='',
db='',
charset='utf8')
def aqi_from_pm(pm):
if pm > 350.5:
return calculate_aqi(pm, 500, 401, 500, 350.5)
elif pm > 250.5:
return calculate_aqi(pm, 400, 301, 350.4, 250.5)
elif pm > 150.5:
return calculate_aqi(pm, 300, 201, 250.4, 150.5)
elif pm > 55.5:
return calculate_aqi(pm, 200, 151, 150.4, 55.5)
elif pm > 35.5:
return calculate_aqi(pm, 150, 101, 55.4, 35.5)
elif pm > 12.1:
return calculate_aqi(pm, 100, 51, 35.4, 12.1)
elif pm >= 0:
return calculate_aqi(pm, 50, 0, 12, 0)
else:
return False
def calculate_aqi(Cp, Ih, Il, BPh, BPl):
a = Ih - Il
b = BPh - BPl
c = Cp - BPl
return round(((a/b) * c + Il))
@retry(wait_fixed=30000, stop_max_attempt_number=3)
def fetch_results():
purpleair = requests.get("https://www.purpleair.com/json")
return purpleair.json()['results']
purpleair_results = fetch_results()
sql = "INSERT INTO `sensor_data` (`purpleair_id`, `time`, `name`, `latitude`, `longitude`, `pm2_5`, `aqi`) VALUES (%s, %s, %s, %s, %s, %s, %s)"
data = []
for measurement in purpleair_results:
is_outside = 'DEVICE_LOCATIONTYPE' in measurement and measurement['DEVICE_LOCATIONTYPE'] == 'outside'
is_recent = 'AGE' in measurement and measurement['AGE'] <= 300
is_valid = 'ID' in measurement and 'Label' in measurement and 'Lat' in measurement and 'Lon' in measurement and 'PM2_5Value' in measurement
if is_outside and is_recent and is_valid:
# Include California and some of the surrounding area
is_longitude = -125.22216797 < measurement['Lon'] < -113.26904297
is_latitude = 32.17561248 < measurement['Lat'] < 42.16340342
if is_longitude and is_latitude:
raw_pm = measurement['PM2_5Value']
pm2_5 = float(raw_pm) if '.' in raw_pm else False
if pm2_5:
data.append([
measurement['ID'],
measurement['LastSeen'],
measurement['Label'].strip(),
measurement['Lat'],
measurement['Lon'],
pm2_5,
aqi_from_pm(pm2_5)
])
cursor = connection.cursor()
for sensor in data:
print(sensor)
cursor.execute(sql, sensor)
connection.commit()
connection.close()
|