Page MenuHomec4science
Files F62159432

dboperations.py
No OneTemporary
Actions

File Metadata

Created
Sat, May 11, 07:37

dboperations.py
View Options

# © All rights reserved. ECOLE POLYTECHNIQUE FEDERALE DE LAUSANNE,
# Switzerland
# SCITAS - Scientific IT and Application Support, 2021
# See the LICENSE.txt file for more details.
import sqlite3
from datetime import datetime
class DBOp:
def __init__(self, db=sqlite3.connect(
"file:/dev/shm/sausagedb?cache=shared", uri=True)):
self.database = db
self.cursor = self.database.cursor()
self.cursor.execute('''CREATE TABLE IF NOT EXISTS account (key text PRIMARY KEY, name text NOT NULL, time float, chf float,\
co2 float, queue float)''')
self.cursor.execute(
'''CREATE UNIQUE INDEX IF NOT EXISTS idx_key ON account (key)''')
self.cursor.execute('''CREATE TABLE IF NOT EXISTS user (key text PRIMARY KEY, name text NOT NULL, account text NOT NULL,\
time float, chf float, co2 float, queue float)''')
self.cursor.execute(
'''CREATE UNIQUE INDEX IF NOT EXISTS idx_key ON user (key)''')
self.database.commit()
def valid_date(self, date):
try:
if datetime.strptime(date, "%Y-%m-%d"):
return True
except ValueError:
return False
def populate(self, esdata, entity, currency):
for record in esdata['aggregations']['cluster']['buckets']:
cluster = record['key']
for acct in record['account']['buckets']:
unit = acct['key']
self.cursor.execute(
"INSERT OR IGNORE INTO account (key, name) VALUES(?,?)",
(cluster + '-' + unit,
unit,
))
if entity == "account":
consumption = acct['cost']['value']
if currency == "chf":
self.cursor.execute(
"UPDATE account SET chf = ? WHERE key = ?", (consumption, cluster + '-' + unit,))
elif currency == "co2":
self.cursor.execute(
"UPDATE account SET co2 = ? WHERE key = ?", (consumption, cluster + '-' + unit,))
elif currency == "time":
self.cursor.execute(
"UPDATE account SET time = ? WHERE key = ?", (consumption, cluster + '-' + unit,))
else:
return False
self.database.commit()
elif entity == "user":
for usr in acct['user']['buckets']:
person = usr["key"]
consumption = usr['cost']['value']
key = cluster + '-' + unit + '-' + person
self.cursor.execute(
"INSERT OR IGNORE INTO user (key, name, account) VALUES(?,?,?)",
(key,
person,
unit,
))
if currency == "chf":
self.cursor.execute(
"UPDATE user SET chf = ? WHERE key = ?", (consumption, key,))
elif currency == "co2":
self.cursor.execute(
"UPDATE user SET co2 = ? WHERE key = ?", (consumption, key,))
elif currency == "time":
self.cursor.execute(
"UPDATE user SET time = ? WHERE key = ?", (consumption, key,))
else:
return False
self.database.commit()
else:
return False
def get_account(self, account):
self.cursor.execute("SELECT * FROM account WHERE name=?", (account,))
return self.cursor.fetchall()
def get_user(self, username, account):
if account == "null":
self.cursor.execute("SELECT * FROM user WHERE name=?", (username,))
else:
self.cursor.execute(
"SELECT * FROM user WHERE name=? AND account=?", (username, account,))
return self.cursor.fetchall()
def show_table(self, table):
query = "SELECT * FROM " + table + " ORDER BY name ASC"
return self.cursor.execute(query).fetchall()
def destroy(self):
self.cursor.close()
self.database.close()

Event Timeline

c4science · Help