Using Database with Python_Coursera_Musical Track Database

[fairypp]

This application will read an iTunes export file in XML and produce a properly normalized database with this structure:

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
);
If you run the program multiple times in testing or with different files, make sure to empty out the data before each run.

You can use this code as a starting point for your application: http://www.pythonlearn.com/code/tracks.zip. The ZIP file contains the Library.xml file to be used for this assignment. You can export your own tracks from iTunes and create a database, but for the database that you turn in for this assignment, only use the Library.xml data that is provided.

To grade this assignment, the program will run a query like this on your uploaded database and look for the data it expects to see:

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
The expected result of this query on your database is:
Track Artist Album Genre
Chase the Ace AC/DC Who Made Who Rock
D.T. AC/DC Who Made Who Rock
For Those About To Rock (We Salute You) AC/DC Who Made Who Rock

My Solution

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 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 = 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>
# <key>Genre</key><string>Industrial</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

#create element tress and find branch
stuff = ET.parse(fname)
all = stuff.findall('dict/dict/dict')#add one 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')
    genre = lookup(entry, 'Genre')#add
    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 or genre is None: 
        continue

    print name, artist, genre, 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()

[prakriti0502]

Greetings!
I wrote the same code (in python3), but the genre fields are none in some tracks, I'm not able to fix the error I'm facing:
File "C:\Users\hp\Desktop\Projects\Practice\venv\tracks\tracks.py", line 85, in
genre_id = cur.fetchone()[0]
TypeError: 'NoneType' object is not subscriptable
Edit: Here we have to handle missing values. I got this error and added a line to my code that
if genre is None:
genre = "Missing"

[mykesse]

Yeah you can do that but there is a line to avoid precisely those NoneType objects which lacks the variable genre:

if name is None or artist is None or album is None or genre is None : continue

That genre is not in the sample code (tracks.py).

However I still haven't managed to the expected result. Mine is

• 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

What about you?

[ehlexlee]

Yeah you can do that but there is a line to avoid precisely those NoneType objects which lacks the variable genre:

if name is None or artist is None or album is None or genre is None : continue

That genre is not in the sample code (tracks.py).

However I still haven't managed to the expected result. Mine is

• 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

What about you?

It's fine, I think his playlist updated, I click submit and it was all cool.

[sheersh2001]

i am getting this trace back in week 4. Can you help me out. I named my file as bhaisahab.py

[ehlexlee]

i am getting this trace back in week 4. Can you help me out. I named my file as bhaisahab.py

Can you post the code? sometimes its just a small mistake, so you can copy the import part from a previous exercise

[Madhukiran29]

Yeah you can do that but there is a line to avoid precisely those NoneType objects which lacks the variable genre:
if name is None or artist is None or album is None or genre is None : continue
That genre is not in the sample code (tracks.py).
However I still haven't managed to the expected result. Mine is

• 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

What about you?

It's fine, I think his playlist updated, I click submit and it was all cool.

i have done the same thing but still getting error while submitting

[tauhiddewan]

Yeah you can do that but there is a line to avoid precisely those NoneType objects which lacks the variable genre:

if name is None or artist is None or album is None or genre is None : continue

That genre is not in the sample code (tracks.py).

However I still haven't managed to the expected result. Mine is

• 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

What about you?

try using this SQL command:

SELECT Track.title,Artist.name,Album.title,Genre.name
FROM Artist JOIN Album JOIN Genre JOIN Track
ON Artist.id = Album.artist_id and Album.id = Track.album_id and Genre.id = Track.genre_id
ORDER by Artist.name,Track.title LIMIT 3

[mrajendrac]

Try the below one, it will work perfectly

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 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 = 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')
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 or genre is None :
    continue
print(name, genre,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()

[Visvas07]

Try the below one, it will work perfectly

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 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 = 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')
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 or genre is None :
    continue
print(name, genre,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()

Iy's not working the genre table has no values and the column genre_id is not even created

[johngcrowley]

I have a question regarding the Lookup() function.

why doesn't the first if clause come after the second?
how do we return the child.text, unless we have first set the variable 'Found' to True?

[PhiPrince]

I have a question regarding the Lookup() function.

why doesn't the first if clause come after the second?
how do we return the child.text, unless we have first set the variable 'Found' to True?

take for example < key >Name< / key >< string >Hello World</ string >, the first time the loop is searching in < key >Name</ key >, the second if statement in the for loop here when looking through the xml file found the tag ( 'key' with child.tag) and the text inside it(Name child.text), now if you were to take these values you wouldn't solve the problem at hand because what you actually need is Hello World not Name, so now by setting the value of found to true the loop will start again with a true value while searching in < string >Hello World< /string > and thus executing the first if statement which is child.text which in this case is Hello World, so you got what you needed.

[Chenlin-marine]

trying to add

if genre is None:
genre = 'Null'

before you cur.execute in sqlite

might help you if you got the error of 'TypeError: 'NoneType' object is not subscriptable'

[NickB1520]

Hello. Need help. I tried to run the code tracks.py on pythonanywhere and I don't understand the problem. New database file would be created but empty. I think it's because of this errormessages:
Traceback (most recent call last):
File "tracks.py", line 48, in
stuff = ET.parse(fname)
File "/usr/lib/python3.8/xml/etree/ElementTree.py", line 1202, in parse
tree.parse(source, parser)
File "/usr/lib/python3.8/xml/etree/ElementTree.py", line 595, in parse
self._root = parser._parse_whole(source)
xml.etree.ElementTree.ParseError: syntax error: line 3, column 72
09:38 ~ $

---

This is the original code which I got from py4e.org

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;

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
);
''')

fname = input('Enter file name: ')
if ( len(fname) < 1 ) : fname = 'Library.xml'

Track ID369

NameAnother One Bites The Dust

ArtistQueen

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

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 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, len, rating, count)
    VALUES ( ?, ?, ?, ?, ? )''',
    ( name, album_id, length, rating, count ) )

conn.commit()

[SameerAlvee]

Hi, the below code works perfectly and all the issues are fixed. (Track table has genre_id data and the Genre table has data in it)

`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 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 = 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')
genre=lookup(entry,'Genre')
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,genre, 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]

if genre is None:
    genre = 'Null'
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()

`