Skip to main content
 首页 » 编程设计

python之音乐轨道数据库

2025年05月04日12qq78292959

所以我试图通过使用 SQLite 打开我正在创建的数据库来获取这篇文章底部的表格。 zip 文件包含我要修改的代码，还包含 itunes 库，我从中获取 SQLite 中表的值。我不确定如何在我的表格中添加流派字段，我也不了解如何提取您可以在本页底部看到的最后三个标题。任何帮助将不胜感激。

所以这是我的家庭作业提示:

音乐轨道数据库

此应用程序将读取 iTunes 导出的 XML 文件并生成具有此结构的正确规范化的数据库:

为了给这个作业打分，程序会在您上传的数据库上运行这样的查询，并查找它希望看到的数据:

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

如果一切都正确完成，这是我应该得到的结果:

Final output Table

这是我的代码，我在更改代码的流派部分时遇到了问题。我应该在 SQLite 中得到一个流派表。此外，我需要获得与上表相同的结果。

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 = raw_input('Enter file name: ') 
if ( len(fname) < 1 ) : fname = 'Library.xml' 
 
# <key>Track ID</key><integer>369</integer> 
# <key>Name</key><string>Another One Bites The Dust</string> 
# <key>Artist</key><string>Queen</string> 
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 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 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 IGNORE INTO Genre (name) 
        VALUES ( ? )''', ( genre, ) ) 
    cur.execute('SELECT id FROM Genre WHERE name = ? ', (genre, )) 
    genre_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()

请您参考如下方法:

这是适合您情况的完整代码:

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 Genre; 
DROP TABLE IF EXISTS Track; 
 
CREATE TABLE Artist ( 
    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 Genre ( 
    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, 
    genre_id  INTEGER, 
    len INTEGER, rating INTEGER, count INTEGER 
); 
''') 
 
 
fname = raw_input('Enter file name: ') 
if ( len(fname) < 1 ) : fname = 'Library.xml' 
 
# <key>Track ID</key><integer>369</integer> 
# <key>Name</key><string>Another One Bites The Dust</string> 
# <key>Artist</key><string>Queen</string> 
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 genre 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 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 IGNORE INTO Genre (name)  
    VALUES ( ? )''', ( genre, ) ) 
    cur.execute('SELECT id FROM Genre WHERE name = ? ', (genre, )) 
    genre_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()


  文章来源：
  https://blog.windname.com/post/acgahf2b1.html
阅读延展