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()