如果你想把你所有的数据放在一个带列名的Pandas数据框架中:

cur.execute("select * from tablename")
datapoints = cur.fetchall()
cols = [desc[0] for desc in cur.description]
df = pd.DataFrame((datapoints) , columns=[cols])

#!/usr/bin/python
import psycopg2
#note that we have to import the Psycopg2 extras library!
import psycopg2.extras
import sys

def main():
    conn_string = "host='localhost' dbname='my_database' user='postgres' password='secret'"
    # print the connection string we will use to connect
    print "Connecting to database\n ->%s" % (conn_string)

    # get a connection, if a connect cannot be made an exception will be raised here
    conn = psycopg2.connect(conn_string)

    # conn.cursor will return a cursor object, you can use this query to perform queries
    # note that in this example we pass a cursor_factory argument that will
    # dictionary cursor so COLUMNS will be returned as a dictionary so we
    # can access columns by their name instead of index.
    cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

    # tell postgres to use more work memory
    work_mem = 2048

    # by passing a tuple as the 2nd argument to the execution function our
    # %s string variable will get replaced with the order of variables in
    # the list. In this case there is only 1 variable.
    # Note that in python you specify a tuple with one item in it by placing
    # a comma after the first variable and surrounding it in parentheses.
    cursor.execute('SET work_mem TO %s', (work_mem,))

    # Then we get the work memory we just set -> we know we only want the
    # first ROW so we call fetchone.
    # then we use bracket access to get the FIRST value.
    # Note that even though we've returned the columns by name we can still
    # access columns by numeric index as well - which is really nice.
    cursor.execute('SHOW work_mem')

    # Call fetchone - which will fetch the first row returned from the
    # database.
    memory = cursor.fetchone()

    # access the column by numeric index:
    # even though we enabled columns by name I'm showing you this to
    # show that you can still access columns by index and iterate over them.
    print "Value: ", memory[0]

    # print the entire row 
    print "Row: ", memory

if __name__ == "__main__":
    main()

我注意到你必须在查询后使用cursor.fetchone()来获取cursor.description中的列列表(即在[desc[0] for desc in curs.description])

如果你想把你所有的数据放在一个带列名的Pandas数据框架中:

cur.execute("select * from tablename")
datapoints = cur.fetchall()
cols = [desc[0] for desc in cur.description]
df = pd.DataFrame((datapoints) , columns=[cols])

要在单独的查询中获得列名，可以查询information_schema。表列。

#!/usr/bin/env python3

import psycopg2

if __name__ == '__main__':
  DSN = 'host=YOUR_DATABASE_HOST port=YOUR_DATABASE_PORT dbname=YOUR_DATABASE_NAME user=YOUR_DATABASE_USER'

  column_names = []

  with psycopg2.connect(DSN) as connection:
      with connection.cursor() as cursor:
          cursor.execute("select column_name from information_schema.columns where table_schema = 'YOUR_SCHEMA_NAME' and table_name='YOUR_TABLE_NAME'")
          column_names = [row[0] for row in cursor]

  print("Column names: {}\n".format(column_names))

要在相同的查询中获取列名作为数据行，您可以使用游标的description字段:

#!/usr/bin/env python3

import psycopg2

if __name__ == '__main__':
  DSN = 'host=YOUR_DATABASE_HOST port=YOUR_DATABASE_PORT dbname=YOUR_DATABASE_NAME user=YOUR_DATABASE_USER'

  column_names = []
  data_rows = []

  with psycopg2.connect(DSN) as connection:
    with connection.cursor() as cursor:
      cursor.execute("select field1, field2, fieldn from table1")
      column_names = [desc[0] for desc in cursor.description]
      for row in cursor:
        data_rows.append(row)

  print("Column names: {}\n".format(column_names))

如果你想获得一个已经关联列标头的pandas数据帧，试试这个:

import psycopg2, pandas

con=psycopg2.connect(
    dbname=DBNAME, 
    host=HOST, 
    port=PORT, 
    user=USER, 
    password=PASSWORD
)

sql = """
select * from x
"""

d = pandas.read_sql_query(sql,con)

con.close()

print(type(d))

print(pandas.DataFrame.head(d))