db_manager.py

# db_manager.py
import sqlite3
from config import STUDENT_DB_PATH
import streamlit as st
import os
import time

MAX_RETRIES = 3
RETRY_DELAY = 2 # seconds

def get_db_connection():
    # Ensure the directory for the SQLite DB exists
    db_dir = os.path.dirname(STUDENT_DB_PATH)
    if db_dir and not os.path.exists(db_dir):
        try:
            os.makedirs(db_dir, exist_ok=True)
            print(f"Created directory for SQLite DB: {db_dir}")
        except Exception as e:
            st.error(f"无法创建SQLite数据库目录 {db_dir}: {e}")
            print(f"Could not create SQLite DB directory {db_dir}: {e}")
            return None # Cannot proceed if directory creation fails

    conn = None
    for attempt in range(MAX_RETRIES):
        try:
            conn = sqlite3.connect(STUDENT_DB_PATH, timeout=10) # Added timeout
            return conn
        except sqlite3.OperationalError as e:
            if "database is locked" in str(e):
                print(f"SQLite DB is locked (Attempt {attempt + 1}/{MAX_RETRIES}). Retrying in {RETRY_DELAY}s...")
                if attempt < MAX_RETRIES - 1:
                    time.sleep(RETRY_DELAY)
                else:
                    st.error("SQLite数据库持续锁定，请稍后再试。")
                    print("SQLite DB remains locked after multiple retries.")
                    return None
            else:
                st.error(f"连接SQLite数据库时出错: {e}")
                print(f"Error connecting to SQLite DB: {e}")
                return None
        except Exception as e: # Catch other potential errors
            st.error(f"连接SQLite数据库时发生未知错误: {e}")
            print(f"Unknown error connecting to SQLite DB: {e}")
            return None
    return None


def init_student_db():
    conn = get_db_connection()
    if conn is None:
        return
    try:
        cursor = conn.cursor()
        cursor.execute('''

        CREATE TABLE IF NOT EXISTS students (

            id INTEGER PRIMARY KEY AUTOINCREMENT,

            name TEXT UNIQUE NOT NULL,

            characteristics_summary TEXT,

            last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP

        )

        ''')
        conn.commit()
    except Exception as e:
        st.error(f"初始化学生数据库表时出错: {e}")
        print(f"Error initializing student DB table: {e}")
    finally:
        if conn:
            conn.close()

def add_or_update_student(name: str, characteristics_summary: str = None):
    conn = get_db_connection()
    if conn is None:
        return False
    try:
        cursor = conn.cursor()
        # Upsert logic: Insert if name doesn't exist, or update if it does.
        # Using INSERT OR IGNORE then UPDATE is a common pattern.
        cursor.execute("INSERT OR IGNORE INTO students (name) VALUES (?)", (name,))
        if characteristics_summary is not None: # Allow updating only name or also characteristics
            cursor.execute("""

            UPDATE students

            SET characteristics_summary = ?, last_updated = CURRENT_TIMESTAMP

            WHERE name = ?

            """, (characteristics_summary, name))
        else: # Only ensure the student exists, update last_updated if already present
             cursor.execute("""

            UPDATE students

            SET last_updated = CURRENT_TIMESTAMP

            WHERE name = ? AND EXISTS (SELECT 1 FROM students WHERE name = ?)

            """, (name,name))

        conn.commit()
        return True
    except Exception as e:
        st.error(f"添加或更新学生 '{name}' 时出错: {e}")
        print(f"Error adding/updating student '{name}': {e}")
        return False
    finally:
        if conn:
            conn.close()

def get_student_characteristics(name: str):
    conn = get_db_connection()
    if conn is None:
        return None
    try:
        cursor = conn.cursor()
        cursor.execute("SELECT characteristics_summary FROM students WHERE name = ?", (name,))
        result = cursor.fetchone()
        return result[0] if result else None
    except Exception as e:
        st.error(f"获取学生 '{name}' 特点时出错: {e}")
        print(f"Error getting characteristics for student '{name}': {e}")
        return None
    finally:
        if conn:
            conn.close()

def get_all_student_names():
    conn = get_db_connection()
    if conn is None:
        return []
    try:
        cursor = conn.cursor()
        cursor.execute("SELECT name FROM students ORDER BY name ASC")
        results = [row[0] for row in cursor.fetchall()]
        return results
    except Exception as e:
        st.error(f"获取所有学生姓名时出错: {e}")
        print(f"Error getting all student names: {e}")
        return []
    finally:
        if conn:
            conn.close()