def get_video_clips(t1, t2):
video_list = []
conn = None
try:
conn = Db.get_connection()
selectQuery = "SELECT id, camera_id, start_time, end_time, file_path FROM video_clips where id in(select video_id from intruder_in_action \
where start_time >= '{}' and end_time <= '{}') order by start_time".format(t1, t2)
cursor = conn.cursor()
cursor.execute(selectQuery)
results = cursor.fetchall()
for row in results:
video_list.append(VideoData(row[0], row[1], row[2], row[3], row[4]))
except Exception as e:
logging.exception("Error while retrieving videos from table")
Db.disconnect(conn)
return video_list
def get_intruder_video_clips(id, t1, t2):
conn = None
video_clips = []
try:
conn = Db.get_connection()
selectQuery = "select id, camera_id, start_time, end_time, file_path from video_clips where id in ( \
SELECT distinct(video_id) FROM intruder_in_action where common_id in \
(select common_id from intruder_in_action \
where intruder_id = {} and start_time >= '{}' and end_time <= '{}') \
union \
select distinct(video_id) FROM intruder_in_action \
where intruder_id={} and start_time >= '{}' and end_time <= '{}' and common_id is null \
order by start_time)".format(id, t1, t2, id, t1, t2)
cursor = conn.cursor()
cursor.execute(selectQuery)
results = cursor.fetchall()
for row in results:
video_clip = VideoData(row[0], row[1], row[2], row[3], row[4])
video_clips.append(video_clip)
except Exception as e:
logging.exception("Error while retrieving videos from table")
Db.disconnect(conn)
return video_clips
def get_by_id(id):
conn = None
intruder = None
try:
conn = Db.get_connection()
selectQuery = (
"SELECT id,frame,start_time FROM intruder_data where id = {}".
format(id))
cursor = conn.cursor()
cursor.execute(selectQuery)
row = cursor.fetchone()
if (row is not None):
intruder = IntruderData(row[0], row[1])
except Exception as e:
logging.exception("Error while loading intruder data from db: %s" %
id)
Db.disconnect(conn)
return intruder
def get_count_by_id(id):
conn = None
count = 0
try:
conn = Db.get_connection()
print "id in get count by id {}".format(id)
#selectQuery2 = "select count(distinct common_id) as count from intruder_in_action where {} >= {} and {} <= {}".format(start_time, t1,end_time,t2)
#selectQuery3 = "select count(intruder_id) as count from intruder_in_action where common_id is null and {} >= {} and {} <= {}".format(start_time, t1,end_time,t2)"
query = "select count(intruder_id) as count from intruder_in_action \
where camera_id = {}".format(id)
cursor = conn.cursor()
cursor.execute(query)
results = cursor.fetchall()
for row in results:
count += row[0]
except Exception as e:
logging.exception("Error while retrieving statistics from table")
Db.disconnect(conn)
return count
def load_by_id(id):
conn = None
camera = None
try:
if CameraData.camera_list is None:
CameraData.load_all()
conn = Db.get_connection()
selectQuery = "SELECT id, location, description FROM camera_data where id = {}".format(id)
cursor = conn.cursor()
cursor.execute(selectQuery)
results = cursor.fetchall()
for row in results:
camera = CameraData(row)
print "Loaded camera: %d" % camera.id
CameraData.camera_list[camera.id] = camera
except Exception as e:
logging.exception("Error while loading camera list from database.")
#print "Error while loading camera list from database. Error: %s" % str(e)
Db.disconnect(conn)
return camera
def day_wise_distribution(d1, d2):
conn = None
count = 0
try:
conn = Db.get_connection()
#selectQuery2 = "select count(distinct common_id) as count from intruder_in_action where {} >= {} and {} <= {}".format(start_time, t1,end_time,t2)
#selectQuery3 = "select count(intruder_id) as count from intruder_in_action where common_id is null and {} >= {} and {} <= {}".format(start_time, t1,end_time,t2)"
data = []
dtemp1 = d1
dtemp2 = dtemp1 + datetime.timedelta(days=1)
while (dtemp2 <= d2):
count = 0
query = "select count(distinct common_id) as count from intruder_in_action \
where start_time >= '{}' and end_time <= '{}'".format(
dtemp1, dtemp2, dtemp1, dtemp2)
query2 = "select count(distinct intruder_id) as count from intruder_in_action \
where common_id is null and start_time >= '{}' and end_time <= '{}'".format(
dtemp1, dtemp2, dtemp1, dtemp2)
cursor = conn.cursor()
cursor.execute(query)
results = cursor.fetchall()
for row in results:
count += row[0]
cursor.execute(query2)
results = cursor.fetchall()
for row in results:
count += row[0]
data.append({"date": str(dtemp1.date()), "count": count})
dtemp1 = dtemp2
dtemp2 = dtemp2 + datetime.timedelta(days=1)
except Exception as e:
logging.exception("Error while retrieving statistics from table")
Db.disconnect(conn)
return data
def delete(self):
conn = None
try:
conn = Db.get_connection()
deleteQuery = ("delete from intruder_data where id = {}".format(
self.id))
cursor = conn.cursor()
cursor.execute(deleteQuery)
conn.commit()
except Exception as e:
logging.exception(
"Error while deleting intruder data from db: %d" % self.id)
Db.disconnect(conn)
def update(self):
conn = None
try:
conn = Db.get_connection()
updateQuery = ("update video_clips set end_time='{}' where id={}".format(self.end_time, self.id))
cursor = conn.cursor()
cursor.execute(updateQuery)
conn.commit()
except Exception as e:
logging.exception("Error while updating video clip")
Db.disconnect(conn)
def insert(self):
conn = None
try:
conn = Db.get_connection()
insertQuery = ("insert into video_clips(camera_id, start_time, file_path) values({},'{}','{}')".format(
self.camera_id, self.start_time, self.file_path))
cursor = conn.cursor()
cursor.execute(insertQuery)
self.id = cursor.lastrowid
conn.commit()
except Exception as e:
logging.exception("Error while saving video clip")
Db.disconnect(conn)
def insert(self):
conn = None
try:
conn = Db.get_connection()
insertQuery = "insert into intruder_in_action(intruder_id,camera_id,video_id,start_time,end_time,start_frame,end_frame) values( \
{},{},{},'{}','{}','{}','{}')" .format( \
self.intruder_id, self.camera_id, self.video_id, self.start_time, self.end_time, self.start_frame, self.end_frame)
print insertQuery
cursor = conn.cursor()
cursor.execute(insertQuery)
conn.commit()
self.id = cursor.lastrowid
except Exception as e:
logging.exception("Error while saving video clip")
Db.disconnect(conn)
def insert(description):
camera = None
try:
conn = Db.get_connection()
print description
insertQuery = "insert into camera_data(description) values('{}')".format(description)
cursor = conn.cursor()
cursor.execute(insertQuery)
conn.commit()
id = cursor.lastrowid
camera = CameraData.load_by_id(id)
print "Id in insert: {}".format(id)
#print id
except Exception as e:
logging.exception("Error while inserting camera description")
Db.disconnect(conn)
return camera
def get_intruders(t1, t2):
conn = None
intruder_id_list = []
try:
conn = Db.get_connection()
selectQuery = "SELECT distinct(intruder_id) FROM intruder_in_action where start_time >= '{}' and end_time <= '{}'".format(t1, t2, t1, t2)
cursor = conn.cursor()
cursor.execute(selectQuery)
results = cursor.fetchall()
for row in results:
intruder_id_list.append(row[0])
except Exception as e:
logging.exception("Error while retrieving intruders from table")
Db.disconnect(conn)
return intruder_id_list
def insert(self):
conn = None
try:
conn = Db.get_connection()
frame_file = ""
if (self.frame is not None):
frame_file = self.frame
insertQuery = (
"insert into intruder_data(frame,camera_id) values('{}', {})".
format(frame_file, self.camera_id))
cursor = conn.cursor()
cursor.execute(insertQuery)
# Fetch the auto increment id
self.id = cursor.lastrowid # conn.insert_id()
print "Intruder ID: %d" % (self.id)
conn.commit()
except Exception as e:
logging.exception("Error while saving intruder data into db")
Db.disconnect(conn)