README.md

In rOpenStats/rsql: Database Agnostic Package to Generate and Process 'SQL' Queries in R

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.

| Release | Usage | Development | |:---------------------------------------------------------------------------------------------|:---------------------------------------------------------------------------------------------------------|:-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | | | | | | | | | | | |

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.

rOpenStats/rsql documentation built on Aug. 22, 2023, 2:16 a.m.