SQL and fetchone

[Scorpio]

hello,

I don't understand the following error. you Ould find under my code and the file I try to enter in a database.

import xml.etree.ElementTree as ET
import sqlite3

conn = sqlite3.connect('trackdb.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 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,
    len INTEGER, rating INTEGER, count INTEGER,
    genre_id
);

CREATE TABLE Genre (
    id INTEGER NOT NULL PRIMARY KEY
        AUTOINCREMENT UNIQUE,
    genre TEXT UNIQUE
);
''')


fname = 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')
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 : 
        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 (genre)
        VALUES (?)''', (genre, ))
    cur.execute('SELECT id FROM Genre WHERE genre = ? ', (genre, ))
    print("=======\n", cur.fetchone()[0], genre)
    genre_id = cur.fetchone()[0]

    cur.execute('''INSERT OR REPLACE INTO Track
        (title, album_id, len, rating, count, genre_id) 
        VALUES ( ?, ?, ?, ?, ?, ?)''',
        (name, album_id, length, rating, count, genre_id))

    conn.commit()

Error:
Enter file name: Library.xml
Dict count: 404
Another One Bites The Dust Queen Greatest Hits 55 100 217103 Rock
=======
 1 Rock
Traceback (most recent call last):
  File "/Users/nic/Desktop/coursera/tracks/tracks2.py", line 86, in <module>
    genre_id = cur.fetchone()[0]
TypeError: 'NoneType' object is not subscriptable
Process terminated with an exit code of 1

for the first tables, my code is running but for the table "genre" is blowed up
why the same code using fetchone doesn't return the same result?

thanks for your help

[buran]

From the docs:

fetchone() - Fetches the next row of a query result set, returning a single sequence, or None when no more data is available.

how many records you expected to be returned? Is it possible that only one and you already fetch it on line 85?

[Scorpio]

the file I try to export is a list of 318 songs with all the details you can expect about each one. but for some of them, they are any genre specified. It s not a problem, normally none should be return for empty genre, isn't it?

[buran]

I think you are confused. You query table genres. There you will have one record per genre and you need the associated id to insert in tracks table. Your SELECT will always return one id (you insert it just before that, so you will always get result). And you consume the result on line 85 for the print.

[Scorpio]

sorry, I am not a nice English speaker. sometimes I mnot totally clear when I try to write what I think.
if I understand the real meaning of your answer, you say without the print everything is running nicely, don't you?
but I ran my code without it and I obtained the same result, I added it to know what is the matter.

so now my problem is solved, I just added an "or genre is null" like that:

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

thanks for your help

[buran]

if I understand the real meaning of your answer, you say without the print everything is running nicely, don't you?

yes, that's what I think

but I ran my code without it and I obtained the same result, I added it to know what is the matter.

I guess that now the error comes at different place, i.e. when you hit a song without genre.
It's a bit tricky to debug without actual input file, but I am glad it works

[beebert69]

sorry, I am not a nice English speaker. sometimes I mnot totally clear when I try to write what I think.
if I understand the real meaning of your answer, you say without the print everything is running nicely, don't you?
but I ran my code without it and I obtained the same result, I added it to know what is the matter.

so now my problem is solved, I just added an "or genre is null" like that:

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

thanks for your help

you should update this line with "genre is None, if any value is "none" then move to the next dict :
if name is None or artist is None or album is None or genre is None :
continue