DataFrameからAccessデータベースに書き込む

DataFrameのto_sql()メソッドを使ってAccessのデータベースにデータを保存する方法を解説します。

カピバラ

Access+Pythonの開発でこれができるようになれば、プログラムもユニットテストもスマートに。

結論は、DataFrame.to_sql()メソッドを使う

初めに、このページで説明する内容のゴールを示しておきます。

DataFrameの内容をAccessのデータベースに保存するには、DataFrame.to_sql()メソッドを使えばいいので、プログラムはこのような感じになります。

engine = "なんらかの方法で作成"

data = pd.DataFrame({
    'ID': np.arange(10),
    'Name': [f"名前{i}" for i in range(10)]})

data.to_sql('MyTable4', engine, if_exists='append', index=False)

ここでネックになるのが、to_sql()メソッドのcon引数（第２引数）です。

ここには、SQLAlchemy.engine.Engineか、sqlite3.Connectionのどちらかのみ指定することができます。

今回はMicrosoft Accessに接続したいので、前者のSQLAlchemy.engine.Engineを指定するしかありません。

SQLAlchemyというのは、Pythonでデータベースを抽象的に取り扱ったりするライブラリなのですが、今回はこのライブラリの基本的な接続インスタンスさえ作成してやれば、あとはDataFrame.to_sql()メソッドがデータベースに書き込む部分はやってくれます。

ですから、課題はSQLAlchemy.engine.Engineをどうやって作るかといった部分になります。

概念的なものも踏まえて、段階的に説明していきます。

環境準備

pyodbcをインストール

Pythonプログラムからデータベースへの接続は、pyodbcモジュールを介しての接続になります。
というわけでpyodbcをインストールしましょう。

> pip install pyodbc

インストールしたら、pyodbcが今の環境で使えるドライバ一覧を確認しましょう。

カピバラ

使えるドライバー＝接続できるデータベース

といったイメージ

import pyodbc

for driver in sorted(pyodbc.drivers()):
    print(driver)

データベースのドライバを何も入れていないなら、このプログラムの実行結果はおそらく次のような感じになると思います。

SQL Server

このように1行しか表示されないことでしょう。

つまり、現状でpyodbcで接続できるのはSQL Serverだけ、ということになります。

今回はMicrosoft Accessに接続したいわけですから、この実行結果にAccessのドライバが含まれてくれるようにならないと、pyodbcをインストールしても接続ができないわけです。

ODBCドライバをインストール

pyodbcがデータベースに接続するために、Microsoft Access用のデータベースドライバをインストールしましょう。
>> Microsoft Access データベースエンジン 2010 再頒布可能コンポーネント

上記リンクから飛んで、「ダウンロード」をクリック

次に、インストールされているMicrosoft Officeに合ったプロセッサ（x86かx64）を選択します。

自分のOfficeが32bitなのか64bitなのかを確認するには、Accessの「ファイルリボン」→「アカウント」→「Access のバージョン情報」と選択すると表示されるダイアログの上のほうに「Microsoft Access for Microsoft 365 MSO 64ビット」などと出てきて確認することができます。

ケンヂまるの環境は64ビット（x64）のOfficeがインストールされていたので、X64版をダウンロードしました。

ファイルをダウンロードしたら、インストールします。
インストールは指示に従うだけの簡単なお仕事。

カピバラ

ここでインストールができない場合は、Microsoft Officeのバージョン（x86・x64）と矛盾している可能性が高い

インストールが終わったら、pyodbcで使えるドライバをもう一度確認してみましょう。

プログラムは先ほどと全く同じです。

import pyodbc

for driver in sorted(pyodbc.drivers()):
    print(driver)

実行してみると、出力結果は先ほどよりも増えているはずです。

Microsoft Access Driver (*.mdb, *.accdb)
Microsoft Access Text Driver (*.txt, *.csv)
Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx)
Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)
SQL Server

「Microsoft Access Driver (*.mdb, *.accdb)」というのがお目当てのドライバです。これで、pyodbcからMicrosoft Accessに接続することができるようになりました。

試しに、Accessデータベースに接続してみましょう。

Pythonプログラムと同じフォルダに、sample_data.accdbを作り、テーブル「MyTable」を作って適当に２フィールド入れておきましょう。
（下図のような感じ）

続いて、[Pythonプログラム→pyodbc→Accessドライバ→Accessデータベース]の順にアクセスするプログラムを書いてみます。

（最終的な目的はDataFrame.to_sql()経由でアクセスすることなので、細かいAPIとかは気にしないでいきましょう。ケンヂまるもほぼほぼ他サイトからパクってきましたｗ）

import pyodbc

db_path = "./sample_data.accdb"

s = "DRIVER=" + \
    "{Microsoft Access Driver (*.mdb, *.accdb)};" + \
    f"DBQ={db_path};"
con = pyodbc.connect(s)

response = con.cursor().execute('select * from MyTable')

for res in response:
    print(f"{res[0]} {res[1]}")

このプログラムを実行すると、

0 名前0
1 名前1
2 名前2
3 名前3
4 名前4

このようにテーブルの情報を取り出してこれます。

このプログラムで、１点だけ気にしてほしい部分があります。
接続する時に渡している文字列です。

この接続文字列は、SQLAlchemy.engine.Engineを作成する時にも必要になってくる重要な部分です。

s = "DRIVER=" + \
    "{Microsoft Access Driver (*.mdb, *.accdb)};" + \
    f"DBQ={db_path};"

この部分ですね。

VBAシステム開発 5.1.3.2.1 ODBC接続文字列の作り方を読むと、よく分からなくても今後もテキトーに接続文字列を作れるようになります。

カピバラ

実はケンヂまるもよくわかっていないから毎回この方法で生成させている。

sqlalchemy-accessのインストール

ここまできたら、今回の課題であるSQLAlchemy.engine.Engineを作るところまであと一歩です。

sqlalchemy-accessモジュールをインストールします。

> pip install sqlalchemy-access

ちなみにsqlalchemy-accessをインストールすると、依存ライブラリであるsqlalchemyとpyodbcもインストールされます。（pyodbcは話の都合上、このページの最初のほうでインストールしてもらいましたけど。）

DataFrameからAccessデータベースに書き込んでみる

インストールができたら、あとはDataFrameからMicrosoft Accessに書き込むプログラムを作っておしまいです。

from sqlalchemy import create_engine
from urllib.parse import quote_plus
import pandas as pd
import numpy as np


# sqlalchemyのengineを作成
def alchemy_engine(db_path):
    con_str = "DRIVER=" + \
              "{Microsoft Access Driver (*.mdb, *.accdb)};" + \
               f"DBQ={db_path};"
    con_str = quote_plus(con_str)
    engine = create_engine(
        f"access+pyodbc:///?odbc_connect={con_str}",
        echo=True)

    return engine


engine = alchemy_engine("./sample_data.accdb")

# 書き込むデータを作成
data = pd.DataFrame({
    'ID': np.arange(10),
    'Name': [f"名前{i}" for i in range(10)]})


# MyTableテーブルに書き出す
data.to_sql('MyTable4', engine, if_exists='append', index=False)

このプログラムを実行して、sample_data.accdbを開けば、MyTableが作成されて中にはデータが入っています。