![]() ![]() Print(name, genre,artist, album, count, rating, length)Ĭur.execute('''INSERT OR IGNORE INTO Artist (name)Ĭur.execute('SELECT id FROM Artist WHERE name = ? ', (artist, ))Ĭur.execute('''INSERT OR IGNORE INTO Genre (name)Ĭur.execute('SELECT id FROM Genre WHERE name = ? ', (genre, ))Ĭur.execute('''INSERT OR IGNORE INTO Album (title, artist_id) If name is None or artist is None or album is None or genre is None : If ( lookup(entry, 'Track ID') is None ) : continue If child.tag = 'key' and child.text = key : If ( len(fname) < 1 ) : fname = 'Library.xml' Try the below one, it will work perfectly ![]() ( name, album_id, genre_id, length, rating, count ) ) execute( '''INSERT OR REPLACE INTO Track (title, album_id, genre_id, len, rating, count) VALUES ( ?, ?, ?, ?, ?, ? )''', execute( 'SELECT id FROM Album WHERE title = ? ', ( album, ))Ĭur. execute( '''INSERT OR IGNORE INTO Album (title, artist_id) VALUES ( ?, ? )''', ( album, artist_id ) )Ĭur. execute( 'SELECT id FROM Genre WHERE name = ? ', ( genre, ))Ĭur. execute( '''INSERT OR IGNORE INTO Genre (name) VALUES ( ? )''', ( genre, ) )Ĭur. execute( 'SELECT id FROM Artist WHERE name = ? ', ( artist, ))Ĭur. execute( '''INSERT OR IGNORE INTO Artist (name) VALUES ( ? )''', ( artist, ) )Ĭur. If name is None or artist is None or album is None or genre is None:Ĭontinue print name, artist, genre, album, count, rating, length cur. Genre = lookup( entry, 'Genre') #add album = lookup( entry, 'Album') If ( lookup( entry, 'Track ID') is None ) : continue name = lookup( entry, 'Name') findall( 'dict/dict/dict') #add one dict/ print 'Dict count:', len( all) text = key :įound = True return None #create element tress and find branch stuff = ET. If ( len( fname) Track ID369 # NameAnother One Bites The Dust # ArtistQueen # GenreIndustrial def lookup( d, key): executescript( ''' DROP TABLE IF EXISTS Artist DROP TABLE IF EXISTS Genre DROP TABLE IF EXISTS Album DROP TABLE IF EXISTS Track 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 ) ''') # Make some fresh tables using executescript() cur. ElementTree as ET import sqlite3 conn = sqlite3. The expected result of this query on your database is:įor Those About To Rock (We Salute You) AC/DC Who Made Who Rock ON Track.genre_id = Genre.ID and Track.album_id = Album.id SELECT Track.title, Artist.name, Album.title, Genre.nameįROM Track JOIN Genre JOIN Album JOIN Artist To grade this assignment, the program will run a query like this on your uploaded database and look for the data it expects to see: 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. The ZIP file contains the Library.xml file to be used for this assignment. ![]() You can use this code as a starting point for your application. If you run the program multiple times in testing or with different files, make sure to empty out the data before each run. Len INTEGER, rating INTEGER, count INTEGER Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, ![]() This application will read an iTunes export file in XML and produce a properly normalized database with this structure: ![]()
0 Comments
Leave a Reply. |