Click here to Skip to main content
15,847,077 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
this is the code i wrote, data are in all the table except Genre, i am using sqlite to execute

Python
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 = input('Enter file name: ')
if ( len(fname) < 1 ) : fname = 'Library.xml'

# <key>Track ID<integer>369
# <key>Name<string>Another One Bites The Dust
# <key>Artist<string>Queen
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()


What I have tried:

this is the correction chat gtp gave me but it didnt solve the problem.

# ... (previous code)
Python
def parse_xml_and_insert():
    conn = sqlite3.connect("trackdb.sqlite")
    cur = conn.cursor()

    xml_data = ET.parse("Library.xml")
    all_tracks = xml_data.findall("dict/dict/dict")

    for track in all_tracks:
        title = None
        artist = None
        genre = None  # Added 'genre' variable
        album = None
        count = None
        rating = None
        length = None

        for i in range(0, len(track) - 1, 2):
            if track[i].text == "Name":
                title = track[i + 1].text
            elif track[i].text == "Artist":
                artist = track[i + 1].text
            elif track[i].text == "Genre":  # Added handling for 'Genre' tag
                genre = track[i + 1].text
            elif track[i].text == "Album":
                album = track[i + 1].text
            elif track[i].text == "Total Time":
                length = int(track[i + 1].text) // 1000
            elif track[i].text == "Rating":
                rating = int(track[i + 1].text)
            elif track[i].text == "Play Count":
                count = int(track[i + 1].text)

        if title is None or artist is None or album is None:
            continue

        # If genre is None, set it to a default value (e.g., "Unknown")
        if genre is None:
            genre = "Unknown"

        print(title, artist, album, genre, 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 (?, ?, ?, ?, ?, ?)''', 
            (title, album_id, genre_id, length, rating, count))

        conn.commit()

    conn.close()
Posted
Updated 27-Jul-23 2:43am
v2
Comments
Richard MacCutchan 27-Jul-23 5:37am    
You have not explained what the actual problem is, or where in the code it occurs.

1 solution

As Richard has said, we have no idea what the error you are getting is, or where it occurs - and without that we can't really help you.

But ... don't do it like that! You are retrieving the ID by assuming that duplicate names never occur: they do. Instead of using "SELECT Id FROM MyTable WHERE column = <the last thing you tried to insert>" use last_insert_rowid() instead - it will return the value of the last IDENTITY value inserted by the current connection.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900