PythonでMS Accessのデータベースを処理するには【Python】

ここではPythonを使用してMicrosoft Accessのデータベースを処理するために必要な前準備を説明します。 さらに、Accessのデータベースに接続してテーブルからレコードを抽出したり、レコードを追加、更新、削除する方法についても解説します。 PythonでAccessのデータベースを処理するには、PythonのODBCモジュール「Pyodbc」をインストールする必要があります。 Pyodbcモジュールは、Pythonのパッケージ管理システム「pip」を使用してインストールします。 さらに、「Microsoft Access データベース エンジン 20XX 再頒布可能コンポーネント」もインストールする必要があります。

Python、Pyodbc、Accessデータベースエンジンはそれぞれ32-Bit版と64-Bit版があります。 32-Bit版と64-Bit版を混在させるとエラーになりますので、32-Bit版か64-Bit版のどちらかに統一することをお勧めします。 ここでは、Python、Pyodbc、Accessのデータベースエンジンを32-Bit版に統一しています。 ちなみに、Pythonは32-Bit版と64-Bit版を共存させることもできます。

Windows上で32-Bit版と64-Bit版のPythonを共存させる方法については、 「記事(Article093)」と「記事(Article094)」で解説しています。 また、64-Bit版のPythonとPyodbcを使用する方法については 「記事(Article097)」で解説しています。

これからデータベースを学習しようとするときは、 MS-AccessではなくPythonに標準で組み込まれているSQLite3を使用して学習することをお勧めします。 MS-Accessのように32-Bitと64-Bitの混在によるトラブルも回避できます。 さらに、Pandasのto_sql()メソッドでDataFrameをAccessに搬出するときは、 「 from sqlalchemy import create_engine」のライブラリが必要ですがSQLite3は不要です。

SQLite3ではデータベースを実際のファイルに保存する方法とメモリ上にデータベースを作成する方法をサポートしています。 なのでデータベースのSQLコマンドを学習するときはとても便利です。 また、Visual Studio CodeのExtensions(拡張機能)をインストールするとMS-Accessのようにデータベースを可視化することも可能です。 SQLite3については「記事(Article039)で詳しく解説しています。

説明文の左側に図の画像が表示されていますが縮小されています。 画像を拡大するにはマウスを画像上に移動してクリックします。 画像が拡大表示されます。拡大された画像を閉じるには右上の[X]をクリックします。 画像の任意の場所をクリックして閉じることもできます。

• Windowsに32-bit版のPyodbcをインストールするには
• Windowsに64-bit版のPyodbcをインストールするには
• PythonでAccessのデータベースを処理するには
• AccessのデータベースをPandasに取り込むには
• AccessのデータベースをPandasに取り込んで棒グラフを作成するには
• 複数のExcelファイルをPandasに取り込んでAccessのデータベースに一元化するには
• Accessのデータベースをクロス集計してPandasに取り込んで線グラフを作成するには

Pyodbcのインストール(Windows 10, 11)

1. Windowsのエクスプローラーを起動する

   C:\Users\xxx\AppData\Local\Programs\Python\Python39-64\Scripts
   C:\Users\xxx\AppData\Local\Programs\Python\Python39-32\Scripts
   

   

   Pythonをまだインストールしていないときは、 「記事(Article001)」をクリックしてPythonを事前にインストールしておいてください。 Windowsのエクスプローラーを起動したら、Pythonがインストールされているフォルダーを探します。 通常32-Bit版のPythonは「C:\Users\...\Python39-32\Scripts」のフォルダーにインストールされます。 フォルダーが見つかったらパス名をメモ帳などにコピーしておきます。 パス名が見つからないときは、エクスプローラーの「表示」をクリックして「隠しファイル」がチェックされているか確認してください。 チェックが外れているときはクリックしてチェックしてください。これでパス名が見つかるはずです。
   

2. Windowsのコマンドプロンプトを開く

   

   Windowsの「スタート」ボタンをクリックしたら「Windows システム ツール」をクリックして展開します。 そして、「コマンドプロンプト」をクリックします。これでコマンドプロンプトのウィンドウが開きます。
   

3. Pyodbcをインストールする

   >cd C:\Users\XPS8910\AppData\Local\Programs\Python\Python39-32\Scripts
   pip install pyodbc
   

   

   コマンドプロンプトに「cd C:\Users\...\Python39-32\Scripts」をコピペ(Copy & Paste)してリターンを押します。 次に「pip install pyodbc」入力してPyodbcモジュールをインストールします。 インストールが完了すると「Successfully installed pyodbc...」が表示されます。
   

   

   「error: Microsoft Visual C++ 14.0 or greater is required.」のエラーが発生したときは、 「ここ」から「Build Tools for Visual Studio 20XX」 をダウンロードしてインストールしてください。 「Error: (‘IM002’, ‘[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)’)」 等のエラーが発生したときは、Python、Pyodbc、Accessのデータベースエンジンのバージョン(32-Bit, 64-Bit)が不一致の可能性がありますのでどちらかに統一してください。
   

MS Accessのデータベースを処理する

1. PyodbcでAccess DBに接続する

   Visual Studio Code(VS Code)を起動したら以下のコードを入力して実行します。 VS Codeをまだインストールしていないときは 「記事(Article001)」を参照して事前にインストールしておいてください。 行1のimportでは「pyodbc」モジュールをインポートしています。 行3-5のforループでは、PyodbcがサポートしているODBCドライバーから「Microsoft Access Driver」に関連するドライバーのみ抽出して表示しています。
   
   実行結果(図5参照)に「Microsoft Access Driver (*.mdb)」と「Microsoft Access Driver (*.mdb, *.accdb)」のドライバーが表示されています。 行6-9では、Accessの「Stats.accdb」データベースに接続するために「接続文字列」を定義しています。 接続文字列にはバックスラッシュ「\」などの特殊文字が含まれるのでrow文字列「r」として定義しています。
   
   行10ではPyodbcのconnect()メソッドでAccessの「Stats.accdb」データベースを接続(開く)しています。 行12-13のforループではAccessの「Stats.accdb」データベースに登録されているテーブルを表示しています。
   
   実行結果(図5)に「Category」と「Stats」テーブルが表示されています。 行14-15ではcursorを閉じてAccessデータベースを切断(閉じる)しています。 ここで使用するAccessの「Stats.accdb」データベースとCategoryテーブル、Statsテーブルの作成手順は、 「記事(Article001)」を参照してください。

   import pyodbc as pyo
   #print(pyo.drivers())
   for driver in pyo.drivers():
       if driver.startswith('Microsoft Access Driver'):
           print(driver)
   con_str = (
   	r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};'
   	r'DBQ=C:\xps8700\asp4x_Data\Stats.accdb;'
   	)
   con = pyo.connect(con_str)
   cursor = con.cursor()
   for table in cursor.tables(tableType='TABLE'):
       print(table.table_name)
   cursor.close()
   con.close()

   

   図5は実行結果です。行5の「print(driver)」で表示したMicrosoft Access Driverと行13の「print(table.table_name)」で表示したテーブル名が表示されています。
   

2. Accessのテーブルからレコードを抽出する

   行8では「Stats.accdb」データベースのCategoryテーブルから全てのレコードを抽出するSQLコマンド(SELECT)を記述しています。 行9ではcursorのfetchall()メソッドを実行してCategoryテーブルのすべてのレコードを抽出して変数「rows」に格納しています。 変数rowsには、Categoryテーブルのレコードが「list型」で格納されます。
   
   行12-13では、Categoryテーブルのレコード件数を取得して表示しています。 行14-15のforループでは、rowsリストからレコードを１件ずつ取得して変数rowに格納します。 そして変数rowに格納されたレコードから「ID」、「CategoryName」、「DateAdded」のフィールドを表示(図6)しています。
   
   「DateAdded」にはレコードを追加した日時が「yyyy/mm/dd hh:mm:ss」の形式で格納されています。 ここでは時刻を無視して日付だけ表示するようにフォーマットしています。 行16-17ではカーソルとデータベースを閉じています。

   import pyodbc as pyo
   con_str = (
   	r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};'
   	r'DBQ=C:\xps8700\asp4x_Data\Stats.accdb;'
   	)
   con = pyo.connect(con_str)
   cursor = con.cursor()
   sql = 'SELECT * FROM Category'
   rows = cursor.execute(sql).fetchall()
   print(type(rows))
   #print(rows)
   rec_count = len(rows)
   print(f'Record Count={rec_count}')
   for row in rows:
   	print(f'ID={row.ID}, Category Name={row.CategoryName}, Date Added={row.DateAdded.strftime("%Y/%m/%d")}')
   cursor.close()
   con.close()

   

   図6は実行結果です。行13の「print()」で表示したレコード件数と行15の「print()」で表示したレコードが出力されています。
   

   

   図7はAccessを起動してCategoryテーブルの全レコードを表示した画面です。
   

3. Accessのテーブルのレコード件数を取得する

   行8はCategoryテーブルのレコード件数を取得するSELECTコマンドを記述しています。 レコード件数を取得するにはAccessのCount()関数を使用します。「AS rec_count」ではフィールド名を定義しています。 行9ではcursorのfetchone()メソッドを実行してCategoryテーブルから1件だけレコードを抽出して変数rowに格納しています。 行10では変数rowに格納されているレコード件数「rec_count」を表示(図8)しています。

   import pyodbc as pyo
   con_str = (
   	r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};'
   	r'DBQ=C:\xps8700\asp4x_Data\Stats.accdb;'
   	)
   con = pyo.connect(con_str)
   cursor = con.cursor()
   sql = 'SELECT Count(*) AS rec_count FROM Category'
   row = cursor.execute(sql).fetchone()
   print(f'Record Count={row.rec_count}')
   cursor.close()
   con.close()

   

   図8は実行結果です。行10の「print()」で表示したレコード件数が出力されています。
   

4. Accessのテーブルから特定のレコードを抽出する

   行8ではCategoryテーブルから特定のレコード(ID=1)を抽出するSELECTコマンドを記述しています。 Categoryテーブルからレコードを絞り込むにはSELECTコマンドにWHERE句を追加します。 「ID=1」のレコードを抽出するには「WHERE ID=1」のように記述します。
   
   ここでは位置パラメータ「?」を使用して「WHERE ID=?」のように記述しています。 行10の「execute(sql, category_id)」では、SELECTコマンドのWHERE句の位置パラメータ「?」に値「1」を代入しています。 そしてcursorのfetchone()メソッドを実行してCategoryテーブルから１レコード取得して変数rowに格納します。 行11では変数rowに格納されているレコードを表示(図9)しています。

   import pyodbc as pyo
   con_str = (
   	r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};'
   	r'DBQ=C:\xps8700\asp4x_Data\Stats.accdb;'
   	)
   con = pyo.connect(con_str)
   cursor = con.cursor()
   sql = 'SELECT * FROM Category WHERE ID=?'
   category_id = 1
   row = cursor.execute(sql, category_id).fetchone()
   print(f'ID={row.ID}, Category Name={row.CategoryName}, Date Added={row.DateAdded.strftime("%Y/%m/%d")}')
   
   cursor.close()
   con.close()

   

   図9は実行結果です。行11の「print()」で表示したレコードが出力されています。
   

5. Accessのテーブルにレコードを追加する

   行8ではCategoryテーブルに新規レコードを追加するINSERTコマンドを記述しています。 INSERTコマンドのINTO句ではテーブル名とフィールドを指定します。 VALUES句ではフィールドの値を指定します。ここではSQLの位置パラメータ「?」を使用しています。
   
   行9ではVALUES句に指定した位置パラメータに代入する値を定義しています。 変数param_valuesには「tuple型」で値を格納します。 行11ではcursorのexecute()メッソを実行してINSERTコマンドを実行しています。 さらに、rowcountプロパティから追加したレコード件数を取得して変数countに格納しています。
   
   行12ではcursorのcommit()メソッドを実行してメモリの内容をデータベースに反映しています。 レコードを追加、更新、削除するときは、必ず「commit()」を実行してメモリの内容を強制的にデータベースに反映させる必要があります。 行13ではexecute()メソッドで追加したレコードの件数を表示しています。 ここでは「Rows affected=1」が表示(図10)されていますのでレコードが１件追加されたことになります。

   import pyodbc as pyo
   con_str = (
   	r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};'
   	r'DBQ=C:\xps8700\asp4x_Data\Stats.accdb;'
   	)
   con = pyo.connect(con_str)
   cursor = con.cursor()
   sql = 'INSERT INTO Category(CategoryFullName, CategoryName, Notes) VALUES(?, ?, ?)'
   param_values = ('Pyodbc 32-bit', 'Pyodbc', 'Added by Pyodbc')
   print(type(param_values))
   count = cursor.execute(sql, param_values).rowcount
   cursor.commit()
   print(f'Rows affected={count}')
   
   cursor.close()
   con.close()

   

   図10は実行結果です。行13の「print()」で表示したレコードの追加件数が出力されています。
   

   

   Categoryテーブルに１件レコードを追加したあとに、Accessを起動してCategoryテーブルを表示(図11)すると新規レコード(ID=14)が登録されているのが確認できました。
   

6. Accessのテーブルのレコードを更新する

   行8はCategoryテーブルのレコードを更新するUPDATEコマンドを記述しています。 UPDATEコマンドのSET句では更新するフィールド名と値を記述します。 ここでは「CategoryName」フィールドを更新します。フィールドの値は位置パラメータ「?」で指定しています。 どのレコードを更新するかはWHERE句で指定します。ここでは「WHERE ID=?」のようにIDを指定して特定のレコードを更新します。
   
   行9ではUPDATEコマンドの位置パラメータ「?」に代入する値を定義しています。 変数param_valuesは「tuple型」で定義する必要があります。 行11ではcursorのexecute()コマンドを実行してレコードを更新します。さらにrowcountプロパティに格納されている更新されたレコード件数を取得して変数countに格納します。 行13では更新されたレコード件数を表示しています。実行結果(図12)に「Rows affected=1」が表示されていますので１件更新されたことになります。

   import pyodbc as pyo
   con_str = (
   	r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};'
   	r'DBQ=C:\xps8700\asp4x_Data\Stats.accdb;'
   	)
   con = pyo.connect(con_str)
   cursor = con.cursor()
   sql = 'UPDATE Category SET CategoryName=? WHERE ID=?'
   param_values = ('Python 3.9.3', 14)
   print(type(param_values))
   count = cursor.execute(sql, param_values).rowcount
   cursor.commit()
   print(f'Rows affected={count}')
   
   cursor.close()
   con.close()

   

   図12は実行結果です。行13の「print()」で表示した更新レコード件数が出力されています。
   

7. Accessのテーブルからレコードを削除する

   行8ではCategoryテーブルからレコードを削除するDELETEコマンドを記述しています。 どのレコードを削除するかはWHERE句を追加して指定します。 ここでは「WHERE ID=?」のように位置パラメータ「?」を使用しています。
   
   行9では位置パラメータに代入する値を定義しています。 位置パラメータが１個のときは「tuple型」ではなく通常の「str型」または「int型」で定義します。 行11ではcursorのexecute()メソッドを実行してレコードを削除しています。さらにrowcountプロパティから削除されたレコード件数を取得して変数countに保存しています。 行13では削除されたレコード件数を表示しています。実行結果(図13)に「Rows affected=1」が表示されているので１件削除されたことになります。

   import pyodbc as pyo
   con_str = (
   	r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};'
   	r'DBQ=C:\xps8700\asp4x_Data\Stats.accdb;'
   	)
   con = pyo.connect(con_str)
   cursor = con.cursor()
   sql = 'DELETE FROM Category WHERE ID=?'
   param_value = 14
   print(type(param_value))
   count = cursor.execute(sql, param_value).rowcount
   cursor.commit()
   print(f'Rows affected={count}')
   
   cursor.close()
   con.close()

   

   図13は実行結果です。行13の「print()」で表示したレコード削除件数が出力されています。
   

   

   Accessを起動してCategoryテーブルを表示(図14)すると「ID=14」のレコードが削除されていることが確認できました。