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.
4
You mentioned Full_Album_Log has a column named sorted_dir, but your SQL uses sorted_root.
1