psycopg2 使用列名而不是列号来获取行数据 [英] psycopg2 use column names instead of column number to get row data

问题描述

所以目前当我执行 SELECT 查询并检索数据时，我必须得到这样的结果:

So currently when I execute SELECT query and retrieve data I have to get results like this:

connection = psycopg2.connect(user="admin",
                              password="admin",
                              host="127.0.0.1",
                              port="5432",
                              database="postgres_db")
cursor = connection.cursor()

cursor.execute("SELECT * FROM user")
users = cursor.fetchall() 

for row in users:
    print(row[0])
    print(row[1])
    print(row[2])

我想要做的是，使用列名而不是整数，就像这样:

What I want to do is, use column names instead of integers, like this:

for row in users:
    print(row["id"])
    print(row["first_name"])
    print(row["last_name"])

这可能吗，如果可能，那该怎么做?

Is this possible, and if it is, then how to do it?

推荐答案

需要使用RealDictCursor，然后才能像字典一样访问结果:

You need to use RealDictCursor, then you can access the results like a dictionary:

import psycopg2
from psycopg2.extras import RealDictCursor
connection = psycopg2.connect(user="...",
                              password="...",
                              host="...",
                              port="...",
                              database="...",
                              cursor_factory=RealDictCursor)
cursor = connection.cursor()

cursor.execute("SELECT * FROM user")
users = cursor.fetchall()

print(users)
print(users[0]['user'])

输出:

[RealDictRow([('user', 'dbAdmin')])]
dbAdmin

这篇关于psycopg2 使用列名而不是列号来获取行数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！