目的：把XML文件中的内容提取出来，并存到sqlite3数据库中。

XML文件在这个包里：tracks.zip

1. 大致步骤

1. 画XML tree找到信息所在路径
2. 把内容从XML中提取出来，先调好XML那部分的代码
3. 确认数据库的关系模式
4. 写数据库那部分的代码
5. 合并为一个程序

2. 画XML tree

XML直接看是很痛苦而且混乱的，先用sublime test把XML文件打开。分层次缩进画出简单的XML tree，大致是下面这样的。最后需要的路径和内容在蓝色框里，(dict/dict/dict)

path.png

1. 这是根节点，第一个dict。

path1.png

2 展开这一层，列出所有的子节点。注意缩略子节点里面的细节。主要有三个子节点，key，dict，array。 Key下面没有子节点，忽略不需要内容。展开dict和array看一下，发现需要的内容在dict中。

path2.png

3. 展开这个dict节点，里面的内容是需要提取的。以下是大致格式。

 <dict>
 <key>369</key>
 <dict>
 <key>Track ID</key><integer>369</integer>
 <key>Name</key><string>Another One Bites The Dust</string>
 <key>Artist</key><string>Queen</string>
 <key>Composer</key><string>John Deacon</string>
 <key>Album</key><string>Greatest Hits</string>
 <key>Genre</key><string>Rock</string>
 <key>Kind</key><string>MPEG audio file</string>
 <key>Size</key><integer>4344295</integer>
 <key>Total Time</key><integer>217103</integer>
 </dict>

2. 提取XML中的内容

2.1 写路径并且检测能否拿到相关信息, 得到的是404个XML节点信息。Dict count is : 404

# python
import xml.etree.ElementTree as ET

fname = 'Library.xml'
stuff = ET.parse(fname)
all = stuff.findall('dict/dict/dict')
print('Dict count is :',len(all))

2.2 根据相关格式把内容抽取出来。

2.2.1 这个格式其实是两行来的,第一行作为索引，第二行才是真正需要的信息。

<key>Track ID</key><integer>369</integer>

好看点的显示是这样：

<key>Track ID</key>
<integer>369</integer>

2.2.2 找一个信息，其实要先遍历dict子集；找到索引后，返回索引后面一行的信息。因为要以相同方式抽取不同内容，所以要写个小函数定义为lookup。

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

2.2.3 组合在一起，尝试抽个'Name'验证一下。以下是正确的输出。这样就有了一个能用的XML部分的代码了。

import xml.etree.ElementTree as ET

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 is :',len(all))

for entry in all:
    if ( lookup(entry, 'Track ID') is None ) : continue
    name = lookup(entry, 'Name')
    if name is None :continue
    print(name)

# output 
Another One Bites The Dust
Asche Zu Asche
Beauty School Dropout
Black Dog
Bring The Boys Back Home
Circles

3. 数据库的关系模式

画出表格和它们之间的关系，会比较方便。起名准确真的很重要。in the dict·表示可以直接抽取，另外一些是其他表中的元素。然后写个SQL把它们存起来。

relationship.png

4. 合并为一个程序

import xml.etree.ElementTree as ET
import sqlite3

conn = sqlite3.connect('hwnet1.sqlite')
cur = conn.cursor()

# Make some fresh tables using executescript()
cur.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 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 = 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 or genre is None :
        continue

    print(name, artist, album, count, rating, length, genre)

    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()

5. 检验结果

最后选以下的信息应该看到这个表。

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

title                                    name        title         name
---------------------------------------  ----------  ------------  ----------
For Those About To Rock (We Salute You)  AC/DC       Who Made Who  Rock
Hells Bells                              AC/DC       Who Made Who  Rock
Shake Your Foundations                   AC/DC       Who Made Who  Rock

---

我不能相信几十行的代码写了几个小时……一定是状态问题！状态！

2018.4.27