MST
星途 面试题库

面试题：SQLite中创建数据库与表时的事务处理及异常情况应对

假设要在SQLite中创建一个复杂的项目管理数据库，其中有项目表projects（包含项目id、项目名称等字段）和任务表tasks（包含任务id、所属项目id、任务描述等字段）。要求在一个事务中完成数据库创建、两张表创建以及插入一条项目初始数据和对应的两条任务数据。请写出实现代码，并阐述如何处理可能出现的异常情况以保证数据完整性。
31.5万 热度难度
数据库SQLite

知识考点

AI 面试

面试题答案

一键面试

实现代码（Python示例，使用sqlite3库）

import sqlite3

try:
    # 连接到SQLite数据库，如果不存在则创建
    conn = sqlite3.connect('project_management.db')
    cursor = conn.cursor()

    # 开启事务
    cursor.execute('BEGIN')

    # 创建项目表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS projects (
            project_id INTEGER PRIMARY KEY AUTOINCREMENT,
            project_name TEXT NOT NULL
        )
    ''')

    # 创建任务表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS tasks (
            task_id INTEGER PRIMARY KEY AUTOINCREMENT,
            project_id INTEGER NOT NULL,
            task_description TEXT NOT NULL,
            FOREIGN KEY (project_id) REFERENCES projects(project_id)
        )
    ''')

    # 插入一条项目初始数据
    cursor.execute('INSERT INTO projects (project_name) VALUES (?)', ('示例项目',))
    project_id = cursor.lastrowid

    # 插入两条对应的任务数据
    cursor.execute('INSERT INTO tasks (project_id, task_description) VALUES (?,?)', (project_id, '任务1描述'))
    cursor.execute('INSERT INTO tasks (project_id, task_description) VALUES (?,?)', (project_id, '任务2描述'))

    # 提交事务
    cursor.execute('COMMIT')

    print("数据库创建、表创建及数据插入成功")

except sqlite3.Error as e:
    # 捕获异常，回滚事务
    conn.execute('ROLLBACK')
    print(f"发生错误: {e}")

finally:
    # 关闭连接
    if conn:
        conn.close()

异常处理以保证数据完整性

  1. 使用事务：在代码中，通过BEGIN开启事务，COMMIT提交事务。如果在事务执行过程中发生错误，使用ROLLBACK回滚事务，这样可以确保所有的操作要么全部成功，要么全部失败，不会出现部分数据更新的情况。
  2. 捕获异常：使用try - except块捕获sqlite3.Error异常。如果在数据库操作过程中出现任何SQLite相关的错误，都会被捕获，然后执行回滚操作，保证数据处于事务开始前的状态。
  3. 外键约束：在tasks表中设置了外键约束FOREIGN KEY (project_id) REFERENCES projects(project_id)，确保tasks表中的project_id引用的是projects表中存在的project_id。如果插入的project_id在projects表中不存在，将会抛出异常，事务会回滚。