diff options
| -rw-r--r-- | .gitignore | 1 | ||||
| -rw-r--r-- | backend/hist_data/README.md | 3 | ||||
| -rwxr-xr-x | backend/hist_data/reduce_event_data.py | 39 | ||||
| -rw-r--r-- | backend/tests/test_reduce_event_data.py | 70 |
4 files changed, 113 insertions, 0 deletions
@@ -3,6 +3,7 @@ /dist/ __pycache__ .venv +.coverage /backend/hist_data/data.db /backend/hist_data/wikidata/events.db diff --git a/backend/hist_data/README.md b/backend/hist_data/README.md index 96c31b5..bfecc1e 100644 --- a/backend/hist_data/README.md +++ b/backend/hist_data/README.md @@ -73,3 +73,6 @@ Some of the scripts use third-party packages: 1. Additional events can be described in `picked/events.json`, with images for them put in `picked` (see the README for details). 1. Can run `gen_picked_data.py` to add those described events to the database. + +## Remove Events Without Images/Descs +1. Run `reduce_event_data.py` to remove data for events that have no image/description. diff --git a/backend/hist_data/reduce_event_data.py b/backend/hist_data/reduce_event_data.py new file mode 100755 index 0000000..15c2ab5 --- /dev/null +++ b/backend/hist_data/reduce_event_data.py @@ -0,0 +1,39 @@ +#!/usr/bin/python3 + +""" +Delete extraneous events from the database that have no image (and consequently no description) +""" + +import argparse +import sqlite3 + +DB_FILE = 'data.db' + +def reduceData(dbFile: str) -> None: + dbCon = sqlite3.connect(dbFile) + dbCur = dbCon.cursor() + # + print('Getting events to delete') + eventsToDel = set() + query = 'SELECT events.id FROM events LEFT JOIN event_imgs ON events.id = event_imgs.id WHERE event_imgs.id IS NULL' + for (eventId,) in dbCur.execute(query): + eventsToDel.add(eventId) + # + print('Deleting events') + iterNum = 0 + for eventId in eventsToDel: + iterNum += 1 + if iterNum % 1e5 == 0: + print(f'At iteration {iterNum}') + # + dbCur.execute('DELETE from events where id = ?', (eventId,)) + dbCur.execute('DELETE from pop where id = ?', (eventId,)) + # + dbCon.commit() + dbCon.close() + +if __name__ == '__main__': + parser = argparse.ArgumentParser(description=__doc__, formatter_class=argparse.RawDescriptionHelpFormatter) + args = parser.parse_args() + # + reduceData(DB_FILE) diff --git a/backend/tests/test_reduce_event_data.py b/backend/tests/test_reduce_event_data.py new file mode 100644 index 0000000..cb54758 --- /dev/null +++ b/backend/tests/test_reduce_event_data.py @@ -0,0 +1,70 @@ +import unittest +import tempfile, os + +from tests.common import createTestDbTable, readTestDbTable +from hist_data.reduce_event_data import reduceData + +class TestReduceData(unittest.TestCase): + def test_reduce(self): + with tempfile.TemporaryDirectory() as tempDir: + # Create temp history db + dbFile = os.path.join(tempDir, 'data.db') + createTestDbTable( + dbFile, + 'CREATE TABLE events (id INT PRIMARY KEY, title TEXT UNIQUE, ' \ + 'start INT, start_upper INT, end INT, end_upper INT, fmt INT, ctg TEXT)', + 'INSERT INTO events VALUES (?, ?, ?, ?, ?, ?, ?, ?)', + { + (1, 'event one', 1900, None, None, None, 0, 'event'), + (2, 'event two', 2452594, None, 2455369, None, 3, 'human'), # 2/11/2002 to 21/06/2010 + (3, 'event three', 2448175, 2451828, None, None, 2, 'discovery'), # 10/10/1990 to 10/10/2000 + } + ) + createTestDbTable( + dbFile, + 'CREATE TABLE pop (id INT PRIMARY KEY, pop INT)', + 'INSERT INTO pop VALUES (?, ?)', + { + (1, 11), + (2, 21), + } + ) + createTestDbTable( + dbFile, + 'CREATE TABLE event_imgs (id INT PRIMARY KEY, img_id INT)', + 'INSERT INTO event_imgs VALUES (?, ?)', + { + (1, 10), + } + ) + createTestDbTable( + dbFile, + 'CREATE TABLE images (id INT PRIMARY KEY, url TEXT, license TEXT, artist TEXT, credit TEXT)', + 'INSERT INTO images VALUES (?, ?, ?, ?, ?)', + { + (10, 'example.com/1', 'cc0', 'artist one', 'credits one'), + } + ) + createTestDbTable( + dbFile, + 'CREATE TABLE descs (id INT PRIMARY KEY, wiki_id INT, desc TEXT)', + 'INSERT INTO descs VALUES (?, ?, ?)', + { + (1, 100, 'desc one'), + } + ) + # Run + reduceData(dbFile) + # Check + self.assertEqual( + readTestDbTable(dbFile, 'SELECT id, title, start, start_upper, end, end_upper, fmt, ctg from events'), + { + (1, 'event one', 1900, None, None, None, 0, 'event'), + } + ) + self.assertEqual( + readTestDbTable(dbFile, 'SELECT id, pop from pop'), + { + (1, 11), + } + ) |