pvZmiK9k

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