[R] DB Connection with MySQL

R with MySQL

R-DB Connect mysql DBI RMySQL

Send Data to SQL Table

This is simple code to import data from mySQL DB

# install.packages("DBI", dependencies = TRUE)
library(DBI)

# System Settings
# Sys.setenv(PKG_CPPFLAGS = "-I//usr/local/mysql-8.0.12-macos10.13-x86_64/include/")
# Sys.setenv(PKG_LIBS="-L/usr/local/mysql-8.0.12-macos10.13-x86_64/lib/ -lmysqlclient")

install.packages("RMySQL", type = "source")

library(DBI)
library(RMySQL)

# Connect to a public database that I'm running on Google's
# cloud SQL service. It contains a copy of the data in the
# datasets package.
con <-  dbConnect(RMySQL::MySQL(),
                  username = "root",
                  password = "password",
                  host = "localhost",
                  port = 3306,
                  dbname = "dbname"
)

data("iris")

dbWriteTable(con, 
             name="iris", 
             value=iris, 
             append = TRUE,
             row.names=FALSE)
# Error in .local(conn, statement, ...) : could not run statement: The used command is not allowed with this MySQL version
    # Then go to terminal access to mysql
    # SET GLOBAL local_infile = 1; (Temporary and no Security)

# If you get error like below
# Error in .local(drv, ...) : 
  # Failed to connect to database: Error: Plugin caching_sha2_password could not be loaded: dlopen(lib/mariadb/plugin/caching_sha2_password.so, 2): image not found

# Run a query
dbGetQuery(con, "SELECT * FROM iris")

# It's polite to let the database know when you're done
dbDisconnect(con)
#> [1] TRUE