In dajuntian/sqlTXT: Submit Multiple SQL Statements and Return a List of Data Frames

knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>",
  fig.path = "README-"
)

sqlTXT

The goal of sqlTXT is to submit multiple SQL statements through a single function call and to get results as a list of data frames.

How does it work?

It first separates the code into segments by ';' and then removes comments. Loop through all the segments, it will send the query into the DBI connection and add data frame to the result for select. What it returns is a list and element in the list is the data frame from select statements.

Installation

You can install sqlTXT from github with:

# install.packages("devtools")
devtools::install_github("dajuntian/sqlTXT")

Example

This is a basic example to show you how to use the package

# generate DBI Connection
con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
DBI::dbWriteTable(con, "mtcars", mtcars)
sqlFromText <- "
--some comments
select * from mtcars;
CREATE TABLE contacts (
 contact_id integer PRIMARY KEY,
first_name text NOT NULL,
last_name text NOT NULL,
email text NOT NULL UNIQUE,
phone text NOT NULL UNIQUE
);
delete from mtcars where gear = 3;
alter table mtcars add snew_column3 char(10);
select * from mtcars;
select * from contacts;
/*end of code*/"


result_from_sql <- sqlTXT::commit_sql(con, sqlFromText, is_file = F)

for (item in result_from_sql) {
  cat("Date frame returned from SQL\n")
  print(head(item))
  cat("\n")
}

dajuntian/sqlTXT documentation built on May 17, 2019, 7:01 p.m.