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

概要

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

基盤データイメージ

部長Ａ━━━━━課長Ａ┳田中
　　　　　　　　　　　┗鈴木
部長Ｂ━━━━━課長Ｂ━山田
部長Ｃ┳次長Ａ┳課長Ｃ━木村
　　　┃　　　┗課長Ｄ┳村田
　　　┃　　　　　　　┗渡部
　　　┗次長Ｂ┳課長Ｅ━福田
　　　　　　　┗課長Ｆ┳原田
　　　　　　　　　　　┗中野

基盤データイメージ

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

Name	Boss
部長Ａ	NULL
部長Ｂ	NULL
部長Ｃ	NULL
課長Ａ	部長Ａ
課長Ｂ	部長Ｂ
田中	課長Ａ
鈴木	課長Ａ
山田	課長Ｂ
次長Ａ	部長Ｃ
次長Ｂ	部長Ｃ
課長Ｃ	次長Ａ
課長Ｄ	次長Ａ
木村	課長Ｃ
村田	課長Ｄ
渡部	課長Ｄ
課長Ｅ	次長Ｂ
課長Ｆ	次長Ｂ
福田	課長Ｅ
原田	課長Ｆ
中野	課長Ｆ

基盤データ

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
次長Ａ	部長Ｃ
次長Ｂ	部長Ｃ
課長Ａ	部長Ａ
課長Ｂ	部長Ｂ
課長Ｃ	部長Ｃ
課長Ｄ	部長Ｃ
課長Ｅ	部長Ｃ
課長Ｆ	部長Ｃ
田中	部長Ａ
鈴木	部長Ａ
山田	部長Ｂ
木村	部長Ｃ
村田	部長Ｃ
渡部	部長Ｃ
福田	部長Ｃ
原田	部長Ｃ
中野	部長Ｃ

取得データ

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 pprint
import sqlite3


def main():

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

    con = None
    cur = None

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

        create_sql = """
            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 = """CREATE TABLE OrganogramInterimResult01 AS """
        # select_sql = """
        #     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 = """CREATE TABLE OrganogramInterimResult02 AS """
        # select_sql = """
        #     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 = """CREATE TABLE OrganogramInterimResult03 AS """
        # select_sql = """
        #     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 = """CREATE TABLE OrganogramInterimResult04 AS """
        # select_sql = """
        #     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 = """CREATE TABLE OrganogramFinallyResult AS """
        select_sql = """
            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("SELECT name FROM sqlite_master WHERE type='table'")]))
        print()

        def pprint_pprint_table(cur, 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(cur, "Organogram")
        # pprint_pprint_table(cur, "OrganogramInterimResult01")
        # pprint_pprint_table(cur, "OrganogramInterimResult02")
        # pprint_pprint_table(cur, "OrganogramInterimResult03")
        # pprint_pprint_table(cur, "OrganogramInterimResult04")
        pprint_pprint_table(cur, "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
再帰深度のカラムを出力対象外に変更。

Link

再帰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/

あとがき

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