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

cursor2.description[0] is a psycopg2.extensions.Column object, you can access its fields by name, e.g.

cursor2.description[0].type_code

From "Programming Python" by Mark Lutz:

curs.execute("Select * FROM people LIMIT 0")
colnames = [desc[0] for desc in curs.description]

If what you want is a dataframe with the data from the db table as its values and the dataframe column names being the field names you read in from the db, then this should do what you want:

import psycopg2 as pq
cn = pq.connect('dbname=mydb user=me')
cr = cn.cursor()
cr.execute('SELECT * FROM test1;')
tmp = cr.fetchall()

# Extract the column names
col_names = []
for elt in cr.description:
    col_names.append(elt[0])

# Create the dataframe, passing in the list of col_names extracted from the description
df = pd.DataFrame(tmp, columns=col_names)

def get_query_results_as_list_of_dicts(query):
        """
        runs a query and returns the result as a dict rather than a tuple
        :param query: SQL formatted string
        :return: list of dictionary objects. one for each result returned from the database
        """
        # Execute the query
        cur.execute(query)

        # Fetch all the rows as a list of dictionaries
        list_of_dicts = [dict(row) for row in cur.fetchall()]

        return list_of_dicts

You could do something like:

rtn_val = cur.fetchall()

table_list = [row["table_name"] for row in rtn_val]
```