def __init__(self):
self.connection = ConnectionSQL()
class SqlServerAccountRepository(AccountRepository):
def __init__(self):
self.connection = ConnectionSQL()
def save(self, account: Account):
self.connection.open()
sql = """
DECLARE @return_value int,
@salida nvarchar(1000),
@estado int
EXEC @return_value = [dbo].[SPI_CreateAccount]
@IdAccount = ?,
@firstName = ?,
@lastName = ?,
@email = ?,
@password = ?,
@userName = ?,
@gender = ?,
@birthday = ?,
@cover = ?,
@created = ?,
@updated = ?,
@contentCreator = ?,
@typeRegister = ?,
@salida = @salida OUTPUT,
@estado = @estado OUTPUT
SELECT @salida as N'@salida',
@estado as N'@estado'
"""
params = (account.idAccount, account.firstName, account.lastName,
account.email, account.password, account.userName,
account.gender, account.birthday, account.cover,
account.createdDate, None, account.contentCreator,
account.typeRegister)
self.connection.cursor.execute(sql, params)
try:
self.connection.save()
print(self.connection.cursor.rowcount, " Account created")
self.connection.close()
return True
except DataBaseException as ex:
raise DataBaseException("Error en la conexión a la BD")
def update(self, account: Account):
self.connection.open()
sql = """
DECLARE @return_value int,
@salida nvarchar(1000),
@estado int
EXEC @return_value = [dbo].[SPU_UpdateAccount]
@idAccount = ?,
@firstName = ?,
@lastName = ?,
@userName = ?,
@birthday = ?,
@cover = ?,
@updated = ?,
@salida = @salida OUTPUT,
@estado = @estado OUTPUT
"""
params = (account.idAccount, account.firstName, account.lastName,
account.userName, account.birthday, account.cover,
account.updatedDate)
self.connection.cursor.execute(sql, params)
try:
self.connection.save()
print(self.connection.cursor.rowcount, " Accounts updated")
self.connection.close()
return True
except DataBaseException as identifier:
raise DataBaseException("Error en la conexión a la BD")
def delete(self, accountId: str):
self.connection.open()
sql = """
DECLARE @return_value int,
@estado int,
@salida nvarchar(1000)
EXEC @return_value = [dbo].[SPD_DeleteAccount]
@idAccount = ?,
@estado = @estado OUTPUT,
@salida = @salida OUTPUT
"""
params = (accountId)
self.connection.cursor.execute(sql, params)
row = self.connection.cursor.rowcount
print(row)
if row == -1:
raise DataBaseException("Error deleting account")
try:
self.connection.save()
self.connection.close()
return True
except DataBaseException as ex:
raise DataBaseException("Error al actualizar la Base de datos: ",
ex)
def exist_account(self, idAccount: str):
sql = """\
DECLARE @return_value int,
@estado int,
@salida nvarchar(1000)
EXEC @return_value = [dbo].[SPS_AccountExist]
@idAccount = ?,
@estado = @estado OUTPUT,
@salida = @salida OUTPUT
SELECT @estado as N'@estado',
@salida as N'@salida'
"""
try:
self.connection.open()
self.connection.cursor.execute(sql, idAccount)
except Exception as ex:
raise DataBaseException("Database connection error")
row = self.connection.cursor.fetchval()
result = False
if row == -1:
result = False
else:
result = True
self.connection.close()
return result
def exist_email(self, email: str):
self.connection.open()
sql = """\
DECLARE @return_value int,
@estado int,
@salida nvarchar(1000)
EXEC @return_value = [dbo].[SPS_EmailExist]
@email = ?,
@estado = @estado OUTPUT,
@salida = @salida OUTPUT
SELECT @estado as N'@estado',
@salida as N'@salida'
"""
self.connection.cursor.execute(sql, email)
row = self.connection.cursor.fetchval()
result = False
if row == -1:
result = False
else:
result = True
self.connection.close()
return result
def exist_userName(self, userName: str):
self.connection.open()
sql = """\
DECLARE @return_value int,
@estado int,
@salida nvarchar(1000)
EXEC @return_value = [dbo].[SPS_UserNameExist]
@userName = ?,
@estado = @estado OUTPUT,
@salida = @salida OUTPUT
SELECT @estado as N'@estado',
@salida as N'@salida'
"""
self.connection.cursor.execute(sql, userName)
row = self.connection.cursor.fetchval()
result = False
if row == -1:
result = False
else:
result = True
self.connection.close()
return result
def exist_userName(self, userName: str):
self.connection.open()
sql = """\
DECLARE @return_value int,
@estado int,
@salida nvarchar(1000)
EXEC @return_value = [dbo].[SPS_UserNameExist]
@userName = ?,
@estado = @estado OUTPUT,
@salida = @salida OUTPUT
SELECT @estado as N'@estado',
@salida as N'@salida'
"""
self.connection.cursor.execute(sql, userName)
row = self.connection.cursor.fetchval()
result = False
if row == -1:
result = False
else:
result = True
self.connection.close()
return result
def login_with_username(self, username, password):
self.connection.open()
sql = """\
DECLARE @return_value int,
@estado int,
@salida nvarchar(1000)
EXEC @return_value = [dbo].[SPS_LoginWithUsername]
@username = ?,
@password = ?,
@estado = @estado OUTPUT,
@salida = @salida OUTPUT
SELECT @estado as N'@estado',
@salida as N'@salida'
"""
self.connection.cursor.execute(sql, username, password)
rows = self.connection.cursor.fetchall()
account = None
if rows:
for row in rows:
account = Account(row.IdAccount, row.FirstName, row.LastName,
row.Email, None, row.UserName, row.Gender,
row.Birthday.strftime('%Y-%m-%d'), row.Cover,
row.CreatedDate.strftime('%Y-%m-%d'), None,
row.ContentCreator, None)
if row.UpdatedDate:
account.updatedDate = row.UpdatedDate.strftime('%Y-%m-%d')
self.connection.close()
return account
def login_with_email(self, email, password):
self.connection.open()
sql = """\
DECLARE @return_value int,
@estado int,
@salida nvarchar(1000)
EXEC @return_value = [dbo].[SPS_LoginWithEmail]
@email = ?,
@password = ?,
@estado = @estado OUTPUT,
@salida = @salida OUTPUT
SELECT @estado as N'@estado',
@salida as N'@salida'
"""
self.connection.cursor.execute(sql, email, password)
rows = self.connection.cursor.fetchall()
account = None
if rows:
for row in rows:
account = Account(row.IdAccount, row.FirstName, row.LastName,
row.Email, None, row.UserName, row.Gender,
row.Birthday.strftime('%Y-%m-%d'), row.Cover,
row.CreatedDate.strftime('%Y-%m-%d'), None,
row.ContentCreator, None)
if row.UpdatedDate:
account.updatedDate = row.UpdatedDate.strftime('%Y-%m-%d')
self.connection.close()
return account
class SqlServerArtistRepository(ArtistRepository):
def __init__(self):
self.connection = ConnectionSQL()
def save(self, artist: Artist):
try:
self.connection.open()
sql = """
DECLARE @return_value int,
@salida nvarchar(1000),
@estado int
EXEC @return_value = [dbo].[SPI_CreateArtist]
@IdArtist = ?,
@Name = ?,
@Cover = ?,
@RegisterDate = ?,
@Description = ?,
@IdAccount = ?,
@salida = @salida OUTPUT,
@estado = @estado OUTPUT
SELECT @salida as N'@salida',
@estado as N'@estado'
"""
params = (artist.idArtist, artist.name, artist.cover,
artist.registerDate, artist.description,
artist.account.idAccount)
self.connection.cursor.execute(sql, params)
self.connection.save()
print(self.connection.cursor.rowcount, "Artist created")
self.connection.close()
return True
except Exception as ex:
raise DataBaseException("Data base connection error")
def update(self, artist: Artist):
self.connection.open()
sql = """
DECLARE @return_value int,
@salida nvarchar(1000),
@estado int
EXEC @return_value = [dbo].[SPU_UpdateArtist]
@IdArtist = ?,
@Name = ?,
@Cover = ?,
@Description = ?,
@salida = @salida OUTPUT,
@estado = @estado OUTPUT
"""
print(artist.description)
params = (artist.idArtist, artist.name, artist.cover,
artist.description)
self.connection.cursor.execute(sql, params)
try:
self.connection.save()
print(self.connection.cursor.rowcount, " Artist updated")
self.connection.close()
return True
except DataBaseException as ex:
raise DataBaseException("Database connection error" + ex)
def delete(self, artistId: str):
self.connection.open()
sql = """
DECLARE @return_value int,
@estado int,
@salida nvarchar(1000)
EXEC @return_value = [dbo].[SPD_DeleteArtist]
@idArtist = ?,
@estado = @estado OUTPUT,
@salida = @salida OUTPUT
"""
params = (artistId)
self.connection.cursor.execute(sql, params)
row = self.connection.cursor.rowcount
print(row)
if row == -1:
raise DataBaseException("Error deleting artist")
try:
self.connection.save()
self.connection.close()
return True
except DataBaseException as ex:
raise DataBaseException("Database connection error ", ex)
def exist_artist(self, idArtist: str):
self.connection.open()
sql = """\
DECLARE @return_value int,
@estado int,
@salida nvarchar(1000)
EXEC @return_value = [dbo].[SPS_ArtistExist]
@IdArtist = ?,
@estado = @estado OUTPUT,
@salida = @salida OUTPUT
SELECT @estado as N'@estado',
@salida as N'@salida'
"""
self.connection.cursor.execute(sql, idArtist)
row = self.connection.cursor.fetchval()
result = False
if row == -1:
result = False
else:
result = True
self.connection.close()
return result
def get_artists_like_of_account(self, idAccount: str):
self.connection.open()
sql = """\
SET NOCOUNT ON;
DECLARE @return_value int,
@estado int,
@salida nvarchar(1000)
EXEC @return_value = [dbo].[SPS_GetArtistsLikeOfAccount]
@idAccount = ?,
@estado = @estado OUTPUT,
@salida = @salida OUTPUT
SELECT @estado as N'@estado',
@salida as N'@salida'
"""
self.connection.cursor.execute(sql, idAccount)
rows = self.connection.cursor.fetchall()
list_artists = []
for row in rows:
artist = Artist(row.IdArtist, row.Name, row.Cover,
row.RegisterDate, row.Description)
list_artists.append(artist)
return list_artists
def search_artists(self, queryCriterion):
self.connection.open()
sql = """
SELECT * FROM Artists WHERE Name Like ? + '%'
"""
self.connection.cursor.execute(sql, queryCriterion)
rows = self.connection.cursor.fetchall()
if self.connection.cursor.rowcount != 0:
list_artists = []
for row in rows:
artist = Artist(row.IdArtist, row.Name, row.Cover,
row.RegisterDate, row.Description)
list_artists.append(artist)
return list_artists
return False
def get_account_artist(self, idAccount: str):
try:
self.connection.open()
sql = """\
DECLARE @return_value int,
@estado int,
@salida nvarchar(1000)
EXEC @return_value = [dbo].[SPS_GetArtistOfAccount]
@idAccount = ?,
@estado = @estado OUTPUT,
@salida = @salida OUTPUT
"""
self.connection.cursor.execute(sql, idAccount)
row = self.connection.cursor.fetchone()
if self.connection.cursor.rowcount != 0:
artist = Artist(row.IdArtist, row.Name, row.Cover,
row.RegisterDate, row.Description)
artist.account.idAccount = row.IdAccount
return artist
else:
return None
except Exception as ex:
raise DataBaseException("Database connection error ", ex)
finally:
self.connection.close()
class SqlServerPersonalTrackRepository(PersonalTrackRepository):
def __init__(self):
self.connection = ConnectionSQL()
def save(self, personalTrack:PersonalTrack):
self.connection.open()
sql = """
DECLARE @return_value int,
@salida nvarchar(1000),
@estado int
EXEC @return_value = [dbo].[SPI_CreatePersonalTrack]
@IdPersonalTrack = ?,
@Title = ?,
@Duration = ?,
@FileTrack = ?,
@Available = ?,
@Album = ?,
@Gender = ?,
@IdAccount = ?,
@salida = @salida OUTPUT,
@estado = @estado OUTPUT
"""
params = (personalTrack.idPersonalTrack,personalTrack.title,personalTrack.duration,personalTrack.fileTrack,personalTrack.available,
personalTrack.album,personalTrack.gender, personalTrack.account.idAccount)
try:
self.connection.cursor.execute(sql,params)
self.connection.save()
if self.connection.cursor.rowcount > 0:
print(self.connection.cursor.rowcount, "Personal track created")
return True
else:
raise DataBaseException("Error in the personal Track creation")
except Exception as ex:
raise ex
finally:
self.connection.close()
def update(self, personalTrack):
self.connection.open()
sql = """
DECLARE @return_value int,
@salida nvarchar(1000),
@estado int
EXEC @return_value = [dbo].[SPU_UpdatePersonalTrack]
@IdPersonalTrack = ?,
@Title = ?,
@Duration = ?,
@FileTrack = ?,
@Available = ?,
@Album = ?,
@Gender = ?,
@IdAccount = ?,
@salida = @salida OUTPUT,
@estado = @estado OUTPUT
"""
params = (personalTrack.idPersonalTrack,personalTrack.title,personalTrack.duration,personalTrack.fileTrack,personalTrack.available,
personalTrack.album,personalTrack.gender, personalTrack.account.idAccount)
print(params)
try:
self.connection.cursor.execute(sql,params)
self.connection.save()
if self.connection.cursor.rowcount > 0:
print(self.connection.cursor.rowcount, "Personal track has been updated")
return True
else:
raise DataBaseException("Error in the personal Track update")
except Exception as ex:
raise ex
finally:
self.connection.close()
def get_personal_tracks_account(self, idAccount):
self.connection.open()
sql = """
SELECT * FROM PersonalTracks
WHERE IdAccount = ?
"""
try:
self.connection.cursor.execute(sql, idAccount)
list_personaltracks = []
rows = self.connection.cursor.fetchall()
for row in rows:
personal_track = PersonalTrack(row.IdPersonalTrack,row.Title, row.Gender,
row.Album, row.Duration,row.FileTrack, row.Available)
personal_track.account.idAccount = row.IdAccount
list_personaltracks.append(personal_track)
return list_personaltracks
except Exception as ex:
print(ex)
return ex
finally:
self.connection.close()
class SqlServerAlbumRepository(AlbumRepository):
def __init__(self):
self.connection = ConnectionSQL()
def save(self, album: Album):
self.connection.open()
sql = """
DECLARE @return_value int,
@salida nvarchar(1000),
@estado int
EXEC @return_value = [dbo].[SPI_CreateAlbum]
@IdAlbum = ?,
@Title = ?,
@Cover = ?,
@Publication = ?,
@RecordCompany = ?,
@IdMusicGender = ?,
@IdAlbumType = ?,
@IdArtist = ?,
@salida = @salida OUTPUT,
@estado = @estado OUTPUT
SELECT @salida as N'@salida',
@estado as N'@estado'
"""
params = (album.idAlbum, album.title, album.cover, album.publication,
album.recordCompany, album.musicGender.idMusicGender,
album.idAlbumType, album.artist.idArtist)
self.connection.cursor.execute(sql, params)
print("Album creado")
try:
self.connection.save()
print(self.connection.cursor.rowcount, "Album created")
self.connection.close()
return True
except DataBaseException as ex:
print(str(ex))
raise DataBaseException("Database error")
def exists_album_gender(self, idAlbumGender: str):
self.connection.open()
sql = """\
DECLARE @return_value int,
@estado int,
@salida nvarchar(1000)
EXEC @return_value = [dbo].[SPS_AlbumGenderExists]
@idMusicGender = ?,
@estado = @estado OUTPUT,
@salida = @salida OUTPUT
SELECT @estado as N'@estado',
@salida as N'@salida'
"""
self.connection.cursor.execute(sql, idAlbumGender)
row = self.connection.cursor.fetchval()
result = False
if row == -1:
result = False
else:
result = True
self.connection.close()
return result
def exists_album_type(self, idAlbumType: str):
self.connection.open()
sql = """\
DECLARE @return_value int,
@estado int,
@salida nvarchar(1000)
EXEC @return_value = [dbo].[SPS_AlbumTypeExist]
@idAlbumType = ?,
@estado = @estado OUTPUT,
@salida = @salida OUTPUT
SELECT @estado as N'@estado',
@salida as N'@salida'
"""
self.connection.cursor.execute(sql, idAlbumType)
row = self.connection.cursor.fetchval()
result = False
if row == -1:
result = False
else:
result = True
self.connection.close()
return result
def exists_album(self, idAlbum: str):
self.connection.open()
sql = """\
DECLARE @return_value int,
@estado int,
@salida nvarchar(1000)
EXEC @return_value = [dbo].[SPS_AlbumExists]
@IdAlbum = ?,
@estado = @estado OUTPUT,
@salida = @salida OUTPUT
SELECT @estado as N'@estado',
@salida as N'@salida'
"""
self.connection.cursor.execute(sql, idAlbum)
row = self.connection.cursor.fetchval()
result = False
if row == -1:
result = False
else:
result = True
self.connection.close()
return result
def get_albums_of_artist(self, idArtist: str):
self.connection.open()
sql = """\
DECLARE @return_value int,
@estado int,
@salida nvarchar(1000)
EXEC @return_value = [dbo].[SPS_GetAlbumsOfArtist]
@IdArtist = ?,
@estado = @estado OUTPUT,
@salida = @salida OUTPUT
SELECT @estado as N'@estado',
@salida as N'@salida'
"""
self.connection.cursor.execute(sql, idArtist)
rows = self.connection.cursor.fetchall()
list_albums = []
for row in rows:
album = Album(row.IdAlbum, row.Title, row.AlbumCover,
row.Publication, row.RecordCompany, row.IdAlbumType)
album.artist.idArtist = row.IdArtist
album.artist.name = row.ArtistName
album.artist.registerDate = row.RegisterDate
album.artist.description = row.Description
album.artist.cover = row.ArtistCover
album.musicGender.idMusicGender = row.IdMusicGender
album.musicGender.genderName = row.GenderName
list_albums.append(album)
return list_albums
def get_albums_like_of_account(self, idAccount: str):
sql = """\
SET NOCOUNT ON;
DECLARE @return_value int,
@estado int,
@salida nvarchar(1000)
EXEC @return_value = [dbo].[SPS_GetAlbumsLikeOfAccount]
@idAccount = ?,
@estado = @estado OUTPUT,
@salida = @salida OUTPUT
SELECT @estado as N'@estado',
@salida as N'@salida'
"""
try:
self.connection.open()
self.connection.cursor.execute(sql, idAccount)
pass
except DataBaseException as ex:
raise ("Database connection error")
rows = self.connection.cursor.fetchall()
list_albums = []
for row in rows:
album = Album(row.IdAlbum, row.Title, row.Cover, row.Publication,
row.RecordCompany, row.IdAlbumType)
album.musicGender.idMusicGender = row.IdMusicGender
album.musicGender.genderName = row.GenderName
album.artist.name = row.ArtistName
album.artist.cover = row.ArtistCover
album.artist.description = row.Description
album.artist.registerDate = row.RegisterDate
album.artist.idArtist = row.IdArtist
list_albums.append(album)
return list_albums
def update(self, album: Album):
pass
def delete(self, idAlbum: str):
pass
def search_albums(self, queryCriterion):
self.connection.open()
sql = """\
EXEC [dbo].[SPS_SearchAlbums]
@queryCriterion = ?
"""
self.connection.cursor.execute(sql, queryCriterion)
rows = self.connection.cursor.fetchall()
if self.connection.cursor.rowcount != 0:
list_albums = []
for row in rows:
album = Album(row.IdAlbum, row.Title, row.AlbumCover,
row.Publication, row.RecordCompany,
row.IdAlbumType)
album.musicGender.idMusicGender = row.IdMusicGender
album.musicGender.genderName = row.GenderName
album.artist.name = row.ArtistName
album.artist.cover = row.ArtistCover
album.artist.description = row.Description
album.artist.registerDate = row.RegisterDate
album.artist.idArtist = row.IdArtist
list_albums.append(album)
return list_albums
return False
class SqlServerTrackRepository(TrackRepository):
def __init__(self):
self.connection = ConnectionSQL()
def save(self, track: Track):
self.connection.open()
sql = """
DECLARE @return_value int,
@salida nvarchar(1000),
@estado int
EXEC @return_value = [dbo].[SPI_CreateTrack]
@IdTrack = ?,
@Title = ?,
@Duration = ?,
@Reproduction = ?,
@FileTrack = ?,
@avaible = ?,
@IdAlbum = ?,
@salida = @salida OUTPUT,
@estado = @estado OUTPUT
"""
params = (track.idTrack, track.title, track.duration,
track.reproductions, track.fileTrack, track.avaible,
track.album.idAlbum)
print(params)
self.connection.cursor.execute(sql, params)
try:
self.connection.save()
if self.connection.cursor.rowcount > 0:
print(self.connection.cursor.rowcount, "Track created")
return True
else:
raise DataBaseException("Error in the Track creation")
except Exception as ex:
print(ex)
raise DataBaseException("Data base connection error")
finally:
self.connection.close()
def update(self, track: Track):
self.connection.open()
sql = """
DECLARE @return_value int,
@salida nvarchar(1000),
@estado int
EXEC @return_value = [dbo].[SPU_UpdateTrack]
@idTrack = ?,
@title = ?,
@duration = ?,
@reproductions = ?,
@fileTrack = ?,
@avaible = ?,
@idAlbum = ?,
@salida = @salida OUTPUT,
@estado = @estado OUTPUT
"""
params = (track.idTrack, track.title, track.duration,
track.reproductions, track.fileTrack, track.avaible,
track.album.idAlbum)
print(params)
try:
self.connection.cursor.execute(sql, params)
self.connection.save()
print(self.connection.cursor.rowcount, " Track updated")
self.connection.close()
return True
except Exception as ex:
raise ex
def delete(self, idTrack: str):
pass
def exists_track(self, idTrack: str):
self.connection.open()
sql = """\
DECLARE @return_value int,
@estado int,
@salida nvarchar(1000)
EXEC @return_value = [dbo].[SPS_TrackExists]
@IdTrack = ?,
@estado = @estado OUTPUT,
@salida = @salida OUTPUT
SELECT @estado as N'@estado',
@salida as N'@salida'
"""
self.connection.cursor.execute(sql, idTrack)
row = self.connection.cursor.fetchval()
result = False
if row == -1:
result = False
else:
result = True
self.connection.close()
return result
def get_tracks_of_playlist(self, idPlaylist: int):
self.connection.open()
sql = """
DECLARE @return_value int
EXEC @return_value = [dbo].[SPS_GetTracksOfPlaylist]
@idPlaylist = ?
"""
self.connection.cursor.execute(sql, idPlaylist)
rows = self.connection.cursor.fetchall()
listTracks = []
for row in rows:
track = Track(row.IdTrack, row.TitleTrack, row.Duration,
row.Reproductions, row.FileTrack, row.Avaible)
track.album.idAlbum = row.IdAlbum
track.album.title = row.TitleAlbum
track.album.cover = row.CoverAlbum
track.album.publication = row.Publication
track.album.recordCompany = row.RecordCompany
track.album.idAlbumType = row.IdAlbumType
track.album.artist.idArtist = row.IdArtist
track.album.artist.name = row.Name
track.album.artist.cover = row.CoverArtist
track.album.artist.registerDate = row.RegisterDate
track.album.artist.description = row.Description
track.album.musicGender.idMusicGender = row.IdMusicGender
track.album.musicGender.genderName = row.GenderName
listTracks.append(track)
self.connection.close()
return listTracks
def get_track(self, idTrack: str):
self.connection.open()
sql = "Select * FROM Tracks WHERE IdTrack = ?"
try:
self.connection.cursor.execute(sql, idTrack)
row = self.connection.cursor.fetchone()
return Track(row.IdTrack, row.Title, row.Duration,
row.Reproductions, row.FileTrack, row.Avaible)
except Exception as ex:
raise DataBaseException("Data base connection error")
finally:
self.connection.close()
def search_tracks(self, queryCriterion: str):
self.connection.open()
sql = """\
EXEC [dbo].[SPS_SearchTracks]
@queryCriterion = ?
"""
self.connection.cursor.execute(sql, queryCriterion)
rows = self.connection.cursor.fetchall()
listTracks = []
if self.connection.cursor.rowcount != 0:
for row in rows:
track = Track(row.IdTrack, row.Title, row.Duration,
row.Reproductions, row.FileTrack, row.Avaible)
track.album.idAlbum = row.IdAlbum
track.album.title = row.AlbumTitle
track.album.cover = row.Cover
track.album.publication = row.Publication
track.album.recordCompany = row.RecordCompany
track.album.idAlbumType = row.IdAlbumType
track.album.artist.idArtist = row.IdArtist
track.album.artist.name = row.ArtistName
track.album.artist.registerDate = row.RegisterDate
track.album.artist.description = row.Description
track.album.musicGender.idMusicGender = row.IdMusicGender
track.album.musicGender.genderName = row.GenderName
listTracks.append(track)
return listTracks
return False
def get_tracks_radio_gender(self, idMusicGender: int):
try:
self.connection.open()
sql = """\
DECLARE @return_value int
EXEC @return_value = [dbo].[SPS_GetRadioByGender]
@IdMusicGender = ?
"""
self.connection.cursor.execute(sql, idMusicGender)
rows = self.connection.cursor.fetchall()
listTracks = []
if self.connection.cursor.rowcount != 0:
for row in rows:
track = Track(row.IdTrack, row.TrackTitle, row.Duration,
row.Reproductions, row.FileTrack,
row.Avaible)
track.album.idAlbum = row.IdAlbum
track.album.title = row.AlbumTitle
track.album.cover = row.AlbumCover
track.album.publication = row.Publication
track.album.recordCompany = row.RecordCompany
track.album.idAlbumType = row.IdAlbumType
track.album.artist.idArtist = row.IdArtist
track.album.artist.name = row.ArtistName
track.album.artist.cover = row.ArtistCover
track.album.artist.registerDate = row.RegisterDate
track.album.artist.description = row.Description
track.album.musicGender.idMusicGender = row.IdMusicGender
track.album.musicGender.genderName = row.GenderName
listTracks.append(track)
return listTracks
except Exception as ex:
print(ex)
raise DataBaseException("Database connection error")
finally:
self.connection.close()
def add_track_played(self, idTrack, reproductionDate, idAccount):
self.connection.open()
sql = """\
DECLARE @return_value int,
@estado int,
@salida nvarchar(1000)
EXEC @return_value = [dbo].[SPI_AddTrackToHistory]
@idAccount = ?,
@idTrack = ?,
@estado = @estado OUTPUT,
@salida = @salida OUTPUT
SELECT @estado as N'@estado',
@salida as N'@salida'
"""
try:
params = (idAccount, idTrack)
print(params)
self.connection.cursor.execute(sql, params)
self.connection.save()
self.connection.close()
return True
except Exception as ex:
raise ex
def get_tracks_account_history(self, idAccount):
self.connection.open()
sql = """\
DECLARE @return_value int,
@estado int,
@salida nvarchar(1000)
EXEC @return_value = [dbo].[SPS_GetTracksHistoryAccount]
@idAccount = ?,
@estado = @estado OUTPUT,
@salida = @salida OUTPUT
SELECT @estado as N'@estado',
@salida as N'@salida'
"""
try:
self.connection.cursor.execute(sql, idAccount)
rows = self.connection.cursor.fetchall()
list_tracks = []
if self.connection.cursor.rowcount != 0:
for row in rows:
track = Track(row.IdTrack, row.Title, row.Duration,
row.Reproductions, row.FileTrack,
row.Avaible)
track.album.idAlbum = row.IdAlbum
track.album.title = row.AlbumTitle
track.album.cover = row.Cover
track.album.publication = row.Publication
track.album.recordCompany = row.RecordCompany
track.album.idAlbumType = row.IdAlbumType
track.album.artist.idArtist = row.IdArtist
track.album.artist.name = row.ArtistName
track.album.artist.registerDate = row.RegisterDate
track.album.artist.description = row.Description
track.album.musicGender.idMusicGender = row.IdMusicGender
track.album.musicGender.genderName = row.GenderName
list_tracks.append(track)
return list_tracks
except Exception as ex:
print(ex)
raise DataBaseException("Database connection error")
finally:
self.connection.close()
def get_tracks_of_album(self, idAlbum: str):
self.connection.open()
sql = """\
DECLARE @return_value int,
@estado int,
@salida nvarchar(1000)
EXEC @return_value = [dbo].[SPS_GetTracksOfAlbum]
@idAlbum = ?,
@estado = @estado OUTPUT,
@salida = @salida OUTPUT
SELECT @estado as N'@estado',
@salida as N'@salida'
"""
try:
self.connection.cursor.execute(sql, idAlbum)
list_tracks = []
rows = self.connection.cursor.fetchall()
for row in rows:
track = Track(row.IdTrack, row.Title, row.Duration,
row.Reproductions, row.FileTrack, row.Avaible)
track.album.idAlbum = row.IdAlbum
track.album.title = row.AlbumTitle
track.album.cover = row.Cover
track.album.publication = row.Publication
track.album.recordCompany = row.RecordCompany
track.album.idAlbumType = row.IdAlbumType
track.album.artist.idArtist = row.IdArtist
track.album.artist.name = row.ArtistName
track.album.artist.registerDate = row.RegisterDate
track.album.artist.description = row.Description
track.album.musicGender.idMusicGender = row.IdMusicGender
track.album.musicGender.genderName = row.GenderName
list_tracks.append(track)
return list_tracks
list_tracks.append(track)
return list_tracks
except Exception as ex:
return DataBaseException("Database connection error")
finally:
self.connection.close()