sqlquery
SQL query editor
Overview
Htmlwidget/Shiny gadget to write SQL queries with autocompletion for SQL keywords and for table names and fields. There are two addins in the package, in both you can write queries, but :
- Non-interactive mode: you cannot run queries but you keep control over your R session;
- Interactive mode: you can run queries and see first rows of result.
Installation
You can install from Github:
source("https://install-github.me/dreamRs/sqlquery")Htmlwidget
Basic usage:
library(sqlquery)
sql_query(value = "SELECT * FROM mtcars")Get autocompletion for tables and fields in a Database:
library(DBI)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(conn = con, name = "mtcars", value = mtcars)
sql_query(conn = con)
Shiny app
Basic usage:
library(sqlquery)
sql_query_app(value = "SELECT * FROM mtcars")With a connection :
library(DBI)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(conn = con, name = "mtcars", value = mtcars)
sql_query_app(conn = con)
Click button see results to display first rows with RStudio's View (if app runs in pane viewer) or in a modal (if app runs in dialog viewer or browser).
To change display mode, use:
options("sqlquery.display.mode" = "dialog")Addins
You can launch addins via RStudio addins menu. If you want to use a connection with addins, you can set option sqlquery.connection in your .Rprofile :
options("sqlquery.connection" = function() {
con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
DBI::dbWriteTable(conn = con, name = "mtcars", value = mtcars)
return(con)
})This has to be a function returning a connection.
If you want to use a default schema, use:
options("sqlquery.schema" = "myschema")