import sqlite3
import xml.dom.minidom as mdom
import xml.etree.ElementTree as ET

conn = sqlite3.connect('chinook.db')
c = conn.cursor()
c.execute("SELECT * FROM customers")
root = ET.Element('Answer')
for customer in c.fetchall():
    customer_xml = ET.SubElement(root, 'customer')
    name, surname = customer[1], customer[2]
    customer_xml.set('name', name)
    customer_xml.set('surname', surname)
    c.execute("SELECT * FROM genres")
    for genre in c.fetchall():
        tracks = []
        c.execute("SELECT InvoiceId FROM invoices WHERE CustomerId = ?", (customer[0],))
        for invoice in c.fetchall():
            c.execute("SELECT * from invoice_items WHERE InvoiceId = ?", (invoice[0],))
            for invoice_item in c.fetchall():
                c.execute("SELECT * from tracks WHERE TrackId = ? AND GenreId = ?", (invoice_item[2], genre[0]))
                for track in c.fetchall():
                    tracks.append((invoice, invoice_item, track))
        if len(tracks) > 0:
            genre_xml = ET.SubElement(customer_xml, 'genre')
            genre_xml.set('name', genre[1])
            for invoice, invoice_item, track in tracks:
                track_xml = ET.SubElement(genre_xml, 'track')
                track_xml.set('name', track[1])
                c.execute("SELECT Title from albums WHERE AlbumId = ?", (track[2],))
                album = c.fetchone()
                track_xml.set('album', album[0])
                track_xml.set('price', str(invoice_item[3]))

st = ET.tostring(root, encoding='unicode')
print(st)
xml = mdom.parseString(st)
outxml = open('result.xml', 'w', encoding='utf8')
outxml.write(xml.toprettyxml())
outxml.close()