to_tree.py

#!/usr/bin/env python3

# go from a csv of nested data to a bunch of sql insert statements
from csv import reader
from uuid import uuid4

file_name = "category.csv"

def do():
    collections = {}
    with open(file_name, "r") as csv_file:
        csv_reader = reader(csv_file)

        for row in csv_reader:
            category = {}
            # second level
            category.setdefault(row[1],[]).append((row[2], row[3]))
            # top level
            collections.setdefault(row[0],[]).append(category)

    for k,v in collections.items():
        collections[k] = mergeFold(v)

    for k,v in collections.items():

        newKey = mkUniq()
        sql = genSql(k, newKey, newKey)
        print(sql)

        for sub,val in v.items():

            k = mkUniq()
            subsql = genSql(sub, k, "{}.{}".format(newKey, k))
            print(subsql)

            for (group, pgKey) in val:
                groupKey = mkUniq()
                print(genSql(group, groupKey, "{}.{}.{}".format(newKey, k, groupKey), pg_id = pgKey))


    return collections

# takes a list of dicts, merges them all into a single dict
# [{}] -> {}
def mergeFold(ds):
    res = {}
    for dictionary in ds:
        for k,v in dictionary.items():
            res.setdefault(k,[]).append(*v)
    return res

def mkUniq():
    s = str(uuid4())
    return s.replace("-", "")

def genSql(s, newKey, path, pg_id=None):
    pg_ref = "null"
    if pg_id:
        pg_ref = "\"{}\"".format(pg_id)

    sql = "insert into tree values (\"{}\", \"{}\", \"{}\", {})".format(newKey, s, path, pg_ref)
    return sql

if __name__ == '__main__':
    do()