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

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

如果你想从db查询中获得一个命名元组obj，你可以使用下面的代码片段:

from collections import namedtuple

def create_record(obj, fields):
    ''' given obj from db returns named tuple with fields mapped to values '''
    Record = namedtuple("Record", fields)
    mappings = dict(zip(fields, obj))
    return Record(**mappings)

cur.execute("Select * FROM people")
colnames = [desc[0] for desc in cur.description]
rows = cur.fetchall()
cur.close()
result = []
for row in rows:
    result.append(create_record(row, colnames))

这允许您访问记录值，如果他们是类属性，即。

记录。id、记录。other_table_column_name等等。

或者更短

from psycopg2.extras import NamedTupleCursor
with cursor(cursor_factory=NamedTupleCursor) as cur:
   cur.execute("Select * ...")
   return cur.fetchall()

你可以做的另一件事是创建一个游标，你可以通过它们的名字引用你的列(这是一个需要，导致我在第一个地方到这个页面):

import psycopg2
from psycopg2.extras import RealDictCursor

ps_conn = psycopg2.connect(...)
ps_cursor = psql_conn.cursor(cursor_factory=RealDictCursor)

ps_cursor.execute('select 1 as col_a, 2 as col_b')
my_record = ps_cursor.fetchone()
print (my_record['col_a'],my_record['col_b'])

>> 1, 2

执行SQL查询后，编写2.7中编写的python脚本

total_fields = len(cursor.description)    
fields_names = [i[0] for i in cursor.description   
    Print fields_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))

我也曾经面临过类似的问题。我用一个简单的技巧来解决这个问题。 假设您在一个列表中有这样的列名

col_name = ['a', 'b', 'c']

然后你就可以跟着做了

for row in cursor.fetchone():
    print zip(col_name, row)