r_code

SQL Query

参考 Schouwenaars (2016)

query

# Connect to the database
library(DBI)
con <- dbConnect(RMySQL::MySQL(),
                 dbname = "tweater",
                 host = "courses.csrrinzqubik.us-east-1.rds.amazonaws.com",
                 port = 3306,
                 user = "student",
                 password = "datacamp")

# Import tweat_id column of comments where user_id is 1: elisabeth
elisabeth <- dbGetQuery(con, "select tweat_id from comments where user_id = 1")

# Print elisabeth
elisabeth

##   tweat_id
## 1       87
## 2       49
## 3       77
## 4       77

参考 rmarkdown

select tweat_id from comments where user_id = 1

tweat_id
87
49
77
77

4 records

fetch

… but make sure to remember this technique when you’re struggling with huge databases! DataCamp

res <- dbSendQuery(con, "SELECT * FROM comments WHERE user_id > 4")

dbGetInfo(res)

## $statement
## [1] "SELECT * FROM comments WHERE user_id > 4"
## 
## $isSelect
## [1] 1
## 
## $rowsAffected
## [1] -1
## 
## $rowCount
## [1] 0
## 
## $completed
## [1] 0
## 
## $fieldDescription
## $fieldDescription[[1]]
## NULL

dbFetch(res, n = 2)

##     id tweat_id user_id message
## 1 1022       87       7   nice!
## 2 1000       77       7  great!

dbFetch(res)

##     id tweat_id user_id  message
## 1 1011       49       5  love it
## 2 1010       88       6    yuck!
## 3 1030       75       6 so easy!

dbClearResult(res)

## [1] TRUE

res只是一个命令，反馈的和 dbGetQuery 不同。

todo 查看 RODBC 有没有类似的函数。 https://cran.r-project.org/web/packages/RODBC/RODBC.pdf 需要自己看。

Schouwenaars, Filip. 2016. “Importing Data in R (Part 2).” 2016. <https://www.datacamp.com/courses/importing-data-in-r-part-2>.