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)