Query MySQL with RMySQL

I’m currently using this more frequently on day to day basis and just wanted to document it here. I use these steps to connect to our live MySQL database from R all the time, and the setup is so simple. Follow my previous blog post on installing RMySQL package, connect to a database (here any MySQL instance), set up a function to make querying easier, and query the database to return results as a data frame.

# First lets make sure we Load the package

>library(RMySQL)

# Then the next step is to Set up a connection to the database. I’m using a "localhost" just for this example blog post, but feel free to use any host IPs to your servers. I'm found of running ?Functionname() to get details - so feel free to run ?MySQL() what most functions do.

>conn <- dbConnect(MySQL(), user="xxxxx", password="xxxxxx",dbname="refugees" host="localhost")

# Here is where the main fun begins, the Function to make it easier to query

>query <- function(...) dbGetQuery(conn, ...)

# Run your query at this point through R and that’s it.

>query("SELECT name, age FROM refugees;")

Correction: Due to change in R version the above stopped working and i was getting this error

"Error in mysqlNewConnection(drv, ...) :

  RS-DBI driver: (Failed to connect to database: Error: Access denied for user 'root'@'localhost' (using password: YES)

)"

Fix below:
Set the driver first

> drv = dbDriver("MySQL")

>conn <- dbConnect(drv, user="xxxxx", password="xxxxxx",dbname="refugees" host="localhost")

worked and the silly error was fixed.