如何使用SQLite递归查询获取文件完整路径？

问题背景 #

现有SQLite的file表结构：

TABLE file(
    "ID" STRING PRIMARY KEY,
    "filename" string NOT NULL,
    "parent" string,
    "is_folder" BOOLEAN NOT NULL,
    FOREIGN KEY (parent) REFERENCES file(ID)
)

原Python实现通过循环查询父目录获取文件路径，深层目录下多次查询导致性能低下：

async def get_file_path(self, ID: str):
    # 性能较差的实现
    cursor = await self.db.execute(
        'SELECT * FROM file WHERE ID = ?', 
        (ID, )
    )
    file = await cursor.fetchone()
    names = [file[1]]
    while file[2]:  # 存在父目录时循环查询
        cursor = await self.db.execute(
            'SELECT * FROM file WHERE ID = ?',
            (file[2], )
        )
        file = await cursor.fetchone()
        names.append(file[1])
    return '/'.join(names[::-1])

解决方案：使用SQLite递归CTE #

利用SQLite的WITH RECURSIVE语法，一次性获取从目标文件到根目录的所有层级，避免多次数据库查询。

递归查询语句 #

WITH RECURSIVE file_path AS (
    -- 起点：查询目标文件本身
    SELECT ID, filename, parent
    FROM file
    WHERE ID = ?
    UNION ALL
    -- 递归：向上遍历父目录
    SELECT f.ID, f.filename, f.parent
    FROM file f
    JOIN file_path fp ON f.ID = fp.parent
)
SELECT filename FROM file_path;

语句解释

• 基础部分：先定位到目标ID对应的文件，作为递归的起始节点。
• 递归部分：通过关联当前节点的parent与父节点的ID，逐层向上遍历，直到父节点为NULL（根目录）时停止。
• 查询结果的filename顺序为「目标文件 → 父目录 → ... → 根目录」，完全匹配期望的输出格式。

优化后的Python实现 #

async def get_file_path(self, ID: str):
    cursor = await self.db.execute('''
        WITH RECURSIVE file_path AS (
            SELECT ID, filename, parent
            FROM file
            WHERE ID = ?
            UNION ALL
            SELECT f.ID, f.filename, f.parent
            FROM file f
            JOIN file_path fp ON f.ID = fp.parent
        )
        SELECT filename FROM file_path;
    ''', (ID,))
    # 提取所有路径片段，按查询顺序组成列表
    path_parts = [row[0] async for row in cursor]
    # 若需要拼接成路径字符串，执行：return '/'.join(path_parts[::-1])
    return path_parts

测试验证 #

使用示例数据：

INSERT INTO file (ID, filename, parent, is_folder) VALUES 
  ('ID1', 'folder_one', NULL, true), 
  ('ID2', 'folder_in_folder_one', 'ID1', true),
  ('ID3', 'another_folder_in_first', 'ID1', true), 
  ('ID4', 'deep_file', 'ID2', false); 

查询ID4时，返回结果为：

["deep_file", "folder_in_folder_one", "folder_one"]

额外优化建议 #

• 为parent字段创建索引：CREATE INDEX idx_file_parent ON file(parent);，大幅提升递归查询的遍历效率，尤其适合数据量较大的场景。
• 对频繁查询的路径结果进行缓存，减少重复数据库操作。

内容的提问来源于stack exchange，提问作者Charwisd