def test_delete_tracklet(tracklet_dict_list):
# set up db & table
if os.path.isfile(sql.fetch_db_filepath()):
os.remove(sql.fetch_db_filepath())
conn = sql.create_connection(sql.fetch_db_filepath())
cur = conn.cursor()
sql.create_specific_table(conn)
# upload data ...
JD = [tracklet_dic['JD'] for tracklet_dic in tracklet_dict_list]
HP = [tracklet_dic['HP'] for tracklet_dic in tracklet_dict_list]
tracklet_name = [
tracklet_dic['tracklet_name'] for tracklet_dic in tracklet_dict_list
]
sql.upsert_tracklets(conn, JD, HP, tracklet_name, tracklet_dict_list)
# query the data & check that required # of dictionaries are returned
list_of_tuples = sql.query_tracklets_jdhp(conn, JD[0], HP[0])
assert isinstance(list_of_tuples, list) and len(list_of_tuples) == 1
# now delete a tracklet & check that one less dictionary is subsequently returned
sql.delete_tracklet(conn, tracklet_dict_list[0]['tracklet_name'])
list_of_tuples = sql.query_tracklets_jdhp(conn, JD[0], HP[0])
assert isinstance(list_of_tuples, list) and len(list_of_tuples) == 0
# Delete the db to facilitate future testing
os.remove(sql.fetch_db_filepath())
def test_tracklets_upsert(tracklet_dict_list):
""" Here we are updating/inserting **lists** of tracklet data """
# set up db & table
if os.path.isfile(sql.fetch_db_filepath()):
os.remove(sql.fetch_db_filepath())
conn = sql.create_connection(sql.fetch_db_filepath())
cur = conn.cursor()
sql.create_specific_table(conn)
# upload data ...
JD = [tracklet_dic['JD'] for tracklet_dic in tracklet_dict_list]
HP = [tracklet_dic['HP'] for tracklet_dic in tracklet_dict_list]
tracklet_name = [
tracklet_dic['tracklet_name'] for tracklet_dic in tracklet_dict_list
]
sql.upsert_tracklets(conn, JD, HP, tracklet_name, tracklet_dict_list)
# test that the data was actually uploaded
cur.execute('SELECT * from tracklets')
f = cur.fetchall()
assert (len(f) == len(tracklet_dict_list)), 'data not uploaded'
for i in range(len(f)):
assert f[i][3] == tracklet_name[i], 'data not uploaded'
# Delete the db to facilitate future testing
os.remove(sql.fetch_db_filepath())
def convenience_func_create_db_and_tables():
'''
In order to save data, we require sql-db to exist,
so let's set that up...
Force deletion then creation of db...
'''
if os.path.isfile(sql.fetch_db_filepath()):
os.remove(sql.fetch_db_filepath())
conn = sql.create_connection(sql.fetch_db_filepath())
cur = conn.cursor()
# Create required table(s)
sql.create_specific_table(conn)
# Double-check that this worked by getting the count of tables with the name
# - if the count is 1, then table exists
cur.execute('SELECT name from sqlite_master WHERE '
'type = "table" AND name = "tracklets"')
res = cur.fetchone()
assert len(res) == 1, 'table does not exist'
conn.close()
def test_tracklet_upsert(tracklet_dict_list):
# set up db & table
if os.path.isfile(sql.fetch_db_filepath()):
os.remove(sql.fetch_db_filepath())
conn = sql.create_connection(sql.fetch_db_filepath())
cur = conn.cursor()
sql.create_specific_table(conn)
# create some data and then upload it ...
tracklet_dict = test_tracklet_dict_list[0]
sql.upsert_tracklet(conn, tracklet_dict['JD'], tracklet_dict['HP'],
tracklet_dict['tracklet_name'], tracklet_dict)
# test that the data was actually uploaded
cur.execute('SELECT * from tracklets')
f = cur.fetchone()
assert (len(f) > 3
and f[3] == tracklet_dict['tracklet_name']), 'data not uploaded'
# Delete the db to facilitate future testing
os.remove(sql.fetch_db_filepath())
def test_table_creation():
expected_table_name = 'tracklets'
# set up db & table
if os.path.isfile(sql.fetch_db_filepath()):
os.remove(sql.fetch_db_filepath())
conn = sql.create_connection(sql.fetch_db_filepath())
cur = conn.cursor()
# Create the table
sql.create_specific_table(conn)
# - get the count of tables with the name
cur.execute(
'SELECT name from sqlite_master WHERE type = "table" AND name = "tracklets"'
)
# - if the count is 1, then table exists
assert len(cur.fetchone()) == 1, 'table does not exist'
# Delete the db to facilitate future testing
os.remove(sql.fetch_db_filepath())
def test_tracklet_query_mutiple_HP(tracklet_dict_list):
# set up db & table
if os.path.isfile(sql.fetch_db_filepath()):
os.remove(sql.fetch_db_filepath())
conn = sql.create_connection(sql.fetch_db_filepath())
cur = conn.cursor()
sql.create_specific_table(conn)
# upload data ...
JD = [tracklet_dic['JD'] for tracklet_dic in tracklet_dict_list]
HP = [tracklet_dic['HP'] for tracklet_dic in tracklet_dict_list]
tracklet_name = [
tracklet_dic['tracklet_name'] for tracklet_dic in tracklet_dict_list
]
sql.upsert_tracklets(conn, JD, HP, tracklet_name, tracklet_dict_list)
# query the data & check that requisite dictionaries are returned
list_of_tuples = sql.query_tracklets_jd_hplist(conn, JD[0], HP)
assert isinstance(list_of_tuples, list) and len(list_of_tuples) == 1
# Delete the db to facilitate future testing
os.remove(sql.fetch_db_filepath())