XcJQvZEN

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