用 Python 批量檢查 sqlite/db3 檔案是否損壞（qbit）

• 對 Python 3.8 適用
• check_db3.py

# encoding: utf-8
# author: qbit
# date: 2022-05-10
# summary: 遍歷檢查 db3 檔案是否正確，統計記錄條數

import os
import sys
import time
import pprint
import sqlite3
import traceback
from multiprocessing import Pool, Lock

SubProcNum = 1      # 子程式數量
TableName = 'my_table_name'
Db3Dir = r'F:\tmp'

def Init4ProcOneFile(lock):
    r""" 子程式初始化函式 """
    global gLock
    gLock = lock

def ProcOneFile(db3file: str):
    r'''
    檔案無損壞時返回 sqlite 檔案中的記錄條數
    '''
    pid = os.getpid()
    if SubProcNum > 2:      # 多程式版本
        with gLock:
            print(f'pid:{pid}, ProcOneFile {db3file} ...')
    else:                   # 單程式版本
        print(f'pid:{pid}, ProcOneFile {db3file} ...')
    conn = sqlite3.connect(db3file)
    cur = conn.cursor()
    sql = f'select count(*) from {TableName};'
    exMsg = ''
    try:
        cur.execute(sql)
        row = cur.fetchone()
    except Exception as ex:
        exMsg = traceback.format_exc()
        if SubProcNum > 2:      # 多程式版本
            with gLock:
                print(f'Error file: {db3file} \n {exMsg}')
        else:
            print(f'Error file: {db3file} \n {exMsg}')
    cur.close()
    conn.close()
    if exMsg:
        return 'no', 0, db3file
    else:
        return 'ok', row[0], db3file

if __name__ == '__main__':
    db3List = []
    for root, dirs, files in os.walk(Db3Dir):
        for file in files:
            pathfile = os.path.join(root, file)
            if not file.endswith('.db3'):
                continue
            db3List.append(pathfile)
    print(f'db3List size: {len(db3List)}')
    if not db3List:
        sys.exit(0)
    
    startTime = time.time()

    lock = Lock()
    print(f'子程式數量: {SubProcNum}')    
    okDb3List = []              # 檔案未損壞，且資料條數大於 0
    zeroDb3List = []            # 檔案可讀，資料條數為 0
    errDb3List = []             # 檔案損壞    
    if SubProcNum > 1:      # 多程式版本     
        # 還未開啟多程式，無需加鎖
        print(f'{db3List=}')
        with Pool(SubProcNum, initializer=Init4ProcOneFile, initargs=(lock,)) as p:
            results = p.imap_unordered(func=ProcOneFile, iterable=db3List)
            recordNum = 0           # 完好檔案的資料總條數
            fileCnt = 0             # 已處理的檔案個數
            for status, num, db3file in results:
                if status == 'ok':
                    if num > 0:
                        recordNum += num
                        okDb3List.append((num, db3file))
                    else:
                        zeroDb3List.append((num, db3file))
                else:
                    errDb3List.append(db3file)
                fileCnt += 1
                with lock:
                    print(f'需處理總檔案個數: {len(db3List)}, '
                            f'已處理檔案個數: {fileCnt}, '
                            f'okDb3List size: {len(okDb3List)}, errDb3List size: {len(errDb3List)}',
                            f'已花費時間: {(time.time()-startTime):.2f}s')
            print(f'需處理總檔案個數: {len(db3List)}, '
                        f'已處理檔案個數: {fileCnt}, '
                        f'okDb3List size: {len(okDb3List)}, errDb3List size: {len(errDb3List)}',
                        f'已花費時間: {(time.time()-startTime):.2f}s')        
        print(f"okDb3List: \n {pprint.pformat(okDb3List)}")
        print(f"zeroDb3List: \n {pprint.pformat(zeroDb3List)}")
        print(f"errDb3List: \n {pprint.pformat(errDb3List)}")
        print(f"db3List size: {len(db3List)}, okDb3List size: {len(okDb3List)}, zeroDb3List size: {len(zeroDb3List)}, errDb3List size: {len(errDb3List)}")
        print(f"okDb3 record number: {recordNum}")
    else:               # 單程式版本
        print(f'{db3List=}')
        recordNum = 0           # 完好檔案的資料總條數
        fileCnt = 0             # 已處理的檔案個數
        for db3file in db3List:
            status, num, db3file = ProcOneFile(db3file)
            if status == 'ok':
                if num > 0:
                    recordNum += num
                    okDb3List.append((num, db3file))
                else:
                    zeroDb3List.append((num, db3file))
            else:
                errDb3List.append(db3file)
            fileCnt += 1
            with lock:
                print(f'需處理總檔案個數: {len(db3List)}, '
                        f'已處理檔案個數: {fileCnt}, '
                        f'okDb3List size: {len(okDb3List)}, errDb3List size: {len(errDb3List)}',
                        f'已花費時間: {(time.time()-startTime):.2f}s')
        print(f"okDb3List: \n {pprint.pformat(okDb3List)}")
        print(f"zeroDb3List: \n {pprint.pformat(zeroDb3List)}")
        print(f"errDb3List: \n {pprint.pformat(errDb3List)}")
        print(f"db3List size: {len(db3List)}, okDb3List size: {len(okDb3List)}, zeroDb3List size: {len(zeroDb3List)}, errDb3List size: {len(errDb3List)}")
        print(f"okDb3 record number: {recordNum}")

    print(f"Time total: {(time.time()-startTime):.2f}s")

本文出自 qbit snap