python - python中Sqlite的NULL值

Question

我的问题是为什么artist_id可以很好地运行但genre_id显示错误为

TypeError：“NoneType”对象不可下标

这是 Library.xml 的一部分，我附上了 Library xml https://www.py4e.com/code3/tracks/Library.xml的 URL

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
    <key>Major Version</key><integer>1</integer>
    <key>Minor Version</key><integer>1</integer>
    <key>Date</key><date>2015-11-24T11:12:10Z</date>
    <key>Application Version</key><string>12.3.1.23</string>
    <key>Features</key><integer>5</integer>
    <key>Show Content Ratings</key><true/>
    <key>Music Folder</key><string>file:///Users/csev/Music/iTunes/iTunes%20Music/</string>
    <key>Library Persistent ID</key><string>B7006C9E9799282E</string>
    <key>Tracks</key>
    <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>

完整代码在第二部分，谢谢您的回答。

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]

import xml.etree.ElementTree as ET
import sqlite3

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

# create table
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 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'
if (len(fname) < 1) : fname = 'track.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')
    genre = lookup(entry, 'Genre')
    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 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()

    cur.execute('''
                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
    '''
    )

标签： pythondatabasesqlite