File size: 5,318 Bytes
276a548
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
f5f402a
 
 
 
 
 
 
276a548
 
 
 
 
 
f5f402a
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
"""
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")