From 9da073000a54fba8f6d38dc37bfcfd9ca0a60d9a Mon Sep 17 00:00:00 2001 From: James Taylor Date: Mon, 10 Jun 2019 15:27:17 -0700 Subject: Use a single cursor for db operations when possible instead of reopening multiple times --- youtube/subscriptions.py | 168 ++++++++++++++++++++++------------------------- 1 file changed, 80 insertions(+), 88 deletions(-) diff --git a/youtube/subscriptions.py b/youtube/subscriptions.py index 7728c0c..5707286 100644 --- a/youtube/subscriptions.py +++ b/youtube/subscriptions.py @@ -63,66 +63,60 @@ def open_database(): # https://stackoverflow.com/questions/19522505/using-sqlite3-in-python-with-with-keyword return contextlib.closing(connection) -def _subscribe(channels): +def with_open_db(function, *args, **kwargs): + with open_database() as connection: + with connection as cursor: + return function(cursor, *args, **kwargs) + + +def _subscribe(cursor, channels): ''' channels is a list of (channel_id, channel_name) ''' # set time_last_checked to 0 on all channels being subscribed to channels = ( (channel_id, channel_name, 0) for channel_id, channel_name in channels) - with open_database() as connection: - with connection as cursor: - cursor.executemany('''INSERT OR IGNORE INTO subscribed_channels (yt_channel_id, channel_name, time_last_checked) - VALUES (?, ?, ?)''', channels) + cursor.executemany('''INSERT OR IGNORE INTO subscribed_channels (yt_channel_id, channel_name, time_last_checked) + VALUES (?, ?, ?)''', channels) # TODO: delete thumbnails -def _unsubscribe(channel_ids): +def _unsubscribe(cursor, channel_ids): ''' channel_ids is a list of channel_ids ''' - with open_database() as connection: - with connection as cursor: - cursor.executemany("DELETE FROM subscribed_channels WHERE yt_channel_id=?", ((channel_id, ) for channel_id in channel_ids)) + cursor.executemany("DELETE FROM subscribed_channels WHERE yt_channel_id=?", ((channel_id, ) for channel_id in channel_ids)) -def _get_videos(number, offset): - with open_database() as connection: - with connection as cursor: - db_videos = cursor.execute('''SELECT video_id, title, duration, channel_name - FROM videos - INNER JOIN subscribed_channels on videos.sql_channel_id = subscribed_channels.id - ORDER BY time_published DESC - LIMIT ? OFFSET ?''', (number, offset)) - - for db_video in db_videos: - yield { - 'id': db_video[0], - 'title': db_video[1], - 'duration': db_video[2], - 'author': db_video[3], - } - -def _get_subscribed_channels(): - with open_database() as connection: - with connection as cursor: - for item in cursor.execute('''SELECT channel_name, yt_channel_id - FROM subscribed_channels - ORDER BY channel_name'''): - yield item +def _get_videos(cursor, number, offset): + db_videos = cursor.execute('''SELECT video_id, title, duration, channel_name + FROM videos + INNER JOIN subscribed_channels on videos.sql_channel_id = subscribed_channels.id + ORDER BY time_published DESC + LIMIT ? OFFSET ?''', (number, offset)) + for db_video in db_videos: + yield { + 'id': db_video[0], + 'title': db_video[1], + 'duration': db_video[2], + 'author': db_video[3], + } -def _add_tags(channel_ids, tags): - with open_database() as connection: - with connection as cursor: - pairs = [(tag, yt_channel_id) for tag in tags for yt_channel_id in channel_ids] - cursor.executemany('''INSERT OR IGNORE INTO tag_associations (tag, sql_channel_id) - SELECT ?, id FROM subscribed_channels WHERE yt_channel_id = ? ''', pairs) +def _get_subscribed_channels(cursor): + for item in cursor.execute('''SELECT channel_name, yt_channel_id + FROM subscribed_channels + ORDER BY channel_name'''): + yield item -def _remove_tags(channel_ids, tags): - with open_database() as connection: - with connection as cursor: - pairs = [(tag, yt_channel_id) for tag in tags for yt_channel_id in channel_ids] - cursor.executemany('''DELETE FROM tag_associations - WHERE tag = ? AND sql_channel_id = ( - SELECT id FROM subscribed_channels WHERE yt_channel_id = ? - )''', pairs) +def _add_tags(cursor, channel_ids, tags): + pairs = [(tag, yt_channel_id) for tag in tags for yt_channel_id in channel_ids] + cursor.executemany('''INSERT OR IGNORE INTO tag_associations (tag, sql_channel_id) + SELECT ?, id FROM subscribed_channels WHERE yt_channel_id = ? ''', pairs) + + +def _remove_tags(cursor, channel_ids, tags): + pairs = [(tag, yt_channel_id) for tag in tags for yt_channel_id in channel_ids] + cursor.executemany('''DELETE FROM tag_associations + WHERE tag = ? AND sql_channel_id = ( + SELECT id FROM subscribed_channels WHERE yt_channel_id = ? + )''', pairs) @@ -133,22 +127,18 @@ def _get_tags(cursor, channel_id): SELECT id FROM subscribed_channels WHERE yt_channel_id = ? )''', (channel_id,))] -def _get_all_tags(): - with open_database() as connection: - with connection as cursor: - return [row[0] for row in cursor.execute('''SELECT DISTINCT tag FROM tag_associations''')] +def _get_all_tags(cursor): + return [row[0] for row in cursor.execute('''SELECT DISTINCT tag FROM tag_associations''')] -def _get_channel_names(channel_ids): +def _get_channel_names(cursor, channel_ids): ''' returns list of (channel_id, channel_name) ''' - with open_database() as connection: - with connection as cursor: - result = [] - for channel_id in channel_ids: - row = cursor.execute('''SELECT channel_name - FROM subscribed_channels - WHERE yt_channel_id = ?''', (channel_id,)).fetchone() - result.append( (channel_id, row[0]) ) - return result + result = [] + for channel_id in channel_ids: + row = cursor.execute('''SELECT channel_name + FROM subscribed_channels + WHERE yt_channel_id = ?''', (channel_id,)).fetchone() + result.append( (channel_id, row[0]) ) + return result def _channels_with_tag(cursor, tag, order=False): @@ -357,7 +347,7 @@ def import_subscriptions(env, start_response): start_response('400 Bad Request', () ) return b'400 Bad Request: Unsupported file format: ' + html.escape(content_type).encode('utf-8') + b'. Only subscription.json files (from Google Takeouts) and XML OPML files exported from Youtube\'s subscription manager page are supported' - _subscribe(channels) + with_open_db(_subscribe, channels) start_response('303 See Other', [('Location', util.URL_ORIGIN + '/subscription_manager'),] ) return b'' @@ -388,7 +378,7 @@ def get_subscription_manager_page(env, start_response): sort_link = util.URL_ORIGIN + '/subscription_manager' main_list_html = '