Python + SQL + XML: NoneType object has no attribute 'getitem'

Question

I'm trying to write a Python program that scans a particular XML Document for data and writes that data to tables in an SQLite database. My code is:

import xml.etree.ElementTree as ET
import sqlite3

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

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'

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')
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 : 
        continue

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 (artist_id, title) 
    VALUES ( ?, ? )''', (artist_id, album  ) )
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()
conn.close()

While the rest of my code is working perfectly fine, the segment:

cur.execute('''INSERT OR IGNORE INTO Genre (name) 
    VALUES ( ? )''', ( genre, ) )
cur.execute('SELECT id FROM Genre WHERE name = ? ', (genre, ))
genre_id = cur.fetchone()[0]

is causing the following traceback:

Traceback (most recent call last):
  File "C:\Python27\Scripts\Course 04\Wk03_Ass01.py", line 83, in <module>
    genre_id = cur.fetchone()[0]
TypeError: 'NoneType' object has no attribute '__getitem__'

I'm utterly baffled as to why this is happening. The segment of code causing the traceback is identical (except for variable names) to the sections above it, which work perfectly. I thought the error might have been occurring because some entries in the 'Genre' database are NULL, but I put the cur.fetchone() statement in an if statement, which also did not work.

cur.execute('''INSERT OR IGNORE INTO Genre (name) 
    VALUES ( ? )''', ( genre, ) )
sqlstr = 'SELECT id FROM Genre WHERE name = ? ', (genre, )
if cur.execute(sqlstr) is None:
    continue
else:
    genre_id = cur.fetchone()[0]

I'd really appreciate any insight into what is going wrong here. Thanks!

thevioletsaber · Accepted Answer · 2016-07-13 15:29:50Z

1

Okay, I found a fix. Evidently the 'Genre' data was NULL at some points and that was screwing up the data. I fixed it using a conditional IF statement as follows:

cur.execute('''INSERT OR IGNORE INTO Genre (name) 
    VALUES ( ? )''', ( genre, ) )
cur.execute('SELECT id FROM Genre WHERE name = ? ', t)
genre_id_val = cur.fetchone()
if genre_id_val is None:
    continue
else:
    genre_id = genre_id_val[0]

The if/else statement worked!

answered Jul 13, 2016 at 15:29

thevioletsaber

1432 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Michał Zaborowski · Accepted Answer · 2016-07-13 09:10:46Z

0

First check if there is something to fetch, and then fetch it... if cur.rowcount == 1: is something you can use to validate if there is single row result. You can also use fetchall() and check the returned list. The difference is that if result is big - you will fetch it all, so rowcount is better option.

EDIT

Try to pass exact string. If that works, problem is with the way you pass query to execute. According to docs, which you for sure knows:

# Do this instead
t = ('RHAT',)
c.execute('SELECT * FROM stocks WHERE symbol=?', t)
print c.fetchone()

edited Jul 13, 2016 at 9:10

answered Jul 13, 2016 at 8:08

Michał Zaborowski

4,4032 gold badges23 silver badges42 bronze badges

3 Comments

thevioletsaber Over a year ago

cur.fetchall() does return a long list of results, so the SQL INSERT and SELECT statements are definitely working as intended.

thevioletsaber Over a year ago

Doing genre_id = cur.fetchone() instead of genre_id = cur.fetchone()[0] returns results, but then I get a different traceback instead. I tried passing the query to Execute the way you suggested, but it didn't affect the results.

thevioletsaber Over a year ago

Okay, I fixed the bug! Thanks for your help though. :)

Collectives™ on Stack Overflow

Python + SQL + XML: NoneType object has no attribute 'getitem'

2 Answers 2

Comments

EDIT

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

EDIT

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related