R Markdown: Creating a SQL table then output it as a dataframe

Question

I am learning how to use SQL in R and R Markdown.

A number of sources says that SQL tables can be created with a SQL chunk and that output.var needs to be in the SQL chunk tag to output that table into an R dataframe. But the dataframe comes out as NULL with the below code:

```{r setup, include=FALSE}

install.packages("RSQLite", repos = "http://cran.us.r-project.org")

library(RSQLite)
library(DBI)
library(knitr)

db <- dbConnect(RSQLite::SQLite(), ":memory:")

knitr::opts_chunk$set(echo = TRUE)
knitr::opts_chunk$set(connection = "db")

```

```{sql, connection = db, output.var = "Order_Detail"}

DROP TABLE IF EXISTS Order_Detail;

--create order table and populate records
CREATE TABLE IF NOT EXISTS Order_Detail (
    invoice_id INTEGER NOT NULL,
    invoice_line INTEGER NOT NULL,
    store_id INTEGER NOT NULL,
    time_stamp DATE NOT NULL,
    product VARCHAR(8) NOT NULL,
    units INTEGER NOT NULL,
    sales NUMERIC(7 , 2 ) NOT NULL,
    cogs NUMERIC(5 , 2 ) NOT NULL
);

INSERT INTO Order_Detail(invoice_id,invoice_line,store_id,time_stamp,product,units,sales,cogs) VALUES (1000,312,3,'2018/12/23','30',1,199.99,28.00);

```

> Order_Detail
NULL

Did I forget something?

Thanks.

Wow, this question could be a real-life "little bobby tables" (xkcd.com/327)! (Although if somebody actually tries this code blindly with a connection to a production database ... the lesson would be well-learned.) — r2evans
– r2evans, Commented Oct 1, 2019 at 23:15

Parfait · Accepted Answer · 2019-10-01 22:34:06Z

2

Simply add a SELECT statement at the end of chunk since right now you only have action queries that do not render a resultset:

...
SELECT * FROM Order_Detail
```

answered Oct 1, 2019 at 22:34

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Cinji18 Over a year ago

As I have already suspected, the SELECT doesn't work because it seems as though the INSERT statement isn't executed so there is nothing in the Order_Detail table.

Parfait Over a year ago

I believe multiiple SQL statements may not be supported in one chunk. Try running multiple dbSendQuery then run SELECT by itself.

Cinji18 Over a year ago

Thank you for letting know. Looks like this works now.

Collectives™ on Stack Overflow

R Markdown: Creating a SQL table then output it as a dataframe

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related