def insert_hikes(connection: sqlite3.Connection,
cursor: sqlite3.Connection.cursor):
# Get number of hikes
cursor.execute("SELECT COUNT(*) FROM camera.hikes")
all_rows = cursor.fetchall()
num_hikes = all_rows[0][0]
# Insert all hikes
cursor.execute("SELECT * FROM camera.hikes")
all_rows = cursor.fetchall()
hike_count = 0
for row in all_rows:
# print(row)
new_id = row[0] + 13
new_path = change_cam_location(row[4], row[0], new_id)
statement = "INSERT INTO hikes (hike_id, start_time, end_time, pictures, path, created_date_time, updated_date_time) VALUES ({id}, {st}, {et}, {p}, '{pth}', '{t1}', '{t2}')".format(
id=new_id,
st=row[1],
et=row[2],
p=row[3],
pth=new_path,
t1=row[5],
t2=row[6])
cursor.execute(statement)
connection.commit()
hike_count += 1
print('Finished inserting {i} of {n} hikes from camera'.format(
i=hike_count, n=num_hikes))
def insert_pictures(connection: sqlite3.Connection,
cursor: sqlite3.Connection.cursor):
# Get number of pictures
cursor.execute("SELECT COUNT(*) FROM camera.pictures")
all_rows = cursor.fetchall()
num_pics = all_rows[0][0]
# Insert all pictures
cursor.execute("SELECT * FROM camera.pictures")
all_rows = cursor.fetchall()
picture_count = 0
for row in all_rows:
print(row)
# statement_jordan = "INSERT INTO pictures (time, altitude, hike, index_in_hike, camera1, camera2, camera3) VALUES ({t}, {a}, {h}, {i}, '{c1}', '{c2}', '{c3}')".format(t=row[1], a=row[2], h=row[4], i=row[5], c1=row[6], c2=row[7], c3=row[8])
statement = "INSERT INTO pictures (time, altitude, hike, index_in_hike, camera1, camera2, camera3) VALUES ({t}, {a}, {h}, {i}, '{c1}', '{c2}', '{c3}')".format(
t=row[0],
a=row[1],
h=row[3],
i=row[4],
c1=row[5],
c2=row[6],
c3=row[7])
cursor.execute(statement)
connection.commit()
picture_count += 1
print('Finished inserting {i} of {n} pictures from camera'.format(
i=picture_count, n=num_pics))
def _query_table(self, cursor: sqlite3.Connection.cursor, query: Text,
raise_exceptions: bool) -> Tuple[Generator, Exception]:
""" Queries and retrives all records in the connected table.
:param cursor: sqlite3 cursor object with the open connection to the DB
:param query: Query to retrive data from table
:param raise_exceptions: Whether to raise expceptions or return them.
:return: Generator of table records, exception raised
"""
try:
records_yielder = cursor.execute(query)
except (sqlite3.OperationalError, sqlite3.DatabaseError) as excep:
exception_raised = exceph.determine_table_access_exception(
exception_obj=excep, calling_obj=self)
else:
exception_raised = None
timestamp_field = self._check_if_timestamp_field_needed(
cursor=cursor)
if timestamp_field:
records_yielder = self._yield_readable_timestamps(
records_yielder, timestamp_field)
else:
records_yielder = (dict(record) for record in records_yielder)
if raise_exceptions and exception_raised:
raise exception_raised
else:
return records_yielder, exception_raised
def __add_all_branches(self, cur: sqlite3.Connection.cursor, tree_id: int, branches=None, num_branches=None):
if branches is not None:
size = num_branches
if size is None:
raise Exception('num_branches not provided')
else:
branches = self.branches
size = self.num_branches
print(' adding {} branches ...'.format(size))
for i in range(size):
branch = branches[i]
cur.execute('INSERT INTO branches ("ind", depth, length, width, angle, pos_x, pos_y, tree_id)'
' VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
[i, branch.depth, branch.length, branch.width, branch.angle,
branch.pos.x, branch.pos.y, tree_id])
def insert_hikes(connection: sqlite3.Connection,
cursor: sqlite3.Connection.cursor):
# Get number of hikes
cursor.execute("SELECT COUNT(*) FROM camera.hikes")
all_rows = cursor.fetchall()
num_hikes = all_rows[0][0]
# Insert all hikes
cursor.execute("SELECT * FROM camera.hikes")
all_rows = cursor.fetchall()
hike_count = 0
for row in all_rows:
print(row)
# statement_jordan = "INSERT INTO hikes (hike_id, avg_altitude, start_time, end_time, pictures) VALUES ({id}, {aa}, {st}, {et}, {p})".format(id=row[0], aa=row[1], st=row[3], et=row[4], p=row[5])
statement = "INSERT INTO hikes (hike_id, start_time, end_time, pictures, path) VALUES ({id}, {st}, {et}, {p}, '{pth}')".format(
id=row[0], st=row[3], et=row[4], p=row[5], pth=row[6])
cursor.execute(statement)
connection.commit()
hike_count += 1
print('Finished inserting {i} of {n} hikes from camera'.format(
i=hike_count, n=num_hikes))
def insert_pictures(connection: sqlite3.Connection,
cursor: sqlite3.Connection.cursor):
# Get number of pictures
cursor.execute("SELECT COUNT(*) FROM camera.pictures")
all_rows = cursor.fetchall()
num_pics = all_rows[0][0]
# Insert all pictures
cursor.execute("SELECT * FROM camera.pictures")
all_rows = cursor.fetchall()
picture_count = 0
count17 = 0
count18 = 0
count19 = 0
count20 = 0
old_id = 0
new_id = 0
for row in all_rows:
# print(row)
if row[2] == 17:
old_id = 17
new_id = 30
count17 += 1
elif row[2] == 18:
old_id = 18
new_id = 31
count18 += 1
elif row[2] == 19:
old_id = 19
new_id = 32
count19 += 1
elif row[2] == 20:
old_id = 20
new_id = 33
count20 += 1
path1 = change_cam_location(row[4], old_id, new_id)
path2 = change_cam_location(row[5], old_id, new_id)
path3 = change_cam_location(row[6], old_id, new_id)
statement = "INSERT INTO pictures (time, altitude, hike, index_in_hike, camera1, camera2, camera3, created_date_time, updated_date_time) VALUES ({t}, {a}, {h}, {i}, '{c1}', '{c2}', '{c3}', '{t1}', '{t2}')".format(
t=row[0],
a=row[1],
h=new_id,
i=row[3],
c1=path1,
c2=path2,
c3=path3,
t1=row[7],
t2=row[8])
cursor.execute(statement)
connection.commit()
picture_count += 1
print('Finished inserting {i} of {n} pictures from camera'.format(
i=picture_count, n=num_pics))
print(
'HIKE17 : {c17} | HIKE18 : {c18} | HIKE19 : {c19} | HIKE20 : {c20}'
.format(c17=count17, c18=count18, c19=count19, c20=count20))
def delete_pictures_hikes(connection: sqlite3.Connection,
cursor: sqlite3.Connection.cursor):
cursor.execute("DELETE FROM camera.pictures")
connection.commit()
cursor.execute("DELETE FROM camera.hikes")
connection.commit()
def get_status(cursor: sqlite3.Connection.cursor):
resultset = cursor.execute(
'SELECT * FROM internet_status ORDER BY id DESC LIMIT 1')
status_id, status, created_at = resultset.fetchone()
return (status, created_at)