python 在双for循环中遍历CTE？

Question

我有 2 个 for 循环。对于循环 1 中的每一行“A”、“B”、“C”，我需要访问分层树以在循环 2 中找到组“X”的所有父级。这让我在需要分别为每一行找到路径的地方使用 CTE。在循环中使用 CTE 并不是确定我可以在何处匹配每个组 ID 的解决方案。引用了此链接，但看不出多少 Looping hierarchy CTE

使用 flask 框架的 cron 作业的代码片段:

    s = select([rt_issues]).\
    where(
        and_(
            rt_issues.c.status !='Closed',
            rt_issues.c.assigned_to != None
        ))
rs = conn.execute(s)
if rs.rowcount > 0:
    s4 = text('with recursive rec_grp as(select id, parent_id, name, head, 1 as level, array[id] as path_info from groups union all select grp1.id, grp1.parent_id, grp1.name, grp1.head, rc.level + 1, rc.path_info||grp1.id from groups grp1 join rec_grp rc on grp1.id = rc.parent_id) select distinct id, parent_id, name, head, path_info from rec_grp order by id')

    rs4 = conn.execute(s4)

    for r in rs:
        head_list = []
        hierarchical_grps = []
        for rr in rs4:
            if ((rr['path_info'][0] == r[rt_issues.c.assignee_group])):
                for g in rr['path_info']:
                    hierarchical_grps.append(g)
        hierarchical_grps = list(set(hierarchical_grps))            
        send_pending_mail(hierarchical_grps, r['id'])
        print hierarchical_grps, 'hierarchical_grps'

    exit(0)

我需要向 问题层次结构中 assignee_group 的所有组长发送邮件。这怎么能实现。如何正确使用循环？我只使用 sqlalchemy 核心，postgresql，python 和 flask。我需要相同的确切代码。

有效的是下面的代码片段:

 mgroup = None
 s = select([rt_issues]).\
     where(
         and_(
             rt_issues.c.status !='Closed',
             rt_issues.c.assigned_to != None
         ))
 rs = conn.execute(s)
 if rs.rowcount > 0:
     for r in rs:
         head_list = []
         hierarchical_grps = []
         mgroup = r[rt_issues.c.assignee_group]
         s4 = text('with recursive rec_grp as(select id, parent_id, name, head, 1 as level, array[id] as path_info from groups where id=' +str(mgroup) + 'union all select grp1.id, grp1.parent_id, grp1.name, grp1.head, rc.level + 1, rc.path_info||grp1.id from groupsgrp1 join rec_grp rc on grp1.id = rc.parent_id) select distinct id,parent_id, name, head, path_info from rec_grp order by id')

     rs4 = conn.execute(s4)
     for rr in rs4:
         if ((rr['path_info'][0] == r[rt_issues.c.assignee_group])):
             for g in rr['path_info']:
                 hierarchical_grps.append(g)
     hierarchical_grps = list(set(hierarchical_grps))
     print hierarchical_grps, 'hierarchical_grps'
     send_pending_mail(hierarchical_grps, r['id'])
 exit(0)

Answer 1

假设 head 列是 bool 值，这将收集设置了 head 标志的组:

rs4 = con.execute(s4)
for rr in rs4:
    if rr['head']:
        head_list.append(rr['id'])

print 'group heads:', head_list

这是假设使用了第二个示例中的查询(注意对 from 子句的更正，“from groupsgrp1”应该是“from groups grp1”):

WITH RECURSIVE rec_grp AS (
  SELECT
    id,
    parent_id,
    name,
    head,
    1          AS level,
    ARRAY [id] AS path_info
  FROM groups
  WHERE id = 4
  UNION ALL
  SELECT
    grp1.id,
    grp1.parent_id,
    grp1.name,
    grp1.head,
    rc.level + 1,
    rc.path_info || grp1.id
  FROM groups grp1
    JOIN rec_grp rc ON grp1.id = rc.parent_id
)
SELECT DISTINCT
  id,
  parent_id,
  name,
  head,
  path_info
FROM rec_grp
ORDER BY id;