It gives the following result:
[('("Murder in the First (1995)",2018,57)',), ('("U-571 (2000)",2020,53)',), ('("Money Train (1995)",2019,52)',), ('("Picnic at Hanging Rock (1975)",2019,52)',)]
Instead, try this:
[("Murder in the First (1995)",2018,57),("U-571 (2000)",2020,53),("Money Train (1995)",2019,52), ("Picnic at Hanging Rock (1975)",2019,52)]
This is my code:
db_conn = None db_conn = db_engine.connect() db_result = db_conn.execute(select([func.getTopSales(2018,2020)])) print(db_result.fetchall()) return db_result
I tried retrieving it as a dict and adding it to a list because the values are the tuple but in string, however casting it does not function as expected in this example.
The function’s psql code is as follows:
create or replace function getTopSales(y1 int, y2 int) returns table (titulo char, año int, ventas int) as $$ with prod_year_sales as ( /*The products with their sales and the year of the sale*/ select orderdetail.prodid, extract (year from orders.orderdate) as "year", count(orderdetail.prodid) as sales from orderdetail natural join orders where extract(year from orders.orderdate) between y1 and y2 group by orderdetail.prodid, "year"), max_sales_year_prod as ( /*The products with most sales by year*/ select a.prodid, a."year", a.sales from prod_year_sales a inner join ( select "year", max(sales) sales from prod_year_sales group by ("year")) as b on a."year" = b."year" and a.sales = b.sales) select /*Get title of the movies that correspond to the sold products*/ movietitle, main."year", sales from imdb_movies im inner join ( select movieid, "year", sales from max_sales_year_prod m inner join products p on m.prodid = p.prodid) as main on im.movieid = main.movieid order by(sales) desc; $$ language sql;
This is the function that the Python file calls, and I anticipate the same results as if I ran a query.