def urlsImdb():
listaElementos = []
connection = None
try:
connection = config.get_connection_by_config()
cursor = connection.cursor()
sql = """
SELECT 'https://www.imdb.com/title/' || imdb_id as url_imdb
FROM item where type = 'Movie' and budget = 0 and imdb_id is not null;
"""
cursor.execute(sql)
record = cursor.fetchall()
for row in record:
listaElementos.append(row[0])
except (Exception, psycopg2.Error) as error:
print("Error while connecting to PostgreSQL", error)
finally:
# closing database connection.
if (connection):
cursor.close()
connection.close()
return listaElementos
def get_poster_url(self, pTipo):
lista_elementos = []
connection = None
try:
connection = config.get_connection_by_config()
cursor = connection.cursor()
sql = "SELECT id, poster_path FROM item where type = '%s' and poster_path is not null;" % pTipo
cursor.execute(sql)
record = cursor.fetchall()
for row in record:
itm = ItemCatalogo(None, None, None, None, None, None, None,
None, None, None, None, None, None)
itm.id = row[0]
itm.poster_path = row[1]
itm.tipo = pTipo
lista_elementos.append(itm)
except (Exception, psycopg2.Error) as error:
print("Error while connecting to PostgreSQL", error)
finally:
# closing database connection.
if (connection):
cursor.close()
connection.close()
return lista_elementos
def insertar(self):
connection = None
try:
connection = config.get_connection_by_config()
cursor = connection.cursor()
sql = """
DO
$do$
BEGIN
IF NOT EXISTS (SELECT * FROM genre WHERE id = %s) THEN
INSERT INTO genre (id, name) VALUES (%s, %s);
end if;
END
$do$
"""
data = (self.id, self.id, self.name)
cursor.execute(sql, data)
except (Exception, psycopg2.Error) as error:
print("Error while connecting to PostgreSQL", error)
finally:
# closing database connection.
if (connection):
cursor.close()
connection.commit()
connection.close()
def get_url_imagen_local():
listaUrl = []
connection = None
try:
connection = config.get_connection_by_config()
cursor = connection.cursor()
sql = "SELECT local_poster_path FROM item WHERE local_poster_path IS NOT NULL;"
cursor.execute(sql)
record = cursor.fetchall()
for row in record:
listaUrl.append(row[0])
except (Exception, psycopg2.Error) as error:
print("Error while connecting to PostgreSQL", error)
finally:
# closing database connection.
if (connection):
cursor.close()
connection.close()
return listaUrl
def get_productoras_from_id(id_pelicula):
lista_productoras = []
connection = None
try:
connection = config.get_connection_by_config()
cursor = connection.cursor()
sql = "select name from item_production_companies where iditem = %s and type = %s limit 2;"
data = (id_pelicula, 'Movie')
cursor.execute(sql, data)
record = cursor.fetchall()
for row in record:
lista_productoras.append(row[0])
except (Exception, psycopg2.Error) as error:
print("Error while connecting to PostgreSQL", error)
finally:
# closing database connection.
if (connection):
cursor.close()
connection.close()
return lista_productoras
def get_data():
lista_elementos = []
connection = None
try:
connection = config.get_connection_by_config()
cursor = connection.cursor()
sql = "SELECT id FROM dataset_final;"
cursor.execute(sql)
record = cursor.fetchall()
for row in record:
lista_elementos.append(row[0])
except (Exception, psycopg2.Error) as error:
print("Error while connecting to PostgreSQL", error)
finally:
# closing database connection.
if (connection):
cursor.close()
connection.close()
return lista_elementos
def get_actores_from_id(id):
lista_actores = []
connection = None
try:
connection = config.get_connection_by_config()
cursor = connection.cursor()
sql = "SELECT id, name, gender FROM item_caracter WHERE type = %s AND iditem = %s LIMIT 28;"
data = ('Movie', id)
cursor.execute(sql, data)
record = cursor.fetchall()
for row in record:
mi_actor = Actor(pType='Movie',
pId=row[0],
pName=row[1],
pGender=row[2],
pIdItem=id)
lista_actores.append(mi_actor)
except (Exception, psycopg2.Error) as error:
print("Error while connecting to PostgreSQL", error)
finally:
# closing database connection.
if (connection):
cursor.close()
connection.close()
return lista_actores
def get_generos_from_id(id_pelicula):
lista_generos = []
connection = None
try:
connection = config.get_connection_by_config()
cursor = connection.cursor()
sql = """
select gen.name
from item_genres itG
inner join genre gen ON gen.id = itG.idgenre
where itG.iditem = %s and itG.type = %s limit 2;
"""
data = (id_pelicula, 'Movie')
cursor.execute(sql, data)
record = cursor.fetchall()
for row in record:
lista_generos.append(row[0])
except (Exception, psycopg2.Error) as error:
print("Error while connecting to PostgreSQL", error)
finally:
# closing database connection.
if (connection):
cursor.close()
connection.close()
return lista_generos
def getAll(pTipo, pProdCompany, pCast):
listaElementos = []
connection = None
try:
connection = config.get_connection_by_config()
cursor = connection.cursor()
if pProdCompany:
sql = """
select id from item itm where
not exists(select * from item_production_companies pc where pc.type = itm.type AND pc.iditem = itm.id)
AND type = '%s';
""" % pTipo
elif pCast:
sql = """
select id from item itm where
not exists(select * from item_caracter pc where pc.type = itm.type AND pc.idItem = itm.id)
AND type = '%s';
""" % pTipo
else:
sql = """
select id from item where type = '%s';
""" % pTipo
cursor.execute(sql)
record = cursor.fetchall()
for row in record:
listaElementos.append(row[0])
except (Exception, psycopg2.Error) as error:
print("Error while connecting to PostgreSQL", error)
finally:
# closing database connection.
if (connection):
cursor.close()
connection.close()
return listaElementos
def actualizaPresupuestoImdb(pTipo, pIdImdb, pPresupuesto):
connection = None
try:
connection = config.get_connection_by_config()
cursor = connection.cursor()
sql = "UPDATE item SET budget = %s WHERE type = %s AND imdb_id = %s;"
data = (pPresupuesto, pTipo, pIdImdb)
cursor.execute(sql, data)
except (Exception, psycopg2.Error) as error:
print("Error while connecting to PostgreSQL", error)
finally:
# closing database connection.
if (connection):
cursor.close()
connection.commit()
connection.close()
def actualiza_ruta_imagen_local(self):
connection = None
try:
connection = config.get_connection_by_config()
cursor = connection.cursor()
sql = "UPDATE item SET local_poster_path = %s WHERE type = %s AND id = %s;"
data = (self.local_poster_path, self.tipo, self.id)
cursor.execute(sql, data)
except (Exception, psycopg2.Error) as error:
print("Error while connecting to PostgreSQL", error)
finally:
# closing database connection.
if (connection):
cursor.close()
connection.commit()
connection.close()
def actualiza_genero(id_pelicula, campo_genero, valor_genero):
connection = None
try:
connection = config.get_connection_by_config()
cursor = connection.cursor()
sql = "UPDATE dataset_final SET " + campo_genero + " = %s WHERE id = %s;"
data = (valor_genero, id_pelicula)
cursor.execute(sql, data)
except (Exception, psycopg2.Error) as error:
print("Error while connecting to PostgreSQL", error)
finally:
# closing database connection.
if (connection):
cursor.close()
connection.commit()
connection.close()
def insertar(self):
connection = None
try:
connection = config.get_connection_by_config()
cursor = connection.cursor()
sql = """
DO
$do$
BEGIN
IF NOT EXISTS (SELECT * FROM item WHERE id = %s AND type = %s) THEN
INSERT INTO item (id, type, popularity, vote_count, poster_path, adult, backdrop_path, original_language, original_title, title,
vote_average, overview, release_date) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
END IF;
END;
$do$
"""
data = (self.id, self.tipo, self.id, self.tipo, self.popularity,
self.vote_count, self.poster_path, self.adult,
self.backdrop_path, self.original_language,
self.original_title, self.title, self.vote_average,
self.overview, self.release_date)
cursor.execute(sql, data)
except (Exception, psycopg2.Error) as error:
print("Error while connecting to PostgreSQL", error)
finally:
# closing database connection.
if (connection):
cursor.close()
connection.commit()
connection.close()
# Categorías del elemento
for cat in self.genre_ids:
try:
connection = config.get_connection_by_config()
cursor = connection.cursor()
sql = """
DO
$do$
BEGIN
IF NOT EXISTS (SELECT * FROM item_genres WHERE idItem = %s AND type = %s AND idGenre = %s) THEN
INSERT INTO item_genres (idItem, type, idGenre) VALUES (%s, %s, %s);
END IF;
END;
$do$
"""
data = (self.id, self.tipo, cat, self.id, self.tipo, cat)
cursor.execute(sql, data)
except (Exception, psycopg2.Error) as error:
print("Error while connecting to PostgreSQL", error)
finally:
# closing database connection.
if (connection):
cursor.close()
connection.commit()
connection.close()