Hugo | Bootstrap | highlightjs | blogdown | Google fonts
github | travis-ci
|
docker hub
Amazon S3 | Amazon Route 53
Copyright © 2019 tomis9
data science tutorials and snippets prepared by tomis9
RMariaDB is an R package available at CRAN that let’s you connect to various SQL databases easily.Why did I switch from RMySQL to RMariaDB? Because of invalid long integer format when downloading data from a database. A pretty nasty bug in RMySQL, which was resolved in RMariaDB.
First you have to create a connection to the database. Remember that you should not provide any credentials in your scripts! Keep them in a separate file or in envorionment variables.
con <- RMariaDB::dbConnect(
drv = RMariaDB::MariaDB(),
user = "",
password = "",
host = "",
dbname = ""
)After you are done with working on your database data, you should close the connection with:
RMariaDB::dbDisconnect(con)query <- "delete from my_table;"
RMariaDB::dbSendQuery(con, query)query <- "select * from my_table;"
rs <- RMariaDB::dbSendQuery(con, query)
tab <- RMariaDB::dbFetch(rs, n = -1)
RMariaDB::dbClearResult(rs)
# equivalent to select * from my_table;
d <- RMariaDB::dbReadTable(con, "my_table")# data.frame's colnames should be the same as in the database
dt <- data.frame(id = 1:10, text = letters[1:10])
RMariaDB::dbWriteTable(
conn = con,
name = "my_table",
value = dt,
overwrite = FALSE,
append = TRUE,
row.names = F
)tab_name <- dplyr::tbl(con, "tab_name")
tab <- dplyr::collect(tab_name) # you can `library(dplyr)` and then `tab_name %>% collect()`dplyr::db_insert_into(con, "my_table", tab)