Description
Database Agnostic Package to Generate and Process 'SQL' Queries in R.
Description
Allows the user to generate and execute select, insert, update and delete 'SQL' queries the underlying database without having to explicitly write 'SQL' code.
README.md
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.