Database Cleanup Script
Relevant source files
The following files were used as context for generating this wiki page:
The Database Cleanup Script and associated cleaning methods are designed to maintain data integrity within the Spotify API Data Collector system. These tools handle the removal of test artifacts, the filtering of low-quality records, and the final refinement of collected metadata to ensure that only relevant and high-popularity tracks are preserved for analysis.
The system utilizes two primary cleaning approaches: a programmatic interface within the DataProcessor service for live database maintenance, and a standalone post-processing script (Clean_finale.py) that operates on exported data to generate a finalized dataset.
Sources: scripts/Clean_finale.py:1-20, service/data_processor.py:112-115
In-Database Cleaning Logic
The DataProcessor class provides an internal method, clean_test_data(), which executes direct SQL commands against the SQLite database to remove entries generated during testing or development phases. This process is essential for keeping the production database free of mock data that could skew statistical results.
Cleanup Components
The cleanup logic targets specific patterns commonly used in test suites:
- Artist-based removal: Deletes tracks where the artist name matches "Test Artist".
- Name-based filtering: Removes tracks with names starting with "Test" or "Track".
The following flow diagram illustrates the internal cleanup sequence within the database:
The diagram shows the sequential execution of SQL DELETE queries against the tracks table.
| Target Pattern | SQL Logic | Implementation File |
|---|---|---|
| Test Artists | artist_name = 'Test Artist' | service/data_processor.py |
| Mock Tracks | track_name LIKE 'Test %' | service/data_processor.py |
| Default Placeholders | track_name LIKE 'Track %' | service/data_processor.py |
Sources: service/data_processor.py:112-132, test/test_data_processor.py:214-230
Final Post-Processing Script
The scripts/Clean_finale.py script serves as a final refinement layer. Unlike the DataProcessor cleanup, this script operates on the exported spotify_data.csv file rather than the live database. It utilizes the pandas library to perform sophisticated filtering and ranking that would be more complex to execute in standard SQL.
Processing Workflow
The script follows a structured data pipeline:
- Load: Reads the raw CSV data into a DataFrame.
- Filter: Identifies and drops rows containing "Test Artist" or "Test" in the name fields.
- Refine: Groups the data by year and extracts the top 200 tracks for each year based on the
track_popularityscore. - Save: Outputs the refined collection to
spotify_data_final.csv.
The sequence diagram demonstrates the transformation from raw collected data to a final curated dataset.
Sources: scripts/Clean_finale.py:10-30
Database Interaction Layer
The cleanup operations rely on the Database wrapper class to handle transaction management and query execution. Specifically, the execute_write method is used for cleanup tasks to ensure changes are committed and the number of affected rows is tracked.
Data Integrity Constraints
The cleanup scripts operate within a schema defined in app/database.py. The tracks table includes a UNIQUE constraint on track_id, which prevents the introduction of duplicate entries even if the cleaning and re-collection processes overlap.
The ER diagram highlights the key fields targeted by the cleanup and refinement logic.
Sources: app/database.py:11-28, app/db.py:53-65
Summary of Cleanup Execution
The cleanup system ensures that the Spotify data collection remains high-quality by providing both real-time database purging and offline dataset refinement. This two-tier approach allows developers to maintain a clean working environment during development while ensuring the final analytical outputs are statistically significant and free of test noise.