python traverse CTE in a double for loop?

Question

I have 2 for loops within each-other. For each row 'A', 'B', 'C' in loop1, I need to access the hierarchical tree to find all the parents of a group 'X' in loop2. This makes me use CTE where I need to find the path for each row separately. Using CTE in a loop is not a solution for sure where I can match for each group id. Referred this link, but could not make out much Looping hierarchy CTE

Code snippet for the cron job using flask framework:

    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)

I need to send mail to all the group heads for the assignee_group in the hierarchy for the issue. How can this be achieved. How to use the loops correctly? I am using sqlalchemy core only, postgresql, python with flask. I need the exact code for the same.

What works is the snippet below:

 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

Assuming that the head column is boolean, this will collect the groups with the head flag set:

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

print 'group heads:', head_list

This is assuming the query from your second example is used (note the correction to the from clause, "from groupsgrp1" should be "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;