Sqlite split string and get rowid from parent folder

Question

I have a database with a filetree from a folder I'm trying to keep track of.

I am wondering if there is an easy way to make a sqlite query to find out the row id of a files parent.

Lets say i have these files in the database:

1. grandparent/parent/child/file
2. grandparent/parent/child/file2
3. grandparent/parent/child/
4. grandparent/parent/

is it possible to select one file, split it by "/" and remove the last element before setting them back together and doing a WHERE query.

This would mean that to find the parent of "grandparent/parent/child/file", we split it into "grandparent", "parent", "child" and "file". Removing the "file" and putting them back together with a "/" delimiter and then search for the match.

Every path entry is unique.

Here is my solution in python:

def inserting_parentID(self):
    query = '''SELECT rowid, Path FROM {}'''.format(self.table_name)
    all_paths = self.cur.execute(query).fetchall()

    for rowid, path in all_paths:
        parent_path_split = path.split("\\")
        if rowid == 1:
            continue
        if path[-1] == "\\":
            parent_path_joined = "\\".join(parent_path_split[:-2]) + "\\"
        else:
            parent_path_joined = "\\".join(parent_path_split[:-1]) + "\\"

        parent_query = '''SELECT rowid FROM {} WHERE Path = "{}"'''.format(self.table_name, parent_path_joined)
        parent_paths = self.cur.execute(parent_query).fetchone()

        update_parentID = '''UPDATE {} SET Parent=? WHERE rowid = "{}"'''.format(self.table_name, rowid)
        self.cur.execute(update_parentID, (parent_paths))
        self.conn.commit()