if ( params is not None ): self.cursor.execute( sql , params )
else: self.cursor.execute( sql )
if ( all ): retval = self.cursor.fetchall()
else: retval = self.cursor.fetchone()
except:
retval = None
return retval
class Query( dict ):
"all sql statments. add as needed"
def __init__( self ):
#good sql statements
self[ "movie_by_movie_id" ] = "SELECT * FROM movies WHERE idMovie=?;"
self[ "studio_by_movie_id" ] = "SELECT studios.studio FROM studio_link_movie, studios WHERE studio_link_movie.idStudio = studios.idStudio AND studio_link_movie.idMovie=?;"
self[ "actors_by_movie_id" ] = "SELECT actors.actor FROM actor_link_movie, actors WHERE actor_link_movie.idActor = actors.idActor AND actor_link_movie.idMovie=? ORDER BY actors.actor;"
self[ "movies_by_genre_id" ] = "SELECT movies.* FROM movies, genre_link_movie WHERE genre_link_movie.idMovie=movies.idMovie AND genre_link_movie.idGenre=? ORDER BY movies.title;"
self[ "movies_by_studio_id" ] = "SELECT movies.* FROM movies, studio_link_movie WHERE studio_link_movie.idMovie=movies.idMovie AND studio_link_movie.idStudio=? ORDER BY movies.title;"
self[ "movies_by_actor_id" ] = "SELECT movies.* FROM movies, actor_link_movie WHERE actor_link_movie.idMovie=movies.idMovie AND actor_link_movie.idActor=? ORDER BY movies.title;"
self[ "movies_by_genre_name" ] = "SELECT movies.* FROM movies, genres, genre_link_movie WHERE genre_link_movie.idGenre=genres.idGenre AND genre_link_movie.idMovie=movies.idMovie AND genres.genre=? ORDER BY movies.title;"
self[ "movies_by_studio_name" ]= "SELECT movies.* FROM movies, studios, studio_link_movie WHERE studio_link_movie.idStudio=studios.idStudio AND studio_link_movie.idMovie=movies.idMovie AND studios.studio LIKE ? ORDER BY movies.title;"
self[ "movies_by_actor_name" ] = "SELECT movies.* FROM movies, actors, actor_link_movie WHERE actor_link_movie.idActor=actors.idActor AND actor_link_movie.idMovie=movies.idMovie AND actors.actor LIKE ? ORDER BY movies.title;"
self[ "incomplete_movies" ] = "SELECT * FROM movies WHERE trailer_urls IS NULL ORDER BY title;"
self[ "version" ] = "SELECT * FROM version;"
self[ "genre_category_list" ] = "SELECT genres.idGenre, genres.genre, count(genre_link_movie.idGenre), count(movies.favorite) FROM genre_link_movie, genres, movies WHERE genre_link_movie.idGenre=genres.idGenre AND genre_link_movie.idMovie=movies.idMovie GROUP BY genres.genre;"
self[ "studio_category_list" ] = "SELECT studios.idStudio, studios.studio, count(studio_link_movie.idStudio), count(studio_link_movie.idStudio) FROM studio_link_movie, studios WHERE studio_link_movie.idStudio=studios.idStudio GROUP BY upper(studios.studio);"
self[ "actor_category_list" ] = "SELECT actors.idActor, actors.actor, count(actor_link_movie.idActor), count(actor_link_movie.idActor) FROM actor_link_movie, actors WHERE actor_link_movie.idActor=actors.idActor GROUP BY upper(actors.actor);"
self[ "rating_category_list" ] = "SELECT rating, count(rating) FROM movies WHERE rating IS NOT NULL AND rating!='' GROUP BY rating;"
self[ "genre_table_list" ] = "SELECT idGenre, genre, updated FROM genres ORDER BY genre;"
self[ "genre_urls_by_genre_id" ] = "SELECT urls FROM genres WHERE idGenre=?;"
self[ "idMovie_by_genre_id" ] = "SELECT idMovie FROM genre_link_movie WHERE idGenre=?;"
self[ "idMovie_in_genre" ] = "SELECT * FROM genre_link_movie WHERE idGenre=? AND idMovie=?;"
self[ "movie_exists" ] = "SELECT idMovie, urls, date_added FROM movies WHERE title LIKE ?;"
self[ "actor_exists" ] = "SELECT idActor FROM actors WHERE actor LIKE ?;"
self[ "studio_exists" ] = "SELECT idStudio FROM studios WHERE studio LIKE ?;"
self[ "favorites" ] = "SELECT * FROM movies WHERE favorite=? ORDER BY title;"
self[ "downloaded" ] = "SELECT * FROM movies WHERE saved_location!=? ORDER BY title;"
self[ "watched" ] = "SELECT * FROM movies WHERE times_watched>? ORDER BY title;"
self[ "recently_added" ] = "SELECT * FROM movies ORDER BY date_added DESC, title LIMIT 30;"
self[ "multiple_trailers" ] = "SELECT * FROM movies WHERE trailer_urls LIKE '%%), (%%' ORDER BY title;"
self[ "hd_trailers" ] = "SELECT * FROM movies WHERE trailer_urls LIKE ? ORDER BY title;"
self[ "no_trailer_urls" ] = "SELECT * FROM movies WHERE (trailer_urls=? OR trailer_urls IS NULL) AND poster IS NOT NULL ORDER BY title;"