def afspraak_invoegen():
data = request.json
db.execute_sql(
"INSERT INTO afspraak(naam, email, tijdstip, behandeling) VALUES ('{}','{}','{}','{}')"
.format(data['naam'], data['email'], data['tijdstip'],
data['behandeling']))
return jsonify({'succes': True}), 200, {'ContenType': 'application/json'}
def create_table():
execute_sql('''CREATE TABLE eventmodel (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
text TEXT,
category CHAR(7),
person char(12),
date DATETIME
)''')
def create_table_for_test():
db.execute_sql('''
CREATE TABLE helpermodel(
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
list_id INT UNSIGNED NOT NULL,
name CHAR(60) NOT NULL
)
''')
def test_remove_all_missing_files_in_db(self):
with flask_app.test_request_context():
movieManager = MovieManager()
expected_count = 22
for i in range(expected_count):
execute_sql('insert into movies(file_name, name) values(?, ?)', ['.filenotfound' + str(i), 'test' + str(i)])
actual_count = movieManager.remove_all_missing_files_in_db()
assert actual_count == expected_count, 'actual: {0}, expected: {1}'.format(actual_count, expected_count)
print('OK - test_remove_all_missing_files_in_db')
def get_current_occupancy():
# todo : optimize
# logger.debug("querying the occupancy from db")
query = "SELECT occupancy FROM occupancy_cache WHERE cubical_label=%s ORDER BY timestamp DESC LIMIT 1"
a = db.execute_sql(query, ('a', ), logger, True)[0][0]
b = db.execute_sql(query, ('b', ), logger, True)[0][0]
c = db.execute_sql(query, ('c', ), logger, True)[0][0]
d = db.execute_sql(query, ('d', ), logger, True)[0][0]
# logger.info("occupancy : a {}, b {}, c {}, d {}".format(a, b, c, d))
return {'a': a, 'b': b, 'c': c, 'd': d}
def get_current_real_lux():
# todo : optimize below code
# logger.debug("querying the lux from db")
query = "SELECT lux FROM lux WHERE label=%s AND pin=%s ORDER BY timestamp DESC LIMIT 1"
a = db.execute_sql(query, ('a', 'tsl_0'), logger, True)[0][0]
b = db.execute_sql(query, ('b', 'tsl_3'), logger, True)[0][0]
c = db.execute_sql(query, ('c', 'tsl_0'), logger, True)[0][0]
d = db.execute_sql(query, ('d', 'tsl_0'), logger, True)[0][0]
logger.info("lux :a {}, b {}, c {}, d {}".format(a, b, c, d))
ret = {'a': a, 'b': b, 'c': c, 'd': d}
return get_rounded_values_dict(ret)
def test_remove_duplicate_movies(self):
with flask_app.test_request_context():
sql = 'select count(_id) as fcount from movies'
expected_count = 1
for i in range(2):
execute_sql('insert into movies(name, imdb_id) values(?, ?)', ['duplicate_movie_name_' + str(i), 'tt9999999'])
count = query_db(sql, one=True)['fcount']
MovieManager().remove_duplicate_movies()
actual_count = count - query_db(sql, one=True)['fcount']
assert actual_count == expected_count, 'actual: {0}, expected: {1}'.format(actual_count, expected_count)
print('OK - test_remove_duplicate_movies')
def afspraak_invoegen():
data = request.json or request.form
print(data)
db.execute_sql(
"INSERT INTO afspraak(datum, tijdstip, kapper, firstname, lastname, email, behandeling, gender) VALUES ('{}', '{}','{}', '{}', '{}', '{}', '{}', '{}')"
.format(data['datum'], data['tijdstip'], data['kapper'],
data['firstname'], data['lastname'], data['email'],
data['behandeling'], data['gender']))
return redirect("/Datum.html", code=302)
def collect_dc_values():
logger.info("dc collection thread started")
while 1:
for label in config.RPI_DEVICES.keys():
url = config.RPI_DEVICES.get(label).get('url') + 'dc'
timestamp = time.time()
for pin in config.RPI_DEVICES.get(label).get('dc_pins'):
dc = get_dc_from_device(url, pin)
db.execute_sql(QUERY_DC_INSERT, (timestamp, label, pin, dc),
logger)
db.execute_sql(QUERY_DC_CACHE_UPSERT,
(timestamp, label, pin, dc), logger)
time.sleep(config.general.get("collect_dc_thread_sleep_time"))
def generate_table():
sql = ('CREATE TABLE `' + db.validate(data[0]) + '` (\n' +
'`id` INT NOT NULL AUTO_INCREMENT,\n' +
'`patient_id` VARCHAR(12) NULL ,\n')
for test in data[1:]:
tmp = test.split(';')
test_name = db.validate(tmp[0])
value = get_table_field(tmp[1][0])
sql = sql + '`' + test_name + '` ' + value + ' NULL , \n'
sql = sql + ('PRIMARY KEY (`id`))')
db.execute_sql(sql)
print '\'' + data[0] + '\' table created'
def collect_lux_values_via_hardware_sensors():
logger.info("collect_lux_values_via_hardware_sensors thread started")
while 1:
for label in config.RPI_DEVICES.keys():
url = config.RPI_DEVICES.get(label).get('url') + 'lux'
timestamp = time.time()
lux = get_lux_from_device(url)
if lux is not None:
logger.info("lux reply from rpi {}".format(str(lux)))
for k, v in lux.items():
db.execute_sql(QUERY_LUX_INSERT, (timestamp, label, v, k),
logger)
else:
logger.error("rpi {} with url {} returned None for lux".format(
label, url))
time.sleep(config.general.get("collect_lux_thread_sleep_time"))
def get_users():
sql = "SELECT user_name, age FROM users"
results = execute_sql(sql, connection)
# Example of the output format
# {
# 'users': [
# {
# 'name': 'Tom',
# 'age': 222
# }
# ]
# }
# create a dictionary with one key "users" and empty list as a value
output = {'users': []}
# add dictionary with user name and age to the list created above
for name, age in results:
output['users'].append({'name': name, 'age': age})
# convert Python dict to JSON object and set appropriate HTTP headers:
# "Content-Type:application/json"
return jsonify(output)
def check_for_table(): sql = 'SELECT COUNT(*) AS exist FROM information_schema.tables WHERE table_schema="' + db.get_database() + '" AND table_name="' + db.validate(data[0]) + '";' cur = db.execute_sql(sql) tmp = cur.fetchone()['exist'] if tmp > 0: print 'table \'' + data[0] + '\' already exists' return True; return False
def get_alle_afspraken():
res = []
afspraken = db.execute_sql('SELECT * FROM afspraak ORDER BY tijdstip')
for afspraak in afspraken:
res.append({
'naam': afspraak['naam'],
'tijdstip': afspraak['tijdstip']
})
return jsonify(res), 200, {'ContentType': 'application/json'}
def remove_all_missing_files_in_db(self):
removed_count = 0
page_number = 0
sql = "update movies set is_active = 'false' where _id = ?"
while True:
query = self.get_all_movies(20, page_number, True)
if len(query['result']) == 0:
break
movies = query['result']
for movie in movies:
file_name = movie['file_name']
file_id = movie['_id']
if not os.path.isfile(file_name):
execute_sql(sql, [file_id])
removed_count = removed_count + 1
page_number = page_number + 1
if removed_count > 0:
execute_sql("delete from movies where is_active = 'false'")
return removed_count
def get_current_occupants_coordinates():
# todo : optimize
# logger.debug("querying the occupant coordinates from db")
ret = []
query = "SELECT occupant_coordinates FROM occupancy_cache WHERE cubical_label=%s ORDER BY timestamp DESC LIMIT 1"
a = db.execute_sql(query, ('a', ), logger, True)[0][0]
if a and len(a) > 0:
ret += (json.loads(a))
b = db.execute_sql(query, ('b', ), logger, True)[0][0]
if b and len(b) > 0:
ret += (json.loads(b))
c = db.execute_sql(query, ('c', ), logger, True)[0][0]
if c and len(c) > 0:
ret += (json.loads(c))
d = db.execute_sql(query, ('d', ), logger, True)[0][0]
if d and len(d) > 0:
ret += (json.loads(d))
logger.info("occupant coordinates : {}".format(ret))
return ret
def maak_afspraak():
data = request.json
if 'tijdstip' in data:
available = db.execute_sql(
"SELECT COUNT(*) AS count FROM afspraak WHERE tijdstip = '{}'".format(data['tijdstip']))
valid = True
for row in available:
if row['count'] == 1:
valid = False
if valid:
db.execute_sql("INSERT INTO afspraak(naam, email, tijdstip) VALUES ('{}','{}','{}')".format(
data['naam'], data['email'], data['tijdstip']))
return jsonify({'success': True}), 200, {'ContentType': 'application/json'}
else:
return jsonify({'success': False, 'reason': 'Date and time already taken'}), 200, {'ContentType': 'application/json'}
else:
return jsonify({'success': False, 'reason': 'Failed to provide date and time for appointment'}), 400, {'ContentType': 'application/json'}
def delete_item(request: Request, task_id: int):
if not security.user_has_role(request, "DELETE_TODO"):
raise HTTPException(
status_code=403,
detail="You don't have permission to delete ToDo's")
result = db.execute_sql(f'''delete from tasks where id = {task_id}''')
if (result == 0):
return "OK"
else:
return result
def vraag_afspraken_op():
dbafspraken = db.execute_sql('SELECT * FROM afspraak ORDER BY tijdstip')
afspraken = []
for afspraak in dbafspraken:
afspraken.append({
'naam': afspraak['naam'],
'tijdstip': afspraak['tijdstip']
})
return jsonify(afspraken), 200, {'ContentType': 'application/json'}
def toggle_complete(request: Request, task_id: int):
if not security.user_has_role(request, "TOGGLE_TODO"):
raise HTTPException(
status_code=403,
detail="You don't have permission to Toggle ToDo's")
result = db.execute_sql(
f'''update tasks set complete = 1 - complete where id = {task_id}''')
if (result == 0):
return "OK"
else:
return result
def get_afspraken_via_email():
data = request.json
res = []
if 'email' in data:
afspraken = db.execute_sql(
"SELECT * FROM afspraak WHERE email = '{}'".format(data['email']))
for afspraak in afspraken:
res.append({
'naam': afspraak['naam'],
'tijdstip': afspraak['tijdstip']
})
return jsonify(res), 200, {'ContentType': 'application/json'}
def get_users_age():
sql = "SELECT sum(age) as total_age FROM users"
results = execute_sql(sql, connection)
# Results is a list of records
# The result of summing is one row and one column
# That is why to the get result we select the first row (index 0) and the
# first column (index 0)
total_age = results[0][0]
return render_template('users_age.html', age=total_age)
def create_tables(): # create patient table if it does not exist patient_sql = '''CREATE TABLE IF NOT EXISTS `patient`( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `patient_id` VARCHAR(10) NULL, `name` VARCHAR(30) NULL, `requested_bd` VARCHAR(30) NULL, `sample_co` DATE NULL, `billed_by` VARCHAR(10) NULL )''' db.execute_sql(patient_sql) # create main table if it does not exist main_sql = '''CREATE TABLE IF NOT EXISTS `main`( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `patient_id` VARCHAR(10) NULL, `test_id` INT NULL, `data_entered` TIMESTAMP NULL, `printed` TIMESTAMP NULL )''' db.execute_sql(main_sql) # create test_info table test_info = ''' CREATE TABLE IF NOT EXISTS `test_info`( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `test_id` INT, `display_name` VARCHAR(50) NULL, `definition_file` VARCHAR(30) NULL, `gui_file` VARCHAR(40) NULL )''' db.execute_sql(test_info)
def create_todo(request: Request, todo: ToDo):
if not security.user_has_role(request, "CREATE_TODO"):
raise HTTPException(
status_code=403,
detail="You don't have permission to create ToDo's")
user = security.get_user(request)
sql = f'''
insert into tasks (task, complete, username)
values('{todo.task}', '{todo.complete}', '{user["username"]}')
'''
result = db.execute_sql(sql)
if (result == 0):
return "OK"
else:
return result
def vraag_afspraken_op():
dbafspraken = db.execute_sql('SELECT * FROM afspraak ORDER BY tijdstip')
afspraken = []
for afspraak in dbafspraken:
afspraken.append({
'datum': afspraak['datum'],
'tijdstip': afspraak['tijdstip'],
'kapper': afspraak['kapper'],
'firstname': afspraak['firstname'],
'lastname': afspraak['lastname'],
'email': afspraak['email'],
'behandeling': afspraak['behandeling'],
'gender': afspraak['gender']
})
return jsonify(afspraken), 200, {'ContentType': 'application/json'}
def conn_id(token):
"Execute code within connection specified by token, close connection"
if request.method == "GET":
if token in db.conndict.keys():
c = db.conndict[token]
return jsonify({"desc": c.desc, "token": token})
else:
return message("token %s not found" % token, 404)
elif request.method == "DELETE":
t = db.close_connection(token)
if t is None:
return message("token %s not found" % token, 404)
return message("token %s deleted" % token, 200)
elif request.method == "POST":
conn = db.get_connection(token)
if conn is None:
return message("token %s not found" % token, 404)
cur = conn.cursor()
json = request.get_json(force=True)
sql = json.get("sql", None)
if sql is None:
return message("sql key not in json data", 400)
invars = json.get("invars", {})
outvars = json.get("outvars", {})
fetchmax = json.get("fetchmax", 500)
try:
if fetchmax is None:
fetchmax = 500
else:
fetchmax = int(fetchmax)
except ValueError:
return message("invalid fetchmax key format", 400)
if fetchmax < 1:
return message("number of rows to fetch should be greater than 0",
400)
desc, data = db.execute_sql(cur, sql, fetchmax, invars, outvars)
cur.close()
return jsonify({
"desc": desc,
"data": data,
"sql": sql,
"fetchmax": fetchmax,
"invars": invars,
"outvars": outvars
})
def vraag_kenniskaart_op():
"""Returnd alle velden van kenniskaart waaronder What Why How"""
billydb = db.execute_sql('SELECT * FROM kenniskaarten')
kenniskaarten = []
for kenniskaart in billydb:
kenniskaarten.append({
'titel': kenniskaart['titel'],
'what': kenniskaart['what'],
'why': kenniskaart['why'],
'how': kenniskaart['how'],
'voorbeeld': kenniskaart['voorbeeld'],
'rol': kenniskaart['rol'],
'vaardigheid': kenniskaart['vaardigheid'],
'hboi': kenniskaart['hboi'],
'datetime': kenniskaart['datetime']
})
return jsonify(kenniskaarten), 200
def get_current_dc():
# todo : optimize
# logger.debug("querying the dc from db")
query = "SELECT dc FROM dc WHERE label=%s ORDER BY timestamp DESC LIMIT 1"
a = db.execute_sql(query, ('a', ), logger, True)[0][0]
b = db.execute_sql(query, ('b', ), logger, True)[0][0]
c = db.execute_sql(query, ('c', ), logger, True)[0][0]
d = db.execute_sql(query, ('d', ), logger, True)[0][0]
e = db.execute_sql(query, ('e', ), logger, True)[0][0]
f = db.execute_sql(query, ('f', ), logger, True)[0][0]
# logger.info("dc : a {}, b {}, c {}, d {}, e {}, f {}".format(a, b, c, d, e, f))
ret = {'a': a, 'b': b, 'c': c, 'd': d, 'e': e, 'f': f}
return ret
def write_to_db(data, consumer, conn_pool, schema):
success, error = True, None
if not is_valid(data, schema):
success = False
error = Exception('Invalid data {}'.format(data))
return success, error
commands = insert_ping_sql(**data)
conn = None
try:
conn = conn_pool.getconn()
conn = execute_sql(commands, conn)
consumer.commit()
except Exception as err:
logger.error(error)
success, error = False, err
finally:
if conn:
conn_pool.putconn(conn)
return success, error
def rescan(self, movies_path, movie_share_path, movie_file_exts, refine_folder_names = True, force_rescan_all = False):
result = 0
if force_rescan_all:
sql = 'delete from movies;'
execute_sql(sql)
assetManager = AssetManager();
if refine_folder_names:
assetManager.refine_folder_names(movies_path)
files = assetManager.get_files(movies_path, movie_share_path, movie_file_exts, False if force_rescan_all else True)
imdb_id = ''
for file in files:
movie = dict()
filename = file['filename']
url = file['url']
movie['name'] = os.path.basename(filename)
(r, ext) = os.path.splitext(filename)
poster_filename = r + '.jpg'
if file['info'].has_key('imdb_id'):
imdb_id = file['info']['imdb_id']
movie = MovieInfoProvider().get_by_imdb_id(imdb_id)
if not movie:
continue
# Merge from local json file content
for k in movie.keys():
if file['info'].has_key(k):
movie[k] = trim_str(file['info'][k])
# Download poster image to local with the same name as the movie file name.
if movie.has_key('poster_url'):
self.download_poster_file(movie['poster_url'], poster_filename)
movie['url'] = url
movie['file_name'] = filename
movie['poster_url'] = url[:-4] + '.jpg'
sql = u'insert into movies({0}) values({1});'.format(', '.join(movie.keys()), ('?,'*len(movie.values()))[:-1])
execute_sql('delete from movies where imdb_id = ?', [imdb_id])
execute_sql(sql, movie.values())
result = result + 1
# Create a '.notscan' file to indicates that this file already be scanned. Next time, it will be ignored
self.create_notscan_file(os.path.dirname(filename))
return result
def teardown_class(cls):
db.execute_sql('DROP TABLE eventmodel')
def teardown(self):
db.execute_sql('TRUNCATE helpermodel')
def setup_class(cls):
db.execute_sql(
'CREATE TABLE model (id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY)'
)
def remove_duplicate_movies(self):
sql = 'delete from movies where _id in (select _id from movies group by imdb_id having count(imdb_id) > 1)'
execute_sql(sql)
def voeg_kenniskaart_toe():
""""Voegt kenniskaart toe in de database"""
data = request.json
sql = f"INSERT INTO kenniskaarten(titel, what, why, how, voorbeeld, rol, vaardigheid, hboi) VALUES ('{data['titel']}','{data['what']}','{data['why']}','{data['how']}','{data['voorbeeld']}','{data['rol']}','{data['vaardigheid']}','{data['hboi']}')"
db.execute_sql(sql)
return jsonify({'success': True}), 200
def drop_table():
db.execute_sql('DROP TABLE helpermodel')
def teardown(self):
db.execute_sql('TRUNCATE eventmodel')
def teardown_class(cls):
db.execute_sql('DROP TABLE model')