RSQL

Allows the user to generate and execute select, insert, update and delete ‘SQL’ queries the underlying database without having to explicitly write ‘SQL’ code.

How to get started

install.packages("RSQL")

How to get started (Development version)

Install the R package using the following commands on the R console:

devtools::install_github("rOpenStats/RSQL", build_opts = NULL)

A simple example

To get started execute the following commands:

# 0.  Load libraries
library(RSQL)

# 1.  RSQL
db.name <- getMtcarsdbPath()
rsql <- createRSQL(drv = RSQLite::SQLite(), dbname = db.name)
query_sql <- rsql$gen_select(
    select_fields = c("*"),
    table = "mtcars")

query_sql <- rsql$gen_select(
    select_fields = c("mpg", "cyl", "disp", "hp", "drat", "wt", "qsec", "vs", "am"),
    table = "mtcars",
    where_fields = "gear",
    where_values = 4)
query_sql
#> [1] "select mpg, cyl, disp, hp, drat, wt, qsec, vs, am from mtcars where (gear) in ('4')"

rsql$execute_select(query_sql)
#>     mpg cyl  disp  hp drat    wt  qsec vs am
#> 1  21.0   6 160.0 110 3.90 2.620 16.46  0  1
#> 2  21.0   6 160.0 110 3.90 2.875 17.02  0  1
#> 3  22.8   4 108.0  93 3.85 2.320 18.61  1  1
#> 4  24.4   4 146.7  62 3.69 3.190 20.00  1  0
#> 5  22.8   4 140.8  95 3.92 3.150 22.90  1  0
#> 6  19.2   6 167.6 123 3.92 3.440 18.30  1  0
#> 7  17.8   6 167.6 123 3.92 3.440 18.90  1  0
#> 8  32.4   4  78.7  66 4.08 2.200 19.47  1  1
#> 9  30.4   4  75.7  52 4.93 1.615 18.52  1  1
#> 10 33.9   4  71.1  65 4.22 1.835 19.90  1  1
#> 11 27.3   4  79.0  66 4.08 1.935 18.90  1  1
#> 12 21.4   4 121.0 109 4.11 2.780 18.60  1  1

update_sql <- rsql$gen_update(
    update_fields = c("vs"),
    values = 1,
    table = "mtcars",
    where_fields = "gear",
    where_values = 4)
update_sql
#> [1] "update mtcars set vs='1' where (gear) in ('4')"
rsql$execute_update(update_sql)
#> <SQLiteResult>
#>   SQL  update mtcars set vs='1' where (gear) in ('4')
#>   ROWS Fetched: 0 [complete]
#>        Changed: 12
rsql$execute_select(query_sql)
#> Warning: Closing open result set, pending rows
#>     mpg cyl  disp  hp drat    wt  qsec vs am
#> 1  21.0   6 160.0 110 3.90 2.620 16.46  1  1
#> 2  21.0   6 160.0 110 3.90 2.875 17.02  1  1
#> 3  22.8   4 108.0  93 3.85 2.320 18.61  1  1
#> 4  24.4   4 146.7  62 3.69 3.190 20.00  1  0
#> 5  22.8   4 140.8  95 3.92 3.150 22.90  1  0
#> 6  19.2   6 167.6 123 3.92 3.440 18.30  1  0
#> 7  17.8   6 167.6 123 3.92 3.440 18.90  1  0
#> 8  32.4   4  78.7  66 4.08 2.200 19.47  1  1
#> 9  30.4   4  75.7  52 4.93 1.615 18.52  1  1
#> 10 33.9   4  71.1  65 4.22 1.835 19.90  1  1
#> 11 27.3   4  79.0  66 4.08 1.935 18.90  1  1
#> 12 21.4   4 121.0 109 4.11 2.780 18.60  1  1

insert.df <- c(4, rep(99, 9))
names(insert.df) <- c("gear", "mpg", "cyl", "disp", "hp", "drat", "wt", "qsec", "vs", "am")
insert.df <- as.data.frame(t(insert.df))
insert_sql <- rsql$gen_insert(
    values = insert.df,
    table = "mtcars")
rsql$execute_insert(insert_sql)
#> <SQLiteResult>
#>   SQL  insert into mtcars(gear, mpg, cyl, disp, hp, drat, wt, qsec, vs, am) values   ( '4', '99', '99', '99', '99', '99', '99', '99', '99', '99' );
#>   ROWS Fetched: 0 [complete]
#>        Changed: 1
rsql$execute_select(query_sql)
#> Warning: Closing open result set, pending rows
#>     mpg cyl  disp  hp  drat     wt  qsec vs am
#> 1  21.0   6 160.0 110  3.90  2.620 16.46  1  1
#> 2  21.0   6 160.0 110  3.90  2.875 17.02  1  1
#> 3  22.8   4 108.0  93  3.85  2.320 18.61  1  1
#> 4  24.4   4 146.7  62  3.69  3.190 20.00  1  0
#> 5  22.8   4 140.8  95  3.92  3.150 22.90  1  0
#> 6  19.2   6 167.6 123  3.92  3.440 18.30  1  0
#> 7  17.8   6 167.6 123  3.92  3.440 18.90  1  0
#> 8  32.4   4  78.7  66  4.08  2.200 19.47  1  1
#> 9  30.4   4  75.7  52  4.93  1.615 18.52  1  1
#> 10 33.9   4  71.1  65  4.22  1.835 19.90  1  1
#> 11 27.3   4  79.0  66  4.08  1.935 18.90  1  1
#> 12 21.4   4 121.0 109  4.11  2.780 18.60  1  1
#> 13 99.0  99  99.0  99 99.00 99.000 99.00 99 99

Troubleshooting

Please note that the ‘RSQL’ project is released with a Contributor Code of Conduct. By contributing to this project, you agree to abide by its terms.