-
Notifications
You must be signed in to change notification settings - Fork 0
/
exercise_15_3.py
70 lines (55 loc) · 1.87 KB
/
exercise_15_3.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
# Exercise 15.3 Tracks
import xml.etree.ElementTree as ET
import sqlite3
conn = sqlite3.connect('trackdb.sqlite')
cur = conn.cursor()
cur.executescript('''
DROP TABLE IF EXISTS Album;
DROP TABLE IF EXISTS Artist;
DROP TABLE IF EXISTS Track;
CREATE TABLE Album (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
title TEXT UNIQUE,
artist_id INTEGER
);
CREATE TABLE Artist (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
name TEXT UNIQUE
);
CREATE TABLE Track (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
title TEXT UNIQUE,
album_id INTEGER,
len INTEGER,
rating INTEGER,
count INTEGER
);
''')
fname = input('Enter a file name: ')
if len(fname) < 1 : fname = 'Library.xml'
def lookup(dic, key) :
found = False
for child in dic :
if found : return child.text
if child.tag == 'key' and child.text == key : found = True
return None
stuff = ET.parse(fname)
all = stuff.findall('dict/dict/dict')
for entry in all :
if lookup(entry, 'Track ID') is None : continue
name = lookup(entry, 'Name')
artist = lookup(entry, 'Artist')
album = lookup(entry, 'Album')
count = lookup(entry, 'Play Count')
rating = lookup(entry, 'Rating')
length = lookup(entry, 'Total Time')
if name is None or artist is None or album is None : continue
cur.execute('INSERT OR IGNORE INTO Artist (name) VALUES (?)', (artist,))
cur.execute('SELECT id FROM Artist WHERE name = ?', (artist,))
artist_id = cur.fetchone()[0]
cur.execute('INSERT OR IGNORE INTO Album (title, artist_id) VALUES (?, ?)', (album, artist_id))
cur.execute('SELECT id FROM Album WHERE title = ?', (album,))
album_id = cur.fetchone()[0]
cur.execute('INSERT OR REPLACE INTO Track (title, album_id, len, rating, count) VALUES (?,?,?,?,?)', (name, album_id, length, rating, count))
conn.commit()
cur.close()