如何使用Python将PostgreSQL多表查询的元组结果转换为带列名的单字典

Hey there! Let's sort out that issue you're having with turning your PostgreSQL query results into dictionaries that include the column names. I can spot a couple of issues in your current code that are causing the error, so let's break this down step by step.

Why Your Current Code Isn't Working #

Your code uses dict(zip(results)), but here's the problem:

• cursor.fetchall() returns a list of tuples, where each tuple represents a row of data from the table.
• zip(results) would try to pair elements across these row tuples, not pair column names with row values.
• Most importantly, you're not fetching the column names from your query, so there's no way to map the tuple values to their respective column keys.

Solution 1: Use RealDictCursor (Easiest Approach) #

Psycopg2 has a built-in cursor class called RealDictCursor that automatically returns rows as dictionaries, with column names as keys. This is the simplest way to get the result you want.

Here's how to adjust your code:

import psycopg2
from psycopg2.extras import RealDictCursor  # Import the special cursor

# Establish the connection
conn = psycopg2.connect(
    database="CONNETpy",
    user='postgres',
    password='pass',
    host='localhost',
    port='5432'
)

# Use RealDictCursor instead of the default cursor
cursor = conn.cursor(cursor_factory=RealDictCursor)

tables = ["Class_A", "Class_B", "Class_C"]
table_results = {}  # To store each table's data as a list of dictionaries

for table in tables:
    cursor.execute(f"SELECT * FROM {table}")
    results = cursor.fetchall()  # Now results is a list of RealDict objects (act like dicts)
    table_results[table] = [dict(row) for row in results]  # Convert to standard dicts if needed
    print(f"Results for {table}:")
    for row in results:
        print(row)

# Close connections when done
cursor.close()
conn.close()

Solution 2: Manually Map Column Names to Row Values #

If you prefer not to use RealDictCursor, you can fetch the column names from the cursor's description attribute, then zip each row tuple with these names to create a dictionary.

Here's the code for this approach:

import psycopg2

# Establish the connection
conn = psycopg2.connect(
    database="CONNETpy",
    user='postgres',
    password='pass',
    host='localhost',
    port='5432'
)

cursor = conn.cursor()
tables = ["Class_A", "Class_B", "Class_C"]
table_results = {}

for table in tables:
    cursor.execute(f"SELECT * FROM {table}")
    results = cursor.fetchall()
    
    # Get column names from cursor description
    column_names = [desc[0] for desc in cursor.description]
    
    # Convert each row tuple to a dictionary
    row_dicts = [dict(zip(column_names, row)) for row in results]
    table_results[table] = row_dicts
    
    print(f"Results for {table}:")
    for row in row_dicts:
        print(row)

# Clean up
cursor.close()
conn.close()

Key Notes #

• Both methods will give you dictionaries where each key is the column name from your PostgreSQL table, and the value is the corresponding data from the row.
• Remember to always close your cursor and connection when you're done with them to avoid resource leaks.
• If you only need a single dictionary per table (e.g., if each table has exactly one row), you can just take row_dicts[0] instead of storing the whole list.

内容的提问来源于stack exchange，提问作者Prem