Pythonのメモ(その3)DBデータのPandas DataFrame での読み書き

Python

Pythonのメモ(その1)SQLAlchemy を使う」で、SQLAlchemy を使って、MySQLのデータベースにアクセスする手順をメモしました。今回は、DataFrameのデータをDBに書き込んだり、或いは、逆にDBからDataFrameに読み込んだりする方法について、メモしました。

Pandas DataFrame の関数・メソッドを使う

Pandas DataFrame には、データベースのデータを直接、読み込んだり、書き込んだりする関数、メソッドが備わっています。

DataFrameにデータを読み込む ~ pd.read_sql()

Pandas DataFrame の read_sql 関数を使うと、データベースから直接、DataFrameにデータを読み込むことができます。(->公式参照
 df = pd.read_sql(sql, con)
のようにして、データをDataFrameに読み込みます。引数は、
 sql:SQLクエリまたはテーブル(テーブル全体を読み込む)
 con:SQLAlchemy接続先(エンジン)
です。
Pythonのメモ(その1)に記載した事例で見てみます。’users’ テーブルから、id, name, fullname の項目を読み込みます。conは(その1)の例を踏襲しました。

import pandas as pd

# 接続先の定義
con = 'mysql://namae:Password-SQL-namae1@localhost/test'

# テーブル全体を読み込む
df = pd.read_sql('users', con)

# SQLクエリで読み込む
df = pd.read_sql('SELECT id, name, fullname FROM users', con)

print(df)

どちらも同じ結果になります。

   id   name         fullname
0   1     ed         Ed Jones
1   2   fred  Fred_Flintstone
2   3   mary    Mary_Contrary
3   4  wendy   Wendy Williams
4   5   jack        Jack Bean

データベースのデータをDataFrameに取り込むことが出来ました。

DataFrameのデータを書き込む ~ to_sql()

Pandas DataFrame の to_sql メソッドを使うと、DataFrameのデータを直接、データベースに書き込むことができます。 (->公式参照

例として、次のDataFrameを作りました。これをデータベースに追加することにします。

df_add = pd.DataFrame([[6, 'james', 'James_Smith'], [7, 'david', 'David_Taylor']], columns=['id','name','fullname'])
print(df_add)
   id   name      fullname
0   6  james   James_Smith
1   7  david  David_Taylor

データベースに書き込むには、pandas の to_sql メソッドを使って、
 df.to_sql(name, con, if_exists='append', index=False)
のようにします。引数は、
 name:データを書き込むテーブル名
 con:SQLAlchemy接続先(エンジン)
です。

if_exists='append' は、テーブルが存在するときにはデータを追加する、の意です。これを書かないとデフォルトの ‘fail’ とされ、テーブルが存在するときにはエラーとなってしまいます。
index=False は、DataFrameのindexを、列として書き込まない、の意です(デフォルトはTrue)。’id’ 列がindexに設定されていないときには必要です。
具体的には次のように記述します。con は前項で定義したものと同じです。

df_add.to_sql('users', con, if_exists='append', index=False)

データベースを確認してみます。

df = pd.read_sql('users', con)
print(df)
   id   name         fullname
0   1     ed         Ed Jones
1   2   fred  Fred_Flintstone
2   3   mary    Mary_Contrary
3   4  wendy   Wendy Williams
4   5   jack        Jack Bean
5   6  james      James_Smith
6   7  david     David_Taylor

id = 6, 7 のデータが追加されました。

なお、id はユニークでなければいけない(primary key として設定してあるので、重複することはできない)ので、もう一度書き込もうとするとエラーになります。

IntegrityError: (MySQLdb._exceptions.IntegrityError) (1062, "Duplicate entry '6' for key 'users.PRIMARY'")

言い換えると、同じ id のデータを更新する(Updateする)ことはできません。
データの中に一つでも重複するものがあれば、エラーとなり、すべてのデータが書き込まれません。

SQLAlchemyでDataFrameのデータを扱う

データの一部を更新したい場合や、エラーとならないデータのみ書き込みたい場合には、DataFrameの行ごとに処理するしかありません。以下では、読み込む場合も含めて、SQLAlchemy を使ってDataFrameとデータベースとの間でデータをやり取りする方法について、例示してみました。

DataFrameにデータを読み込む

前回のおさらい

まず、前回(Pythonのメモ(その1))のおさらいです。データベース、モデル(クラス)をそれぞれ database.py,model.py で定義をして、セッションを作成しました。

from database import Session
from model import User

session = Session()

これを前提として、前回は次のようにデータを取得し、表示しました。

d = session.query(User).all()
for x in d:
    print(x.id, x.name, x.fullname)
1     ed         Ed Jones
2   fred  Fred_Flintstone
3   mary    Mary_Contrary
4  wendy   Wendy Williams
5   jack        Jack Bean

d は、Userクラスのオブジェクトのリストです。

print(d)
print(type(d))
[<model.User object at 0x7fb84e5084c0>, <model.User object at 0x7fb84e508460>, <model.User object at 0x7fb84e508520>, <model.User object at 0x7fb84e5085e0>, <model.User object at 0x7fb84e5086a0>] 
 <class 'list'>

前回の print文では、それぞれのオブジェクトの中身を一つづつ取得し、表示しました。

リストにして読み込む

これと同じようにして、オブジェクトの中身をリストにすれば、DataFrameに読み込むことができます。リスト内包表記を使って次のようにしてみました。別途、列名を付しました。

import pandas as pd

d = session.query(User).all()
df = pd.DataFrame([[x.id,x.name,x.fullname] for x in d], columns=['id','name','fullname'])
print(df)
   id   name         fullname
0   1     ed         Ed Jones
1   2   fred  Fred_Flintstone
2   3   mary    Mary_Contrary
3   4  wendy   Wendy Williams
4   5   jack        Jack Bean

ところで、この例はUserクラス全体を抽出していますが、属性を一つづつ指定して抽出した場合は、次のようになります。

dd = session.query(User.id,User.name,User.fullname).all()
df = pd.DataFrame([[x.id,x.name,x.fullname] for x in dd], columns=['id','name','fullname'])

ここでは属性をひとつづつ、「全部」指定したので、結果としては同じになりました。このとき、ddは、属性のタプルのリストになっています。

print(dd)
[(1, 'ed', 'Ed Jones'), (2, 'fred', 'Fred_Flintstone'), (3, 'mary', 'Mary_Contrary'), (4, 'wendy', 'Wendy Williams'), (5, 'jack', 'Jack_Bean')]

従って、この場合は直接、DataFrameに読み込むことができます。

df = pd.DataFrame(dd, columns=['id','name','fullname'])

結果は同じなので省略します。

var関数を経由して読み込む

別の方法として、vars関数を使ってオブジェクトを辞書型に変換して、DataFrameに読み込むこともできます。例えば、オブジェクトの一つにvars関数を適用すると、

print(vars(d[0]))
{'_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x7fb84e508490>, 'fullname': 'Ed Jones', 'id': 1, 'name': 'ed'}

となります。これは、__dict__属性を取得しても同じです。

print(d[0].__dict__)
{'_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x7fb84e508490>, 'fullname': 'Ed Jones', 'id': 1, 'name': 'ed'}

これをDataFrameに読み込みます。ただし、'_sa_instance_state' は不要なので、削除しました。また、列の順序が変わってしまうので、必要であれば、列順序の変更を行います。

df = pd.DataFrame([vars(x) for x in d]).drop('_sa_instance_state', axis=1).reindex(['id', 'name', 'fullname'], axis=1)

結果は同じなので省略します。こちらの方法では、取得する中身を一つづつ設定する必要はありません。

DataFrameのデータを書き込む

データベースに次のDataFrameのデータを追加することにします。

df_add = pd.DataFrame([[5, 'james', 'James_Smith'], [6, 'david', 'David_Taylor']], columns=['id','name','fullname'])
print(df_add)
   id   name      fullname
0   5  james   James_Smith
1   6  david  David_Taylor

このうち id=5 は、データベースのデータと重複しているので、to_sql メソッドを使おうとするとエラーになるのでした。既に存在する id=5 のデータは更新し、存在しない id=6 のデータは追加したいのです。
やり方は様々あろうかと思いますが、次のようにしてみました。

for i in range(len(df_add)):
    dic = df_add.iloc[i, :].to_dict()
    if session.query(User).filter(User.id==dic['id']).update(dic) == 0:
        session.add(User(**dic))

session.commit()

DataFrameの1行ごとに次のような処理を行いました。
・DataFrame の1行を辞書型に変換する。
・DB内の id の存在を確認し、該当するものがあれば update する。その場合は 1 が返される。
・0 が返るときは、update するものがない場合なので、データを add する。
 User に辞書型変数 dic を展開して渡し、データを追加する。
・最後に、session.commit() する。

データベースを確認してみます。( DataFrame の read_sql 関数を使いました。)

q = session.query(User)
df = pd.read_sql(q.statement, q.session.bind)
print(df)
   id   name         fullname
0   1     ed         Ed Jones
1   2   fred  Fred_Flintstone
2   3   mary    Mary_Contrary
3   4  wendy   Wendy Williams
4   5  james      James_Smith
5   6  david     David_Taylor

id=5 が更新され、id=6 が追加されました。
ほかにも方法はいろいろあると思いますが、SQLAlchemy を使って、DataFrame のデータをDBに取り込む(データの追加・更新をする)一つの方法として。

なお、上記の read_sql 関数での読み込みに際しては、q を Queryオブジェクトとして、q.statement でSQLクエリを、q.session.bind でエンジンを取得して、引数に設定しました。

print(type(q))
print(q.statement)
print(q.session.bind)
<class 'sqlalchemy.orm.query.Query'>
SELECT users.id, users.name, users.fullname FROM users
Engine(mysql://namae:***@localhost/test)
タイトルとURLをコピーしました