HOME > 技術情報 > Pythonで統計解析 > MS Accessファイル (accdb形式) へのアクセス【Python】
投稿日:

MS Accessファイル (accdb形式) へのアクセス【Python】

統計解析する元データはcsvやExcel形式など様々な形式で収集されますが、Excelのデータは必ずしも解析に適した形で保存されているとは限りません。また、ビッグデータを扱うような巨大なデータベースの場合は、Excelでデータを保存するのは無理があります。そのような場合は、汎用形式ではなくデータを扱うために設計された専用のデータベース形式を用いてデータを集積するのが一番です。ここでは身近なデータベースソフトとしてMicrosoft Officeに含まれているAccessを使ってみましょう。

Microsoft Accessのデータベース形式は.accdb形式ですが、それをPythonからアクセスする方法を説明します。

なお、データベースを扱うためには、SQLの知識があった方がよいですが、ここで説明するように一度pandas.DataFrame形式として読み込んでしまえばSQLの知識はなくても解析できます。

開発環境

  • Python 3.7.7
  • Pandas 1.0.3
  • pyodbc 4.0.30

pyodbcモジュールの取得

PythonからODBCを用いてデータベースに接続するためには、pyodbcモジュールを用います。AnacondaでPythonをインストールするとpyodbcも含まれていますが、まだpyodbcがインストールされていない場合は、condaから以下のようにインストールしましょう。

conda install pyodbc

ODBCはリレーショナルデータベースへアクセスするための共通インターフェースなので、pyodbcを用いればAccessのみならずSQL ServerやMySQL、SQLiteなどの各種データベースに接続することができます。

データベースエンジンをインストールする

Microsoft Access のデータベースエンジン

Microsoft Accessのデータベースエンジンには以下の2種類があります。従来からあるのがMicrosoft Jet データベースエンジンでWindowsに標準で組み込まれてきましたが、.accdbファイルには対応しておらず、また32ビット版しかありません。Microsoft Access 2007 以降の標準となっているデータベースエンジンはMicrosoft ACEとなっています。ただし、こちらはWindowsには標準では組み込まれておらず、自分でインストールする必要があります。

Microsoft Jet データベースエンジン

  • Microsoft Access 2003 までのデータベースエンジン
  • Jet 4.0 で開発終了
  • 64ビット版の提供なし

Microsoft ACE データベースエンジン

  • Microsoft Access 2007 以降のデータベースエンジン
  • Jetとも下位互換性があり.mdbファイルの読み書きもできる

自分の環境のデータベースエンジンの確認・インストール

以下のプログラムを実行しインストールされているデータベースエンジンの一覧を取得します。

import pyodbc
print(pyodbc.drivers())

この結果に ‘Microsoft Access Driver (*.mdb, *.accdb)’ が含まれていれば、すでにAccessのデータベースエンジンがインストール済みですが、含まれていなければインストールする必要があります。以下から64ビット版をダウンロードし、インストールします。

もう一度、先ほどのプログラムを実行し、結果に ‘Microsoft Access Driver (*.mdb, *.accdb)’ が含まれるようになったことを確認してください。

ポイント
Microsoft Accessをインストールしていてもデータベースエンジンが一緒にインストールされているとは限らないので、必ず確認してください。

Accessデータベースに接続する

サンプルデータベース

ここでは、「C:\BioTech-Lab」フォルダにSampleDB.accdbというファイル名で以下のデータベースを保存します。

基本的なデータベースの操作方法

pyodbcを用いたMicrosoft Accessへの接続方法は公式ドキュメントに説明されています。

まず、pyodbcのconnect関数で新規のConnectionインスタンスを生成し、次にConnectionのcursorメソッドで新規のカーソル(=Cursorクラスのインスタンス)を生成します。

conn_str = (
    r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
    r'DBQ=C:\BioTech-Lab\SampleDB.accdb;'
    )
conn = pyodbc.connect(conn_str)
ポイント
データベースにおける「カーソル」とは、データにアクセスする際の検索条件と現在位置(＝その検索結果のうち現在どのデータにアクセスしいているのか)を保持する仕組みです。
カーソルを用いることで、データベースから検索結果を1件ずつ処理していくことができます。

このカーソルにSQL文で条件を指定することで、データの取得・追加・更新を行うことができます。なお、データを取得する際は、カーソルからRowインスタンスとしてデータを取り出す(=fetch)ことができます。

データの取得を行う

CursorクラスのexecuteメソッドにSQLのSELECT文を指定して、実行します。

sql = 'SQL SELECT文'
cursor.execute(sql)

なお、取得した結果はCursorクラスのfetchallメソッドを用いて、1件ずつ取り出すことができます。

for row in cursor.fetchall():
    print(row)

なお、.accdbファイルをpandas.DataFrameとして読み込むのなら、pandas.read_sql関数にConnectionとSQLを渡すだけで簡単に取得することができます。

sql = 'SQL SELECT文'
df = pandas.read_sql(sql, conn)

データの追加・更新を行う

CursorクラスのexecuteメソッドにSQLのINSERT文やUPDATE文を指定し、commitメソッドを実行することでデータの追加・更新処理を実行できます。

sql = 'SQL INSERT文 / UPDATE文'
cursor.execute(sql)
cursor.commit()

データベースの接続を閉じる

CursorインスタンスのCloseメソッドでカーソルを閉じて、ConnectionインスタンスのCloseメソッドでConnectionを終了させます。

cursor.close()
conn.close()

pyodbcによるデータベース操作の例

データベースに含まれるテーブルの一覧を取得する

以下のようにしてサンプルDBに含まれるテーブルの一覧を取得してみましょう。

import pyodbc

# データベースに接続します
conn_str = (
    r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
    r'DBQ=C:\BioTech-Lab\SampleDB.accdb;'
    )
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()

# データベースに含まれるテーブルを取得します
for table_info in cursor.tables(tableType='TABLE'):
    print(table_info.table_name)

# データベースの接続を閉じます
cursor.close()
conn.close()

SampleTable

これで、サンプルDBに含まれているテーブル名であるSampleTableが得られました。データベースにテーブルが複数含まれている場合は、その一覧を取得できます。

データベースのテーブルをDataFrameとして取得する

サンプルDBに含まれるSampleTableテーブルのデータをすべて取得します。その後の解析がしやすいようにpandas.DataFrameとしてデータを取得してみましょう。

import pyodbc
import pandas as pd
 
# データベースに接続します
conn_str = (
    r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
    r'DBQ=C:\BioTech-Lab\SampleDB.accdb;'
    )
conn = pyodbc.connect(conn_str)
 
# データベースの指定したテーブルのデータをすべて抽出します
sql = 'SELECT * FROM SampleTable'
df = pd.read_sql(sql, conn)
print(df)
 
# データベースの接続を閉じます
conn.close()

   ID         日付     名前   支払額
0   1 2020-06-01   田中太郎  3000
1   2 2020-06-03   高橋次郎  4500
2   3 2020-06-09  竹内新次郎  2800
3   4 2020-06-19   佐藤花子  6000
4   5 2020-06-23   山田一郎  4200
5   6 2020-06-30  阿部恵美子  2340
6   7 2020-07-02  佐々木花子  4350
7   8 2020-07-13  渡辺美和子  8000

DataFrameの作成方法は以下の記事をご覧ください。

DataFrameの作成【Python】

なお、SQLを扱ったことのない方でも、SQLの一番基本的な以下の構文さえ理解すれば使うことができます。

SELECT * FROM テーブル名

これは「テーブル名」で指定したテーブルのすべてのデータを抽出するという命令で、テーブル名にデータを取得したいテーブルの名前を指定します。

条件を満たすデータをDataFrameとして取得する

大規模なデータベースの場合は、すべてのデータを取得して解析するのは非効率な場合もあります。ここでは、取得するデータを条件で絞って解析する方法を見ていきましょう。

サンプルDBに含まれるSampleTableテーブルから2020年6月のデータだけを抽出して、pandas.DataFrameとして取得してみます。条件の指定はSQLのSELECT文のWHERE句で行います。なお、Microsoft Accessで日付を表すときは#で囲む必要があります。

import pyodbc
import pandas as pd
 
# データベースに接続します
conn_str = (
    r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
    r'DBQ=C:\BioTech-Lab\SampleDB.accdb;'
    )
conn = pyodbc.connect(conn_str)
 
# データベースの指定したテーブルのデータのうち、指定された条件のものを抽出します
sql = 'SELECT * FROM SampleTable WHERE 日付 BETWEEN #2020/6/1# AND #2020/6/30#'
df = pd.read_sql(sql, conn)
print(df)
 
# データベースの接続を閉じます
conn.close()

   ID         日付     名前   支払額
0   1 2020-06-01   田中太郎  3000
1   2 2020-06-03   高橋次郎  4500
2   3 2020-06-09  竹内新次郎  2800
3   4 2020-06-19   佐藤花子  6000
4   5 2020-06-23   山田一郎  4200
5   6 2020-06-30  阿部恵美子  2340

これで2020年6月のデータだけ抽出できました。

名前で抽出する場合や、ワイルドカードを使う場合などは以下のようになります。

# 「田中太郎」のデータを抽出する
sql = "SELECT * FROM SampleTable WHERE 名前='田中太郎'"

# 名前が「田」で始まる人を抽出する(ワイルドカードを用いる)
sql = "SELECT * FROM SampleTable WHERE 名前 LIKE '田%'"

SQL上で文字列を扱うときは ‘ (クォーテーション)で囲む必要があるので、SQL文自体は ” (ダブルクォーテーション)で囲みましょう。また、使えるワイルドカードは以下の通りです。

  • %：0文字以上の任意の文字列
  • _：任意の一文字

データベースにデータを追加する

SQLのINSERT文を用いることでテーブルにデータを追加することができます。データの操作を行う場合は、CursorクラスのexecuteメソッドでSQLを実行してから、commitメソッドでデータの更新をする必要があります。

import pyodbc

# データベースに接続します
conn_str = (
    r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
    r'DBQ=C:\BioTech-Lab\SampleDB.accdb;'
    )
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()

# データベースにデータを追加します
sql = "INSERT INTO SampleTable(日付, 名前, 支払額) VALUES(#2020/7/19#, '山中一郎', 3900)"
cursor.execute(sql)
cursor.commit()

# データベースの接続を閉じます
cursor.close()
conn.close()

これで、このようにデータが追加されました。

なお、IDはAccessでオートナンバー型にしているので、INSERT文で指定しなくても自動的に割り振られます。

データベースのデータを更新する

SQLのUPDATE文を使うことでデータの更新を行うことができます。先ほどと同様に、CursorクラスのexecuteメソッドでSQLを実行してから、commitメソッドでデータの更新をする必要があります。

import pyodbc

# データベースに接続します
conn_str = (
    r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
    r'DBQ=C:\BioTech-Lab\SampleDB.accdb;'
    )
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()

# データベースの「山中一郎」の支払額を変更します
sql = "UPDATE SampleTable SET 支払額=4000 WHERE 名前='山中一郎'"
cursor.execute(sql)
cursor.commit()

# データベースの接続を閉じます
cursor.close()
conn.close()

これで、先ほど追加した山中一郎さんの支払額を変更できました。

データベースのデータを削除する

SQLのDELETE文を使うことでデータの更新を行うことができます。先ほどと同様に、CursorクラスのexecuteメソッドでSQLを実行してから、commitメソッドでデータの更新をする必要があります。

import pyodbc

# データベースに接続します
conn_str = (
    r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
    r'DBQ=C:\BioTech-Lab\SampleDB.accdb;'
    )
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()

# データベースから「山中一郎」のデータを削除する
sql = "DELETE FROM SampleTable WHERE 名前='山中一郎'"
cursor.execute(sql)
cursor.commit()

# データベースの接続を閉じます
cursor.close()
conn.close()

これで山中一郎さんのデータが削除されました。

スポンサーリンク

コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

日本語が含まれない投稿は無視されますのでご注意ください。（スパム対策）