There is a question for the community.

There is a certain DB database in it there is a table of accounts like:

account_id | parent_id ------------------------ 5555 | None (пустой)

If parent_id = empty, then this is the parent for other accounts.

If parent_id contains an integer number then this is the parent's account_id, for example

 account_id | parent_id ----------------------- 6666 | 5555

I need to count the number of transitions by parenting from parent_id = None to the last child or vice versa.

Such code turned out:

 # -*- coding: utf-8 -*- import psycopg2 import psycopg2.extras sql = "select * from accounts" try: conn = psycopg2.connect("dbname='DB' user='postgres'" \ " host='postgres.contora.com' password='xxxxxxxx'") except psycopg2.Error as err: print("Connection error: {}".format(err)) try: cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) cur.execute(sql) data = cur.fetchall() except psycopg2.Error as err: print("Query error: {}".format(err)) data_dict = [] for row in data: data_dict.append(dict(row)) start_count = 0 for data_dict[start_count] in data_dict: ib_level = 0 if data_dict[start_count].get('parent_id') is None: data_dict[start_count].update({'IB_level': ib_level}) print('IB level 0:\n') print(data_dict[start_count]) elif isinstance(data_dict[start_count].get('parent_id'), int): ib_level_calc = ib_level + 1 data_dict[start_count].update({'IB_level': ib_level_calc}) print('IB level 1:\n') print(data_dict[start_count]) start_count = start_count + 1

In the code, I take data from the database, convert it to a list of type dictionaries (account_id, parent_id), compare each dictionary in the list with the parenting criterion and add a new IB_level item with the transition value (0 parent, 1 descendant, 2 descendant of children) to the checked dictionary .

It is possible to count the transition only from 0 to 1 only 1 attitude. The problem is that the number of transitions is unknown, and it is required to bring in all degrees of relations how far each account is from its parent with parent_id = None, expressed by the number IB_level.

Thanks in advance for any information.

    1 answer 1

    Found how to do it.

     # Prereq. # sudo apt-get install libpq-dev # sudo pip3 install psycopg2 # -*- coding: utf-8 -*- import psycopg2 import psycopg2.extras sql = "select * from accounts" try: conn = psycopg2.connect("dbname='db' user='user'" \ " host='ps.contora.com' password='xxxxxxx'") except psycopg2.Error as err: print("Connection error: {}".format(err)) try: cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) cur.execute(sql) data = cur.fetchall() except psycopg2.Error as err: print("Query error: {}".format(err)) class Account: def __init__(self, i, p, id_m, ac_n, mt4_g): self.account_id = i self.parent_id = p self.ib_number = id_m self.account_number = ac_n self.mt4_group = mt4_g data_dict = [] for row in data: data_dict.append(Account(row.get('account_id'), row.get('parent_id'), row.get('ib_number'), row.get('account_number'), row.get('mt4_group') )) cLvlAccs = [None] cLvl = 0 while len(cLvlAccs) > 0: newLvlAccs = [] for acc in data_dict: if (cLvlAccs.count(acc.parent_id) > 0): acc.lvl = cLvl newLvlAccs.append(acc.account_id) cLvlAccs = newLvlAccs cLvl += 1 for acc in data_dict: print("{0},{1},{2},{3},{4},{5}".format(acc.account_id, acc.parent_id, acc.lvl, acc.ib_number, acc.account_number, acc.mt4_group ))