如何从psycopg2游标获得列名列表?

摘自Mark Lutz的《Programming Python》:

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

对于在单独的查询中获取列名，可以查询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))

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

#!/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()

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

然后你就可以跟着做了

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

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

total_fields = len(cursor.description)
fields_names = [i[0] for i in cursor.description
Print fields_names

如果你想从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

 # You can use this function
def getColumns(cursorDescription):
columnList = []
for tupla in cursorDescription:
columnList.append(tupla[0])
return columnList

如果你想获取熊猫数据帧列标题已经关联，试试这个:

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))

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

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