XMLTODDL MULTITABLE

from lxml import etree
import re



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


    ddl = ""

    ref = tbl.getroot().findall("table")

    for tree in ref:

        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(re.findall('[VARCHAR]+', spec[i])):
                    values += '"'+ row[i] +'"' + ", "
                    continue
                values += row[i] + ", "


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

        ddl = ddl + ans + '\n' + '\n'

    return ddl


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





    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"))