Python psycopg2 postgres select columns including field names

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)

You could also map over it which looks a bit nicer:

cursor.execute(open("blah.sql", "r").read())
data = cursor.fetchall()
cols = list(map(lambda x: x[0], cursor.description))
df = DataFrame(data, columns=cols)

The column names are available as cr.description[0][0], cr.description[1][0], etc. If you want it in exactly the format you show, you need to do some work to extract it and stick it in front of the result set.