blocks|key|3957921|text|来自Mark+Lutz的"Programming+Python“：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3957922|curs.execute("Select+*+FROM+people+LIMIT+0")
colnames+=+[desc[0]+for+desc+in+curs.description]|code-block|syntax|javascript|3957923|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

From "Programming Python" by Mark Lutz:

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

blocks|key|1847966|text|要在单独的查询中获取列名，可以查询information_schema.columns表。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|1847967|#!/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))|code-block|syntax|javascript|1847968|要在与数据行相同的查询中获取列名，可以使用游标的字段：|1847969|#!/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))|1847970|entityMap^0|8|11|0|0|0|6|O|3|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@$9|V|A|W|B|C]|$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|M|3|N|5|H|7|Z|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|10|8|@]|D|@]|E|$]]]|P|$]]

To get the column names in a separate query, you can query the information_schema.columns table.

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

To get column names in the same query as data rows, you can use the description field of the cursor: 

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

blocks|key|5375553|text|我注意到你必须在查询后使用cursor.fetchone()来获取cursor.description+(即[desc[0]+for+desc+in+curs.description])中的列的列表|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|5375554|entityMap|0|LINK|mutability|MUTABLE|url|https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-fetchone.html|1|https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-description.html^0|D|H|X|I|1I|12|D|H|0|X|I|1|0^^$0|@$1|2|3|4|5|6|7|P|8|@$9|Q|A|R|B|C]|$9|S|A|T|B|C]|$9|U|A|V|B|C]]|D|@$9|W|A|X|1|Y]|$9|Z|A|10|1|11]]|E|$]]|$1|F|3|-4|5|6|7|12|8|@]|D|@]|E|$]]]|G|$H|$5|I|J|K|E|$L|M]]|N|$5|I|J|K|E|$L|O]]]]

I have noticed that you must use <a href="https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-fetchone.html" rel="nofollow"><code>cursor.fetchone()</code></a> after the query to get the list of columns in <a href="https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-description.html" rel="nofollow"><code>cursor.description</code></a> (i.e in <code>[desc[0] for desc in curs.description]</code>)

blocks|key|3957990|text|#!/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+->%25s"+%25+(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
++++#+%25s+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+%25s',+(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()|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|3957991|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>#!/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 -&gt;%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 -&gt; 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()
</code></pre>

blocks|key|3958007|text|我以前也遇到过类似的问题。我使用一个简单的技巧来解决这个问题。假设列表中有列名，如下所示|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3958008|col_name+=+['a',+'b',+'c']|code-block|syntax|javascript|3958009|然后，您可以执行以下操作|3958010|for+row+in+cursor.fetchone():
++++print+zip(col_name,+row)|3958011|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

I also used to face similar issue. I use a simple trick to solve this.
Suppose you have column names in a list like

<pre><code>col_name = ['a', 'b', 'c']
</code></pre>

Then you can do following

<pre><code>for row in cursor.fetchone():
 print zip(col_name, row)
</code></pre>

blocks|key|1545839|text|执行SQL查询后，按照2.7中编写的python脚本编写|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1545840|total_fields+=+len(cursor.description)++++
fields_names+=+[i[0]+for+i+in+cursor.description+++
++++Print+fields_names|code-block|syntax|javascript|1545841|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

After executing SQL query write following python script written in 2.7

<pre><code>total_fields = len(cursor.description) 
fields_names = [i[0] for i in cursor.description 
 Print fields_names
</code></pre>

blocks|key|3958070|text|如果你想要一个命名的元组obj+from+db查询，你可以使用下面的代码片段：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3958071|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))|code-block|syntax|javascript|3958072|这允许您访问记录值，就好像它们是类属性一样。|3958073|3958074|+record.id、record.other_table_column_name等|blockquote|3958075|3958076|3958077|甚至更短|3958078|from+psycopg2.extras+import+NamedTupleCursor
with+cursor(cursor_factory=NamedTupleCursor)+as+cur:
+++cur.execute("Select+*+...")
+++return+cur.fetchall()|3958079|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|X|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|Y|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|10|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|11|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|12|8|@]|9|@]|A|$E|F]]|$1|S|3|-4|5|6|7|13|8|@]|9|@]|A|$]]]|T|$]]

If you want to have a named tuple obj from db query you can use the following snippet:

<pre><code>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))
</code></pre>

This allows you to access record values as if they were class properties i.e.

<blockquote>
 record.id, record.other_table_column_name, etc.
</blockquote>

or even shorter

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

blocks|key|1541668|text|你可以做的另一件事是创建一个光标，这样你就可以通过它们的名称来引用你的列(这是我最初进入这个页面的原因)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1541669|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|code-block|syntax|javascript|1541670|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Another thing you can do is to create a cursor with which you will be able to reference your columns by their names (that's a need which led me to this page in the first place):

<pre><code>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'])

&gt;&gt; 1, 2
</code></pre>

blocks|key|1848118|text|+#+You+can+use+this+function
+def+getColumns(cursorDescription):
+++++columnList+=+[]
+++++for+tupla+in+cursorDescription:
+++++++++columnList.append(tupla[0])
+++++return+columnList+|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1848119|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

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

blocks|key|1545877|text|如果你想获得一个已经关联了列标题的pandas数据框，试试这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1545878|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))|code-block|syntax|javascript|1545879|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

If you're looking to get a pandas data frame with column headers already associated, try this:
<pre><code>import psycopg2, pandas

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

sql = &quot;&quot;&quot;
select * from x
&quot;&quot;&quot;

d = pandas.read_sql_query(sql,con)

con.close()

print(type(d))

print(pandas.DataFrame.head(d))
</code></pre>

I would like a general way to generate column labels directly from the selected column names, and recall seeing that python's psycopg2 module supports this feature.

How do I get a list of column names from a psycopg2 cursor?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我想要一种直接从所选列名生成列标签的通用方法，并回想一下python的psycopg2模块支持此功能。

问如何从psycopg2游标获取列名列表？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何从psycopg2游标获取列名列表？EN