將DBF檔案(dBase, FoxPro等)中的資料轉換到SQLite

liongis發表於2024-07-17
SQLite

將DBF檔案(dBase, FoxPro等)中的資料轉換到SQLite，可遍歷指定目錄下所有的dbf檔案。可參考以下程式，本程式參考了dbf-to-sqlite： 

# _*_coding: utf-8 _*_
'''
@File:        main.py
@Time:        2024/07/17
@Author:      LionGIS
@Contact:     liongis@163.com
@Description: 將DBF檔案(dBase, FoxPro等)中的資料轉換到SQLite
'''

import fnmatch
import os
import click
import dbf
from pathlib import Path
from sqlite_utils import Database

def main(dbf_paths, sqlite_db, table_name=None, verbose=None):
    """
    Convert DBF files (dBase, FoxPro etc) to SQLite
    https://github.com/simonw/dbf-to-sqlite
    """
    db = Database(sqlite_db)

    db_files = []
    # 遍歷目錄
    # for root, dirs, files in os.walk(dbf_paths):
    files = os.listdir(dbf_paths)
    for file in files:
        if fnmatch.fnmatch(file, '*.dbf'):
            if table_name:
                if (table_name + ".dbf").lower() == file.lower():
                    db_files.append(os.path.join(dbf_paths, file))
            else:
                db_files.append(os.path.join(dbf_paths, file))

    for path in db_files:
        table_name = Path(path).stem
        if verbose:
            click.echo('載入 {} 插入表 "{}"'.format(path, table_name))
        table = dbf.Table(str(path))
        table.open()
        columns = table.field_names
        db[table_name].insert_all(dict(zip(columns, list(row))) for row in table)
        table.close()
    db.vacuum()

if __name__ == "__main__":
    # 根目錄
    base_path = r"c:/demo/"

    #  dbf檔案路徑
    dbf_path = base_path + ""
    #  sqlite檔案路徑 + 檔名
    sqlite_db = base_path + "database.db"
    #  表名,None為所有dbf檔案
    table_name = None
    main(dbf_path, sqlite_db, table_name, True)

相關文章