PythonとSQLite3と再帰SQLで木構造のルートの取得

概要

Nameごとの木の一番上のBossを取得。
下部に記載のLinkを参考にして作成。

基盤データイメージ

部長A━━━━━課長A┳田中
           ┗鈴木
部長B━━━━━課長B━山田
部長C┳次長A┳課長C━木村
   ┃   ┗課長D┳村田
   ┃       ┗渡部
   ┗次長B┳課長E━福田
       ┗課長F┳原田
           ┗中野

基盤データイメージ

上の図の関係を隣接リストにしたのが下の表。

Name Boss
部長A NULL
部長B NULL
部長C NULL
課長A 部長A
課長B 部長B
田中 課長A
鈴木 課長A
山田 課長B
次長A 部長C
次長B 部長C
課長C 次長A
課長D 次長A
木村 課長C
村田 課長D
渡部 課長D
課長E 次長B
課長F 次長B
福田 課長E
原田 課長F
中野 課長F

基盤データ

Name Boss
BuchoA None
BuchoB None
BuchoC None
KachoA BuchoA
KachoB BuchoB
Tanaka KachoA
Suzuki KachoA
Yamada KachoB
JichoA BuchoC
JichoB BuchoC
KachoC JichoA
KachoD JichoA
Kimura KachoC
Murata KachoD
Watabe KachoD
KachoE JichoB
KachoF JichoB
Fukuda KachoE
Harada KachoF
Nakano KachoF

取得データイメージ

Name Boss
次長A 部長C
次長B 部長C
課長A 部長A
課長B 部長B
課長C 部長C
課長D 部長C
課長E 部長C
課長F 部長C
田中 部長A
鈴木 部長A
山田 部長B
木村 部長C
村田 部長C
渡部 部長C
福田 部長C
原田 部長C
中野 部長C

取得データ

Name Boss
JichoA BuchoC
JichoB BuchoC
KachoA BuchoA
KachoB BuchoB
KachoC BuchoC
KachoD BuchoC
KachoE BuchoC
KachoF BuchoC
Suzuki BuchoA
Tanaka BuchoA
Yamada BuchoB
Fukuda BuchoC
Harada BuchoC
Kimura BuchoC
Murata BuchoC
Nakano BuchoC
Watabe BuchoC

Pythonコード

import sqlite3
import pprint

def main():

    try:
       #print("PRESS ENTER KEY TO BOOT")
       #input()

        con = None
        cur = None

        con = sqlite3.connect(":memory:")
        cur = con.cursor()

        create_sql = f"""
            CREATE TABLE Organogram (
                Name TEXT
               ,Boss TEXT
            )
        """
        cur.execute(create_sql)

        cur.execute("INSERT INTO Organogram VALUES('BuchoA', NULL    )")
        cur.execute("INSERT INTO Organogram VALUES('BuchoB', NULL    )")
        cur.execute("INSERT INTO Organogram VALUES('BuchoC', NULL    )")

        cur.execute("INSERT INTO Organogram VALUES('KachoA', 'BuchoA')")
        cur.execute("INSERT INTO Organogram VALUES('KachoB', 'BuchoB')")
        cur.execute("INSERT INTO Organogram VALUES('Tanaka', 'KachoA')")
        cur.execute("INSERT INTO Organogram VALUES('Suzuki', 'KachoA')")
        cur.execute("INSERT INTO Organogram VALUES('Yamada', 'KachoB')")

        cur.execute("INSERT INTO Organogram VALUES('JichoA', 'BuchoC')")
        cur.execute("INSERT INTO Organogram VALUES('JichoB', 'BuchoC')")

        cur.execute("INSERT INTO Organogram VALUES('KachoC', 'JichoA')")
        cur.execute("INSERT INTO Organogram VALUES('KachoD', 'JichoA')")
        cur.execute("INSERT INTO Organogram VALUES('Kimura', 'KachoC')")
        cur.execute("INSERT INTO Organogram VALUES('Murata', 'KachoD')")
        cur.execute("INSERT INTO Organogram VALUES('Watabe', 'KachoD')")

        cur.execute("INSERT INTO Organogram VALUES('KachoE', 'JichoB')")
        cur.execute("INSERT INTO Organogram VALUES('KachoF', 'JichoB')")
        cur.execute("INSERT INTO Organogram VALUES('Fukuda', 'KachoE')")
        cur.execute("INSERT INTO Organogram VALUES('Harada', 'KachoF')")
        cur.execute("INSERT INTO Organogram VALUES('Nakano', 'KachoF')")

#       create_sql = f"""CREATE TABLE OrganogramInterimResult01 AS """
#       select_sql = f"""
#           WITH RECURSIVE Recursions(Name, Boss) AS (
#               SELECT   Organogram.Name
#                       ,Organogram.Name
#               FROM     Organogram
#               UNION
#               SELECT   Organogram.Name
#                       ,Recursions.Boss
#               FROM     Organogram
#                       ,Recursions
#               WHERE    Organogram.Boss = Recursions.Name
#           )
#           SELECT   Name
#                   ,Boss
#           FROM     Recursions
#       """
#       cur.execute(create_sql + select_sql)

#       create_sql = f"""CREATE TABLE OrganogramInterimResult02 AS """
#       select_sql = f"""
#           WITH RECURSIVE Recursions(Depth, Name, Boss) AS (          --change
#               SELECT   0                                             --change
#                       ,Organogram.Name                               --insert comma
#                       ,Organogram.Name
#               FROM     Organogram
#               UNION
#               SELECT   Recursions.Depth + 1                          --change
#                       ,Organogram.Name                               --insert comma
#                       ,Recursions.Boss
#               FROM     Organogram
#                       ,Recursions
#               WHERE    Organogram.Boss = Recursions.Name
#           )
#           SELECT   Depth                                             --change
#                   ,Name                                              --insert comma
#                   ,Boss
#           FROM     Recursions
#       """
#       cur.execute(create_sql + select_sql)

#       create_sql = f"""CREATE TABLE OrganogramInterimResult03 AS """
#       select_sql = f"""
#           WITH RECURSIVE Recursions(Depth, Name, Boss) AS (
#               SELECT   0
#                       ,Organogram.Name
#                       ,Organogram.Name
#               FROM     Organogram
#               UNION
#               SELECT   Recursions.Depth + 1
#                       ,Organogram.Name
#                       ,Recursions.Boss
#               FROM     Organogram
#                       ,Recursions
#               WHERE    Organogram.Boss = Recursions.Name
#           )
#           SELECT   Depth
#                   ,Name
#                   ,Boss                                              --insert
#           FROM (                                                     --insert
#               SELECT   *                                             --insert
#                       ,max(Depth) over(partition by Name) as DepthMax--insert
#               FROM     Recursions                                    --insert space
#           )                                                          --insert
#           WHERE    Depth = DepthMax                                  --insert
#           ORDER BY Depth                                             --insert
#                   ,Name                                              --insert
#                   ,Boss                                              --insert
#       """
#      cur.execute(create_sql + select_sql)

#       create_sql = f"""CREATE TABLE OrganogramInterimResult04 AS """
#       select_sql = f"""
#           WITH RECURSIVE Recursions(Depth, Name, Boss) AS (
#               SELECT   0
#                       ,Organogram.Name
#                       ,Organogram.Name
#               FROM     Organogram
#               UNION
#               SELECT   Recursions.Depth + 1
#                       ,Organogram.Name
#                       ,Recursions.Boss
#               FROM     Organogram
#                       ,Recursions
#               WHERE    Organogram.Boss = Recursions.Name
#           )
#           SELECT   Depth
#                   ,Name
#                   ,Boss
#           FROM (
#               SELECT   *
#                       ,max(Depth) over(partition by Name) as DepthMax
#               FROM     Recursions
#           )
#           WHERE    Depth = DepthMax
#           AND      Depth > 0                                         --insert
#           ORDER BY Depth
#                   ,Name
#                   ,Boss
#       """
#       cur.execute(create_sql + select_sql)

        create_sql = f"""CREATE TABLE OrganogramFinallyResult AS """
        select_sql = f"""
            WITH RECURSIVE Recursions(Depth, Name, Boss) AS (
                SELECT   0
                        ,Organogram.Name
                        ,Organogram.Name
                FROM     Organogram
                UNION
                SELECT   Recursions.Depth + 1
                        ,Organogram.Name
                        ,Recursions.Boss
                FROM     Organogram
                        ,Recursions
                WHERE    Organogram.Boss = Recursions.Name
            )
            SELECT --Depth                                             --delete
                     Name                                              --delete comma
                    ,Boss
            FROM (
                SELECT   *
                        ,max(Depth) over(partition by Name) as DepthMax
                FROM     Recursions
            )
            WHERE    Depth = DepthMax
            AND      Depth > 0
            ORDER BY Depth
                    ,Name
                    ,Boss
        """
        cur.execute(create_sql + select_sql)

        print("sqlite_master")
        pprint.pprint(tuple([row[0] for row in cur.execute(f"SELECT name FROM sqlite_master WHERE type='table'")]))
        print(" ")

        def pprint_pprint_table(table):
            print(f"{table}")
            pprint.pprint(tuple([row[1] for row in cur.execute(f"PRAGMA table_info({table})")]))
            pprint.pprint(tuple(cur.execute(f"SELECT * FROM {table}")))
            print(" ")

        pprint_pprint_table("Organogram")
#       pprint_pprint_table("OrganogramInterimResult01")
#       pprint_pprint_table("OrganogramInterimResult02")
#       pprint_pprint_table("OrganogramInterimResult03")
#       pprint_pprint_table("OrganogramInterimResult04")
        pprint_pprint_table("OrganogramFinallyResult")

    except Exception as e: print(e)
    finally:
        cur = cur.close() if cur is not None else None
        con = con.close() if con is not None else None

        print(" ")
        print("PRESS ENTER KEY TO EXIT")
        input()

if __name__ == "__main__":
    main()
中間結果

行頭でコメントアウトしている部分を、
行頭のコメントアウトをアンコメントしてコードを有効化すると、
以下の途中経過の情報を出力可能です。

OrganogramInterimResult01
再帰SQLで総当たり(?)した情報を出力。

OrganogramInterimResult02
再帰深度(再帰回数)を保持するカラムを追加。

OrganogramInterimResult03
Nameごとの最大深度のレコードのみを取得。

OrganogramInterimResult04
再帰深度0のレコードを出力対象外に変更。

終結

OrganogramFinallyResult
再帰深度のカラムを出力対象外に変更。

再帰SQL -図解-
https://qiita.com/Shoyu_N/items/f1786f99545fa5053b75

そろそろSQLのウィンドウ関数を理解したい
https://qiita.com/w-sato-ist/items/63600a3ab84aad38e879
https://qiita.com/w-sato-ist/items/75bf6bb60ccafda82840
https://qiita.com/w-sato-ist/items/86aab7e7722ff9454ec0

SQL便利な関数
https://avinton.com/academy/sql-intermediate/

あとがき

ノンプログラマーの素人が記述をしたコードです。
狭い利用範囲と少ない利用頻度での確認ですので、
記載内容に間違いや勘違いがあるかもしれません。
上記内容を参照の際は自己責任でお願い致します。