5Pdzj02E

1import sqlite3
2import xml.etree.ElementTree as ET
3
4conn = sqlite3.connect('trackdb.sqlite')
5cur = conn.cursor()
6
7cur.executescript('''
8DROP TABLE IF EXISTS Artist;
9DROP TABLE IF EXISTS Genre;
10DROP TABLE IF EXISTS Album;
11DROP TABLE IF EXISTS Track;
12
13CREATE TABLE Artist (
14    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
15    name TEXT UNIQUE
16);
17CREATE TABLE Genre (
18    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
19    name TEXT UNIQUE
20);
21CREATE TABLE Track (
22    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
23    title TEXT UNIQUE,
24    album_id INTEGER,
25    genre_id INTEGER,
26    len INTEGER, rating INTEGER, count  INTEGER
27
28);
29CREATE TABLE Album (
30    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
31    artist_id INTEGER,
32    title TEXT UNIQUE
33);
34''')
35
36fname = input('Enter File Name:')
37if len(fname)<1 : fname = 'Library.xml'
38def lookup (d, key):
39    found = False
40    for child in d:
41        if found : return child.text
42        if child.tag == 'key' and child.text ==key:
43            found = True
44        return None    
45
46parsed  = ET.parse(fname)
47all = parsed.findall('dict/dict/dict')
48print('Dict count: ', len(all))
49for entry in all :
50    if(lookup(entry, 'Track ID') is None) : continue
51    name = lookup(entry, 'Name')
52    artist = lookup(entry, 'Artist')
53    album = lookup(entry, 'Album')
54    genre = lookup(entry,'Genre')
55    count = lookup(entry, 'Play Count')
56    rating = lookup(entry, 'Rating')
57    length = lookup(entry, 'Total Time')
58
59    if name is None or  artist is None or album is None or genre is None : 
60        continue
61    print(name, artist, album, genre, count, rating, length)
62    cur.execute('''INSERT OR IGNORE INTO Artist (name) VALUES (?)''', (artist, ))
63    cur.execute('SELECT id FROM Artist WHERE name = ? ', (artist, ))
64    artist_id = cur.fetchone()[0]
65    cur.execute('''INSERT OR IGNORE INTO Genre (name) 
66        VALUES ( ? )''', ( genre, ) )
67    cur.execute('SELECT id FROM Genre WHERE name = ? ', (genre, ))
68    genre_id = cur.fetchone()[0]
69
70    cur.execute('''INSERT OR IGNORE INTO Album (title, artist_id) 
71        VALUES ( ?, ? )''', ( album, artist_id ) )
72    cur.execute('SELECT id FROM Album WHERE title = ? ', (album, ))
73    album_id = cur.fetchone()[0]
74
75    cur.execute('''INSERT OR REPLACE INTO Track
76        (title, album_id, genre_id, len, rating, count) 
77        VALUES ( ?, ?, ?, ?, ?, ? )''', 
78        ( name, album_id, genre_id, length, rating, count ) )
79
80    conn.commit()
81
82sqlstr = 'SELECT Track.title, Artist.name, Album.title, Genre.name FROM Track JOIN Genre JOIN Album JOIN Artist ON Track.genre_id = Genre.ID and Track.album_id = Album.id AND Album.artist_id = Artist.id ORDER BY Artist.name LIMIT 3'
83print()
84for row in cur.execute(sqlstr):
85    print(str(row[0]), str(row[1]), str(row[2]), str(row[3]))
86cur.close()