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