88 lines
2.8 KiB
Python
88 lines
2.8 KiB
Python
#!/usr/bin/env python3
|
||
# -*- coding: utf-8 -*-
|
||
|
||
import sqlite3
|
||
import os
|
||
import uuid
|
||
from datetime import datetime
|
||
|
||
def init_database():
|
||
"""初始化SQLite数据库"""
|
||
db_path = '/Users/moshui/Documents/survey/data/survey.db'
|
||
|
||
# 如果数据库已存在，先删除
|
||
if os.path.exists(db_path):
|
||
os.remove(db_path)
|
||
print(f"已删除旧数据库: {db_path}")
|
||
|
||
# 创建数据库连接
|
||
conn = sqlite3.connect(db_path)
|
||
cursor = conn.cursor()
|
||
|
||
# 创建学员信息表
|
||
cursor.execute('''
|
||
CREATE TABLE students (
|
||
id TEXT PRIMARY KEY,
|
||
name TEXT NOT NULL,
|
||
school TEXT NOT NULL,
|
||
grade TEXT NOT NULL,
|
||
phone TEXT NOT NULL,
|
||
selected_tag TEXT,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
)
|
||
''')
|
||
|
||
# 创建答题会话表
|
||
cursor.execute('''
|
||
CREATE TABLE quiz_sessions (
|
||
id TEXT PRIMARY KEY,
|
||
student_id TEXT NOT NULL,
|
||
questions_config TEXT NOT NULL,
|
||
status TEXT DEFAULT 'created',
|
||
started_at TIMESTAMP,
|
||
completed_at TIMESTAMP,
|
||
total_score INTEGER DEFAULT 0,
|
||
max_score INTEGER DEFAULT 100,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (student_id) REFERENCES students (id)
|
||
)
|
||
''')
|
||
|
||
# 创建答题记录表 - 修改为JSON格式存储，添加用户信息字段
|
||
cursor.execute('''
|
||
CREATE TABLE quiz_answers (
|
||
id TEXT PRIMARY KEY,
|
||
session_id TEXT NOT NULL UNIQUE,
|
||
student_name TEXT NOT NULL,
|
||
student_school TEXT NOT NULL,
|
||
student_grade TEXT NOT NULL,
|
||
selected_tag TEXT,
|
||
answers_data TEXT NOT NULL,
|
||
total_score INTEGER DEFAULT 0,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (session_id) REFERENCES quiz_sessions (id)
|
||
)
|
||
''')
|
||
|
||
# 提交更改
|
||
conn.commit()
|
||
|
||
print("数据库表创建成功:")
|
||
print("- students: 学员信息表")
|
||
print("- quiz_sessions: 答题会话表")
|
||
print("- quiz_answers: 答题记录表 (JSON格式)")
|
||
|
||
# 创建索引
|
||
cursor.execute('CREATE INDEX idx_students_name ON students(name)')
|
||
cursor.execute('CREATE INDEX idx_sessions_student ON quiz_sessions(student_id)')
|
||
cursor.execute('CREATE INDEX idx_answers_session ON quiz_answers(session_id)')
|
||
|
||
conn.commit()
|
||
conn.close()
|
||
|
||
print("索引创建完成")
|
||
print(f"数据库初始化完成: {db_path}")
|
||
|
||
if __name__ == "__main__":
|
||
init_database() |