XMLTODDL

from lxml import etree




def XML2DDL(source: str):
    tree = etree.parse(source)


    ddl = ""




    data_arr = []
    item_dict = {}
    prm_k = []
    frg_k = {}
    frg_ref = {}

    spec = {}

    ctr = -1



    for p in tree.iter():
        if p.tag == "tables":
            continue

        if(p.tag == "columns" or p.tag == "rows"):
            continue

        if(p.tag == "table"):
            spec["table_name"] = p.attrib["name"]
            continue

        if (p.tag == "constraints"):
            continue

        if (p.tag == "primary-key"):
            for k in p.getchildren():
                if k.tag == "column-ref":
                    prm_k.append(k.attrib["name"])
            continue

        if (p.tag == "foreign-key"):
            frg_k[(p.attrib['name'], p.attrib['references'])] = []
            frg_ref[(p.attrib['name'], p.attrib['references'])] = []
            for k in p.getchildren():
                if k.tag == "column-ref":
                    frg_k[(p.attrib['name'], p.attrib['references'])].append(k.attrib["name"])
                if k.tag == "referenced-column":
                    frg_ref[(p.attrib['name'], p.attrib['references'])].append(k.attrib['name'])
            continue

        if p.tag == "column-ref":
            continue
        if p.tag == "referenced-column":
            continue

        if(p.tag == "column"):
            spec[p.attrib["name"]] = p.attrib["type"]
            continue

        if (p.tag == "row"):
            if (len(item_dict)):
                data_arr.append(item_dict)
            item_dict = {}
            continue

        item_dict[p.tag] = str(p.text)
    data_arr.append(item_dict)


    ans  = "DROP TABLE IF EXISTS " + spec["table_name"] + ';' + '\n'


    if frg_k and frg_ref:
        final_str = ''
        for i in frg_k:
            frg_str = ' FOREIGN KEY ('
            for k in frg_k[i]:
                frg_str += k
                frg_str += ','
            frg_str = frg_str[:len(frg_str) - 1] + ')' + " REFERENCES " + i[1] + '('

            for k in frg_ref[i]:
                frg_str += k
                frg_str += ','
            frg_str = frg_str[:len(frg_str) - 1] + '), '
            final_str += frg_str
        final_str = final_str[:len(final_str)-2]
    else:
        final_str = ''


    ans  +=  "CREATE TABLE " + spec["table_name"] + " ("
    for item in spec:
        if(item != "table_name"):
            ans += (item + " " + spec[item] + ", ")
    if prm_k:
        primk = ", PRIMARY KEY("
        for j in prm_k:
            primk += j
            primk += ','
        primk = primk[:len(primk)-1] + ')'

    else:
        primk = ''

    ans = ans[:len(ans) - 2] + primk + final_str + ')'';' + '\n'


    temp = "("
    for i in spec:
        if(i != "table_name"):
            temp += i + ", "
    temp = temp[:len(temp) - 2] + ")"


    for row in data_arr:
        ans += "INSERT INTO " + spec["table_name"]  + temp + " values"
        values  = "("
        for i in row:
            if(spec[i] == 'VARCHAR(50)'):
                values += '"'+ row[i] +'"' + ", "
                continue
            values += row[i] + ", "


        values = values[:len(values) - 2] + ')'
        ans += values + ';' + '\n'
    return ans


print(XML2DDL(r"C:\Users\PCAsusrog\Downloads\file8.xml"))