【Python】散乱しているAccessのDB情報を取得する（後編）

前回の続き

目標

• 検索用のExcelシートとして出力
• 結果をデータベース化する　※今後予定

参考資料

下記資料を参考にさせていただきました。ありがとうございました。

・Excelオートフィルタ設定　について

手順

使用するライブラリをインストールしておく。

コマンドプロンプト

$ pip install pyodbc
$ pip install pandas
$ pip install openpyxl

pandas
データを効率的に扱う(取込・加工・集計・分析)ためのライブラリ
今回は、DataFrame変換・Excel出力に使用

openpyxl
PythonからExcelファイルを操作するためのライブラリ
今回は、Excelを開く・フィルタ設定・保存に使用

下記のPythonコードを実行する。

Python

# ライブラリをインポート
import pyodbc
# 追加----------
import pandas as pd
import openpyxl
# --------------

# DBのファイルパス指定
db_paths = []
db_paths.append(r'C:\AAA\database1.accdb')
db_paths.append(r'C:\BBB\database2.accdb')

# 追加----------
# 配列を初期化
arr = []
# --------------

for db_path in db_paths:
    # データベース接続
    con_str = (
        'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
        f'DBQ={db_path};'
    )
    conn = pyodbc.connect(con_str)
    cursor = conn.cursor()

    #ファイルパス名を分割
    split_path = db_path.rsplit('\\',1)
    # print(split_path[0]) #DBパス出力
    # print(split_path[1]) #DB名出力

    # データベース内のテーブル取得
    for table_info in cursor.tables(tableType='TABLE'):
        # print(f'\t{table_info.table_name}') #テーブル名出力

        # テーブル内のカラム名（＋データ１行のみ）取得
        result = conn.execute(f'SELECT TOP 1 * FROM [{table_info.table_name}]')

        # 全カラムのメタデータ群取得
        columns_metadatas = result.description

        # １カラムずつ参照
        for column_metadatas in columns_metadatas:
            # print(f'\t\t{column_metadatas[0]}') #メタデータ群のうち、カラム名(0)出力
        
            # 追加----------
            # DBパス,DB名,テーブル名,カラム名を配列に格納
            arr.append([split_path(0), split_path(1), table_info.table_name, column_metadatas[0]])
            # --------------
    # データベースを閉じる
    cursor.close()
    conn.close()

# 追加----------
# 配列をpandas.DataFrameに変換
df = pd.DataFrame(arr, columns=['DBパス','DB名','テーブル名','カラム名'])

# Excel出力
wb_path = r'C:\CCC\DB情報一覧.xlsx'
df.to_excel(wb_path, index=False)

# Excelシートにオートフィルタ設定をする
wb = openpyxl.load_workbook(wb_path)
ws = wb['Sheet1']
ws.auto_filter.ref = 'A1:D1'
ws.save(wb_path)
ws.close
# --------------

実行結果

下記のような形式のExcelシートが出力される。
🔽はExcelのフィルタ機能。テーブル名やカラム名を簡単に検索できる。

DBパス🔽	DB名🔽	テーブル名🔽	カラム名🔽
C:\AAA	database1.accdb	テーブル名A	カラム名a
C:\AAA	database1.accdb	テーブル名A	カラム名b
C:\BBB	database2.accdb	テーブル名B	カラム名c
C:\BBB	database2.accdb	テーブル名B	カラム名d

コード解説（一部抜粋）

    # print(split_path[0]) #DBパス出力
    # print(split_path[1]) #DB名出力
    # print(f'\t{table_info.table_name}') #テーブル名出力
    # print(f'\t\t{column_metadatas[0]}') #メタデータ群のうち、カラム名(0)出力

    # 追加----------
    # DBパス,DB名,テーブル名,カラム名を配列に格納
    arr.append([split_path[0], split_path[1], table_info.table_name, column_metadatas[0]])
    # --------------

（前編）では、上記４行のコードで結果を出力していたのを、
（後編）では、４項目を配列に追加する方法に変更した。

# 配列をpandas.DataFrameに変換
df = pd.DataFrame(arr, columns=['DBパス','DB名','テーブル名','カラム名'])

PandasのDataFrame：データを２次元(行と列)で格納できる。
・対象データ：arr(上で作成した配列)
・columns：任意の列名を付ける

print(df)

作成したDataFrameを出力すると、下記のようなデータ形式になっている。

	DBパス	DB名	テーブル名	カラム名
0	C:\AAA	database1.accdb	テーブル名A	カラム名a
1	C:\AAA	database1.accdb	テーブル名A	カラム名b

# Excel出力
wb_path = r'C:\CCC\DB情報一覧.xlsx'
df.to_excel(wb_path, index=False)

作成したDataFrameをExcelに出力する。
・index：行番号など(今回は不要=False)

# Excelシートにオートフィルタ設定をする
wb = openpyxl.load_workbook(wb_path)
ws = wb['Sheet1']
ws.auto_filter.ref = 'A1:D1'
ws.save(wb_path)
ws.close

オートフィルタ設定をしておく。
Excelシートの先頭行に三角ボタン(🔽)が表示され、検索しやすくなる。
「auto_filter.ref」で実装できる。範囲は全データ選択の方が正しそうだが、１行指定でもいけたので一旦これで。