Before you can execute any SQL you will need connections to the
databases you want to use. Either define them yourself (e.g. using
DBI::dbConnect()), or place their specifications in a yaml
file and use connect():
library(sqlhelper)
conf_fn <- "examples/sqlhelper_db_conf.yml"
readLines(conf_fn) |>
writeLines()
#> #### sqlhelper_db_conf.yml ####
#>
#> simple_sqlite:
#> driver_type: sqlite
#> description: "A simple connection to an in-memory database"
#> connection:
#> Server: ":memory:"
#>
#> pool_sqlite:
#> driver_type: sqlite
#> pool: yes
#> description: "A pooled connection to an in-memory database"
#> connection:
#> Server: ":memory:"
connect(conf_fn)vignette("connections") and connect()
describe in detail how to specify and manage connections. In particular,
note that sqlhelper defines a configuration search path.
This means that if you tend to connect to the same databases in every
session, you can avoid supplying file names by placing yaml files in the
search path. Similarly, if you are a database admin you can supply
connections to your analysts by placing such a file in the site-wide
directory of the search path.
sqlhelper’s principal function is
run_files(); use it to execute files or list of files. If
you have used connect() to set up your connections you can
simply pass file names to run_files().
# Write iris to sqlhelper's default connection
DBI::dbWriteTable(default_conn(), name = "IRIS", value = iris)
# write some queries in a .sql file
file_to_run <- "examples/example.sql"
readLines(file_to_run) |> writeLines()
#> -- qname = how_many_irises
#> SELECT count(*) as N FROM IRIS;
#>
#> -- qname = short_petal_setosa
#> select Species, `Petal.Length`
#> FROM IRIS
#> WHERE Species = "setosa"
#> AND `Petal.Length` < {petal_length}
#Define a parameter
petal_length <- 1.3
# Run the queries and save the results
results <- run_files(file_to_run)
# Inspect the results. By default, run_files() returns a list of the results of
# each query in the files you provided. Results of a specific query can be accessed by the
# the name of the query. See the article 'Executing SQL' for more on named queries.
results
#> $how_many_irises
#> N
#> 1 150
#>
#> $short_petal_setosa
#> Species Petal.Length
#> 1 setosa 1.1
#> 2 setosa 1.2
#> 3 setosa 1.0
#> 4 setosa 1.2
results$short_petal_setosa
#> Species Petal.Length
#> 1 setosa 1.1
#> 2 setosa 1.2
#> 3 setosa 1.0
#> 4 setosa 1.2This example illustrates two key aspects of run_files().
Queries can be parameterized using the familiar
glue::glue_sql() syntax, and queries can be named using
interpreted comments for easier access to the results. Other aspects of
execution can also be controlled using interpreted comments;
run_files() and vignette("execution") describe
this in detail.
For exploratory work you might just want to run some short queries
interactively. Use the function run_queries() for this.
# write some queries
my_queries <- c(
showtabs = "SELECT name FROM sqlite_schema WHERE type ='table' AND name NOT LIKE 'sqlite_%'",
how_many_irises = "select count(*) from iris"
)
# Run the queries and save the results
results <- run_queries(my_queries)
# Inspect the results. runqueries() returns a list with one element per query.
# You can access them using the names of the queries:
results$showtabs
#> name
#> 1 IRIS
results$how_many_irises
#> count(*)
#> 1 150run_queries() and vignette("execution")
describe in detail the options for running individual queries.
Prior to execution, run_files() reads and prepares SQL
files using the functions read_sql() and
prepare_sql() respectively. These functions can be also be
used in isolation to read and/or prepare SQL for execution without
actually executing it. This can be useful if you need paramaterize your
SQL in a differently (for example by using DBI::dbBind()),
or if you need to debug your parameterized SQL.