将SQL数据快速序列化到python列表中

Question

我有一个带有两个表的Postgresql数据库： （1） 使用者 （2） 用户输入

这是规范化的，因此用户和用户_条目之间有一对多的关系。例如，用户中的条目将具有

id=0, email=user@email.com, name=Alice Mcbob

然后我可以在user_条目中有两个条目：

id=0, user_id=0, major=english, class='lit101'
id=1, user_id=0, major=english, class='science101'
id=2, user_id=0, major=science, class='lit101'

这将是一个有两个专业的学生，我可以根据其中任何一个来设置关键点

我有一些python代码，我试图返回一个用户列表（作为对象），每个用户都有一个类列表。例如，我想返回：

[
  {
      'email': 'user@user.com,
      'name': 'Alice McBob',
      'classes': [
         'lit101',
         'science101'
      ],
  },
     'email': ...
   ...
]

我目前的策略是执行一个fetchall（），在这里我抓取每个用户并将其与每个类内部连接，然后为每个用户创建一个唯一的标识符，这样我就可以执行user_id-major并确定我要查找的用户。然后，我有一个标识符列表，可以跟踪。我检查每个条目，看看我以前是否见过它（检查标识符是否存在）。如果有，将该类添加到该类的人员列表中。否则，创建一个新的类列表

然而，这是一个超级复制器，性能不佳。我只有500个用户，这样做大约需要15秒。因为这是一个api，我一直在做get，所以这真的不理想。此外，代码感觉非常笨拙，因为重复是一个问题

我有两个职能：

def get_full_list(conn=None):
    user_list = []
    prev_checklist = []
    query = """
        SELECT id, email, name, major, class FROM 
            users
            INNER JOIN user_entries
            ON users.id=user_entries.user_id 
    """
    with conn.cursor() as cur:
        cur.execute(query)
        for row in cur.fetchall():
            identifier = str(row[0]) + '-' + str(row[3])
            user = return_user_from_row(identifier, user_list, prev_checklist, row)
            user_list.append(user)
conn.commit()
return json.dumps(user_list)

def return_user_from_row(identifier="", user_list=[], prev_checklist=[], row=None):
    if identifier in prev_checklist:
        index = prev_checklist.index(identifier)
        user = user_list[index]
        user_classes = user['class']
        user_classes.append(str(row[4])
        user['class'] = user_classes
        user_list[index] = user
    else: 
        prev_checklist.append(identifier)
        user = {
            'email': '' if row[1] == None else row[1],
            'name': '' if row[2] == None else row[2],
            'major': '' if row[3] == None else row[3],
            'class': [''] if row[4] == None else [row[4]],
        }
    return user

是否有一个更简单、性能更高的版本，我可以利用SQL命令本身为我做很多这方面的工作？我错过了一些简单的东西吗

Answer 1

这里有一个SQL查询来执行所有。尝试：

select json_agg(json_build_object
(
 'email', u.email, 
 'name', u.name, 
 'classes', (select json_agg(distinct class) from user_entries ue where ue.user_id = u.id)
))
from users u;

还有其他方法可以编写相同的查询（例如使用cross join lateral）。我认为这本书很容易阅读