Quick access to MySQL using R

Package RMySQL turns R to a powerful MySQL client. Below are the simple steps to use it:

Install and load RMySQL:

install.packages("RMySQL")
library(RMySQL)

Connect to a MySQL database:

connection = dbConnect(MySQL(), host = "localhost", username = "root", password ="password", dbname = "databasename")

If you do not know the database name, you can omit it for now and use the following to find out all databases assigned to this user:

dbGetQuery(connection, "show databases")

To send queries:

# This sends the query but the result stays in MySQL server.
query = dbSendQuery(con, "query")  

# Retrieve n rows of the result, and put it in a data.frame. If n is set to -1, it will retrieve all rows.
data = dbFetch(query, n)  

# Clear the query on MySQL server side.
dbClearResult(query) 

Another function allows you to do all the three steps – query, fetch and clear at once:

data = dbGetQuery(con, "query")

A good practice is always remembering to disconnect:

dbDisconnect(connection)