r - 我如何 “flush”数据到我的RSQLite磁盘数据库？

Question

我使用R包dbplyr和RSQLite创建了一个数据库，但是尽管我写(和读)了一个表，但我的数据库在磁盘上的大小为零字节。这是我的脚本:

library("RSQLite")
library("dbplyr")
library("dplyr")

data(mtcars)

con <- DBI::dbConnect(RSQLite::SQLite(), dbname = "./mtcars.db")
copy_to(con, mtcars, "mtcars")

print(tbl(con, "mtcars"))

但是，正如您从ls -l最后看到的那样，即使脚本确实从数据库中读取了mtcar，它的数据库大小也是0(所以它就在其中)。我想使用数据库文件与另一个程序共享数据，那么如何定期将数据“刷新”到磁盘上？

tbrowne@calculon:~/scratch$ R -f dplysqlite.r 

R version 3.2.3 (2015-12-10) -- "Wooden Christmas-Tree"
Copyright (C) 2015 The R Foundation for Statistical Computing
Platform: x86_64-pc-linux-gnu (64-bit)

R is free software and comes with ABSOLUTELY NO WARRANTY.
You are welcome to redistribute it under certain conditions.
Type 'license()' or 'licence()' for distribution details.

  Natural language support but running in an English locale

R is a collaborative project with many contributors.
Type 'contributors()' for more information and
'citation()' on how to cite R or R packages in publications.

Type 'demo()' for some demos, 'help()' for on-line help, or
'help.start()' for an HTML browser interface to help.
Type 'q()' to quit R.

> 
> library("RSQLite")
> library("dbplyr")
Warning messages:
1: replacing previous import by ‘rlang::enquo’ when loading ‘dbplyr’ 
2: replacing previous import by ‘rlang::quo’ when loading ‘dbplyr’ 
3: replacing previous import by ‘rlang::quos’ when loading ‘dbplyr’ 
4: replacing previous import by ‘rlang::quo_name’ when loading ‘dbplyr’ 
> library("dplyr")

Attaching package: ‘dplyr’

The following objects are masked from ‘package:dbplyr’:

    ident, sql

The following objects are masked from ‘package:stats’:

    filter, lag

The following objects are masked from ‘package:base’:

    intersect, setdiff, setequal, union

> 
> data(mtcars)
> 
> con <- DBI::dbConnect(RSQLite::SQLite(), dbname = "./mtcars.db")
> copy_to(con, mtcars, "mtcars")
> 
> print(tbl(con, "mtcars"))
# Source:   table<mtcars> [?? x 11]
# Database: sqlite 3.19.3 [/home/tbrowne/scratch/mtcars.db]
     mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
   <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
 1  21.0     6 160.0   110  3.90 2.620 16.46     0     1     4     4
 2  21.0     6 160.0   110  3.90 2.875 17.02     0     1     4     4
 3  22.8     4 108.0    93  3.85 2.320 18.61     1     1     4     1
 4  21.4     6 258.0   110  3.08 3.215 19.44     1     0     3     1
 5  18.7     8 360.0   175  3.15 3.440 17.02     0     0     3     2
 6  18.1     6 225.0   105  2.76 3.460 20.22     1     0     3     1
 7  14.3     8 360.0   245  3.21 3.570 15.84     0     0     3     4
 8  24.4     4 146.7    62  3.69 3.190 20.00     1     0     4     2
 9  22.8     4 140.8    95  3.92 3.150 22.90     1     0     4     2
10  19.2     6 167.6   123  3.92 3.440 18.30     1     0     4     4
# ... with more rows
> 
> 
tbrowne@calculon:~/scratch$ ls -l
total 4
-rw-rw-r-- 1 tbrowne tbrowne 194 Oct 31 11:04 dplysqlite.r
-rw-r--r-- 1 tbrowne tbrowne   0 Oct 31 11:04 mtcars.db

Answer 1

您没有使用 RSQLite documentation建议的模式。该文档使用dbWriteTable将数据帧复制到SQLite表中:

dbWriteTable(con, "mtcars", mtcars)

根据此文档，您的完整代码如下所示:

con <- dbConnect(RSQLite::SQLite(), "./mtcars.db")
data(mtcars)
dbWriteTable(con, "mtcars", mtcars)
dbListTables(con)
# Fetch all query results into a data frame:
dbGetQuery(con, "SELECT * FROM mtcars")