【SQLite + Python + Pandas】SQLiteをPandas（DataFrame）で扱う

Pythonを使ってSQLiteを扱う。
SQLiteへのSELECT結果が大きい場合は配列やrowで扱うよりも、PandasのDataFrameを使うと楽に集計、書き込みできる

サンプルテーブル

このエントリで扱うサンプルのテーブルはこんな感じ。

SELECT * FROM test_member_list LIMIT 10;

id	mei	kana	seibetsu	ketsueki
1	谷川 純典	タニカワ スミノリ	男	B
2	松村 秀司	マツムラ シュウジ	男	O
3	三谷 郁美	ミタニ イクミ	女	B
4	三谷 梨枝子	サンヤ リエコ	女	AB
5	近藤 沙耶香	コンドウ サヤカ	女	A
6	中山 雅義	ナカヤマ マサヨシ	男	O
7	金城 英則	キンジョウ ヒデノリ	男	O
8	土屋 興亜	ツチヤ キョウア	男	O
9	中西 明博	ナカニシ アキヒロ	男	O
10	竹内 好博	タケウチ ヨシヒロ	男	B

上表の通り、氏名と性別と血液型を格納したシンプルな名簿がサンプル¹。 最大100件あるが、ここでは上位10件のみ。

取得

SELECTでデータを取得し、DataFrameに格納する。

sample_select.py

# SELECT

import os
from pathlib2 import Path
import sqlite3
import pandas

if __name__ == '__main__':

    # カレントディレクトリをスクリプト配置箇所に変更
    os.chdir(Path(__file__).parent)
    print("作業ディレクトリ:{0}".format(os.getcwd()))

    # DBファイル準備
    db_name = u'sample.db'
    conn = sqlite3.connect(db_name)
    cursor = conn.cursor()

    # SELECT結果をDataFrame
    df = pandas.read_sql_query(sql=u"SELECT * FROM test_member_list LIMIT :limit", con=conn, params={"limit":3})
    print(df)
        #=>        id    mei       kana seibetsu ketsueki
        #=>    0   1  谷川 純典  タニカワ スミノリ        男        B
        #=>    1   2  松村 秀司  マツムラ シュウジ        男        O
        #=>    2   3  三谷 郁美    ミタニ イクミ        女        B

    # いつもどおりDataFrame加工する（2行目までスライスして行にアクセス）
    df_oft = df.iloc[:2]

    for i, row in df_oft.iterrows():
        print("index:{0}, mei:{1}".format(i, row['mei']))
            #=> index:0, mei:谷川 純典
            #=> index:1, mei:松村 秀司

SELECTする時はPandasのread_sql_query()を使う。
引数に実行するSELECTクエリ、コネクションを渡せば結果がDataFrameとして返却される。
DataFrameのカラム名にはSELECT結果のカラム名がそのまま入る。

また、サンプルコードに記載した通りパラメタクエリも使えるので、通常のPythonでsqlite3モジュールを使う時と同様にクエリにパラメタを仕込める。

挿入

INSERTを行うサンプルコード。
テーブルに新規レコードを挿入する。

sample_insert.py

# INSERT

import os
from pathlib2 import Path
import sqlite3
import pandas

if __name__ == '__main__':

    # カレントディレクトリをスクリプト配置箇所に変更
    os.chdir(Path(__file__).parent)
    print("作業ディレクトリ:{0}".format(os.getcwd()))

    # DBファイル準備
    db_name = u'sample.db'
    conn = sqlite3.connect(db_name) 
    cursor = conn.cursor()

    # テーブル構造を取得するため1件だけ取得してみる
    df = pandas.read_sql_query(sql=u"SELECT * FROM test_member_list LIMIT 1", con=conn)
    print(df)
        #=>         id    mei       kana seibetsu ketsueki
        #=>     0   1  谷川 純典  タニカワ スミノリ        男        B

    # INSERTするレコードを作成する
    df_ins = pandas.DataFrame([[110, u"西住 みほ", u"ニシズミ ミホ", u"女", u"A"]], columns=df.columns)
    print(df_ins)
        #=>         id    mei     kana seibetsu ketsueki
        #=>     0  110  西住 みほ  ニシズミ ミホ        女        A

    # INSERT
    df_ins.to_sql(u"test_member_list", conn, if_exists='append', index=None)
    
    # 確認でSELECTする
    df_sel = pandas.read_sql_query(sql=u"SELECT * FROM test_member_list", con=conn)
    print(df_sel.tail(3))
        #=>       id     mei         kana seibetsu ketsueki
        #=> 98    99   安部 育雄       アベ イクオ        男       AB
        #=> 99   100  古川 伝三郎  フルカワ デンザブロウ        男        A
        #=> 100  110   西住 みほ      ニシズミ ミホ        女        A

INSERTを行うにはINSERTする対象のテーブルと同じカラムを含むDataFrameを準備する。 サンプルコードでは、DataFrameオブジェクトのdfにSELECT結果1行分を行っている。

下記抜粋の通り、INSERT用のDataFrameオブジェクトdf_insに書き込むデータを準備する。DataFrameのカラム要素はテーブルのカラム構造と同一にするので……columns=df.columnsと先ほど1件だけ取得したDataFrameのカラム情報をそのまま渡してコピーしている²。
自動発番やデフォルト値が入る列等がある場合は、カラム情報取得のために準備するDataFrame（ここでいうdf）のSELECT分を必要なカラムだけに絞るか、columns=[u"mei", u"kana", u"seibetsu", u"ketsueki"]といった感じに自力でカラム情報を作成するのもありかも³。

    # INSERTするレコードを作成する
    df_ins = pandas.DataFrame([[110, u"西住 みほ", u"ニシズミ ミホ", u"女", u"A"]], columns=df.columns)

準備したDataFrame（ここではdf_ins）の.to_sql()を使う。
.to_sql()はINSERTするテーブル名、コネクション、if_existsパラメータは挿入のため'append'としておく。

テーブルまるごとをDataFrameのレコードで入れ替える場合は'replace'とする。
この辺のDataFrame.to_sql()に関するあれこれは下記参照。

pandas.DataFrame.to_sql — pandas 0.24.0 documentation
https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html

大量のレコードを一度にINSERTしたい場合等は、カラム定義がINSERT先のテーブルと一致したDataFrameを準備すれば良いので楽なのでfor文などのループでINSERTクエリを組み立てては発行するよりもかなり楽だと思う。

更新

結果的に、DataFrame内のキーなどを判別して自動でUPDATEを発行したり、それに基づくクエリを構築してくれたりといったことは無いみたい。

いい方法があったらここに追記するかもだけど。

ここは泥臭くUPDATEするレコードに対しては自力でUPDATEクエリを組むしかないみたい。

余談

SQLite以外は？

SQLite以外の他のDBMSのSELECT結果をDataFrameで取得したり、INSERTやUPDATEを行うには同じ手法で行けるのか？

答えとしては、DataFrame.to_sql()等に渡すコネクションを換えて上げれば同じように動作する。
例えばMySQLの場合は下記のような感じになるっぽい。

from sqlalchemy import create_engine

db_engine = create_engine('mysql://{user}:{pass}@{host_name}:{port}/{shema}'.format({'user':'user', 'pass':'password', 'host':'db_server', 'port':3306, 'schema':'test_table'))

with db_engine.begin() as conn:
    df.to_sql('table_name', con=conn, if_exists='append', index=False)

ただし、あんまり検証していないのでこの辺は後で要確認。

超余談

.NET Framework系の言語（C#、VisualBasic）にはDataSetとかDataTableなる便利なものがあったので、メモリが許すならある程度のレコードを取得して、2次元表のイメージのままデータをアレコレできた。
PythonだとそれにあたるのがDataFrameだろうか。使い勝手はだいぶ違うけど、感覚は近い。こんな言い方をすると詳しい人からすると辛辣なツッコミが入るかもだが。

とりあえず、DataTableはLINQを使ってレコード操作が可能。DataFrameも特定の条件を元に抽出やソートを行うことができるので、そのうちそういうカンペでもまとめておこうかな。

買おうかなと思ってる本

一度、PandasとかNumPyとかあたりのことをネットで必要最低限調べるよりは体系的に学んだ方が良いんじゃないかと、そのあたりの本を買おうかと検討中...

Pythonによるデータ分析入門 第2版 ―NumPy、pandasを使ったデータ処理

pandasクックブック ―Pythonによるデータ処理のレシピ―

---

1. サンプルは自前のテストデータジェネレータ（姓、名の組み合わせ）からランダム生成したもの。実在の人物と名称が一致してもなんら関係はありません。 ↩

2. もっと良い方法があるかも。DataFrameのメソッドで同一のカラム情報を持った空っぽのDataFrameオブジェクトを作成するメソッドとか無いだろうか？ ↩

3. DBへの接続を1回減らすという意味では後述の通り自力でカラム定義した方が健全か？↩