postgresql Python psycopg2 postgres 选择包含字段名称的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17143132/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Python psycopg2 postgres select columns including field names
提问by Tahnoon Pasha
Hi I'd like to get a table from a database, but include the field names so I can use them from column headings in e.g. Pandas where I don't necessarily know all the field names in advance
嗨，我想从数据库中获取一个表，但要包含字段名称，这样我就可以从列标题中使用它们，例如 Pandas，我不一定事先知道所有字段名称
so if my database looks like
所以如果我的数据库看起来像
table test1
table test1
a | b | c
---+---+---
1 | 2 | 3
1 | 2 | 3
1 | 2 | 3
1 | 2 | 3
1 | 2 | 3
How can I do a
我该怎么做
import psycopg2 as pq
cn = pq.connect('dbname=mydb user=me')
cr = cn.cursor()
cr.execute('SELECT * FROM test1;')
tmp = cr.fetchall()
tmp
such that tmp shows
这样 tmp 显示
[('a','b','c'),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3)]
Thanks
谢谢
回答by Peter Eisentraut
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.
列名可用作cr.description[0][0]、cr.description[1][0]等。如果您希望它的格式与您显示的完全一致，您需要做一些工作来提取它并将其粘贴在结果集的前面。
回答by EdTech
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:
如果您想要的是一个数据框，它以 db 表中的数据作为其值，并且数据框列名称是您从 db 中读取的字段名称，那么这应该执行您想要的操作：
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)
回答by Pauline Kelly
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)
回答by cdvillagra
You can use two loop cases, to not use pandas:
您可以使用两个循环案例，不使用Pandas：
temp = []
for x in result:
temp2 = {}
c = 0
for col in cursor.description:
temp2.update({str(col[0]): x[c]})
c = c+1
temp.append(temp2)
print(temp)
This will prints any like this:
这将打印如下任何内容：
[{'column1':'foo1','column2':'foo1'},{'column1':'foo2','column2':'foo2'},...]
I hope this help you! Cheers
我希望这对你有帮助！干杯
回答by KailiC
import psycopg2 as pq
cn = pq.connect('dbname=mydb user=me')
cr = cn.cursor()
cr.execute('SELECT * FROM test1;')
tmp = cr.fetchall() #Hi, these are your codes that build a connection to a psql server
cols = []
for col in tmp.description:
cols.append(col[0]) #Collect all column names into an empty list, cols
tmp.insert(0, tuple(cols)) #insert elements by list.insert(index, new_item) method
The output is
输出是
[('a','b','c'),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3)]
