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

From "Programming Python" by Mark Lutz:

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

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

cursor2.description[0].type_code

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

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)

The result of fetchall() is a sequence of tuples. You can loop over the sequence and print the first (index 0) element of each tuple:

cur.execute("SELECT \"Value\" FROM deployment WHERE (\"Key\" = 'DUMPLOCATION')")
records = cur.fetchall()
for record in records:
    print(record[0])

Or simpler, if you are sure the query returns no more than one row, use fetchone() which gives a single tuple representing returned row, e.g.:

cur.execute("SELECT \"Value\" FROM deployment WHERE (\"Key\" = 'DUMPLOCATION')")
row = cur.fetchone()
if row: # check whether the query returned a row
    print(row[0])

You could do something like:

rtn_val = cur.fetchall()

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