两列上的数组\u agg，其输出未被识别为lis

Question

我在Python中使用psycopg2的一个Postgresql查询中使用array\u agg。你知道吗

我发现的问题是在循环遍历结果行时。 从查询生成的列不理解一列是列表的列表。相反，它认为这是一个str列表

以下是数据库示例：

+---------+-------------+-----------------+
| student | grade_value |  grade_comment  |
+---------+-------------+-----------------+
| Paul    | 1           | Very good       |
| John    | 1           | Very good       |
| John    | 2           | Average         |
| Mark    | 1           | Very good       |
| Mark    | 3           | Could do better |
| Mark    | 1           | Very good       |
+---------+-------------+-----------------+

我的问题是：

    connection = psycopg2.connect(ls.CONNECTION_STRING)

    cursor = connection.cursor(cursor_factory=RealDictCursor)

    cursor.execute(
        "SELECT student, array_agg('(' || grade_value || ',' || grade_comment || ')') as grades"
        "FROM random_table"
        "GROUP BY student"
    )

    students_grades = cursor.fetchall()
    # This returns something like: RealDictRow([('student', John), ('grades', ['(1,Very good)', '(2,Average)'])]), RealDictRow([('student', Paul), ('grades', ['(1,Very good)'])])

    for student in students_grades:
       for grade in student['grades']:
           print(grade)
           print(type(grade))

最后的print（grade）值的格式如下：（1，非常好） 但是查询说类型是一个字符串。因此，我无法通过键入grade[1]来访问成绩注释。它认为等级是一根弦。你知道吗

你知道怎么解决这个问题吗？你知道吗

Answer 1

拆分字符串，使其成为一个列表。根据需要进行预处理。你知道吗

for grade in student['grades']:
    # Let's say grade = "(A,Very good)"
    g = grade.replace('(', '').replace(')','') # g  > "A,Very good"
    l = g.split(',') # l  > ["A", "Very good"]
    print(l[1]) #  > "Very good"

在IPython测试：

In [1]: grade = "(A,Very good)"

In [2]: g = grade.replace('(', '').replace(')', '')

In [3]: l = g.split(',')

In [4]: print(l[0])
A

In [5]: print(l[1])
Very good

编辑：

In [4]: grade = "(A,Very good, but needs some improvement.)"

In [5]: g = grade.replace('(', '').replace(')', '')

In [6]: l = g.split(',', 1)

In [7]: print(l[0])
A

In [8]: print(l[1])
Very good, but needs some improvement.

Answer 2

您不需要将字符串集中在array\u agg中，只需将列传递到数组中即可。
Psycopg2将在postgres和python之间强制转换正确的类型，正如您在输出中看到的students_grade['grades']作为列表获取：

cursor.execute("""
select
    student,
    array_agg(array[grade_value, grade_comment]) as grades
from random_table
group by student""")

students_grades = cursor.fetchall()

for students_grade in students_grades:
    print(students_grade['student'])

    for grade in students_grade['grades']:
        print("%s %s" % (type(grade), grade))

输出：

Tom
<class 'list'> ['2', 'Good']
<class 'list'> ['3', 'Very good']
John
<class 'list'> ['2', 'Very good']

编辑：

如果需要聚合不同的类型，可以聚合成一个JSON对象：

cursor.execute("""
select
    abc as student,
    json_agg(json_build_object('grade', grade_value, 'comment', array[grade_comment])) as grades
from foo
group by student""")

输出：

Tom
<class 'dict'> {'grade': 2, 'comment': ['Good']}
<class 'dict'> {'grade': 3, 'comment': ['Very good']}
John
<class 'dict'> {'grade': 2, 'comment': ['Very good']}