Python3を使ってMS Access(*.accdb)へ接続してみた

最近、DjangoのデータベースバックエンドにAzure SQL Databaseが使えるライブラリの存在を知りました。
michiya/django-pyodbc-azure

　
そこから、MS AccessをDjangoのデータベースバックエンドとして使えるのかが気になりました。

ただ、そもそもPython3を使ってMS Accessへ接続できるのかどうか分からなかったため、Python3で動作するライブラリを調べてみました。

環境

• Windows10
• Python 3.4.3 32bit版

用意したAccessデータベース

以下のテーブル内容を持つsample.accdbを用意しました。

itemテーブル

item_id	item_name
1	ringo
2	みかん

DB-APIについて

ライブラリを調べている中でDB-APIという単語をよく見かけました。それについては以下が参考になりました。

• PEP 0249 -- Python Database API Specification v2.0 | Python.org
• Python の DB-API (Database API) とは何か - CUBE SUGAR CONTAINER

ライブラリの調査

stackoverflowにライブラリがまとめられていました。

• MS Access library for python - Stack Overflow
• linux - What do I need to read Microsoft Access databases using Python? - Stack Overflow

それらのライブラリのPython3対応を調べてみたところ、ODBCとOLEDBのどちらの方法でも対応してそうでした*1。

ODBC

2つのライブラリがありました。

• pyodbc (3.0.10)
• pypyodbc (1.3.3)

また、ORMのSQLAlchemy + pyodbcでも動作しそうだったため、以下のバージョンで試すことにしました。

• SQLAlchemy (1.0.9) + pyodbc (3.0.10)

OLEDB

Python3で動作しそうだったのは、以下の組み合わせでした。

• pywin32 (219) + adobdapi (2.6.0.7)

　
なお、pywin32単独でも接続できそうでしたが、サンプル的なソースコードを見たらあまりにもツラそうだったため、試すのは諦めました。

• CX's Hello, World! » Hello, ADO(Python) World!

pyodbcによる接続

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

(env) >pip install pyodbc
...
Successfully installed pyodbc-3.0.10

　
以下のコードで接続できました。

pyodbc_runner.py

import pyodbc
import config # この中で、accdbファイルのフルパスを指定

def main():
    # formatで`{`を使うため、`{`を重ねることでエスケープ
    con_str = 'Driver={{Microsoft Access Driver (*.mdb, *.accdb)}};Dbq={0};'.format(config.PATH_ACCDB)
    conn = pyodbc.connect(con_str)
    cur = conn.cursor()
    cur.execute("select item_name from item")
    
    for c in cur.fetchall():
        print(c[0]) #=> `ringo`, `みかん
        
    cur.close()
    conn.close()
    
if __name__ == '__main__':
    main()

　
なお、format()では接続文字列中の{や}をエスケープする必要があるため、{{や}}としています。

• 6.1. string — Common string operations — Python 3.4.4rc1 documentation
• How can I print a literal "{}" characters in python string and also use .format on it? - Stack Overflow

pypyodbcによる接続

pypyodbcもpipでインストールできます。

(env) >pip install pypyodbc
...
Successfully installed pypyodbc-1.3.3

　
pypyodbcでは以下の2つのメソッドが用意されていました。

pypyodbc.connect()

以下のリンクの通り、pyodbcとほぼ同じようにして接続できました。
python_ms_access_sample/pypyodbc_runner.py at master · thinkAmi-sandbox/python_ms_access_sample

pypyodbc.win_connect_mdb()

accdbと同じ構造を持つmdbファイルを用意して試したところ、問題なく接続できました。
PyPyODBC functions for generating MDB file - Google Project Hosting

pypyodbc_mdb_runner.py

import pypyodbc
import config

def main():
    conn = pypyodbc.win_connect_mdb(config.PATH_MDB)
    cur = conn.cursor()

    # 以下、pypyodbc_runner.pyと同じなので省略

　
なお、win_connect_mdb()の引数にaccdbファイルを指定した場合、

pypyodbc.Error: ('HY000', "[HY000] [Microsoft][ODBC Microsoft Access Driver] データベース '(不明)' を開くことができません。アプリケーションで認識できないデータベースであるか、またはファイルが破損しています。 ")

というエラーで動作しませんでした。

pywin32 + adobdapi による接続

以下のページより、Python3 32bit版向けの最新バージョンpywin32-219.win32-py3.4.exeをダウンロードし、exe実行によるインストールを行います。
Python for Windows Extensions - Browse /pywin32 at SourceForge.net

　
virtualenvにシステムのpywin32を引き継ぐため、--system-site-packagesを指定して実行します。

>virtualenv --system-site-packages env

　
最後にadobdapiをpipでインストールします。

(env) >pip install adodbapi
...
Successfully installed adodbapi-2.6.0.7

　
接続については、以下のリンクの通りpyodbcとほぼ同じような実装で可能でした。
python_ms_access_sample/oledb_runner.py at master · thinkAmi-sandbox/python_ms_access_sample

SQLAlchemy + pyodbcによる接続

MS Accessを扱えそうなPythonのORMを探したところ、SQLAlchemyがありました。

ただ、SQLAlchemy本体ではMS Accessへの接続をサポートしていないため*2、接続用のdialectが別途必要でした。

公式ページではsqlalchemy-accessが紹介されていたため、今回はそれを利用します。
Dialects — SQLAlchemy 1.1 Documentation

　
sqlalchemyはPyPIより、sqlalchemy-accessはBitbucketより、それぞれpipでインストールします。

# 本体
(env) >pip install sqlalchemy

# MS Access用dialect
(env) >pip install https://bitbucket.org/zzzeek/sqlalchemy-access/get/default.zip

　
あとは以下を参考にして実装したところ、接続できました。

• Django使いのためのSQLAlchemyチュートリアル - SELECT * FROM life;
• SQLALchemyと僕
• zzzeek / sqlalchemy-access / 課題 / #4 - simple example as doc — Bitbucket
  • 接続文字列の表記方法が参考になった

sqlalchemy_runner.py

import sqlalchemy
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
import config

Base = declarative_base()
class Item(Base):
    # テーブル名の指定が必要
    __tablename__ = 'item'
    
    item_id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
    item_name = sqlalchemy.Column(sqlalchemy.String)
    
    # print()された時に表示する内容
    def __str__(self):
        return self.item_name

def main():
    # `access`dialectを使うため、sqlalchemy-accessパッケージが必要
    # formatで`{`を使うため、`{`を重ねることでエスケープ
    url = "access+pyodbc:///?Driver={{Microsoft Access Driver (*.mdb, *.accdb)}};Dbq={0};".format(config.PATH_ACCDB)
    
    engine = sqlalchemy.create_engine(url)
    Session = scoped_session(sessionmaker(autocommit=False,
                                        autoflush=False,
                                        bind=engine))
    session = Session()
    for item in session.query(Item):
        print(item) 

if __name__ == '__main__':
    main()

ソースコード

ここまでの内容のソースコードは、GitHubに上げておきました。
thinkAmi-sandbox/python_ms_access_sample

試してみたけれどできなかったこと

試してみたけれどできなかったことも残しておきます。

SQLAlchemy + sqlacodegenによる、MS Accessのモデル生成

Djangoでは

(env) > python manage.py inspectdb > models.py

とすることで、既存のデータベースからDjangoのModelを作ることができます。

SQLAlchemyで同じようなことができないかを調べたところ、

• agronholm / sqlacodegen — Bitbucket
• ksindi/sqlautocode

などのライブラリを使えば良さそうでした。

ただ、sqlautocodeはREADMEでsqlacodegenへ切り替えるように書かれていたため、sqlacodegenを試してみました。
karky7のブログ: Pythonのsqlacodegenが便利です

　
pipでインストールしてから実行してみたところ、エラーとなりました。

(env) > sqlacodegen "access+pyodbc:///?Driver={Microsoft Access Driver (*.mdb, *.accdb)}%3bDbq=path\to\sample.accdb%3b"
...
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Microsoft Access Driver] 'msysobjects' \ufffd\u0313\u01c2\u074e\ufffd\u8320\ufffd\ufffd\ufffd\ufffd\ufffd\u0202\ufffd\ufffd\u0302\u0141A\ufffd\ufffd\ufffdR\ufffd[\ufffdh\ufffd\ufffd\u01c2\u074e\ufffd邱\ufffd\u0182\ufffd\ufffd\u0142\ufffd\ufffd\u0702\ufffd\ufffd\ufffdB (-1907) (SQLExecDirectW)") [SQL: "select name from msysobjects where type=1 and name not like 'MSys%'"]

システムテーブルへのアクセス権限がなさそうな感じでしたので、権限を追加してみます。

　
まずはMS Accessのシステムテーブルを表示します。
Access で「隠しオブジェクト」「システムオブジェクト」を表示したい

• ナビゲーションウィンドウの上で右クリック、ナビゲーションオプションを選択
• 表示オプションで、システムオブジェクトの表示にチェックを入れる

　
続いて、msysobjectsテーブルへの権限を追加します。
ms access - Record cannot be read; no read permission on 'MSysObjects' - Stack Overflow

• Microsoft Visual Basic for Applications を開く
• イミディエイトウィンドウで、ログインしているユーザを確認
  • ? CurrentUserと入力し、Adminと返ってくればOK
• イミディエイトウィンドウで、MSysRelationshipsテーブルに対する権限を追加
  • CurrentProject.Connection.Execute “GRANT SELECT ON MSysRelationships TO Admin”
    • コマンド実行が成功しても、成功のメッセージは表示されません

　
再度実行してみたところ、NotImplementedErrorが出たため、生成を諦めました。

(env) >sqlacodegen "access+pyodbc:///?odbc_connect=Driver={Microsoft Access Driver (*.mdb, *.accdb)}%3bDbq=D:\dev\sandbox\sqlalchemy_access\sample.accdb%3b"
...
  File "d:\dev\sandbox\sqlalchemy_access\env\lib\site-packages\sqlalchemy\engine\interfaces.py", line 340, in get_view_names
    raise NotImplementedError()
NotImplementedError

DjangoのデータベースバックエンドにMS Accessを使う

ある意味本題みたいなものです。

DjangoでMS Accessをデータベースバックエンドとして扱えるライブラリがないか探してみたところ、django-pyodbc-accessがありました。
EBNull/django-pyodbc-access

ただ、Python3に対応してなさそうだったため、fork先のブランチpy3を使ってみました。
18F/django-pyodbc-access at py3

　
まずはGitHubからpy3ブランチをpip installします。
参考：python - pip install from github repo branch - Stack Overflow

# django-pyodbc-access
(env) >pip install git+https://github.com/18F/django-pyodbc-access.git@py3

　
Django1.9でinspectdbを試してみたところ、以下のエラーが出ました。

  File "D:\dev\sandbox\django_msaccess\env\lib\site-packages\django\db\backends\base\introspection.py", line 54, in <genexpr>
    if include_views or ti.type == 't')
AttributeError: 'str' object has no attribute 'type'

　
関係するdjango.db.backendsの構造が1.7と1.8の間で異なっているのが原因かもしれないと考え、サポート外のバージョンですが、1.7を使うことにします。

• django.db.backendsの構造
  • 1.7系 django/django/db/backends at 1.7.11 · django/django
  • 1.8系 django/django/db/backends at 1.8.7 · django/django

　
あとはinspectdbでsixが使われるみたいなので、以下のような感じでインストールします。

(env) >pip install django==1.7.11 pyodbc six

　
インストール後に試してみたところ、一部機能のみ動作する結果になりました。

• inspectdbは、外部キーの設定も含めて動作
  • python manage.py inspectdb > path\to\<file_name>.py でファイルにModelができた
  • ただし、システムテーブルへのアクセス権追加が必要
• fixtureやrunserverなどはエラーで動作せず
  • NotImplementedError: subclasses of BaseDatabaseWrapper may require a get_connection_params() method
  • AttributeError: 'DatabaseWrapper' object has no attribute 'Database'

　
そのため、MS AccessをDjangoのデータベースバックエンドとして使うのは厳しそうだと感じ、諦めることにしました。

*1:接続文字列はAccess connection strings - ConnectionStrings.comに記載がありました

*2:過去の0.7版ではサポートしているような表記もありました： Engine Configuration — SQLAlchemy 0.7 Documentation