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 する必要はありません。
> 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は同じ関数で操作できるので楽です。