def __commit(self, statement, values): db = sqlite3.connect(self.DB_NAME) cursor = db.cursor() committed = cursor.execute(statement, values) db.commit() printer.pretty_print("committed: " + str(statement)) return committed
def photos_for_album(self, album_name): album_id = self.album_id_for_name(album_name) if album_id: select = 'SELECT * from ' + self.PHOTOS_TABLE + ' LEFT JOIN ' + self.PHOTOSALBUMS_TABLE + ' pa on pa.photoID= ' + self.PHOTOS_TABLE +'.id' + ' WHERE pa.albumID=' + str(album_id) printer.pretty_print_positive(select) photos = self.__execute(select) return photos.fetchall()
def add_photo_to_album(self, photo_id, album_name): album_id = self.album_id_for_name(album_name) if album_id: # update_statement = 'INSERT ' + self.PHOTOSALBUMS_TABLE + ' SET albumID=? WHERE photoID=?' insert_statement = self.insert_photosalbums_statement() next_id = self.number_of_photosalbums() + 1 values = (next_id, photo_id, album_id) printer.pretty_print_positive(insert_statement) printer.pretty_print_positive(str(values)) self.__commit(insert_statement, values) return True else: self.insert_new_album(album_name) self.add_photo_to_album(photo_id, album_name) return False
def photos_for_album(self, album_name): album_id = self.album_id_for_name(album_name) if album_id: select = ( "SELECT * from " + self.PHOTOS_TABLE + " LEFT JOIN " + self.PHOTOSALBUMS_TABLE + " pa on pa.photoID= " + self.PHOTOS_TABLE + ".id" + " WHERE pa.albumID=" + str(album_id) ) printer.pretty_print_positive(select) photos = self.__execute(select) return photos.fetchall()
def __init__(self): db = sqlite3.connect(self.DB_NAME) cursor = db.cursor() create_PHOTOS_TABLE_command = Kensing.create_table_command_with_properites_and_qualifiers(self.PHOTOS_TABLE, self.photo_properties, self.photo_qualifiers) create_ALBUMS_TABLE_command = Kensing.create_table_command_with_properites_and_qualifiers(self.ALBUMS_TABLE, self.album_properties, self.album_qualifiers) create_PHOTOSALBUMS_TABLE_command = Kensing.create_table_command_with_properites_and_qualifiers(self.PHOTOSALBUMS_TABLE, self.photosalbums_properties, self.photosalbums_qualifiers) printer.pretty_print('Creating Photos Table with command: ' + create_PHOTOS_TABLE_command) printer.pretty_print('Creating Albums Table with command: ' + create_ALBUMS_TABLE_command) printer.pretty_print('Creating PHOTOSAlbums Table with command: ' + create_PHOTOSALBUMS_TABLE_command) cursor.execute(create_PHOTOS_TABLE_command) cursor.execute(create_ALBUMS_TABLE_command) cursor.execute(create_PHOTOSALBUMS_TABLE_command) db.commit()
def insert_new_photo(self, photo_data, photo_date, rating=0, album_name=None): insert = self.insert_photos_statement() album_id = self.album_id_for_name(album_name) printer.pretty_print(insert) data_hash = photo_data.__hash__() photo_id = self.number_of_photos() + 1 many_to_many = self.insert_photosalbums_statement() printer.pretty_print_positive(many_to_many) try: if album_id: self.__commit(many_to_many, (photo_id, album_id)) self.__commit(insert, (photo_id, photo_data, str(photo_date), data_hash, rating)) return True except Exception, e: printer.pretty_print_error(str(e)) return False
def __init__(self): db = sqlite3.connect(self.DB_NAME) cursor = db.cursor() create_PHOTOS_TABLE_command = Kensing.create_table_command_with_properites_and_qualifiers( self.PHOTOS_TABLE, self.photo_properties, self.photo_qualifiers ) create_ALBUMS_TABLE_command = Kensing.create_table_command_with_properites_and_qualifiers( self.ALBUMS_TABLE, self.album_properties, self.album_qualifiers ) create_PHOTOSALBUMS_TABLE_command = Kensing.create_table_command_with_properites_and_qualifiers( self.PHOTOSALBUMS_TABLE, self.photosalbums_properties, self.photosalbums_qualifiers ) printer.pretty_print("Creating Photos Table with command: " + create_PHOTOS_TABLE_command) printer.pretty_print("Creating Albums Table with command: " + create_ALBUMS_TABLE_command) printer.pretty_print("Creating PHOTOSAlbums Table with command: " + create_PHOTOSALBUMS_TABLE_command) cursor.execute(create_PHOTOS_TABLE_command) cursor.execute(create_ALBUMS_TABLE_command) cursor.execute(create_PHOTOSALBUMS_TABLE_command) db.commit()
def __execute(self, statement): db = sqlite3.connect(self.DB_NAME) cursor = db.cursor() executed = cursor.execute(statement) printer.pretty_print("Executed: " + str(statement)) return executed