From 74e967d16050472e1e30b2301b8460dad1bc91f0 Mon Sep 17 00:00:00 2001 From: Michaël Ball Date: Sun, 2 Aug 2015 10:46:53 +0100 Subject: Move to different sqlite driver. Performance improvements. --- models/album.py | 42 +++++++++++++++++-------------- models/artist.py | 42 +++++++++++++++++-------------- models/track.py | 75 ++++++++++++++++++++++++++++---------------------------- 3 files changed, 84 insertions(+), 75 deletions(-) (limited to 'models') diff --git a/models/album.py b/models/album.py index 24af0b5..b2625db 100644 --- a/models/album.py +++ b/models/album.py @@ -6,8 +6,10 @@ class Album(): def __init__(self, id=None, **kwargs): if id is not None: db = DbManager() - for row in db.execute("""SELECT * FROM album WHERE id = ?""", - (id,)): + cursor = db.cursor() + + for row in cursor.execute("SELECT * FROM album WHERE id = ?", + (id,)): setattr(self, "id", id) setattr(self, "name", row[1]) setattr(self, "date", row[2]) @@ -17,20 +19,19 @@ class Album(): def delete(self): db = DbManager() + cursor = db.cursor() for track in self.tracks: track.delete() delete_sql = "DELETE FROM album WHERE id = ?" - db.execute(delete_sql, (self.id,)) + cursor.execute(delete_sql, (self.id,)) delete_track_rel_sql = "DELETE FROM album_track WHERE album_id = ?" - db.execute(delete_track_rel_sql, (self.id,)) + cursor.execute(delete_track_rel_sql, (self.id,)) delete_artist_rel_sql = "DELETE FROM album_artist WHERE album_id = ?" - db.execute(delete_artist_rel_sql, (self.id,)) - - db.commit() + cursor.execute(delete_artist_rel_sql, (self.id,)) return True @@ -42,11 +43,12 @@ class Album(): setattr(self, "_artists", []) db = DbManager() + cursor = db.cursor() - for row in db.execute("""SELECT artist.* FROM artist INNER JOIN - album_artist ON artist.id = - album_artist.artist_id WHERE album_id = ? - ORDER BY name ASC""", (self.id,)): + for row in cursor.execute("""SELECT artist.* FROM artist INNER JOIN + album_artist ON artist.id = + album_artist.artist_id WHERE album_id = ? + ORDER BY name ASC""", (self.id,)): artist = Artist(id=row[0], name=row[1], sortname=row[2], musicbrainz_artistid=row[3]) self._artists.append(artist) @@ -61,11 +63,12 @@ class Album(): setattr(self, "_tracks", []) db = DbManager() + cursor = db.cursor() - for row in db.execute("""SELECT track.* FROM track - INNER JOIN album_track ON track.id = - album_track.track_id WHERE album_id = ? - ORDER BY tracknumber ASC""", (self.id,)): + for row in cursor.execute("""SELECT track.* FROM track + INNER JOIN album_track ON track.id = + album_track.track_id WHERE album_id = ? + ORDER BY tracknumber ASC""", (self.id,)): track = Track(id=row[0], tracknumber=row[1], name=row[2], grouping=row[3], filename=row[4]) @@ -83,14 +86,14 @@ class Album(): if len(dirty_attributes) > 0: db = DbManager() + cursor = db.cursor() set_clause = utils.update_clause_from_dict(dirty_attributes) dirty_attributes[id] = self.id sql = " ".join(("UPDATE album"), set_clause, "WHERE id = :id") - db.execute(sql, dirty_attributes) - db.commit() + cursor.execute(sql, dirty_attributes) def search(**search_params): """Find an album with the given params @@ -103,6 +106,7 @@ class Album(): albums = [] db = DbManager() + cursor = db.cursor() # unpack search params where_params = {} @@ -116,9 +120,9 @@ class Album(): result = None if where_clause: statement = " ".join(("SELECT * FROM album", where_clause)) - result = db.execute(statement, value_params) + result = cursor.execute(statement, value_params) else: - result = db.execute("SELECT * FROM album") + result = cursor.execute("SELECT * FROM album") for row in result: albums.append( diff --git a/models/artist.py b/models/artist.py index 7d36457..1ac27d5 100644 --- a/models/artist.py +++ b/models/artist.py @@ -6,8 +6,10 @@ class Artist: def __init__(self, id=None, **kwargs): if id is not None: db = DbManager() - for row in db.execute("""SELECT * FROM artist WHERE id = ?""", - (id,)): + cursor = db.cursor() + + for row in cursor.execute("SELECT * FROM artist WHERE id = ?", + (id,)): setattr(self, "id", id) setattr(self, "name", row[1]) setattr(self, "sortname", row[2]) @@ -18,20 +20,19 @@ class Artist: def delete(self): db = DbManager() + cursor = db.cursor() for album in self.albums: album.delete() delete_sql = "DELETE FROM artist WHERE id = ?" - db.execute(delete_sql, (self.id,)) + cursor.execute(delete_sql, (self.id,)) delete_track_rel_sql = "DELETE FROM artist_track WHERE artist_id = ?" - db.execute(delete_track_rel_sql, (self.id,)) + cursor.execute(delete_track_rel_sql, (self.id,)) delete_album_rel_sql = "DELETE FROM album_artist WHERE artist_id = ?" - db.execute(delete_album_rel_sql, (self.id,)) - - db.commit() + cursor.execute(delete_album_rel_sql, (self.id,)) return True @@ -43,11 +44,12 @@ class Artist: setattr(self, "_tracks", []) db = DbManager() + cursor = db.cursor() - for row in db.execute("""SELECT track.* FROM track - INNER JOIN artist_track ON track.id = - artist_track.track_id WHERE artist_id = ? - ORDER BY name ASC""", (self.id,)): + for row in cursor.execute("""SELECT track.* FROM track + INNER JOIN artist_track ON track.id = + artist_track.track_id WHERE artist_id = ? + ORDER BY name ASC""", (self.id,)): track = Track(id=row[0], tracknumber=row[1], name=row[2], grouping=row[3], filename=row[4]) @@ -63,11 +65,12 @@ class Artist: setattr(self, "_albums", []) db = DbManager() + cursor = db.cursor() - for row in db.execute("""SELECT album.* FROM album - INNER JOIN album_artist ON album.id = - album_artist.album_id WHERE artist_id = ? - ORDER BY date ASC""", (self.id,)): + for row in cursor.execute("""SELECT album.* FROM album + INNER JOIN album_artist ON album.id = + album_artist.album_id WHERE artist_id = ? + ORDER BY date ASC""", (self.id,)): album = Album(id=row[0], name=row[1], date=row[2]) self._albums.append(album) @@ -83,14 +86,14 @@ class Artist: if len(dirty_attributes) > 0: db = DbManager() + cursor = db.cursor() set_clause = utils.update_clause_from_dict(dirty_attributes) dirty_attributes[id] = self.id sql = " ".join(("UPDATE artist"), set_clause, "WHERE id = :id") - db.execute(sql, dirty_attributes) - db.commit() + cursor.execute(sql, dirty_attributes) def search(**search_params): """Find an artist with the given params @@ -103,6 +106,7 @@ class Artist: artists = [] db = DbManager() + cursor = db.cursor() # unpack search params where_params = {} @@ -116,9 +120,9 @@ class Artist: result = [] if where_clause: statement = " ".join(("SELECT * FROM artist", where_clause)) - result = db.execute(statement, value_params) + result = cursor.execute(statement, value_params) else: - result = db.execute("SELECT * FROM artist") + result = cursor.execute("SELECT * FROM artist") for row in result: artists.append( diff --git a/models/track.py b/models/track.py index 4d06872..854b3ee 100644 --- a/models/track.py +++ b/models/track.py @@ -1,4 +1,4 @@ -import sqlite3 +import apsw from common import utils from db.db_manager import DbManager @@ -14,8 +14,10 @@ class Track: if id is not None: db = DbManager() - for row in db.execute("""SELECT * FROM track WHERE id = ?""", - (id,)): + cursor = db.cursor() + + for row in cursor.execute("""SELECT * FROM track WHERE id = ?""", + (id,)): setattr(self, "id", row[0]) setattr(self, "tracknumber", row[1]) setattr(self, "name", row[2]) @@ -28,10 +30,10 @@ class Track: def delete(self): db = DbManager() + cursor = db.cursor() delete_sql = "DELETE FROM track WHERE id = ?" - db.execute(delete_sql, (self.id,)) - db.commit() + cursor.execute(delete_sql, (self.id,)) return True @@ -41,11 +43,12 @@ class Track: setattr(self, "_album", None) db = DbManager() + cursor = db.cursor() - for row in db.execute("""SELECT album.* FROM album INNER JOIN - album_track ON album.id = - album_track.album_id WHERE track_id = ? - LIMIT 1""", (self.id,)): + for row in cursor.execute("""SELECT album.* FROM album INNER JOIN + album_track ON album.id = + album_track.album_id WHERE track_id = ? + LIMIT 1""", (self.id,)): setattr(self, "_album", Album(row[0])) return self._album @@ -54,12 +57,13 @@ class Track: def artists(self): if not hasattr(self, "_artists"): db = DbManager() + cursor = db.cursor() setattr(self, "_artists", []) - for row in db.execute("""SELECT artist.* FROM artist INNER JOIN - artist_track ON artist.id = - artist_track.artist_id WHERE - artist.id = ?""", (self.id,)): + for row in cursor.execute("""SELECT artist.* FROM artist INNER JOIN + artist_track ON artist.id = + artist_track.artist_id WHERE + artist.id = ?""", (self.id,)): self._artists.append(Artist(row[0])) return self._artists @@ -130,7 +134,7 @@ class Track: musicbrainz_artistid)) artist = Artist( - id=c.lastrowid, name=artist_name, + id=db.last_insert_rowid(), name=artist_name, sortname=artistsort, musicbrainz_artistid=musicbrainz_artistid ) @@ -174,7 +178,7 @@ class Track: musicbrainz_albumid) VALUES (?,?,?)""", (album_name, album_date, mb_albumid)) - album = Album(id=c.lastrowid, name=album_name, + album = Album(id=db.last_insert_rowid(), name=album_name, date=album_date, musicbrainz_albumid=mb_albumid) elif album_name: @@ -191,7 +195,7 @@ class Track: c.execute("""INSERT INTO album (name, `date`) VALUES (?,?)""", (album_name, album_date)) - album = Album(id=c.lastrowid, name=album_name, + album = Album(id=db.last_insert_rowid(), name=album_name, date=album_date) if album: @@ -213,7 +217,7 @@ class Track: album_id) VALUES(?,?)""", (artist.id, album.id) ) - except sqlite3.IntegrityError: + except apsw.ConstraintError: pass track_number = None @@ -242,7 +246,7 @@ class Track: c.execute("""INSERT INTO album_track (album_id, track_id) VALUES(?,?)""", (album.id, self.id)) - except sqlite3.IntegrityError: + except apsw.ConstraintError: pass for artist in artists: @@ -250,11 +254,9 @@ class Track: c.execute("""INSERT INTO artist_track (artist_id, track_id) VALUES(?,?)""", (artist.id, self.id)) - except sqlite3.IntegrityError: + except apsw.ConstraintError: pass - db.commit() - c.close() return True def save(self): @@ -267,14 +269,14 @@ class Track: if len(dirty_attributes) > 0: db = DbManager() + cursor = db.cursor() set_clause = utils.update_clause_from_dict(dirty_attributes) dirty_attributes[id] = self.id sql = " ".join(("UPDATE track"), set_clause, "WHERE id = :id") - db.execute(sql, dirty_attributes) - db.commit() + cursor.execute(sql, dirty_attributes) def search(**search_params): """Find a track with the given params @@ -287,6 +289,7 @@ class Track: """ db = DbManager() + cursor = db.cursor() tracks = [] # unpack search params @@ -301,9 +304,9 @@ class Track: result = None if where_clause: statement = " ".join(("SELECT * FROM track", where_clause)) - result = db.execute(statement, value_params) + result = cursor.execute(statement, value_params) else: - result = db.execute("SELECT * FROM track") + result = cursor.execute("SELECT * FROM track") for row in result: tracks.append( @@ -315,10 +318,11 @@ class Track: def find_by_path(path): db = DbManager() + cursor = db.cursor() track = None - for row in db.execute("SELECT * FROM track WHERE filename = ? LIMIT 1", - (path,)): + for row in cursor.execute("""SELECT * FROM track WHERE filename = ? + LIMIT 1""", (path,)): track = Track(row[0]) return track @@ -393,7 +397,6 @@ class Track: c.execute("""UPDATE artist SET sortname = ? WHERE id = ?""", (artistsort, artist.id)) - db.commit() else: c.execute("""INSERT INTO artist @@ -402,7 +405,7 @@ class Track: musicbrainz_artistid)) artist = Artist( - id=c.lastrowid, name=artist_name, + id=db.last_insert_rowid(), name=artist_name, sortname=artistsort, musicbrainz_artistid=musicbrainz_artistid ) @@ -446,7 +449,7 @@ class Track: musicbrainz_albumid) VALUES (?,?,?)""", (album_name, album_date, mb_albumid)) - album = Album(id=c.lastrowid, name=album_name, + album = Album(id=db.last_insert_rowid(), name=album_name, date=album_date, musicbrainz_albumid=mb_albumid) elif album_name: @@ -464,7 +467,7 @@ class Track: c.execute("""INSERT INTO album (name, `date`) VALUES (?,?)""", (album_name, album_date)) - album = Album(id=c.lastrowid, name=album_name, + album = Album(id=db.last_insert_rowid(), name=album_name, date=album_date) for artist in artists: @@ -475,7 +478,7 @@ class Track: album_id) VALUES(?,?)""", (artist.id, album.id) ) - except sqlite3.IntegrityError: + except apsw.ConstraintError: pass track_number = None @@ -507,7 +510,7 @@ class Track: (track_number, track_name, track_grouping, filename)) - track = Track(id=c.lastrowid, tracknumber=track_number, + track = Track(id=db.last_insert_rowid(), tracknumber=track_number, name=track_name, grouping=track_grouping, filename=filename) @@ -516,7 +519,7 @@ class Track: c.execute("""INSERT INTO album_track (album_id, track_id) VALUES(?,?)""", (album.id, track.id)) - except sqlite3.IntegrityError: + except apsw.ConstraintError: pass for artist in artists: @@ -524,9 +527,7 @@ class Track: c.execute("""INSERT INTO artist_track (artist_id, track_id) VALUES(?,?)""", (artist.id, track.id)) - except sqlite3.IntegrityError: + except apsw.ConstraintError: pass - db.commit() - c.close() return True -- cgit v1.2.3