def create_table():
"""Create a table"""
queries = ["CREATE TABLE %s (%s VARCHAR(32) PRIMARY KEY, %s REAL)" % (table_name, filename_column, time_column),
get_index_query(table_name, filename_column),
get_index_query(table_name, time_column)]
for query in queries:
execute_query(settings.database, query)
def create_table():
"""Create a table"""
foreign_key_query = 'FOREIGN KEY(%s) REFERENCES %s(%s)' % (
image_column, images_table_name, images_filename_column)
query = "CREATE TABLE %s (%s VARCHAR(32) PRIMARY KEY, %s VARCHAR(32), %s VARCHAR(64), %s VARCHAR(32), %s)" % (
table_name, id_column, image_column, object_type_column,
bounding_box_column, foreign_key_query)
execute_query(settings.database, query)
def create_table():
"""Create a table"""
queries = [
"CREATE TABLE %s (%s VARCHAR(32), %s VARCHAR(32), %s REAL, %s VARCHAR(128))"
% (table_name, ip_column, mac_column, time_column, description_column),
get_index_query(table_name, time_column),
get_index_query(table_name, mac_column)
]
for query in queries:
execute_query(settings.database, query)
def get_last_detections(max_results=5):
query = 'SELECT * FROM {} INNER JOIN {} ON {}.image={}.filename ORDER BY {} DESC LIMIT {}' \
.format(table_name, images_table_name, table_name, images_table_name, images_time_column, max_results)
results = execute_query(settings.database, query)
return results
def get_all_detections(max_results=5):
query = 'SELECT * FROM {} DESC LIMIT {}'.format(table_name, max_results)
results = execute_query(settings.database, query)
return results
def save_or_update(filename, object_type, bounding_box=None):
new_id = uuid.uuid4()
query = 'INSERT INTO {} VALUES ("{}", "{}", "{}", "{}")' \
.format(table_name, new_id, filename, object_type, bounding_box)
execute_query(settings.database, query)
return new_id
def get_all_connections(max_results=5):
query = 'SELECT * FROM {} ORDER BY {} DESC LIMIT {}'.format(
table_name, time_column, max_results)
results = execute_query(settings.database, query)
return results
def get_time_by_mac(mac):
query = 'SELECT {} FROM {} WHERE {} = "{}" ORDER BY {} DESC'.format(
time_column, table_name, mac_column, mac, time_column)
results = execute_query(settings.database, query)
return parse(results[0][0])
def save_or_update(ip, mac, time, description):
query = 'INSERT INTO {} VALUES ("{}", "{}", {}, "{}")'.format(
table_name, ip, mac, time, description)
execute_query(settings.database, query)
def save_or_update(filename, seconds):
query = 'INSERT INTO {} VALUES ("{}", {})'.format(table_name, filename, seconds)
execute_query(settings.database, query)