如何在Python中捕获create.sql的所有SQLite错误？

我有用于创建SQLite3数据库并填充测试数据的create.sql和populate.sql文件，生成的数据库会保存下来做进一步分析。我用Python自动化处理多组这类SQL文件，代码如下：

db_mem = sqlite3.connect("\:memory:")
cur = db_mem.cursor()
try:
    with open(row['path']+'/' + 'create.sql') as fp:
        cur.executescript(fp.read())  
except:
    creates.append('C-error')
    continue
else:
    creates.append('Ok')
    try:
        with open(row['path']+'/' + 'populate.sql') as fp:
            cur.executescript(fp.read())  
    except sqlite3.Error as x:
        populates.append('P-error')
    else:
        populates.append('Ok')
        db_disk = sqlite3.connect(f'./SQL_Final/{index}_out.db')
        db_mem.backup(db_disk)

但执行create.sql时，Python只能捕获到一个错误，而在SQLite命令行里执行.read create.sql能输出多个错误。比如下面这个错误的create.sql示例：

DROP TABLE IF EXISTS Comp;
CREATE TABLE Comp (
    idComp INTEGER PRIMARY KEY,
    nomeComp TEXT,
    dataInicio TEXT,
    dataFim TEXT
);
DROP TABLE IF EXISTS Game;
DROP TABLE IF EXISTS Stade;
DROP TABLE IF EXISTS Club;
DROP TABLE IF EXISTS Squad;
CREATE TABLE Game (
    idGame INTEGER PRIMARY KEY,
    golosFora INTEGER,
    golosCasa INTEGER,
    data DATE,
    jornada INTEGER,
    duração TIME,
    nomeComp TEXT REFERENCES Comp,
    nomeStade TEXT REFERENCES Stade,
    nomeSquadFora TEXT REFERENCES Squad,
    nomeSquadCasa TEXT REFERENCES Squad,
    CONSTRAINT CHECK_Game_golosFora CHECK (Game_golosFora >= 0),
    CONSTRAINT CHECK_Game_golosCasa CHECK (Game_golosCasa >= 0),
    CONSTRAINT CHECK_Game_jornada CHECK (jornada > 0)
);
CREATE TABLE Stade (
    nomeStade TEXT PRIMARY KEY,
    local TEXT NOT NULL,
    idGame INTEGER REFERENCES Game
);
CREATE TABLE Club (
    nomeClub TEXT PRIMARY KEY,
    país TEXT NOT NULL
);
CREATE TABLE Squad (
    nomeSquad TEXT PRIMARY Key,
    nomeClub TEXT REFERENCES Club
);

SQLite命令行执行结果：

Error: near line 2: in prepare, table "Comp" has more than one primary key (1)
Error: near line 13: in prepare, no such column: Game_golosFora (1)

Python执行仅捕获到单个错误：

Error: table "Comp" has more than one primary key

请问怎么才能捕获所有错误？

问题根源在于executescript()的执行机制：它会把整个SQL脚本作为一个事务运行，一旦遇到第一个错误就终止整个脚本，只抛出当前错误。而SQLite命令行默认是逐条执行语句，遇到错误会继续处理后续内容，因此能输出所有错误。

要捕获所有错误，需要将SQL脚本拆分为单独语句，逐个执行并收集每个语句的错误：

1. 拆分SQL语句：以分号;为分隔符拆分脚本，但要避开字符串、注释中的分号，避免误拆分。
2. 逐条执行并捕获错误：遍历拆分后的语句，单独执行每个语句并捕获错误，收集所有错误信息。

修改后的示例代码如下：

import sqlite3
import re

def split_sql_script(script):
    # 正则表达式拆分SQL语句，适配字符串、注释中的分号
    pattern = re.compile(
        r';(?=(?:[^"]*"[^"]*")*[^"]*$)(?![^()]*\))',
        re.MULTILINE | re.DOTALL
    )
    statements = pattern.split(script)
    # 过滤空语句和单行注释
    return [stmt.strip() for stmt in statements if stmt.strip() and not stmt.strip().startswith('--')]

db_mem = sqlite3.connect(':memory:')
cur = db_mem.cursor()
creates = []
populates = []

# 处理create.sql
try:
    with open(row['path']+'/' + 'create.sql') as fp:
        script = fp.read()
        statements = split_sql_script(script)
        all_errors = []
        for stmt in statements:
            try:
                cur.execute(stmt)
            except sqlite3.Error as e:
                all_errors.append(f"语句执行失败：'{stmt[:50]}...' - {str(e)}")
        if all_errors:
            creates.append(f'C-error: {"; ".join(all_errors)}')
        else:
            creates.append('Ok')
except Exception as e:
    creates.append(f'C-error: {str(e)}')
else:
    # 处理populate.sql
    try:
        with open(row['path']+'/' + 'populate.sql') as fp:
            script = fp.read()
            statements = split_sql_script(script)
            all_errors = []
            for stmt in statements:
                try:
                    cur.execute(stmt)
                except sqlite3.Error as e:
                    all_errors.append(f"语句执行失败：'{stmt[:50]}...' - {str(e)}")
            if all_errors:
                populates.append(f'P-error: {"; ".join(all_errors)}')
            else:
                populates.append('Ok')
                db_disk = sqlite3.connect(f'./SQL_Final/{index}_out.db')
                db_mem.backup(db_disk)
                db_disk.close()
    except Exception as e:
        populates.append(f'P-error: {str(e)}')

db_mem.close()

注意事项 #

• 正则拆分规则可根据脚本复杂度调整，如果存在大量多行注释或特殊格式字符串，需要优化正则表达式适配场景。
• 这种逐条执行的方式和SQLite命令行行为一致，会保留前面成功执行的语句结果。如果需要保持事务原子性（全成功或全失败），可以在执行前开启事务，收集完所有错误后统一回滚。

内容的提问来源于stack exchange，提问作者MrT77