Why the fetchall is returning a list with only one element, that is a tuple which contains a string with the tuples instead of a list of tuples?

63 views Asked by At

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.

0

There are 0 answers