def get_experiment_target(experiment_id: int) -> pd.DataFrame:
database = Database()
query = f"SELECT project_id FROM experiment WHERE id = {experiment_id}"
project_id = database.read(query=query)[0]['project_id']
registry = Registry()
return registry.get_dataset(path=f"{project_id}-{experiment_id}",
key='target')
def get_experiment_model(experiment_id: int) -> object:
database = Database()
query = f"SELECT project_id FROM experiment WHERE id = {experiment_id}"
project_id = database.read(query=query)[0]['project_id']
registry = Registry()
return registry.get_model(path=f"{project_id}-{experiment_id}",
key='model')
def __init__(self, data):
self.link = Database("Intelligence")
self.search_type = list(data.keys())[0]
self.search_value = data[self.search_type]
self.result = {}
self.result["data"] = []
super().__init__()
def test_rollback(self):
"""Ensure that the database rollback method removes changes to the data
"""
# insert test data and rollback
self.connection.query('INSERT INTO test_data (variable) VALUES (1)')
self.connection.rollback()
self.connection.close()
# reopen connection and query to ensure data was removed
self.connection = Database(database=TEST_DATABASE)
result = self.connection.query('SELECT * FROM test_data')
self.assertEqual(result, [])
def test_commit(self):
"""Ensure that the database commit method retains
the data after reconnection.
"""
# insert test data and commit
self.connection.query('INSERT INTO test_data (variable) VALUES (1)')
self.connection.commit()
self.connection.close()
# reopen connection and query to ensure data was retained
self.connection = Database(database=TEST_DATABASE)
result = self.connection.query('SELECT * FROM test_data')
self.assertEqual(result, [(1, )])
def decorated(*args, **kwargs):
"""creates the decorator"""
token = None
if 'Authorization' in request.headers:
token = request.headers['Authorization']
if not token:
return jsonify({'message': 'Token is missing!'}), 401
try:
data = jwt.decode(token, 'mysecret')
database = Database()
query = database.get_by_argument('users', 'email', data['email'])
current_user = User(query[0], query[1], query[2], query[3])
except:
return jsonify({'message': 'Token is invalid!'}), 401
return f(current_user, *args, **kwargs)
def get_availability(self):
with Database() as db:
sql = 'SELECT "day", start, "type" FROM availability WHERE user_id = %s'
result = db.query(sql, self.id)
results = []
for r in result:
results.append([r[0], convert_time(r[1]), r[2]])
return results
def by_email(cls, email):
with Database() as db:
if db.exists("user", email=email):
sql = 'SELECT id FROM "user" WHERE email = %s'
id = db.query(sql, email)[0][0]
else:
raise KeyError('User {} not found'.format(email))
return User(id)
def create(cls, course, start, end, day, location):
with Database() as db:
id = uuid.uuid4().int
id = int(str(id)[:10])
sql = 'INSERT INTO "session" (id, course, start, "end", "day", location) VALUES (%s, %s, %s, %s, %s, %s)'
db.query(sql, id, course, start, end, day, location)
return Session(id, course)
def create(cls, name):
with Database() as db:
id = uuid.uuid4().int
id = int(str(id)[:10])
sql = 'INSERT INTO "course" (id, "name") VALUES (%s, %s)'
db.query(sql, id, name)
return Course(id)
def __init__(self, id):
self.id = id
self.name = "CSSE1001"
with Database() as db:
if db.exists("course", id=self.id):
sql = 'SELECT "name" FROM "course" WHERE id = %s'
self.name = db.query(sql, self.id, limit=1)[0][0]
else:
raise KeyError('Course {} not found'.format(self.id))
def login(cls, email, password):
with Database() as db:
password = hashlib.sha224(bytes(password, "utf-8")).hexdigest()
sql = 'SELECT "id", password FROM "user" WHERE email = %s'
user_id, db_password = db.query(sql, email)[0]
if password == db_password:
return User(user_id)
return None
def test_with_commit(self):
"""Ensure that an error free with statement commits when exiting
Performs an error free with block and ensures the data remains
"""
with Database(database=TEST_DATABASE) as db:
db.query('INSERT INTO test_data (variable) VALUES (1)')
result = self.connection.query('SELECT * FROM test_data')
self.assertEqual(result, [(1, )])
class Display_Intelligence():
def __init__(self):
self.link = Database("Intelligence")
super().__init__()
def display_ioc(self, type):
result = {}
result["data"] = []
select_result = self.link.select(type,
query={},
sort="disclosure_time")
if select_result:
for alert in select_result:
alert.pop("_id")
result["data"].append(alert)
self.link.close()
result["data"] = list_dict_duplicate_removal(result["data"])
return result
def ioc_count(self):
result = {}
count = 0
for ioc_type in ioc:
result[ioc_type] = self.link.count(ioc_type)
count += count + result[ioc_type]
result["count"] = count
self.link.close()
# result["data"] = list_dict_duplicate_removal(result["data"])
return result
def test_with_rollback(self):
"""Ensure that an error occurring in a database with context forces the database to rollback
Raises an error in the with context and ensures that modifications were removed
"""
try:
with Database(database=TEST_DATABASE) as db:
db.query('INSERT INTO test_data (variable) VALUES (1)')
raise Exception
except:
pass
result = self.connection.query('SELECT * FROM test_data')
self.assertEqual(result, [])
def __init__(self, id, course):
self.id = id
self.course = course
self.start = datetime.now()
self.end = datetime.now()
self.day = datetime.now()
self.location = "SAD"
with Database() as db:
if db.exists("session", id=self.id):
sql = 'SELECT start, "end", "day", location FROM "session" WHERE id = %s AND course_id = %s'
self.start, self.end, self.day, self.location = db.query(
sql, self.id, self.course, limit=1)[0]
else:
raise KeyError('Session Time {} not found'.format(self.id))
def create(cls, email, password, permissions):
with Database() as db:
if db.exists("user", email=email):
return None
password = hashlib.sha224(bytes(password, "utf-8")).hexdigest()
id = uuid.uuid4().int
id = int(str(id)[:10])
sql = 'INSERT INTO "user" (id, email, permission, password) VALUES (%s, %s, %s, %s)'
db.query(sql, id, email, permissions, password)
return User(id)
def __init__(self, id):
self.id = id
self.email = "*****@*****.**"
self.permissions = 0
with Database() as db:
if db.exists("user", id=self.id):
sql = 'SELECT "email", permission FROM "user" WHERE id = %s'
self.email, self.permissions = db.query(sql, self.id,
limit=1)[0]
else:
raise KeyError('User {} not found'.format(self.id))
self.is_authenticated = True
self.is_active = True
self.is_anonymous = False
def run_experiment(project_id: int, engine: str, model: object,
target_col: str, train_data: pd.DataFrame,
test_data: pd.DataFrame, metrics: dict) -> int:
_ENGINES = ['sklearn']
if engine not in _ENGINES:
raise Exception(
f'Engine not registered, must be one of the following: {",".join(_ENGINES)}'
)
database = Database()
query = f"INSERT INTO experiment (project_id, engine) VALUES ('{project_id}', '{engine}')"
experiment_id = database.write(query=query)
registry = Registry()
registry.put_model(path=f"{project_id}-{experiment_id}",
key='pre-model',
model=model)
registry.put_metrics(path=f"{project_id}-{experiment_id}",
key='metrics',
metrics=metrics)
registry.put_dataset(path=f"{project_id}-{experiment_id}",
key='test',
dataset=test_data)
registry.put_dataset(path=f"{project_id}-{experiment_id}",
key='train',
dataset=train_data)
pipeline = Pipeline()
conf = {
'experiment_id': experiment_id,
'project_id': project_id,
'target_col': target_col
}
triggered = pipeline.trigger_dag(dag_id=f'{engine}-pipeline',
data=dict(conf=conf))
if triggered:
query = f"UPDATE experiment SET status = 'submitted' WHERE id = {experiment_id}"
database.write(query=query)
else:
query = f"UPDATE experiment SET status = 'submission-failed' WHERE id = {experiment_id}"
database.write(query=query)
raise Exception('Pipeline failed to run experiment')
return experiment_id
import pickle
import time
UDP_IP = socket.gethostname() # returns the IP of this device
UDP_PORT = 5015 # port number
print(UDP_IP)
# initialize UDP socket
sock = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
sock.setsockopt(socket.SOL_SOCKET, socket.SO_REUSEADDR, 2)
# bind socket to address
sock.bind((socket.gethostname(), UDP_PORT))
print("waiting for incoming messages...")
print("press CTRL+C to exit")
db = Database()
con, cursor = db.connect()
START_TIME = time.time()
while True:
data, addr = sock.recvfrom(136) # receive data with certain buffer size
data = pickle.loads(data)
print(f"received following data: {data} from {addr}\n"
) # decode incoming message
print('Adding record to database...')
try:
cursor.execute(
"""INSERT INTO "sensor_data" (name, value, date) VALUES (%s,%s,%s)""",
(data["name"], data["value"], data["date"]))
from api.database import Database
from api.models import LoggedInResponse, ShortURL, SuccessfulShortURLResponse
from api.utils import authenticate_token, create_short_url, create_user
load_dotenv(".env")
API_BASE = os.environ.get("API_BASE_URL", "http://*****:*****@app.on_event("startup")
async def startup() -> None:
await db.connect()
await db.initialize_tables()
@app.on_event("shutdown")
async def shutdown() -> None:
await db.disconnect()
@app.get("/discord", response_model=LoggedInResponse)
import tornado.ioloop
import tornado.web
import json
import sys
import os
from api.database import Database
from api.service import Service, UnknownInstanceError
from api.import_data import import_local_data
from api.endpoint import Endpoint
if __name__ == "__main__":
try:
# initialize database schema (SQLAlchemy)
db = Database("hivery.db")
# pre-process raw data files and load into database
import_local_data(db, "data/companies.json", "data/people.json", "data/foods.json")
# pass database to service
service = Service(db)
# construct the API endpoint
endpoint = Endpoint(service)
# start listening on the API endpoint
endpoint.run(port_num=8888)
except ImportError as err:
print("data import failed because:: " + str(err))
from flask_restful import Resource, Api
from flask import Flask, session, request, url_for, render_template, redirect, abort, escape, flash
from flask import current_app as app
from werkzeug.security import generate_password_hash, check_password_hash
from functools import wraps
from datetime import datetime
from api.database import Database
database = Database()
users = database.get_users()
def login_required(f):
@wraps(f)
def wrap(*args, **kwargs):
print(request.data)
print(request.environ)
if session.get("authenticated"):
return f(*args, **kwargs)
else:
return {"error": "You need to authenticate to use this endpoint"}, 401
return wrap
class Login(Resource):
def __init__(self):
super().__init__()
def post(self):
data = request.json
def get(self): members = Database.getmembers() return members
def setup_method(self, test_method):
"""Create/Wipe a table called test_data where tests will be run"""
self.connection = Database(database=TEST_DATABASE)
self.connection.query('DROP TABLE IF EXISTS test_data')
self.connection.query('CREATE TABLE test_data (variable INTEGER)')
class TestDatabase(unittest.TestCase):
def setup_method(self, test_method):
"""Create/Wipe a table called test_data where tests will be run"""
self.connection = Database(database=TEST_DATABASE)
self.connection.query('DROP TABLE IF EXISTS test_data')
self.connection.query('CREATE TABLE test_data (variable INTEGER)')
def teardown_method(self, test_method):
"""Drop the test_data table used to perform tests"""
self.connection.query('DROP TABLE test_data')
self.connection.close()
def test_commit(self):
"""Ensure that the database commit method retains
the data after reconnection.
"""
# insert test data and commit
self.connection.query('INSERT INTO test_data (variable) VALUES (1)')
self.connection.commit()
self.connection.close()
# reopen connection and query to ensure data was retained
self.connection = Database(database=TEST_DATABASE)
result = self.connection.query('SELECT * FROM test_data')
self.assertEqual(result, [(1, )])
def test_rollback(self):
"""Ensure that the database rollback method removes changes to the data
"""
# insert test data and rollback
self.connection.query('INSERT INTO test_data (variable) VALUES (1)')
self.connection.rollback()
self.connection.close()
# reopen connection and query to ensure data was removed
self.connection = Database(database=TEST_DATABASE)
result = self.connection.query('SELECT * FROM test_data')
self.assertEqual(result, [])
def test_close(self):
"""Ensure that the close method closes the database connection"""
with self.assertRaises(InterfaceError):
self.connection.close()
self.connection.query('SELECT * FROM test_data')
def test_with_commit(self):
"""Ensure that an error free with statement commits when exiting
Performs an error free with block and ensures the data remains
"""
with Database(database=TEST_DATABASE) as db:
db.query('INSERT INTO test_data (variable) VALUES (1)')
result = self.connection.query('SELECT * FROM test_data')
self.assertEqual(result, [(1, )])
def test_with_rollback(self):
"""Ensure that an error occurring in a database with context forces the database to rollback
Raises an error in the with context and ensures that modifications were removed
"""
try:
with Database(database=TEST_DATABASE) as db:
db.query('INSERT INTO test_data (variable) VALUES (1)')
raise Exception
except:
pass
result = self.connection.query('SELECT * FROM test_data')
self.assertEqual(result, [])
def test_limit(self):
"""Ensure that the limit keyword of the query method works as expected
Performs several queries with varying limits and compares the results to the expected results
"""
self.connection.query(
'INSERT INTO test_data (variable) VALUES (1), (2), (3), (4), (5)')
result = self.connection.query('SELECT * FROM test_data', limit=1)
self.assertEqual(result, [(1, )])
result = self.connection.query('SELECT * FROM test_data', limit=3)
self.assertEqual(result, [(1, ), (2, ), (3, )])
result = self.connection.query('SELECT * FROM test_data')
self.assertEqual(result, [(1, ), (2, ), (3, ), (4, ), (5, )])
def test_exists(self):
"""Ensure that the exists method accurately reports the existence of rows in the database"""
self.connection.query(
'INSERT INTO test_data (variable) VALUES (1), (2), (3), (4), (5)')
self.assertTrue(self.connection.exists('test_data'))
self.assertTrue(self.connection.exists('test_data', variable=3))
self.assertFalse(self.connection.exists('test_data', variable=6))
def test_bool(self):
"""Ensure that the database returns the correct boolean value based on whether it is open or closed"""
self.assertTrue(self.connection)
self.connection.close()
self.assertFalse(self.connection)
def test_del(self):
"""Ensure that the __del__ magic method works in the same way the close works"""
with self.assertRaises(InterfaceError):
self.connection.__del__()
self.connection.query('SELECT * FROM test_data')
def test_database_init(app):
from api.database import Database
db = Database(app)
assert db.app == app
def create_project(name: str) -> int:
query = f"INSERT INTO project (name) VALUES ('{name}')"
database = Database()
return database.write(query=query)
from flask_restful import Resource, Api
from flask import Flask, session, request, url_for, render_template, redirect, abort, escape, flash
from flask import current_app as app
from datetime import datetime
from random import choice
import string
from api.database import Database
from api.account import login_required
database = Database()
posts = database.get_posts()
class Post(Resource):
def __init__(self):
super().__init__()
@login_required
def post(self):
data = request.json
if data.get("operation") == "add":
post_type = data.get("type")
post = {
"post_id": "".join([choice(string.ascii_letters + string.digits) for char in range(8)]),
"username": session["user"].get("username"),
"location": {
"type": "Point",
"coordinates": data.get("coordinates")
def get_experiment(experiment_id: int) -> dict:
query = f"SELECT * FROM experiment WHERE id = {experiment_id}"
database = Database()
return database.read(query=query)[0]
import os
from flask import jsonify
from api import app
from api.database import Database
db = Database()
config_name = os.getenv('APP_SETTINGS')
@app.errorhandler(405)
def url_not_found(error):
return jsonify({'message': 'Requested method not allowed'}), 405
@app.errorhandler(404)
def page_not_found(error):
return jsonify({'message': 'page not found, check the url'}), 404
@app.errorhandler(500)
def internal_error(error):
return "500 error"
if __name__ == '__main__':
db.create_tables()
app.run()
def get(self):
return {'status': 'OK' if Database.ping() else 'BROKEN'}
