Saving detections to a file

[buran]

Here is very basic example. NOTE: it's not the best way to structure the code - a lot of repeating code (you should go OOP for example). It's just to give you idea

import random
import sqlite3

sqlite_file = 'sample.sqlite'

def setup(sqlite_file):
    conn = sqlite3.connect(sqlite_file)
    cur = conn.cursor()
    cur.execute('CREATE TABLE IF NOT EXISTS locations (loc TEXT, unique (loc))')
    cur.execute('CREATE TABLE IF NOT EXISTS timestamp (case_id INTEGER, datetime TEXT)')
    conn.commit()
    conn.close()
    
def add_record(sqlite_file, loc):
    conn = sqlite3.connect(sqlite_file)
    cur = conn.cursor()
    cur.execute('INSERT OR IGNORE INTO locations (loc) VALUES (?)', (loc,))
    cur.execute('INSERT INTO timestamp (case_id, datetime) VALUES ((SELECT rowid from locations WHERE loc=?), CURRENT_TIMESTAMP)', (loc,)) # NOTE timestamp would be GMT
    conn.commit()
    conn.close()
    
def get_num_detections(sqlite_file,loc):
    conn = sqlite3.connect(sqlite_file)
    cur = conn.cursor()
    cur.execute('SELECT COUNT(a.rowid) FROM timestamp as a JOIN locations as b ON a.case_id = b.rowid WHERE b.loc = ?', (loc, ))
    result = cur.fetchone()[0]
    conn.close()
    return result
    
def get_stat(sqlite_file):
    conn = sqlite3.connect(sqlite_file)
    cur = conn.cursor()
    cur.execute('SELECT b.loc, COUNT(a.rowid) FROM timestamp as a JOIN locations as b ON a.case_id = b.rowid GROUP BY b.loc ORDER BY b.loc ASC')
    result = cur.fetchall()
    conn.close()
    return result
    
def get_detections(sqlite_file,loc):
    conn = sqlite3.connect(sqlite_file)
    cur = conn.cursor()
    cur.execute('SELECT b.loc, datetime(datetime, "localtime") FROM timestamp as a JOIN locations as b ON a.case_id = b.rowid WHERE b.loc = ?', (loc, ))
    result = cur.fetchall()
    conn.close()
    return result
    
    
# initial setup of db tables if not exists
setup(sqlite_file)

#just 4 sample locations
locations = ['loc A', 'loc B', 'loc C', 'loc D']

#add some random observations, in production it would come from your live stream
for obj in range (100):
    loc = random.choice(locations)
    add_record(sqlite_file, loc)

# example of query DB    
print(get_num_detections(sqlite_file, 'loc A'))
for loc, num in get_stat(sqlite_file):
    print(f'{loc} --> {num}')

print()
for loc, tstamp in get_detections(sqlite_file, loc='loc A'):
    print(f'{loc} --> {tstamp}')

There are tools that you can use to view and manage the DB, e.g. https://sqlitestudio.pl/ but also many others

---

he N in the first table is dependent on the amount of records with the same CaseID if you understand what I mean

You should not store number of detections in the table. These statistics should be derived in a query, so that it's not possible to have discrepancy between real number of detections stored in the DB and the count if stored in the DB

---

I just seen your other thread. before that I didn't really understand your example, e.g. 1,2,3,4. that's why i used sample location names.
given that these are 4 numbers it may be better if you have 4 columns xmin, ymin, xmax, ymax. You may keep loc column as descriptive name for location.
or you can join all 4 numbers in a single str with some separator and store this string in loc (instead of 'loc A' etc)