def get_tracks_by_artist(artist):
db = DatabaseConnector()
db.execute(_CREATE_MUSIC)
query = "SELECT * FROM music WHERE artist='" + artist + "'"
tracks = db.execute(query)
db.close_connection()
return tracks
def get_tracks_from_album_by_artist(album, artist):
db = DatabaseConnector()
db.execute(_CREATE_MUSIC)
query = "SELECT * FROM music WHERE artist='" + artist + "' AND album='" + album + "' ORDER BY track_number ASC"
tracks = db.execute(query)
db.close_connection()
return tracks
def get_tracks_from_directory(directory):
db = DatabaseConnector()
db.execute(_CREATE_MUSIC)
query = "SELECT * FROM music WHERE directory='" + directory + "'"
tracks = db.execute(query)
db.close_connection()
return tracks
def get_albums_by_artist(artist):
db = DatabaseConnector()
db.execute(_CREATE_MUSIC)
query = "SELECT DISTINCT albums FROM music WHERE artist='" + artist + "'"
albums = db.execute(query)
db.close_connection()
return albums
def get_genres():
db = DatabaseConnector()
db.execute(_CREATE_MUSIC)
query = "SELECT DISTINCT genres FROM music"
genres = db.execute(query)
db.close_connection()
return genres
def get_all_tracks():
db = DatabaseConnector()
db.execute(_CREATE_MUSIC)
query = "SELECT * FROM music"
tracks = db.execute(query)
db.close_connection()
return tracks
def get_tracks_from_album_by_artist(album, artist):
db = DatabaseConnector()
db.execute(_CREATE_MUSIC)
query = "SELECT * FROM music WHERE artist='" + artist + "' AND album='" + album + "' ORDER BY track_number ASC"
tracks = db.execute(query)
db.close_connection()
return tracks
def get_genres():
db = DatabaseConnector()
db.execute(_CREATE_MUSIC)
query = "SELECT DISTINCT genres FROM music"
genres = db.execute(query)
db.close_connection()
return genres
def insert_cover(album, path):
db = DatabaseConnector()
db.execute(_CREATE_MUSIC_COVERS)
query = "INSERT OR IGNORE INTO music_covers (album, path) VALUES (?, ?)"
db.execute(query, (album, path,))
db.commit()
db.close_connection()
def get_all_ebooks():
db = DatabaseConnector()
db.execute(_CREATE_EBOOKS)
query = "SELECT * FROM ebooks"
ebooks = db.execute(query)
db.close_connection()
return ebooks
def insert_movie(path,
name,
category=None,
genre=None,
year=None,
cover_path=None,
director=None):
db = DatabaseConnector()
db.execute(_CREATE_MOVIES)
added_on = db.generate_timestamp()
if name is None:
name = os.path.splitext(os.path.split(path)[-1])[0]
query = "INSERT OR IGNORE INTO movies (path, category, name, cover_path, \
director, genre, year, added_on) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
db.execute(query, (
path,
category,
name,
cover_path,
director,
genre,
year,
added_on,
))
db.commit()
db.close_connection()
def get_authors():
db = DatabaseConnector()
db.execute(_CREATE_AUDIOBOOKS)
query = "SELECT DISTINCT author FROM audiobooks"
authors = db.execute(query)
db.close_connection()
return authors
def get_all_ebooks():
db = DatabaseConnector()
db.execute(_CREATE_EBOOKS)
query = "SELECT * FROM ebooks"
ebooks = db.execute(query)
db.close_connection()
return ebooks
def get_authors():
db = DatabaseConnector()
db.execute(_CREATE_AUDIOBOOKS)
query = "SELECT DISTINCT author FROM audiobooks"
authors = db.execute(query)
db.close_connection()
return authors
def get_directors():
db = DatabaseConnector()
db.execute(_CREATE_MOVIES)
query = "SELECT DISTINCT director FROM movies"
directors = db.execute(query)
db.close_connection()
return directors
def get_movies_by_director(director):
db = DatabaseConnector()
db.execute(_CREATE_MOVIES)
query = "SELECT * FROM movies WHERE director='" + director + "'"
movies = db.execute(query)
db.close_connection()
return movies
def get_movies_from_genre(genre):
db = DatabaseConnector()
db.execute(_CREATE_MOVIES)
query = "SELECT * FROM movies WHERE genre='" + genre + "'"
movies = db.execute(query)
db.close_connection()
return movies
def get_genres():
db = DatabaseConnector()
db.execute(_CREATE_MOVIES)
query = "SELECT DISTINCT genre FROM movies"
genres = db.execute(query)
db.close_connection()
return genres
def get_all_movies():
db = DatabaseConnector()
db.execute(_CREATE_MOVIES)
query = "SELECT * FROM movies"
movies = db.execute(query)
db.close_connection()
return movies
def get_audiobooks_by_author(author):
db = DatabaseConnector()
db.execute(_CREATE_AUDIOBOOKS)
query = "SELECT * FROM audiobooks WHERE author='" + author + "'"
audiobooks = db.execute(query)
db.close_connection()
return audiobooks
def get_movies_by_director(director):
db = DatabaseConnector()
db.execute(_CREATE_MOVIES)
query = "SELECT * FROM movies WHERE director='" + director + "'"
movies = db.execute(query)
db.close_connection()
return movies
def get_tracks_by_artist(artist):
db = DatabaseConnector()
db.execute(_CREATE_MUSIC)
query = "SELECT * FROM music WHERE artist='" + artist + "'"
tracks = db.execute(query)
db.close_connection()
return tracks
def get_all_movies():
db = DatabaseConnector()
db.execute(_CREATE_MOVIES)
query = "SELECT * FROM movies"
movies = db.execute(query)
db.close_connection()
return movies
def remove_from_favourite_music(path):
db = DatabaseConnector()
db.execute(_CREATE_FAVOURITE_MUSIC)
query = "DELETE FROM favourite_music WHERE path='" + path + "'"
db.execute(query)
db.commit()
db.close_connection()
def get_movies_from_genre(genre):
db = DatabaseConnector()
db.execute(_CREATE_MOVIES)
query = "SELECT * FROM movies WHERE genre='" + genre + "'"
movies = db.execute(query)
db.close_connection()
return movies
def get_genres():
db = DatabaseConnector()
db.execute(_CREATE_MOVIES)
query = "SELECT DISTINCT genre FROM movies"
genres = db.execute(query)
db.close_connection()
return genres
def remove_from_favourite_music(path):
db = DatabaseConnector()
db.execute(_CREATE_FAVOURITE_MUSIC)
query = "DELETE FROM favourite_music WHERE path='" + path + "'"
db.execute(query)
db.commit()
db.close_connection()
def get_tracks_from_directory(directory):
db = DatabaseConnector()
db.execute(_CREATE_MUSIC)
query = "SELECT * FROM music WHERE directory='" + directory + "'"
tracks = db.execute(query)
db.close_connection()
return tracks
def get_albums_by_artist(artist):
db = DatabaseConnector()
db.execute(_CREATE_MUSIC)
query = "SELECT DISTINCT albums FROM music WHERE artist='" + artist + "'"
albums = db.execute(query)
db.close_connection()
return albums
def get_directors():
db = DatabaseConnector()
db.execute(_CREATE_MOVIES)
query = "SELECT DISTINCT director FROM movies"
directors = db.execute(query)
db.close_connection()
return directors
def get_tracks_from_genre(genre):
db = DatabaseConnector()
db.execute(_CREATE_MUSIC)
query = "SELECT * FROM music WHERE genre='" + genre + "'"
tracks = db.execute(query)
db.close_connection()
return tracks
def get_tracks_from_genre(genre):
db = DatabaseConnector()
db.execute(_CREATE_MUSIC)
query = "SELECT * FROM music WHERE genre='" + genre + "'"
tracks = db.execute(query)
db.close_connection()
return tracks
def get_all_tracks():
db = DatabaseConnector()
db.execute(_CREATE_MUSIC)
query = "SELECT * FROM music"
tracks = db.execute(query)
db.close_connection()
return tracks
def get_audiobooks_by_author(author):
db = DatabaseConnector()
db.execute(_CREATE_AUDIOBOOKS)
query = "SELECT * FROM audiobooks WHERE author='" + author + "'"
audiobooks = db.execute(query)
db.close_connection()
return audiobooks
def get_cover(album):
db = DatabaseConnector()
db.execute(_CREATE_MUSIC_COVERS)
query = "SELECT path FROM music_covers WHERE album='" + album + "'"
cover = db.execute(query)
db.close_connection()
if cover:
return cover[0]["path"]
def get_cover(album):
db = DatabaseConnector()
db.execute(_CREATE_MUSIC_COVERS)
query = "SELECT path FROM music_covers WHERE album='" + album + "'"
cover = db.execute(query)
db.close_connection()
if cover:
return cover[0]["path"]
def get_favourite_music():
db = DatabaseConnector()
db.execute(_CREATE_FAVOURITE_MUSIC)
db.execute(_CREATE_MUSIC)
query = "SELECT favs.id, favs.path, year, genre, artist, album, track_number, title, directory, added_on \
FROM favourite_music AS favs JOIN music ON favs.path=music.path ORDER BY favs.id DESC"
favourite_music = db.execute(query)
db.close_connection()
return favourite_music
def is_in_favourite_music(path):
db = DatabaseConnector()
db.execute(_CREATE_FAVOURITE_MUSIC)
query = "SELECT * FROM favourite_music WHERE path='" + path + "'"
favourite_music = db.execute(query)
db.close_connection()
if favourite_music:
return True
else:
return False
def insert_to_favourite_music(path):
if is_in_favourite_music(path):
return False
db = DatabaseConnector()
db.execute(_CREATE_FAVOURITE_MUSIC)
query = "INSERT OR IGNORE INTO favourite_music (path) VALUES (?)"
db.execute(query, (path,))
db.commit()
db.close_connection()
return True
def insert_to_favourite_music(path):
if is_in_favourite_music(path):
return False
db = DatabaseConnector()
db.execute(_CREATE_FAVOURITE_MUSIC)
query = "INSERT OR IGNORE INTO favourite_music (path) VALUES (?)"
db.execute(query, (path, ))
db.commit()
db.close_connection()
return True
def is_in_favourite_music(path):
db = DatabaseConnector()
db.execute(_CREATE_FAVOURITE_MUSIC)
query = "SELECT * FROM favourite_music WHERE path='" + path + "'"
favourite_music = db.execute(query)
db.close_connection()
if favourite_music:
return True
else:
return False
def get_favourite_music():
db = DatabaseConnector()
db.execute(_CREATE_FAVOURITE_MUSIC)
db.execute(_CREATE_MUSIC)
query = "SELECT favs.id, favs.path, year, genre, artist, album, track_number, title, directory, added_on \
FROM favourite_music AS favs JOIN music ON favs.path=music.path ORDER BY favs.id DESC"
favourite_music = db.execute(query)
db.close_connection()
return favourite_music
def insert_cover(album, path):
db = DatabaseConnector()
db.execute(_CREATE_MUSIC_COVERS)
query = "INSERT OR IGNORE INTO music_covers (album, path) VALUES (?, ?)"
db.execute(query, (
album,
path,
))
db.commit()
db.close_connection()
def insert_movie(path, name, category=None, genre=None, year=None, cover_path=None, director=None):
db = DatabaseConnector()
db.execute(_CREATE_MOVIES)
added_on = db.generate_timestamp()
if name is None:
name = os.path.splitext(os.path.split(path)[-1])[0]
query = "INSERT OR IGNORE INTO movies (path, category, name, cover_path, \
director, genre, year, added_on) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
db.execute(query, (path, category, name, cover_path, director, genre, year, added_on,))
db.commit()
db.close_connection()
def insert_audiobook(path, category=None, name=None, author=None, year=None):
db = DatabaseConnector()
db.execute(_CREATE_AUDIOBOOKS)
added_on = db.generate_timestamp()
if name is None:
name = os.path.splitext(os.path.split(path)[-1])[0]
query = "INSERT OR IGNORE INTO audiobooks (path, category, name, author, \
year, added_on) VALUES (?, ?, ?, ?, ?, ?)"
db.execute(query, (path, category, name, author, year, added_on,))
db.commit()
db.close_connection()
def insert_track(path, directory):
values = _get_metadata(path)
if not values:
return False
db = DatabaseConnector()
db.execute(_CREATE_MUSIC)
values.append(path)
values.append(directory)
values.append(db.generate_timestamp())
query = "INSERT OR IGNORE INTO music (year, genre, artist, album, track_number, \
title, path, directory, added_on) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"
db.execute(query, values)
db.commit()
db.close_connection()
def insert_many_tracks(tracks_list):
db = DatabaseConnector()
db.execute(_CREATE_MUSIC)
added_on = db.generate_timestamp()
for idx, track in enumerate(tracks_list):
values = _get_metadata(track[0]) # path as the first item
if not values:
continue
tracks_list[idx] = values + [track[0], track[1], added_on]
query = "INSERT OR IGNORE INTO music (year, genre, artist, album, track_number, \
title, path, directory, added_on) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"
db.executemany(query, tracks_list)
db.commit()
db.close_connection()
def insert_many_tracks(tracks_list):
db = DatabaseConnector()
db.execute(_CREATE_MUSIC)
added_on = db.generate_timestamp()
for idx, track in enumerate(tracks_list):
values = _get_metadata(track[0]) # path as the first item
if not values:
continue
tracks_list[idx] = values + [track[0], track[1], added_on]
query = "INSERT OR IGNORE INTO music (year, genre, artist, album, track_number, \
title, path, directory, added_on) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"
db.executemany(query, tracks_list)
db.commit()
db.close_connection()
def insert_track(path, directory):
values = _get_metadata(path)
if not values:
return False
db = DatabaseConnector()
db.execute(_CREATE_MUSIC)
values.append(path)
values.append(directory)
values.append(db.generate_timestamp())
query = "INSERT OR IGNORE INTO music (year, genre, artist, album, track_number, \
title, path, directory, added_on) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"
db.execute(query, values)
db.commit()
db.close_connection()
def insert_audiobook(path, category=None, name=None, author=None, year=None):
db = DatabaseConnector()
db.execute(_CREATE_AUDIOBOOKS)
added_on = db.generate_timestamp()
if name is None:
name = os.path.splitext(os.path.split(path)[-1])[0]
query = "INSERT OR IGNORE INTO audiobooks (path, category, name, author, \
year, added_on) VALUES (?, ?, ?, ?, ?, ?)"
db.execute(query, (
path,
category,
name,
author,
year,
added_on,
))
db.commit()
db.close_connection()