一所懸命に手抜きする

監査の仕事をしています。ITを利用し、良い意味で「手抜き」することは効率化と精度アップに役立つと思うんです。部下への引き継ぎのためにまずは諸々頭から引っ張り出そうとブログを始めました。

[ Main ]  [ 別館 ]
当ブログで取り上げられている事案やデータベース、人物等はすべてフィクションです。

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をまとめたものと言えます。

  1. rs<-dbSendQuery(con,sqlText) SQLの結果をDBIResult(のサブクラスSQLiteReult)として返す。
  2. dbFetch(rs) DBIResul結果セットからdata.frame形式で取り出す。
  3. dbClearResult DBIResult結果セットをクリアしてリソース開放
データベース切断
# 一般
dbDisconnect(コネクション)
# 上記コードでの使用例
dbDisconnect(con)

これもDBIの関数です。

このように

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

広告を非表示にする