def listSeriesByCategory(id):
id = str(id)
with dbConnect() as cursor:
cursor.execute(
"SELECT Name, Description FROM Series where Category LIKE ?",
("%" + id + ",%", ))
return cursor.fetchall()
def getCategoryIdByName(name):
id_list = []
for n in name:
with dbConnect() as cursor:
cursor.execute(
'SELECT CategoryId FROM Category where Description = ?', (n, ))
id_list.append(cursor.fetchone()[0])
return id_list
def addNewUser(data):
with dbConnect() as cursor:
cursor.execute(
'Insert INTO User(Username,Password, Role) Values(?,?,?)', (
data["Username"],
data["Password"],
data["Role"],
))
return cursor.fetchall()
def addNewSeries(data):
with dbConnect() as cursor:
cursor.execute(
'INSERT INTO Series(Name,Description,Category) '
'Values(?,?,?)', (
data["Name"],
data["Description"],
data["Category"],
))
print("Insert Successful")
return cursor.fetchone()
def addNewEpisode(data):
with dbConnect() as cursor:
cursor.execute(
'INSERT INTO Episodes(Name,Description,Series,EpisodeNumber,SeasonNumber,FileLocation,DateAdded) '
'Values(?,?,?,?,?,?,?)', (
data["Name"],
data["Description"],
data["Series"],
data["EpisodeNumber"],
data["SeasonNumber"],
data["FileLocation"],
data["DateAdded"],
))
return cursor.fetchall()
def listAllCategories():
with dbConnect() as cursor:
cursor.execute('SELECT CategoryId,Description FROM Category')
return cursor.fetchall()
def addNewCategory(data):
with dbConnect() as cursor:
cursor.execute("INSERT INTO Category(Description) Values(?)",
(data["Description"], ))
def getCategoryNameById(id):
with dbConnect() as cursor:
cursor.execute('SELECT Description FROM Category where CategoryId = ?',
(id, ))
return cursor.fetchone()
def getRoleIdFromName(name):
with dbConnect() as cursor:
cursor.execute('SELECT RoleId FROM Roles where Description = ?',
(name, ))
return cursor.fetchall()
def listAllRoles():
with dbConnect() as cursor:
cursor.execute('SELECT Description FROM Roles')
return cursor.fetchall()
def getSeriesIdByName(name):
with dbConnect() as cursor:
cursor.execute('SELECT SeriesId FROM Series where Name = ?', (name, ))
return cursor.fetchone()
def listAllSeries():
with dbConnect() as cursor:
cursor.execute('SELECT SeriesId, Name,Description FROM Series')
return cursor.fetchall()
def listAllUsers():
with dbConnect() as cursor:
cursor.execute('SELECT Username, Psssword FROM User')
return cursor.fetchall()
def listAllEpisodes():
with dbConnect() as cursor:
cursor.execute(
'SELECT Name, Description,EpisodeNumber, SeasonNumber, FileLocation FROM Episodes'
)
return cursor.fetchall()
def getEpisodeByEpisodeId(id):
with dbConnect() as cursor:
cursor.execute(
"SELECT Name, Description,EpisodeNumber, SeasonNumber, FileLocation from Episodes WHERE EpisodeId = ?",
(id, ))
return cursor
def listAllEpisodesBySeries(series):
with dbConnect() as cursor:
cursor.execute(
"SELECT e.EpisodeId, e.Name, e.EpisodeNumber, e.SeasonNumber FROM Episodes e inner join Series s on s.SeriesId = e.Series where s.Name = ?",
(series, ))
return cursor.fetchall()
def listRecentEpisodes():
with dbConnect() as cursor:
cursor.execute(
'SELECT Name, Description,EpisodeNumber, SeasonNumber, FileLocation FROM Episodes ORDER BY DateAdded desc LIMIT 5'
)
return cursor.fetchall()
def VerifyUser(un, ps):
with dbConnect() as cursor:
cursor.execute(
'SELECT Username, Password, Role FROM User WHERE Username = ? and Password = ?',
(un, ps))
return cursor.fetchall()
