a-query-the-database.Rmd
library(sensoreport)connect_db(): Connect to the sensory database
With connect_db(), you can open a connection to query
the sensory database. The database is stored in the package for
demonstration purposes.
con_db <- connect_db()
# List tables in the sensory database
DBI::dbListTables(con_db)
#> [1] "HEDONIC" "PRODUCTS" "SENSORY" "SESSIONS"
# Read the sensory data
dplyr::tbl(con_db, dplyr::sql("SELECT * FROM SENSORY LIMIT 10"))
#> # Source: SQL [10 x 4]
#> # Database: sqlite 3.41.2 [/home/runner/work/_temp/Library/sensoreport/database/sensory_db.sqlite]
#> PRODUCT SESSION CONSUMER GROUP
#> <chr> <chr> <chr> <chr>
#> 1 PROD1 SESS2301 1 pear
#> 2 PROD1 SESS2301 2 citrus
#> 3 PROD1 SESS2301 3 sugar
#> 4 PROD1 SESS2301 4 rasperry
#> 5 PROD1 SESS2301 5 flowers
#> 6 PROD1 SESS2301 6 flowers
#> 7 PROD1 SESS2301 7 rasperry
#> 8 PROD1 SESS2301 8 vanilla
#> 9 PROD1 SESS2301 9 sugar
#> 10 PROD1 SESS2301 10 citrus
# Read the hedonic data
dplyr::tbl(con_db, dplyr::sql("SELECT * FROM HEDONIC LIMIT 10"))
#> # Source: SQL [10 x 4]
#> # Database: sqlite 3.41.2 [/home/runner/work/_temp/Library/sensoreport/database/sensory_db.sqlite]
#> PRODUCT SESSION CONSUMER SCORE
#> <chr> <chr> <chr> <int>
#> 1 PROD1 SESS2301 1 1
#> 2 PROD1 SESS2301 2 2
#> 3 PROD1 SESS2301 3 1
#> 4 PROD1 SESS2301 4 1
#> 5 PROD1 SESS2301 5 2
#> 6 PROD1 SESS2301 6 1
#> 7 PROD1 SESS2301 7 1
#> 8 PROD1 SESS2301 8 1
#> 9 PROD1 SESS2301 9 3
#> 10 PROD1 SESS2301 10 3
# Read the sessions data
dplyr::tbl(con_db, dplyr::sql("SELECT * FROM SESSIONS"))
#> # Source: SQL [6 x 5]
#> # Database: sqlite 3.41.2 [/home/runner/work/_temp/Library/sensoreport/database/sensory_db.sqlite]
#> SESSION DATE DEPARTMENT COUNTRY LEADER
#> <chr> <chr> <chr> <chr> <chr>
#> 1 SESS2301 2023-01-10 SENSO France Diane D
#> 2 SESS2302 2023-04-15 SENSO US Murielle B
#> 3 SESS2303 2023-04-30 SENSO US Margot B
#> 4 SESS2304 2023-05-21 SENSO France Swann F
#> 5 SESS2305 2023-06-01 SENSO US Estelle P
#> 6 SESS2306 2023-06-28 SENSO France Florence M
# Read the products data
dplyr::tbl(con_db, dplyr::sql("SELECT * FROM PRODUCTS LIMIT 10"))
#> # Source: SQL [10 x 3]
#> # Database: sqlite 3.41.2 [/home/runner/work/_temp/Library/sensoreport/database/sensory_db.sqlite]
#> PRODUCT INFO BRAND
#> <chr> <chr> <chr>
#> 1 PROD1 Lorem ipsum dolor sit amet Brand A
#> 2 PROD2 Lorem ipsum dolor sit amet Brand B
#> 3 PROD3 Lorem ipsum dolor sit amet Brand C
#> 4 PROD4 Lorem ipsum dolor sit amet Brand D
#> 5 PROD5 Lorem ipsum dolor sit amet Brand A
#> 6 PROD6 Lorem ipsum dolor sit amet Brand B
#> 7 PROD7 Lorem ipsum dolor sit amet Brand C
#> 8 PROD8 Lorem ipsum dolor sit amet Brand D
#> 9 PROD9 Lorem ipsum dolor sit amet Brand A
#> 10 PROD10 Lorem ipsum dolor sit amet Brand B
# Disconnect
DBI::dbDisconnect(con_db, shutdown = TRUE)