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))
