[Python] MS Access .mdbファイルにアクセスする

Windows上で、Pythonからマイクロソフトのアクセスファイルを扱うために、pyodbcを使います。

pyodbc

pyodbcは、PythonからODBCを通してデータベースを操作するライブラリです。SQL構文でデータベースとやりとりができます。

pip install pyodbc でインストールできます。

$ pip install pyodbc

.mdbへの接続

以下のマニュアルの通りに進めれば接続できると思います。

Connecting to Microsoft Access

英語なので意訳しておきます。

アクセスODBCドライバ

Windows上でのアクセスODBCドライバは2種類あります。

Microsoft Access Driver (*.mdb)…古い32-bit “Jet” ODBCドライバ。Windowsと一緒にインストールされる。サポートされない。
Microsoft Access Driver (*.mdb, *.accdb) … 新しい”ACE” ODBC driver。 Windowsとは一緒にインストールされないが、MSオフィスと一緒にインストールされる。MSオフィスをインストールしていない場合は、ドライバをこちらからダウンロードできる。32ビットバージョンと64ビットバージョンがあるので、下記でドライバのインストール状況を確認してからインストールする。

アクセスODBCドライバのインストール状況の確認

Pythonのインタラクティブモードで下記を実行します。

>>> import pyodbc
>>> [x for x in pyodbc.drivers() if x.startswith('Microsoft Access Driver')]

返ってくるリストによって、どのドライバをインストールするか決めます。

空のリスト…64ビットバージョンの”ACE”ドライバをインストールする。
[‘Microsoft Access Driver (*.mdb)’]… .accdbファイルを扱う場合は、
32ビットバージョンの”ACE”ドライバをインストールする。
[‘Microsoft Access Driver (*.mdb, *.accdb)’ ]…既にドライバはインストールされているので必要ない。

pyodbcでアクセス

以下のコードで既存のデータベースに接続してみます。ファイル名は適当に変更して下さい。

import pyodbc

conn_str = (
    r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
    r'DBQ=C:\path\to\mydb.accdb;'
    )
cnxn = pyodbc.connect(conn_str)
crsr = cnxn.cursor()
for table_info in crsr.tables(tableType='TABLE'):
    print(table_info.table_name)

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

インタラクティブモードで一通りいじってみます。

以下は目を通した方が良い感じです。

Tips and Tricks by Database Platform

意訳しておきます。

ODBCリンクテーブルを含むデータベースへの接続

ODBCリンクテーブルを含むAccessデータベースに接続する場合は、Accessデータベースに接続する前にpyodbc.poolingを無効にします。

import pyodbc
pyodbc.pooling = False
cnxn = pyodbc.connect(r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ= ... ")

新しいデータベースの作成

AccessのDDLはCREATE DATABASEをサポートしていないため、空のデータベースファイルを新しく作成する場合は、無料で使えるサードパーティのmsaccessdbモジュールを使います。

Access ODBCドライバの制限

Access ODBCドライバは、 ” (multi-valued) Lookup “フィールドや ” Attachment “フィールドなどの複雑な列のタイプを完全にサポートすることはできません。データを読み取ることができる場合はありますが、操作に支障がでることがあります。

このような操作が必要な場合は、 Access DAO を IronPython や Python for .NET と組み合わせることで解決できるかもしれません。

その他、一般的なMS Access の仕様は、以下で確認できます。

Access specifications

Cursor.columnsを呼び出す際のUnicodeDecodeError

Accessのテーブル定義にオプションの “Description”列の情報が含まれていると、Cursor.columnsメソッドを使用したときに、UnicodeDecodeErrorが発生する可能性があります。

これはpyodbcではなく、Access ODBCドライバのバグだと思われます。

issue #328

データベースの作成

残念ながらpyodbcでは作成できません。

以下のモジュールを使わせていただきます。

msaccessdb

空のデータベースを作成してみます。

>>> import msaccessdb
>>> import os
>>> msaccessdb.create(os.getcwd() + '/new.accdb')

どうやらできたようです。

テーブルの作成

まずはデータベースに接続します。

>>> import pyodbc
>>> conn_str = (
...     r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
...     r'DBQ=C:\path\to\new.accdb;'
... )
>>> conn = pyodbc.connect(conn_str)
>>> cursor = conn.cursor()

テーブルを作成してみます。

>>> sql_create_table = (
...     'CREATE TABLE users (ID COUNTER PRIMARY KEY, name VARCHAR(20), country VARCHAR(20), age int, birthday datetime);'
... )
>>> cursor.execute(sql_create_table)
‹pyodbc.Cursor object at 0x0000024BB8880558›
>>> cursor.commit()

データの挿入

テーブルにデータを挿入してみます。

>>> sql_insert_user_taro = (
...     "INSERT INTO users (name, country, age, birthday)"
...     "VALUES  ('Taro', 'Japan', 20, '2000-1-1');"
... )
>>> cursor.execute(sql_insert_user_taro)
‹pyodbc.Cursor object at 0x0000024BB8880558›
>>> sql_insert_user_mike = (
...     "INSERT INTO users (name, country, age, birthday)"
...     "VALUES  ('Mike', 'USA', 19, '2000-1-5');"
... )
>>> cursor.execute(sql_insert_user_mike)
‹pyodbc.Cursor object at 0x0000024BB8880558›
# 省略
>>> cursor.commit()

挿入したデータの確認

挿入したデータを確認します。

>>> sql_select_all = "SELECT * FROM users"
>>> cursor.execute(sql_select_all)
‹pyodbc.Cursor object at 0x0000024BB8880558›
>>> for row in cursor.fetchall():
...     print(row)
...
(1, 'Taro', 'Japan', 20, datetime.datetime(2000, 1, 1, 0, 0))
(2, 'Mike', 'USA', 19, datetime.datetime(2000, 1, 5, 0, 0))
(3, 'Nancy', 'BRAZIL', 19, datetime.datetime(2000, 1, 10, 0, 0))
(4, 'KIM', 'Korea', 24, datetime.datetime(1995, 1, 15, 0, 0))
(5, 'Ping', 'China', 21, datetime.datetime(1998, 1, 20, 0, 0))

データの更新

Taroさんのデータを更新します。

>>> sql_update_taro = "UPDATE users SET age=19 where name='Taro'"
>>> cursor.execute(sql_update_taro)
‹pyodbc.Cursor object at 0x0000024BB8880558›
>>> sql_update_taro = "UPDATE users SET name='太郎' where name='Taro'"
>>> cursor.execute(sql_update_taro)
‹pyodbc.Cursor object at 0x0000024BB8880558›
>>> cursor.commit()

更新したデータの確認

太郎さんのデータがちゃんと更新されているか確認します。

>>> sql_select_taro = ("select * from users where name='太郎'")
>>> cursor.execute(sql_select_taro).fetchone()
(1, '太郎', 'Japan', 19, datetime.datetime(2000, 1, 1, 0, 0))

データの削除

Mikeさんのデータを削除して、その後削除されているか確認します。

>>> cursor.execute("DELETE FROM users where name='Mike'")
‹pyodbc.Cursor object at 0x0000024BB8880558›
>>> cursor.commit()
>>> cursor.execute("SELECT * FROM users")
‹pyodbc.Cursor object at 0x0000024BB8880558›
>>> for row in cursor.fetchall():
...     print(row)
... 
(1, '太郎', 'Japan', 19, datetime.datetime(2000, 1, 1, 0, 0))
(3, 'Nancy', 'BRAZIL', 19, datetime.datetime(2000, 1, 10, 0, 0))
(4, 'KIM', 'Korea', 24, datetime.datetime(1995, 1, 15, 0, 0))
(5, 'Ping', 'China', 21, datetime.datetime(1998, 1, 20, 0, 0))

集計関数

集計関数を使って平均年齢を計算してみます。

>>> cursor.execute("SELECT AVG(age) FROM USERS").fetchval()
20.75

接続の終了

カーソルを閉じて、データベースへの接続を終了します。

>>> cursor.close()
>>> conn.close()