How to return back a list instead of tuple in psycopg2

Question

I have the following query

    cursor.execute(
    """
    SELECT transform(row_to_json(t)) FROM 
        (select * from table 
         where a = %s 
         and b = %s limit 1000) t;
    """
    , (a_value, b_value))

Running records = cursor.fetchall() will return a list of size 1 tuples.

Is there anyway to return just a list of lists?

I am asking this because I'd like to transform the list of lists into a numpy matrix, and for looping through to turn the singleton tuples into a list is slow.

Answer 1

When you have more then one rows you can use the following code

result = [r[0] for r in cur.fetchall()]

Answer 2

As a quick fix you can return an array:

cursor.execute("""
    select array_agg(transform(row_to_json(t)))
    from (
        select * from table 
        where a = %s and b = %s
        limit 1000
    ) t;
""", (a_value, b_value))

As Psycopg adapts Postgresql arrays to Python lists then just get that list:

records = cursor.fetchall()[0][0]

I guess it is possible to subclass cursor to return lists in instead of tuples but if you are not dealing with huge sets I think it is not worth the trouble.