pgx-analyzer / database /db_manager.py
PGx Analyzer Team
Fix permanent data deletion: add persistent disk deletion flag file, cache purging, SQLite vacuuming, and selective sample removal
f5f402a
Raw History Blame Contribute Delete
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")