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

# 前回の続き
https://qiita.com/MinaraiProgrammer/items/4514daf48739d6997687


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


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

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

https://kamiki.blog/openpyxl-auto-filter-of-excel/


# 手順
使用するライブラリをインストールしておく。
```:コマンドプロンプト
$ pip install pyodbc
$ pip install pandas
$ pip install openpyxl
```
>pandas
データを効率的に扱う(取込・加工・集計・分析)ためのライブラリ
今回は、DataFrame変換・Excel出力に使用

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

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

```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|


# コード解説（一部抜粋）
```python
    # 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]])
    # --------------
```
（前編）では、上記４行のコードで結果を出力していたのを、
（後編）では、４項目を配列に追加する方法に変更した。

```python
# 配列をpandas.DataFrameに変換
df = pd.DataFrame(arr, columns=['DBパス','DB名','テーブル名','カラム名'])
```
PandasのDataFrame：データを２次元(行と列)で格納できる。
・対象データ：arr(上で作成した配列)
・columns：任意の列名を付ける

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

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

```python
# Excel出力
wb_path = r'C:\CCC\DB情報一覧.xlsx'
df.to_excel(wb_path, index=False)
```
作成したDataFrameをExcelに出力する。
・index：行番号など(今回は不要=False)

```python
# 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」で実装できる。範囲は全データ選択の方が正しそうだが、１行指定でもいけたので一旦これで。