3aq23LnS

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
14CREATE TABLE Artist (
15    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
16    name    TEXT UNIQUE
17);
18
19CREATE TABLE Genre (
20    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
21    name    TEXT UNIQUE
22);
23
24CREATE TABLE Album (
25    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
26    artist_id  INTEGER,
27    title   TEXT UNIQUE
28);
29
30CREATE TABLE Track (
31    id  INTEGER NOT NULL PRIMARY KEY 
32        AUTOINCREMENT UNIQUE,
33    title TEXT  UNIQUE,
34    album_id  INTEGER,
35    genre_id  INTEGER,
36    len INTEGER, rating INTEGER, count INTEGER
37);
38''')
39
40# fname = raw_input('Enter file name: ')
41fname = 'Library.xml'
42if ( len(fname) < 1 ) : fname = 'Library.xml'
43
44# <key>Track ID</key><integer>369</integer>
45# <key>Name</key><string>Another One Bites The Dust</string>
46# <key>Artist</key><string>Queen</string>
47def lookup(d, key):
48    found = False
49    for child in d:
50        if found : return child.text
51        if child.tag == 'key' and child.text == key : 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 ) : continue
59
60    name = lookup(entry, 'Name')
61    artist = lookup(entry, 'Artist')
62    album = lookup(entry, 'Album')
63    count = lookup(entry, 'Play Count')
64    rating = lookup(entry, 'Rating')
65    length = lookup(entry, 'Total Time')
66
67    if name is None or artist is None or album is None : 
68        continue
69
70    print name, artist, album, count, rating, length
71
72    cur.execute('''INSERT OR IGNORE INTO Artist (name) 
73        VALUES ( ? )''', ( artist, ) )
74    cur.execute('SELECT id FROM Artist WHERE name = ? ', (artist, ))
75    artist_id = cur.fetchone()[0]
76
77    cur.execute('''INSERT OR IGNORE INTO Album (title, artist_id) 
78        VALUES ( ?, ? )''', ( album, artist_id ) )
79    cur.execute('SELECT id FROM Album WHERE title = ? ', (album, ))
80    album_id = cur.fetchone()[0]
81
82    cur.execute('''INSERT OR REPLACE INTO Track
83        (title, album_id, len, rating, count) 
84        VALUES ( ?, ?, ?, ?, ? )''', 
85        ( name, album_id, length, rating, count ) )
86
87    conn.commit()