def sample_weather_data(location):
loc_id, _, latitude, longitude = location
data = fetch_weather_info_data(latitude, longitude)
temp = data.get('main', {}).get('temp')
wind_speed = data.get('wind', {}).get('speed')
humidity = data.get('main', {}).get('humidity')
values = (loc_id, temp, wind_speed, humidity, datetime.now())
query = (
'INSERT INTO history (location_id, temperature, wind_speed, humidity, sample_time) '
'VALUES (?, ?, ?, ?, ?);')
execute_query(query, values)
def test_storage_execute_scalar(self):
query = '''
INSERT INTO locations (id, name, latitude, longitude) VALUES
(1, "NAME_1", "LAT_1", "LONG_1"),
(2, "NAME_2", "LAT_2", "LONG_2"),
(3, "NAME_3", "LAT_3", "LONG_3");
'''
storage.execute_query(query)
count = storage.execute_scalar('SELECT COUNT(*) FROM locations;')
self.assertEqual(count, 3)
sys.stdout.write('\nTest "storage_execute_scalar" passed')
def test_storage_execute_query(self):
query = '''
INSERT INTO locations (id, name, latitude, longitude) VALUES
(1, "NAME_1", "LAT_1", "LONG_1"),
(2, "NAME_2", "LAT_2", "LONG_2"),
(3, "NAME_3", "LAT_3", "LONG_3");
'''
storage.execute_query(query)
rows = storage.execute_query(
'SELECT id, name, latitude, longitude FROM locations ORDER BY id;')
self.assertEqual(rows, [(1, 'NAME_1', 'LAT_1', 'LONG_1'),
(2, 'NAME_2', 'LAT_2', 'LONG_2'),
(3, 'NAME_3', 'LAT_3', 'LONG_3')])
sys.stdout.write('\nTest "storage_execute_query" passed')
def get_current_location():
current_location_id = storage.get_text_value('CURRENT_LOCATION_ID')
if current_location_id:
query = 'SELECT id, name, latitude, longitude FROM locations WHERE id=%s' % current_location_id
result = execute_query(query)
else:
result = None
location = result[0] if result else None
return location
def save_locations(locations, deleted_locations):
persisted = {loc[0]: loc for loc in get_locations()}
updated = []
inserted = []
if deleted_locations:
deleted_ids = ', '.join(l[0] for l in deleted_locations)
query = 'DELETE FROM locations WHERE id in (%s)' % deleted_ids
storage.execute_query(query)
for loc in locations:
loc = list(loc)
loc_id = loc[0]
if loc_id == '-1':
inserted.append(loc)
elif loc != persisted[loc_id]:
updated.append(loc)
if inserted:
values = ', '.join('(%s)' % ', '.join('"%s"' % f for f in loc[1:])
for loc in inserted)
query = 'INSERT INTO locations (name, latitude, longitude) VALUES %s' % values
storage.execute_query(query)
if updated:
for loc in updated:
loc_id, name, latitude, longitude = loc
query = 'UPDATE locations SET name="%s", latitude="%s", longitude="%s" WHERE ID=%s'
query = query % (name, latitude, longitude, loc_id)
storage.execute_query(query)
if __name__ == '__main__':
print("Inserting test historical weather data\n")
data = [(6.0, 5.21, 61),
(4.1, 4.06, 62),
(3.0, 3.02, 62),
(7.1, 2.41, 61),
(7.5, 2.91, 63),
(7.7, 4.40, 64),
(8.2, 4.96, 65),
(8.9, 5.32, 67),
(9.6, 6.06, 67),
(9.7, 6.74, 70)]
execute_query('DELETE FROM history;')
for loc_id, name, _, _ in get_locations():
print('Generating historical data for %s' % name)
temp_offset = 20*random.random()
wind_speed_offset = 10*random.random()
humidity_offset = 10*random.random()
for d, sample_time in zip(data, sampling_time_sequence()):
temp = '%.2f' % (d[0] + temp_offset)
wind_speed = '%.2f' % (d[1] + wind_speed_offset)
humidity = '%.2f' % (d[2] + humidity_offset)
values = (loc_id, temp, wind_speed, humidity, sample_time)
query = ('INSERT INTO history (location_id, temperature, wind_speed, humidity, sample_time) '
'VALUES (?, ?, ?, ?, ?);')
# print(values)
execute_query(query, values)
def get_locations():
query = 'SELECT id, name, latitude, longitude FROM locations;'
locations = ([str(f) for f in r] for r in storage.execute_query(query))
return locations
def get_historical_temp_data(location_id):
query = 'SELECT temperature, sample_time FROM history WHERE location_id = %s ORDER BY sample_time DESC;'
return execute_query(query % location_id)