智能简历平台Plus——数据库（4）

数据库作为数据存储的工具具有多方面的优点

• 数据结构化和组织化： 数据库能够将数据以表格形式进行结构化和组织，每个数据项都有明确的字段和类型。这种结构化使得数据容易被理解、管理和操作，提高了数据的整体质量和一致性。

• 数据一致性： 关系型数据库通过事务管理确保数据的一致性。在执行数据操作时，数据库会保证事务的原子性、一致性、隔离性和持久性（ACID特性），从而有效地防止数据损坏或不一致的情况。

• 数据安全性： 数据库提供了强大的安全性控制机制，包括权限管理、数据加密、审计功能等。管理员可以为不同的用户或角色分配不同的访问权限，保护敏感数据免受未经授权的访问或恶意攻击。

• 高效的数据检索和查询： 数据库使用索引等数据结构来优化数据检索和查询操作，使得在大数据量下也能快速响应用户的查询请求。这种优化能力大大提升了数据的访问效率。

• 数据备份与恢复： 数据库支持定期的数据备份和恢复操作，确保即使在意外情况下，如硬件故障或数据损坏，也能快速恢复数据到先前的状态，保证业务的连续性和数据的可靠性。

---

具体代码实现

# 创建用户详细信息表
cursor.execute('''CREATE TABLE IF NOT EXISTS user_details
                  (user_id INTEGER PRIMARY KEY AUTOINCREMENT,
                   resume_name TEXT,
                   image BLOB,
                   name TEXT,
                   age INTEGER,
                   gender TEXT,
                   email TEXT,
                   phone TEXT,
                   nation TEXT,
                   political_outlook TEXT,
                   address TEXT,

                   school_name TEXT,
                   major TEXT,
                   educational_background TEXT,
                   readingTime_start TEXT,
                   readingTime_end TEXT,
                   experience_details TEXT,
                   
                   intention_position TEXT,
                   intention_city TEXT,
                   intention_salary TEXT,
                   attend_time TEXT,
                   
                   program_name TEXT,
                   program_timeRole TEXT,
                   program_timeStart TEXT,
                   program_timeEnd TEXT,
                   program_experience TEXT,
                   
                   internshipTime_companyName TEXT,
                   internshipTime_position TEXT,
                   internshipTime_start TEXT,
                   internshipTime_end TEXT,
                   internship_experience TEXT,
                   
                   schoolExperience_name TEXT,
                   schoolExperience_role TEXT,
                   schoolExperience_experience TEXT,
                   
                   speciality TEXT,
                   
                   honor TEXT,
                   
                   selfEvaluation TEXT,
                   FOREIGN KEY(user_id) REFERENCES users(id))''')
conn.commit()

user_details表是以user_id为主键，以users表的id为外键

插入用户详细信息

def insert_user_detail(conn,user_id, resume_name, image, name, age, gender, email, phone, nation, political_outlook, address,
                       school_name, major, educational_background, readingTime_start, readingTime_end,
                       experience_details, intention_position, intention_city, intention_salary, attend_time,
                       program_name, program_timeRole, program_timeStart, program_timeEnd, program_experience,
                       internshipTime_companyName, internshipTime_position, internshipTime_start, internshipTime_end,
                       internship_experience, schoolExperience_name, schoolExperience_role, schoolExperience_startTime,
                       schoolExperience_endTime, schoolExperience_experience, speciality, honor, selfEvaluation):
    try:
        cursor = conn.cursor()
        cursor.execute('''INSERT INTO user_details (user_id,resume_name, image, name, age, gender, email, phone, nation, political_outlook, address,
                                                    school_name, major, educational_background, readingTime_start, readingTime_end,
                                                    experience_details, intention_position, intention_city, intention_salary, attend_time,
                                                    program_name, program_timeRole, program_timeStart, program_timeEnd, program_experience,
                                                    internshipTime_companyName, internshipTime_position, internshipTime_start, internshipTime_end,
                                                    internship_experience, schoolExperience_name, schoolExperience_role, schoolExperience_startTime,
                                                    schoolExperience_endTime, schoolExperience_experience, speciality, honor, selfEvaluation)
                          VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)''',
                       (user_id,resume_name, image, name, age, gender, email, phone, nation, political_outlook, address,
                        school_name, major, educational_background, readingTime_start, readingTime_end,
                        experience_details, intention_position, intention_city, intention_salary, attend_time,
                        program_name, program_timeRole, program_timeStart, program_timeEnd, program_experience,
                        internshipTime_companyName, internshipTime_position, internshipTime_start, internshipTime_end,
                        internship_experience, schoolExperience_name, schoolExperience_role, schoolExperience_startTime,
                        schoolExperience_endTime, schoolExperience_experience, speciality, honor, selfEvaluation))
        conn.commit()
        print("用户详细信息插入成功")
    except sqlite3.Error as e:
        print(f"发生数据库错误: {e}")
        conn.rollback()

更新数据库内容

def update_user_details(user_id, new_details):
    try:
        # 构建更新语句
        update_query = '''UPDATE user_details SET 
                          resume_name = ?,
                          image = ?,
                          name = ?,
                          age = ?,
                          gender = ?,
                          email = ?,
                          phone = ?,
                          nation = ?,
                          political_outlook = ?,
                          address = ?,
                          school_name = ?,
                          major = ?,
                          educational_background = ?,
                          readingTime_start = ?,
                          readingTime_end = ?,
                          experience_details = ?,
                          intention_position = ?,
                          intention_city = ?,
                          intention_salary = ?,
                          attend_time = ?,
                          program_name = ?,
                          program_timeRole = ?,
                          program_timeStart = ?,
                          program_timeEnd = ?,
                          program_experience = ?,
                          internshipTime_companyName = ?,
                          internshipTime_position = ?,
                          internshipTime_start = ?,
                          internshipTime_end = ?,
                          internship_experience = ?,
                          schoolExperience_name = ?,
                          schoolExperience_role = ?,
                          schoolExperience_startTime = ?,
                          schoolExperience_endTime = ?,
                          schoolExperience_experience = ?,
                          speciality = ?,
                          honor = ?,
                          selfEvaluation = ?
                          WHERE user_id = ?'''

        # 从 new_details 中提取需要更新的值
        data_tuple = (new_details.get('resume_name', None),
                      new_details.get('image', None),
                      new_details.get('name', None),
                      new_details.get('age', None),
                      new_details.get('gender', None),
                      new_details.get('email', None),
                      new_details.get('phone', None),
                      new_details.get('nation', None),
                      new_details.get('political_outlook', None),
                      new_details.get('address', None),
                      new_details.get('school_name', None),
                      new_details.get('major', None),
                      new_details.get('educational_background', None),
                      new_details.get('readingTime_start', None),
                      new_details.get('readingTime_end', None),
                      new_details.get('experience_details', None),
                      new_details.get('intention_position', None),
                      new_details.get('intention_city', None),
                      new_details.get('intention_salary', None),
                      new_details.get('attend_time', None),
                      new_details.get('program_name', None),
                      new_details.get('program_timeRole', None),
                      new_details.get('program_timeStart', None),
                      new_details.get('program_timeEnd', None),
                      new_details.get('program_experience', None),
                      new_details.get('internshipTime_companyName', None),
                      new_details.get('internshipTime_position', None),
                      new_details.get('internshipTime_start', None),
                      new_details.get('internshipTime_end', None),
                      new_details.get('internship_experience', None),
                      new_details.get('schoolExperience_name', None),
                      new_details.get('schoolExperience_role', None),
                      new_details.get('schoolExperience_startTime', None),
                      new_details.get('schoolExperience_endTime', None),
                      new_details.get('schoolExperience_experience', None),
                      new_details.get('speciality', None),
                      new_details.get('honor', None),
                      new_details.get('selfEvaluation', None),
                      user_id)

        # 执行更新语句
        cursor.execute(update_query, data_tuple)
        conn.commit()
        print(f"用户 {user_id} 的详细信息已成功更新")
    except sqlite3.Error as e:
        print(f"更新用户详细信息时出错：{e}")

删除用户详细信息

def delete_user_detail(user_id):
    # 构建删除语句
    delete_query = "DELETE FROM user_details WHERE user_id = ?"

    # 执行删除操作
    cursor.execute(delete_query, (user_id,))
    conn.commit()