Preview and save SQL out from SQL file.

[PosiPals]

I am curious. In SQL script file, I can only preview the SQL document, but not saving the data into R environment.

In SQL file:

-- !preview conn=DBI::dbConnect(drv = RSQLite::SQLite(),dbname = ".../SQL Files/sql_starter.sqlite")


SELECT cut, AVG(price) as ave_price
FROM diamonds
GROUP BY cut

Unlike in R markdown where you can actually save the output via output.var argument.

SELECT cut, AVG(price) as ave_price
FROM diamonds
GROUP BY cut

Output:

> ave_price
        cut ave_price
1      Fair  4358.758
2      Good  3928.864
3     Ideal  3457.542
4   Premium  4584.258
5 Very Good  3981.760

Is there any way to retrieve the data? I am still fairly new in using SQL in R/RStudio.

[nirgrahamuk]

@PosiPals

I am curious. In SQL script file, I can only preview the SQL document, but not saving the data into R environment.

I think thats correct.

If you are asking how to run that sql code and have it result in a data.frame in the R environment, then I would just consider it a text source of query, and write the R code that can run that code on a given database connection.

for example if the sql script was called "test_1.sql"
then in some other R or Rmarkdown script you could have code like :

# sql script to queryable text
f1 <- file("test_1.sql","r")
q1 <- readLines(f1) 
close(f1)
q2 <- paste0(q1,collapse = "\n")


conn <- DBI::dbConnect(RSQLite::SQLite()) # or some other connection

(result_df1 <- DBI::dbGetQuery(conn,q2))

[PosiPals]

Thanks for the assist, pal.

Now, I can get the results from any SQL script:

sql_res_query <- function(conn, sql_path) {
  f1 <- file(sql_path, "r")
  q1 <- readLines(f1)
  close(f1)
  q2 <- paste0(q1,collapse = "\n")
  res <- DBI::dbGetQuery(conn, q2)
  
  return(res)
}

The output:

> conn <- DBI::dbConnect(drv = RSQLite::SQLite(),
                       dbname = ".../SQL Files/sql_starter.sqlite")
> sql_res_query(conn,".../SQL Files/mpg_query_1.sql")
        class cyl  avg_hwy
1     2seater   8 24.80000
2     compact   4 29.46875
3     compact   5 29.00000
4     compact   6 25.30769
5     midsize   4 29.18750
6     midsize   6 26.26087
7     midsize   8 24.00000
8     minivan   4 24.00000
9     minivan   6 22.20000
10     pickup   4 20.66667
11     pickup   6 17.90000
12     pickup   8 15.80000
13 subcompact   4 30.80952
14 subcompact   5 28.50000
15 subcompact   6 24.71429
16 subcompact   8 21.60000
17        suv   4 23.75000
18        suv   6 18.50000
19        suv   8 16.78947