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)