Pandasを使ったデータベースとの接続

Pandasを使ったデータベースとの接続

このページでは python でDBを扱う方法を紹介します。
今回はsqlAlchemyを使ってpandasのdataframeにDBの値を格納する方法を紹介します。

使用するライブラリ

pandas

python上でExcelのような表形式のデータを簡単に高速で扱うことが可能なライブラリです。

DBから吸い出したデータを加工するのに便利です。

公式ドキュメント：http://pandas.pydata.org/pandas-docs/stable/

sqlAlchemy

pandasでは直接DBと接続して読み込むことができる関数に

pandas.read_sql()

がありますが、編集時点(2023年10月)時点では警告文が出るようになっています(UserWarning)。

内容を読むと「pandas.read_sql()によるsql接続は今後非推奨になります。以降はsqlAlchemyを使って接続しろ」と書かれていたので、sqlAlchemyを使って接続するようにします。

公式ドキュメント:https://www.sqlalchemy.org

1.pandasのインストール

pip install pandas

2.sqlAlchemyのインストール

pip install sqlalchemy

3.sqlAlchemyを使ってDBと接続する

Accessとの接続

pip install sqlalchemy-access

Accessを使う場合↑をインストールしておかないと下記のエラーが出ます。

🚫 sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:access.pyodbc

import pandas as pd
from sqlalchemy import create_engine

#Access設定
Access_path = r"C:XXXX" #接続するAccessのPATH

#接続に使用するドライバーの設定 ※頻発エラー
driver = r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};' 
dbq = "DBQ={};".format(Access_path)
conn_str = (
        driver+dbq
)

engine_Access = create_engine("access+pyodbc:///?odbc_connect=" + conn_str)
sql = "SELECT * FROM table名"

df = pd.read_sql(sql,engine_Access) #dataframe dfにtableデータを格納

※１：Accessに接続するためのドライバーが必要になりますが、専用のページからダウンロードする必要があります。（Accessファイルが入っていても未インストールの場合があります！）

※１現在入っているドライバを確認するには

pip install pyodbc

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

Microsoft Access Driver (*.mdb, *.accdb)が入っていない場合は下記リンクより取得する必要があります。

Microsoft Access データベースエンジン2016 最頻布可能コンポーネント

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

インストールが終わったら、pyodbcで使えるドライバを使ってもう一度確認。おそらく下記が含まれているはず。

`Microsoft Access Driver (*.mdb, *.accdb)`←これが必要！
`SQL Server`

このようになっていれば、ドライバが入っているので準備OK

（インストールできない場合はOfficeのバージョンとpythonのバージョンがあっていない可能性などが考えられる(Office 32bit、python 64bitとか)）

主キーを設定する

Accessの場合忘れがちになります

テーブルをデザインビューで開く

→主キーに設定する列を選択

→タブにある主キーのボタンを押すと列の横に鍵マークができてOK

参考：主キーがない場合のエラーコード

raise sa_exc.ArgumentError(
sqlalchemy.exc.ArgumentError: Mapper Mapper[Table(テーブル名)] could not assemble any primary key columns for mapped table 'テーブル名'

SQLserverとの接続

Windows認証の場合

import pandas as pd
import urllib
from sqlalchemy import create_engine,text

start = time.time()
driver='{SQL Server}'
server = "" #サーバ名
database = ''　#データベース名
trusted_connection='yes'   #Windows認証の場合

odbc_connect = urllib.parse.quote_plus(
    'DRIVER='+ driver +';SERVER='+server+';DATABASE='+database+';trusted_connection=yes')

engine_SQLserver = create_engine('mssql+pyodbc:///?odbc_connect=' + odbc_connect)

sql = "SELECT * FROM table名"
  
df = pd.read_sql(sql,engine_SQLserver)

ユーザ名・パスワード接続の場合

import pandas as pd
import urllib
from sqlalchemy import create_engine,text

start = time.time()
driver='{SQL Server}'
server = "
username = '' #ログインユーザ名
password = '' #パスワード
database = '' #データベース名

odbc_connect = urllib.parse.quote_plus(
    'DRIVER='+ driver +';SERVER='+server+';DATABASE='+database+'UID='
		+username+';PWD='+password+';DATABASE='+database)

engine_SQLserver = create_engine('mssql+pyodbc:///?odbc_connect=' + odbc_connect)

sql = "SELECT * FROM table名"
  
df = pd.read_sql(sql,engine_SQLserver)