【問題解】Musical Track Database(Using Databases with Python)
ツッコミ:えっと、その時なぜ記録しなかったのか忘れてしまいましたが、たぶんサボって解法をして問題を書いていなかったので、今日誰かが聞いているのを見つけて、ついでに私の方法を書いてみましょう.当時は満点の時にサボっていたので、今日は研究がてらサボらない方法も整えました.もっと良いメッセージもあります.
テーマ:Musical Track Database
This application will read an iTunes export file in XML and produce a properly normalized database with this structure:
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://www.py4e.com/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.
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:
The expected result of the modified query on your database is: (shown here as a simple HTML table with titles)
Track
Artist
Album
Genre
Chase the Ace
AC/DC
Who Made Who
Rock
D.T.
AC/DC
Who Made Who
Rock
For Those About To Rock (We Salute You)
AC/DC
Who Made Who
Rock
私の解法1(サボり解法):
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 = input('Enter file name: ') if ( len(fname) < 1 ) : fname = 'Library.xml'
# Track ID369 # NameAnother One Bites The Dust # ArtistQueen 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')
if name is None or artist is None or album is None : continue
print(name, artist, album, 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 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()
以上のコードを実行する後にそのtrackdbが生成される.sqlite、それからDB Browserで開いて、表の中のデータが大体正しいことを発見して、そのgenreの表を除いて、その表の中には何もなくて、私が後ろのコードを修正していないため(私はCREATE TABLEの部分だけを変更した)、だから物を書いていないで、怠けているなら完全にこの表の1行のデータ(1,Rock)だけをあげることができて、それからTrack表の中のgenre_idはすべて1(SQL命令を実行する)に設定して、このように整理してからそのSQL命令を実行すると必ず条件に合ったテーブルを生成することができますが、正解ではありませんが、満点です.
私の解法2(正しい解法):
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 = input('Enter file name: ') if ( len(fname) < 1 ) : fname = 'Library.xml'
# Track ID369 # NameAnother One Bites The Dust # ArtistQueen 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') genre = lookup(entry, 'Genre') 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 or genre 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()は、実行すべき場所に猫の虎のようにgenreとgenreを加えることです.idですから、後で実行すると正解に違いありません.
私が言いたいのは
1、サボって解法するのはいいですが、時間を割いて正しい解法を考えなければなりません.
2,先生のコードがなぜ書かれているのかよく分からない場合は、ソースファイルを開いて(あの.xmlです)、all=stuffというものを見つけることができます.findall('dict/dict/dict')は曲が並ぶ位置で、あとは曲ごとの要素を抽出するだけです.
テーマ:Musical Track Database
This application will read an iTunes export file in XML and produce a properly normalized database with this structure:
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
);
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://www.py4e.com/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.
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:
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
The expected result of the modified query on your database is: (shown here as a simple HTML table with titles)
Track
Artist
Album
Genre
Chase the Ace
AC/DC
Who Made Who
Rock
D.T.
AC/DC
Who Made Who
Rock
For Those About To Rock (We Salute You)
AC/DC
Who Made Who
Rock
私の解法1(サボり解法):
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 = input('Enter file name: ') if ( len(fname) < 1 ) : fname = 'Library.xml'
# Track ID369 # NameAnother One Bites The Dust # ArtistQueen 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')
if name is None or artist is None or album is None : continue
print(name, artist, album, 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 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()
以上のコードを実行する後にそのtrackdbが生成される.sqlite、それからDB Browserで開いて、表の中のデータが大体正しいことを発見して、そのgenreの表を除いて、その表の中には何もなくて、私が後ろのコードを修正していないため(私はCREATE TABLEの部分だけを変更した)、だから物を書いていないで、怠けているなら完全にこの表の1行のデータ(1,Rock)だけをあげることができて、それからTrack表の中のgenre_idはすべて1(SQL命令を実行する)に設定して、このように整理してからそのSQL命令を実行すると必ず条件に合ったテーブルを生成することができますが、正解ではありませんが、満点です.
私の解法2(正しい解法):
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 = input('Enter file name: ') if ( len(fname) < 1 ) : fname = 'Library.xml'
# Track ID369 # NameAnother One Bites The Dust # ArtistQueen 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') genre = lookup(entry, 'Genre') 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 or genre 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()は、実行すべき場所に猫の虎のようにgenreとgenreを加えることです.idですから、後で実行すると正解に違いありません.
私が言いたいのは
1、サボって解法するのはいいですが、時間を割いて正しい解法を考えなければなりません.
2,先生のコードがなぜ書かれているのかよく分からない場合は、ソースファイルを開いて(あの.xmlです)、all=stuffというものを見つけることができます.findall('dict/dict/dict')は曲が並ぶ位置で、あとは曲ごとの要素を抽出するだけです.