2

I'm trying to figure out why my query is not working when I use DELETE, but it works when I use SELECT:

    def delete_artists_and_albums_without_tracks(self, test=True):
        # *it is better to use `NOT EXISTS` instead  `NOT IN` because it will
        #  also delete the rows containing null id's

        if test == True:
            # Check the albums
            self._cursor.execute('''
SELECT Ab.Title
FROM CoreAlbums Ab
WHERE NOT EXISTS (
    SELECT T.AlbumID
    FROM CoreTracks T
    WHERE T.AlbumId = Ab.AlbumId)''')
            albums=self._cursor.fetchall()

            # Check the artists
            self._cursor.execute('''
SELECT Ar.Name
FROM CoreArtists Ar
WHERE NOT EXISTS (
    SELECT T.ArtistID
    FROM CoreTracks T
    WHERE T.ArtistID = Ar.ArtistID)''')     
            artists=self._cursor.fetchall()

            return albums, artists


        # Delete the albums
        self._cursor.execute('''
DELETE 
FROM CoreAlbums Ab
WHERE NOT EXISTS (
    SELECT T.AlbumID
    FROM CoreTracks T
    WHERE T.AlbumId = Ab.AlbumId)''')
        self._connection.commit()

        # Delete the artists
        self._cursor.execute('''
DELETE 
FROM CoreArtists Ar
WHERE NOT EXISTS (
    SELECT T.ArtistID
    FROM CoreTracks T
    WHERE T.ArtistID = Ar.ArtistID)''') 

        self._connection.commit()

When I run using SELECT (test=True) I get the following output:

[('album_name1',), ('album_name2',)]
[('artist_name1',), ('artist_name2',)]

but when I use DELETE (test=False), I get the following error:

Traceback (most recent call last):
  File "Database.py", line 563, in <module>
    printl(db.delete_artists_and_albums_without_tracks(False))
  File "Database.py", line 396, in delete_artists_and_albums_without_tracks
    WHERE T.AlbumId = Ab.AlbumId)''')
sqlite3.OperationalError: near "Ab": syntax error

I guess I'm skipping something really basic but I'm having problems to find the error..

The post of the duplicated answer didn't help me, I still having the Ab problem. I fixed this by doing:

self._cursor.execute('''SELECT AlbumID FROM CoreTracks GROUP BY AlbumID''')
album_ids=[item[0] for item in self._cursor.fetchall()]
query='''DELETE FROM CoreAlbums WHERE NOT AlbumId IN ({})'''.format(','.join('?'*len(album_ids)))
self._cursor.execute(query, album_ids)
  • Can you post one function that demonstrates the problem with one SQL statement? The posted code is overly long, overly complex (4 SQL statements) and incomplete (no test=False branch) – Lorenzo Gatti Mar 30 '16 at 16:28
  • @LorenzoGatti I didn't because I thought I was missing something noob. I'll make a working example, but actually the code I posted could be read as just `SQL` and remove all the python code. –  Mar 30 '16 at 16:31

0 Answers0