It returns 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)',)]
Instead of 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)]
Here 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 try to getting it as a dict and adding to a list because the values are the tuple but in string, but when I cast it it does not work.
The psql code of the function is this:
`
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 is calling and expect the same as if I execute a query.