Question 412246 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Create list of dictionaries from Postgres table

I want to create a list of dictionaries from a postgres table. I use this code:

rows_dict = cur.fetchall() # fetch all rows from the Postgres Database
  
columns = []
entities_list = []
for column in cur.description:
    columns.append(column[0].lower())
entities = {}
for row in rows_dict:
    for i in range(len(row)):
        entities[columns[i]] = row[i]
        if isinstance(row[i], str):
            entities[columns[i]] = row[i].strip()
    print('Output-A:', entities)
    entities_list.append(entities) # add dictionary to the entity_list

print('Output-B:', entities_list) # Here I get a list with x-times the same dictionary... Why?

Output-A is what I expect at this indent level (and which is correct): multiple dictionaries. With an .append(entities) I want to add these dictionaries to the entity_list. However, the entity_list is populated multiple times with the same (=last!) dictionary in the loop. I do not understand what is going wrong as the .append(entities) is at the same indent as the Output-A. Any idea what is going wrong there?

Tags:

2

Answers


  1. 0 
    rows_dict = cur.fetchall()  # fetch all rows from the Postgres Database
  
columns = []
entities_list = []

# Get column names from cur.description
for column in cur.description:
    columns.append(column[0].lower())

# Iterate through each row and create a new dictionary per row
for row in rows_dict:
    entities = {}  # Create a new dictionary for each row
    for i in range(len(row)):
        entities[columns[i]] = row[i]
        if isinstance(row[i], str):
            entities[columns[i]] = row[i].strip()
    print('Output-A:', entities)
    entities_list.append(entities)  # Add the new dictionary to the entity_list

print('Output-B:', entities_list)  # This will now print the correct list of dictionaries

    The entities dictionary gets reset to an empty one every time we go through a new row in the outer loop. This way, a fresh dictionary is made for each row.

    So In you code entities = {} is inside the loop, so a fresh dictionary is created for each row and appended to the list.

    Login or Signup to reply.
  2. 0

    You’re declaring entities outside the loop, so you end up updating the same object over and over again.

    However, all of your code simplifies to a couple of list comprehensions:

    def strip_if_string(val):
    if isinstance(val, str):
        return val.strip()
    return val


columns = [column[0].lower() for column in cur.description]
entities_list = [dict(zip(columns, (strip_if_string(v) for v in row))) for row in cur.fetchall()]
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search