Python array manipulation from psycopg2 PostgreSQL query

Question

I am using psycopg2 to do a psql query.

cur.execute("SELECT DISTINCT name FROM product")
result = cur.fetchall()
print(result)

[('product1',), ('product2',), ('product3',), ('product4',)]

I need to reformat this array to make an API endpoint. Right now it is a list of tuples where the second value of the tuple is empty. A simple loop iterating through gets the job done.

results=[]
for item in result:
    results.append(item[0])
print(results)

['product1','product2','product3','product4']

However this query can get rather large. And iterating through the entire list adds a delay to the query that doesn't seem necessary. Is there a way to flatten the array in constant time, or a differnt psycopg2 function that returns in the format I need?

Daniel · Accepted Answer · 2016-07-06 20:23:36Z

2

The time needed to convert the list is negligible compared to the database query. But you don't need to create a list first-hand:

cur.execute("SELECT DISTINCT name FROM product")
result = [item for item, in cur]
print(result)

answered Jul 6, 2016 at 20:23

Daniel

42.9k4 gold badges57 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Clodoaldo Neto · Accepted Answer · 2016-07-06 20:23:28Z

1

Aggregate in the query:

query = '''
    select array_agg(distinct name)
    from product
'''
cursor.execute(query)
rs = cursor.fetchall()[0][0]
print rs

Output:

['product1', 'product2', 'product3', 'product4']

answered Jul 6, 2016 at 20:23

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Collectives™ on Stack Overflow

Python array manipulation from psycopg2 PostgreSQL query

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related