Menu
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.
657
Python psycopg2 fetchall The fetchall fetches all the (remaining) rows of a query result, returning them as a list of tuples. An empty list is returned if there is no more record to fetch.
class cursor. Allows Python code to execute PostgreSQL command in a database session. Cursors are created by the connection. cursor() method: they are bound to the connection for the entire lifetime and all the commands are executed in the context of the database session wrapped by the connection.
This module is a generic place used to hold little helper functions and classes until a better place in the distribution is found.
When you have more then one rows you can use the following code
result = [r[0] for r in cur.fetchall()]
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.
7
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
