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_A) #dataframe dfにtableデータを格納
※1:Accessに接続するためのドライバーが必要になりますが、専用のページからダウンロードする必要があります。(Accessファイルが入っていても未インストールの場合があります!)
※1現在入っているドライバを確認するには
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)
Discussion