I’m using an SQL database to try to keep track of music files after they have been copied to a new location and modified. I’m executing the statement inside of a Python script.

There is three tables: Seed_Album_Log, Sorted_Album_Log, and Full_Album_Log.

Seed_Album_Log is a record of music files on a NAS, three columns: root, artist, album.

Sorted_Album_Log does the same thing but with files that have been copied to a different computer. Three columns, root, artist, album.

Full_Album_Log is a combination of the other two, six columns: seed_root, seed_artist, seed_album, sorted_album, sorted_artist, sorted_root.

The SQL statement should combine rows of the Seed_Album_Log and Sorted_Album_Log where they have matching Album and Artist. Basically it should end up so that the Full_Album_Log is a record of which albums have not been copied to the destination and sorted, and which ones have already.

For speed’s sake, I want to ignore any albums that already have a row in Full_Album_Log that already have all 6 columns filled out. They don’t have to match, as sometimes the sorted files will have different tags that I will fill in manually.

Here is the current command, written with the help of CoPilot and ChatGPT:

    sql = """
    INSERT INTO full_album_log (seed_root, seed_artist, seed_album, sorted_album, sorted_artist, sorted_root)
    SELECT
        seed.root,
        seed.artist,
        seed.album,
        sort.album,
        sort.artist,
        sort.root
    FROM
        seed_album_log seed
    LEFT JOIN
        sorted_album_log sort
    ON
        seed.artist = sort.artist
        AND seed.album = sort.album
    WHERE 
        NOT EXISTS (
            SELECT 1
            FROM full_album_log full
            WHERE full.seed_root = seed.root
            AND full.sorted_root IS NOT NULL
        );
    """
    cursor.execute(sql)

The WHERE statement checks to see if there is a row in full_album_log with the same root directory that also has the sorted root directory filled in with anything. If no such row exists, the INSERT statement executes.

I appreciate any and all help with trying to figure this out!

Problem: When I run this, it returns the error: “sqlite3.OperationalError: near “WHERE”: syntax error”

Neither ChatGPT nor CoPilot could figure out what the error might be, and I’m at a total loss myself. I know the statement above the WHERE works, if I comment out the WHERE it still fills the database fine but doesn’t skip the full rows.