Psycopg2 Use Column Names Instead Of Column Number To Get Row Data

So currently when I execute SELECT query and retrieve data I have to get results like this: connection = psycopg2.connect(user='admin', password='admi

Solution 1:

You need to use RealDictCursor, then you can access the results like a dictionary:

import psycopg2
from psycopg2.extras import RealDictCursor
connection = psycopg2.connect(user="...",
                              password="...",
                              host="...",
                              port="...",
                              database="...",
                              cursor_factory=RealDictCursor)
cursor = connection.cursor()

cursor.execute("SELECT * FROM user")
users = cursor.fetchall()

print(users)
print(users[0]['user'])

Output:

[RealDictRow([('user', 'dbAdmin')])]
dbAdmin

Solution 2:

no need to call fetchall() method, the psycopg2 cursor is an iterable object you can directly do:

cursor.execute("SELECT * FROM user")

for buff in cursor:
    row = {}
    c = 0for col in cursor.description:
        row.update({str(col[0]): buff[c]})
        c += 1print(row["id"])
    print(row["first_name"])
    print(row["last_name"])