Mi Band Master のデータをRで読み込む(2)　RSQLiteはDBIを利用しているらしい

RからSQLiteデータベースを操作

　シャオミのMi Band 2 のデータをMi Band Master　というアプリで出力すると、db.sqlite というファイルが作成されます。これはSQLite形式のデータベースです。このデータベースをRで読み込む方法を実例で勉強しましたので備忘録作成…

データベース操作はDBIで

　RからSQLによりデータを抽出するには、sqldf,RSQLiteやRMySQL,RPostgreSQLなどのパッケージを使うことになります。これらのパッケージは、いずれの場合も、DBIパッケージを自動的にインポートします。 DBIは DataBase Interface のことで、各パッケージの裏方としてデータベース操作を担当しています。つまりデータベースの操作は DBI で、SQL は各パッケージで分担するようなイメージです。

RからDBIを利用してSQLiteにアクセスする

　R から SQLite データベースにアクセスするには、RSQLite パッケージを用いることが多いと思います。この場合にもデータベース操作は DBI の関数を用います。 liblary("RSQLite")とすると　DBI　も自動的に読み込まれますので明示的に DBI を require する必要はありません。

c:/wrk/db.sqlite のテーブル sleep からデータを抽出する例


> library("RSQLite")
> con = dbConnect(SQLite(), "C:/wrk/db.sqlite", synchronous="off")
> dbListTables(con)
 [1] "alarm"               "android_metadata"    "app_notification"   
 [4] "call_notification"   "event_notification"  "func_button_action"
 [7] "func_button_profile" "heartrate"           "settings"           
[10] "sleep"               "sms_notification"    "sqlite_sequence"    
[13] "stats"               "steps"  

> dbGetQuery(con, "select * from sleep")
   id start_time   end_time awake deep light                                            stages deleted manually
1   1 1516031280 1516050300     0  182   135  10L25D41L42D10L12D17L44D3L18D7L12D13L12D6L17D28L       0       NA
2   2 1516115340 1516136880     0  142   217            14L33D26L20D62L43D44L17D7L14D10L15D54L       0       NA
3   3 1516200780 1516223400     0  187   190       10L24D4L20D21L39D20L69D31L13D5L11D83L11D16L       0       NA

> dbDisconnect(con)
>

データベースへの接続

# 一般
con <- dbConnect(ドライバ, host="host名", port=ポート番号, dbname="データベース名", user="ユーザー名", password="パスワード")
# SQLite
con <- dbConnect(SQLite(), データベースパス)
# 上記コードでの使用例
con = dbConnect(SQLite(), "C:/wrk/db.sqlite", synchronous="off")

　SQLiteの場合には、ドライバ名とデータベースパスだけで接続できます。SQLite()はRSQLite::SQLite()と書くこともあるようにRSQLiteの関数であり、SQLiteに接続するドライバを返します。これはDBIで使うドライバ DBD にあたります。 synchronous="off"はデータをOSに渡したら同期せずにすぐに処理を続ける設定で、その分速いらしいことが　 https://www.sqlite.org/pragma.html#pragma_synchronous 　に書いてあるようです。多分。
　一般にはconは(データベース・)コネクションと呼びますが、SQLiteの場合にはデータベースそのものとなります。
　dbconnect() はDBIの関数ですから、SQLiteでなくともMySQL,PostgreSQLいずれのデータベースに対しても同じように使えます。ただし、引数が微妙に違うのが厄介ですが

データベース中のテーブル一覧

# 一般
dbListTables(データベースコネクション)
# 上記コードでの使用例
dbListTables(con)

　dbListTables()もDBIの関数です。さらに、テーブルの中のフィールド一覧を表示するdbListFields()もあるのですが、select *で全フィールドを抽出して確認できるので私はあまり使用しません。必要ならSQLからPRAGMA table_info()を呼び出しています。

# 一般
dbListFields(データベースコネクション, テーブル名)
# 上記コードでは使用していない

SQL実行

# 一般
dbGetQuery(データベースコネクション, SQL)
# 上記コードでの使用例
dbGetQuery(con, "select * from sleep")

　dbGetQuery()もDBIの関数です。https://www.rdocumentation.org/packages/DBI/versions/0.5-1/topics/dbGetQuery の説明によると、デフォルトでdbSendQuery()、dbFetch()、dbClearResult()、を呼び出すようになっているとのこと。 select専用らしいのですが、互換性の観点からデータ操作もできてしまうこともあります。updateなどはdbExecuteを使うようにとのこと。 dbGetQuery(co,sqlText)は下記1～3をまとめたものと言えます。

rs<-dbSendQuery(con,sqlText)　SQLの結果をDBIResult(のサブクラスSQLiteReult)として返す。
dbFetch(rs) DBIResul結果セットからdata.frame形式で取り出す。
dbClearResult DBIResult結果セットをクリアしてリソース開放

データベース切断

# 一般
dbDisconnect(コネクション)
# 上記コードでの使用例
dbDisconnect(con)

これもDBIの関数です。

このように

　操作対象となるデータベースが何であってもDBIは同じ関数で操作できるので楽です。

一所懸命に手抜きする

デスクワークばかりのスポーツ嫌いで50歳も過ぎ、いよいよ足腰に衰えを感じつつある昨今。