1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
|
## ----setup, include=FALSE-----------------------------------------------------
knitr::opts_chunk$set(
echo = TRUE,
error = Sys.getenv("IN_PKGDOWN") != "true" || (getRversion() < "3.5")
)
## -----------------------------------------------------------------------------
library(DBI)
con <- dbConnect(
RMariaDB::MariaDB(),
host = "relational.fel.cvut.cz",
port = 3306,
username = "guest",
password = "ctu-relational",
dbname = "sakila"
)
dbListTables(con)
dbDisconnect(con)
## ----eval = FALSE-------------------------------------------------------------
# con <- dbConnect(
# RMariaDB::MariaDB(),
# host = "relational.fel.cvut.cz",
# port = 3306,
# username = "guest",
# password = keyring::key_get("relational.fel.cvut.cz", "guest"),
# dbname = "sakila"
# )
## -----------------------------------------------------------------------------
con <- dbConnect(
RMariaDB::MariaDB(),
host = "relational.fel.cvut.cz",
port = 3306,
username = "guest",
password = "ctu-relational",
dbname = "sakila"
)
dbListFields(con, "film")
## -----------------------------------------------------------------------------
df <- dbReadTable(con, "film")
head(df, 3)
## -----------------------------------------------------------------------------
df <- dbGetQuery(con, "SELECT film_id, title, description FROM film WHERE release_year = 2006")
head(df, 3)
## -----------------------------------------------------------------------------
df <- dbGetQuery(con, "SELECT film_id, title, description FROM film WHERE release_year = 2006 AND rating = 'G'")
head(df, 3)
## ----message=FALSE------------------------------------------------------------
library(dplyr)
lazy_df <-
tbl(con, "film") %>%
filter(release_year == 2006 & rating == "G") %>%
select(film_id, title, description)
head(lazy_df, 3)
## -----------------------------------------------------------------------------
dbDisconnect(con)
|
