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)