Spaces:
Configuration error
Configuration error
PGx Analyzer Team
Fix permanent data deletion: add persistent disk deletion flag file, cache purging, SQLite vacuuming, and selective sample removal
f5f402a Download database/db_manager.py from Satya737/pgx-analyzer: direct link, hf CLI and curl.
- Browser
- Download file 5.32 kB
-
https://huggingface.co/spaces/Satya737/pgx-analyzer/resolve/main/database/db_manager.py
- Command line
-
hf download hf://spaces/Satya737/pgx-analyzer/database/db_manager.py
-
curl -L -o db_manager.py https://huggingface.co/spaces/Satya737/pgx-analyzer/resolve/main/database/db_manager.py
5.32 kB
| """ | |
| SQLite Database Persistence Layer for Incremental Sample Storage and Scalability. | |
| Supports appending 1,000 to 10,000+ sample batches with duplicate checking. | |
| """ | |
| import sqlite3 | |
| import pandas as pd | |
| from typing import Set, List, Tuple, Dict, Any | |
| import os | |
| import logging | |
| logger = logging.getLogger(__name__) | |
| class DatabaseManager: | |
| def __init__(self, db_path: str = "pharmacogenomics.db"): | |
| self.db_path = db_path | |
| self._init_db() | |
| def _get_connection(self): | |
| return sqlite3.connect(self.db_path) | |
| def _init_db(self): | |
| """Creates table schema if it does not exist.""" | |
| with self._get_connection() as conn: | |
| cursor = conn.cursor() | |
| cursor.execute(""" | |
| CREATE TABLE IF NOT EXISTS samples ( | |
| sample_id TEXT PRIMARY KEY, | |
| gender TEXT, | |
| date_of_birth TEXT, | |
| native_place TEXT, | |
| state TEXT, | |
| region TEXT, | |
| test_requested TEXT, | |
| three_generations TEXT, | |
| cyp2c19_2 TEXT, | |
| cyp2c19_3 TEXT, | |
| cyp2c19_17 TEXT, | |
| diplotype TEXT, | |
| phenotype TEXT, | |
| created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP | |
| ) | |
| """) | |
| conn.commit() | |
| def get_existing_sample_ids(self) -> Set[str]: | |
| """Returns set of all sample IDs in database.""" | |
| with self._get_connection() as conn: | |
| cursor = conn.cursor() | |
| cursor.execute("SELECT sample_id FROM samples") | |
| rows = cursor.fetchall() | |
| return {row[0] for row in rows} | |
| def insert_batch(self, df: pd.DataFrame) -> Tuple[int, int]: | |
| """ | |
| Inserts new samples into database. | |
| Returns tuple of (inserted_count, skipped_duplicate_count). | |
| """ | |
| existing_ids = self.get_existing_sample_ids() | |
| inserted_count = 0 | |
| skipped_count = 0 | |
| records = [] | |
| for _, row in df.iterrows(): | |
| sid = str(row.get('Sample_ID', '')).strip() | |
| if not sid or sid in existing_ids: | |
| skipped_count += 1 | |
| continue | |
| records.append(( | |
| sid, | |
| str(row.get('Gender_Clean', row.get('Gender', ''))), | |
| str(row.get('Date_of_Birth', '')), | |
| str(row.get('Native_Place_Clean', row.get('Native_Place', ''))), | |
| str(row.get('State_Clean', row.get('State', ''))), | |
| str(row.get('Region', '')), | |
| str(row.get('Test_Requested', '')), | |
| str(row.get('Three_Generations_Residency', '')), | |
| str(row.get('CYP2C19*2', '')), | |
| str(row.get('CYP2C19*3', '')), | |
| str(row.get('CYP2C19*17', '')), | |
| str(row.get('Diplotype', '')), | |
| str(row.get('Phenotype', '')) | |
| )) | |
| existing_ids.add(sid) | |
| inserted_count += 1 | |
| if records: | |
| with self._get_connection() as conn: | |
| cursor = conn.cursor() | |
| cursor.executemany(""" | |
| INSERT INTO samples ( | |
| sample_id, gender, date_of_birth, native_place, state, region, | |
| test_requested, three_generations, cyp2c19_2, cyp2c19_3, | |
| cyp2c19_17, diplotype, phenotype | |
| ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) | |
| """, records) | |
| conn.commit() | |
| return inserted_count, skipped_count | |
| def load_all_samples(self) -> pd.DataFrame: | |
| """Loads all historical samples into a Pandas DataFrame.""" | |
| with self._get_connection() as conn: | |
| df = pd.read_sql_query("SELECT * FROM samples", conn) | |
| if not df.empty: | |
| # Rename columns back to standard dataframe schema | |
| df = df.rename(columns={ | |
| 'sample_id': 'Sample_ID', | |
| 'gender': 'Gender_Clean', | |
| 'date_of_birth': 'Date_of_Birth', | |
| 'native_place': 'Native_Place', | |
| 'state': 'State', | |
| 'region': 'Region', | |
| 'test_requested': 'Test_Requested', | |
| 'three_generations': 'Three_Generations_Residency', | |
| 'cyp2c19_2': 'CYP2C19*2', | |
| 'cyp2c19_3': 'CYP2C19*3', | |
| 'cyp2c19_17': 'CYP2C19*17', | |
| 'diplotype': 'Diplotype', | |
| 'phenotype': 'Phenotype' | |
| }) | |
| return df | |
| def delete_sample_by_id(self, sample_id: str): | |
| """Deletes a specific sample record by sample_id from database.""" | |
| with self._get_connection() as conn: | |
| cursor = conn.cursor() | |
| cursor.execute("DELETE FROM samples WHERE sample_id = ?", (str(sample_id).strip(),)) | |
| conn.commit() | |
| def clear_database(self): | |
| """Clears all records from database.""" | |
| with self._get_connection() as conn: | |
| cursor = conn.cursor() | |
| cursor.execute("DELETE FROM samples") | |
| conn.commit() | |
| cursor.execute("VACUUM") | |