python 操作Oracle 自关联表进行树结构复制算法

最新推荐文章于 2024-12-22 19:04:56 发布

原创最新推荐文章于 2024-12-22 19:04:56 发布 · 547 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql

Python 同时被 2 个专栏收录

72 篇文章

订阅专栏

oracle

21 篇文章

订阅专栏

本文介绍了一种使用递归算法实现的树形结构复制方法，该方法能够在关系型数据库中高效地复制树节点及其子节点，适用于需要进行树形结构操作的应用场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

最近一个项目中，用关系型表来存储树型结构，其中有一段树节点复制的算法，典型的递归运用，可作为递归算法参考练习。

    def CheckItem_GET_ById(self, dataid):
        """
        通过id取完整记录
        :param dataid:数据id
        :return:fieldlabel, pid, fieldtype, required,  imglimit, dsporder, id, bz
        """
        c = self.conn.cursor()
        keys = self.CheckItemTableColumns.keys()
        cursor = c.execute(
            f"select {','.join(keys)} "
            f"from {self.CheckItemTable} where id = :1", (dataid,))
        return cursor.fetchone()


    def CheckItem_ADD_CopyTree(self, from_id, to_pid, added_list):
        if from_id in added_list:
            return added_list # 排除掉新添加的id,又作为源来添加，避免无限递归.
        c = self.conn.cursor()
        srcid = from_id
        toid = to_pid
        if toid <=0: # 复制目的不存在，需要新建为方案
            c.execute(f"insert into {self.CheckItemTable}  (pid,fieldlabel,fieldtype,required,imglimit,dsporder,bz)  "
                      f"select  0,fieldlabel||' 复制',fieldtype,required,imglimit,dsporder,bz "
                      f"from {self.CheckItemTable} where id = :1", (srcid,))
            lastrowid = c.lastrowid
            cursor = c.execute(f"select id from {self.CheckItemTable} where rowid=:1 ", (lastrowid,))
            row = cursor.fetchone()
            new_pid = row[0]
            added_list.append(new_pid)
        else:
            c.execute(f"insert into {self.CheckItemTable}  (pid,fieldlabel,fieldtype,required,imglimit,dsporder,bz)  "
                      f"select  :1,fieldlabel,fieldtype,required,imglimit,dsporder,bz "
                      f"from {self.CheckItemTable} where id = :2", (toid, srcid))
            lastrowid = c.lastrowid
            cursor = c.execute(f"select id from {self.CheckItemTable} where rowid=:1 ", (lastrowid,))
            row = cursor.fetchone()
            new_pid = row[0]
            c.execute(f"insert into {self.ChooseItemTable} (mainid,selectname,selectvalue) "
                      f"select :1,selectname,selectvalue from {self.ChooseItemTable} where mainid=:2", (new_pid, srcid))

            added_list.append(new_pid)


        cids = self.CheckItem_GET_ByPid(srcid)
        for nid in cids:
            self.CheckItem_ADD_CopyTree(nid[0], new_pid, added_list)
        self.conn.commit()