Python crosses CTE in double loop?

Question

I have 2 for loops inside each other. For each row “A”, “B”, “C” in loop1, I need to access the hierarchical tree to find all the parents of group “X” in loop2. This makes me use CTE, where I need to find the path for each line separately. Using CTE in a loop is not a solution for sure where I can map for each group identifier. Wrote this link, but could not distinguish the CTE Integral Hierarchy

Code snippet for specifying cron using a flask frame:

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 group heads for assignee_group in the hierarchy for the problem . How can this be achieved. How to use loops? I use only sqlalchemy core, postgresql, python with jar. I need the exact code for the same.

What works, snapshot 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 the head column has a boolean value, it will collect 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 

It is assumed that the request from your second example is used (note that the correction to the from, from from groupsgrp1 clause should be "from grp1 groups"):

 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;