Python用psycopg2查询PostgreSQL仅获表最后一条数据，如何获取全量数据？

问题描述 #

使用psycopg2连接PostgreSQL，遍历Class_A、Class_B、Class_C三个表，查询name、department、overall_percentage字段，意图将每个表的所有数据以「表名: 字典列表」的嵌套形式输出，但运行后仅得到每个表的最后一条数据。

原代码 #

import psycopg2

# establishing the connection
conn = psycopg2.connect(
   database="CONNE py",
    user='postgres',
    password='asdf',
    host='localhost',
    port= '5432'
)

cursor=conn.cursor()

tables = ["Class_A", "Class_B", "Class_C"]

outer_dict={}
           
for table in tables:
    cursor.execute(f'SELECT name, department, overall_percentage FROM {table}')
    rows=cursor.fetchall()
    
    inner_dict={}
    
    for row in rows:
        name, department, overall_percentage=row
        
        inner_dict={
            'name':name,
            'department':department,
            'overall_percentage':overall_percentage
            }
        outer_dict[table]=inner_dict
    

print(outer_dict)
cursor.close()

当前输出 #

{'Class_A': {'name': 'Ranga', 'department': 'MCA', 'overall_percentage': 72}, 'Class_B': {'name': 'Delli', 'department': 'MBA', 'overall_percentage': 62}, 'Class_C': {'name': 'Mohan', 'department': 'B.tech AI', 'overall_percentage': 92}}

期望输出示例 #

{'Class_A': [{'name': 'Ranga', 'department': 'MCA', 'overall_percentage': 72}, {'name': 'Ranga', 'department': 'MCA', 'overall_percentage': 72}, {'name': 'Ranga', 'department': 'MCA', 'overall_percentage': 72}]}

错误原因 #

• 遍历每行数据时，每次循环都会重新创建一个新的inner_dict字典，覆盖之前的内容，没有把所有行的字典保存起来。
• 给outer_dict[table]赋值时，每次都用单个字典覆盖，最终只会保留最后一次循环的结果（也就是最后一行数据）。
• 需求是每个表对应一个字典列表，但你用了单个字典来存储，无法容纳多行数据。

修正后的代码 #

把存储每行数据的容器改成列表，每次循环将新的字典添加到列表中，最后把整个列表赋值给outer_dict[table]：

import psycopg2

# establishing the connection
conn = psycopg2.connect(
   database="CONNE py",
    user='postgres',
    password='asdf',
    host='localhost',
    port= '5432'
)

cursor=conn.cursor()

tables = ["Class_A", "Class_B", "Class_C"]

outer_dict={}
           
for table in tables:
    cursor.execute(f'SELECT name, department, overall_percentage FROM {table}')
    rows=cursor.fetchall()
    
    # 把inner_dict改成列表，用来存储当前表的所有行字典
    inner_list = []
    
    for row in rows:
        name, department, overall_percentage=row
        
        # 创建当前行的字典，并添加到列表中
        inner_row_dict = {
            'name':name,
            'department':department,
            'overall_percentage':overall_percentage
        }
        inner_list.append(inner_row_dict)
    
    # 将整个列表赋值给outer_dict对应的表名键
    outer_dict[table] = inner_list
    

print(outer_dict)
cursor.close()
# 记得关闭连接，原代码漏了这一步
conn.close()

额外优化提示 #

可以用列表推导式简化内层循环，让代码更简洁：

import psycopg2

conn = psycopg2.connect(
   database="CONNE py",
    user='postgres',
    password='asdf',
    host='localhost',
    port= '5432'
)

cursor=conn.cursor()
tables = ["Class_A", "Class_B", "Class_C"]
outer_dict={}
           
for table in tables:
    cursor.execute(f'SELECT name, department, overall_percentage FROM {table}')
    rows=cursor.fetchall()
    # 用列表推导式直接生成字典列表
    outer_dict[table] = [
        {'name': name, 'department': dept, 'overall_percentage': perc}
        for name, dept, perc in rows
    ]

print(outer_dict)
cursor.close()
conn.close()

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