CODEWITHSUNDEEP
pythonlistpostgresqlnumpypsycopg2
Ouwen Huang
Ouwen Huang

Reputation: 1097

How to return back a list instead of tuple in psycopg2

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.

Upvotes: 21

Views: 28050

Answers (3)

Mawty
Mawty

Reputation: 464

you may also use this code

result = cur.fetchall()
x = map(list, list(result))
x = sum(x, [])

Upvotes: 2

Dimo Boyadzhiev
Dimo Boyadzhiev

Reputation: 1357

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

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

Upvotes: 39

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125284

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.

Upvotes: 8

Related Questions