def _store_city(resp): """Check cities exists in the DB, and if not stores them in the places table.""" city = resp['city'] row = database.query_row('SELECT COUNT(*) FROM Place WHERE Id = ?', (city['id'],)) if (row[0] == 0): database.execute('INSERT INTO Place (Id, Name, CoordLon, CoordLat, Country) VALUES (?,?,?,?,?)', (city['id'], city['name'], city['coord']['lon'], city['coord']['lat'], city['country'])) database.commit() app.logger.info('Place %s added', city['name']) return city['id']
def get_transaction(transaction_id): transaction = query_row( """SELECT ts.name AS source, at.amount AS amount, tt.name AS type, at.timestampe AS timestamp, u.username AS trnasaction_user FROM account_transactions AS at JOIN transaction_types AS tt ON tt.transaction_type_id = at.transaction_type_id JOIN transaction_source as ts ON ts.source_id = at.source_id LEFT JOIN account_transaction_user as atu ON at.transaction_id = atu.transaction_id LEFT JOIN users as u ON u.user_id = atu.user_id WHERE at.transaction_id = ?""", (transaction_id, )) return transaction
def get_account_info(account_id): account_info = query_row( """SELECT a.account_number AS account_number, an.name AS name, SUM(CASE tt.positive WHEN 1 THEN at.amount ELSE 0 END) - SUM(CASE tt.positive WHEN 1 THEN 0 ELSE at.amount END) AS balance FROM accounts AS a LEFT JOIN account_name AS an ON an.account_id = a.account_id LEFT JOIN account_transactions AS at ON at.account_id = a.account_id JOIN transaction_types AS tt ON tt.transaction_type_id = at.transaction_type_id WHERE a.account_id = ? """, (account_id, )) return account_info
def _store_measurements(place_id, resp): """Stores the measurement values in the database.""" i = 0 j = 0 for item in resp['list']: m = {} dt = datetime.datetime.utcfromtimestamp(int(item['dt'])) if 'main' in item: if 'temp' in item['main']: m[MT_TEMP] = item['main']['temp'] if 'temp_min' in item['main']: m[MT_TEMP_MIN] = item['main']['temp_min'] if 'temp_max' in item['main']: m[MT_TEMP_MAX] = item['main']['temp_max'] if 'pressure' in item['main']: m[MT_PRESS] = item['main']['pressure'] if 'sea_level' in item['main']: m[MT_PRESS_SEALEV] = item['main']['sea_level'] if 'grnd_level' in item['main']: m[MT_PRESS_GRNDLEV] = item['main']['grnd_level'] if 'humidity' in item['main']: m[MT_HUMID] = item['main']['humidity'] if 'wind' in item: if 'speed' in item['wind']: m[MT_WIND_SPEED] = item['wind']['speed'] if 'deg' in item['wind']: m[MT_WIND_DEG] = item['wind']['deg'] if 'rain' in item: if '3h' in item['rain']: m[MT_RAIN_3H] = item['rain']['3h'] for key in m: row = database.query_row('SELECT COUNT(*) FROM Measurement WHERE Time = ? AND Place = ? AND Type = ?', (dt, place_id, key)) if row[0] == 0: (rowcount, lastid) = database.execute('INSERT INTO Measurement (Time, Place, Type, Value) VALUES (?,?,?,?)', (dt, place_id, key, m[key])) i = i + 1 else: j = j + 1 database.commit() app.logger.info('%s measurements inserted for %s' % (i, place_id)) if j > 0: app.logger.info('%s measurements skipped for %s' % (j, place_id))