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

背景

• 各部署に多くのAccessファイル(データベース)が散乱している
• 参照したい項目がどこにあるか一目で分からず、手当たり次第Accessファイルを開くしかない

目標

• Pythonを学習する
• Access(データベース名)、テーブル名、項目名を取得する
• 結果をデータベース化する　※今後予定

参考資料

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

• Accessへの接続 / テーブル情報取得　について

• descriptionプロパティの使用　について

手順

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

コマンドプロンプト

$ pip install pyodbc

pyodbc
Pythonから任意のDBにODBC接続するためのライブラリ
今回は、AccessDBへの接続に使用

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

Python

# モジュールをインポート
import pyodbc

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

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)出力

    # データベースを閉じる
    cursor.close()
    conn.close()

実行結果

下記のような実行結果が出力される。tab(\t)で見やすくしている。
どのDBにどのテーブル、項目が含まれているかが分かる。

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

コード解説（一部抜粋）

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

ファイルパス名(例：C:\AAA\database1.accdb)を、rsplit()で分割した。
Windowsだと'\\'(バックスラッシュ２つ)でないとエラーになる。

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

最終的にカラム名のみが取得できればよいので、データは「TOP 1」で１行のみに限定した。
SELECT文の[ ]は、Accessテーブル名によるエラーを回避するため。

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

        # １カラムずつ参照
        for column_metadatas in columns_metadatas:
            print(f'\t\t{column_metadatas[0]}') #メタデータ群のうち、カラム名(0)出力

カラム名の取得には、descriptionプロパティを使用した。

descriptionプロパティの説明文は下記の通り。
カラムについてのメタデータが格納されている。
例えば「型」の情報を取得する場合は、[1]を指定する。

The metadata for the columns returned in the last SQL SELECT statement, in the form of a list of tuples. Each tuple contains seven fields:

0.name of the column (or column alias)
1.type code, the Python-equivalent class of the column, e.g. str for VARCHAR
2.display size (pyodbc does not set this value)
3.internal size (in bytes)
4.precision
5.scale
6.nullable (True/False)