Musical Track Database

This application will read an iTunes export file in XML and produce a properly normalized database,?If you run the program multiple times in testing or with different files, make sure to empty out the data before each run.

You can use this code as a starting point for your application:?http://·/code3/tracks.zip. The ZIP file contains the?Library.xml?file to be used for this assignment. You can export your own tracks from iTunes and create a database, but for the database that you turn in for this assignment, only use the?Library.xml?data that is provided.

Check code:

SELECT Track.title, Artist.name, Album.title, Genre.name FROM Track JOIN Genre JOIN Album JOIN Artist ON Track.genre_id = Genre.ID and Track.album_id = Album.id AND Album.artist_id = Artist.id ORDER BY Artist.name LIMIT 3

个人收获：

?lookup函数思路：首先if False:print，然后找到了标签头置为True，这样下一个就会成为值

def lookup(d, key): found = False for child in d: if found : return child.text if child.tag == 'key' and child.text == key : found = True return None

个人解法：

import xml.etree.ElementTree as ET import sqlite3 conn = sqlite3.connect('trackdb.sqlite') cur = conn.cursor() # Make some fresh tables using executescript() cur.executescript(''' DROP TABLE IF EXISTS Artist; DROP TABLE IF EXISTS Album; DROP TABLE IF EXISTS Track; DROP TABLE IF EXISTS Genre; CREATE TABLE Artist ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, name TEXT UNIQUE ); CREATE TABLE Genre ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, name TEXT UNIQUE ); CREATE TABLE Album ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, artist_id INTEGER, title TEXT UNIQUE ); CREATE TABLE Track ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, title TEXT UNIQUE, album_id INTEGER, genre_id INTEGER, len INTEGER, rating INTEGER, count INTEGER );''') fname = 'Library.xml' def lookup(d, key): found = False for child in d: 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') print('Dict count:', len(all)) 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') genre = lookup(entry, 'Genre') if name is None or genre is None or artist is None or album is None : continue print(name, artist, album, genre, count, rating, length) 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 Genre (name) VALUES ( ? )''', ( genre, ) ) cur.execute('SELECT id FROM Genre WHERE name = ? ', (genre, )) genre_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, genre_id, len, rating, count) VALUES ( ?, ?, ?, ?, ?, ? )''', ( name, album_id, genre_id, length, rating, count ) ) conn.commit()

---

1.本站遵循行业规范，任何转载的稿件都会明确标注作者和来源；2.本站的原创文章，会注明原创字样,如未注明都非原创，如有侵权请联系删除！；3.作者投稿可能会经我们编辑修改或补充；4.本站不提供任何储存功能只提供收集或者投稿人的网盘链接。