Last chance! 50% off unlimited learning
Sale ends in
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 |
|---|---|---|
install.packages("RSQL")Install the R package using the following commands on the R console:
devtools::install_github("rOpenStats/RSQL", build_opts = NULL)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 1update_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 1insert.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 99Please 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.
install.packages('RSQL')