Thursday, May 5, 2011

Accessing MySQL through R

Connecting to MySQL is made very easy with the RMySQL package. To connect to a MySQL database simply install the package and load the library.

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


Connecting to MySQL:
Once the RMySQL library is installed create a database connection object.

mydb = dbConnect(MySQL(), user='user', password='password', dbname='database_name', host='host')


Listing Tables and Fields:
Now that a connection has been made we list the tables and fields in the database we connected to.

dbListTables(mydb)

This will return a list of the tables in our connection.

dbListFields(mydb, 'some_table')

This will return a list of the fields in some_table.

Running Queries:
Queries can be run using the dbSendQuery function.

dbSendQuery(mydb, 'drop table if exists some_table, some_other_table')

In my experience with this package any SQL query that will run on MySQL will run using this method.

Making tables:
We can create tables in the database using R dataframes.

dbWriteTable(mydb, name='table_name', value=data.frame.name)


Retrieving data from MySQL:
To retrieve data from the database we need to save a results set object.

rs = dbSendQuery(mydb, "select * from some_table")

I believe that the results of this query remain on the MySQL server, to access the results in R we need to use the fetch function.

data = fetch(rs, n=-1)

This saves the results of the query as a data frame object. The n in the function specifies the number of records to retrieve, using n=-1 retrieves all pending records.

7 comments:

  1. wonderful information, I had come to know about your blog from my friend nandu , hyderabad,i have read atleast 7 posts of yours by now, and let me tell you, your website gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanks a ton once again, Regards, MySql online trainingamong the MySql in Hyderabad. Classroom Training in Hyderabad India

    ReplyDelete
  2. The above comment is slightly disguised advertising spam. Such scum are a burden to the internet. At least it tells you who not to do business with. However, I and many others think this post by tsperry is a particularly useful post and we are actually very grateful.

    ReplyDelete
  3. haha - well said!

    ReplyDelete
  4. Which R version is compatible with RMySQL ?

    ReplyDelete
  5. Thanks. Very efficient way to access large mysql-tables in R which otherwise may not even load in R.

    ReplyDelete
  6. How to insert value from textbox to database table in Rshiny

    ReplyDelete
  7. Thank you for writing this article, jsut one change i would suggest "mydb = dbConnect(MySQL(), user='user', password='password', dbname='database_name', host='host')" in this query add port as last argument with port number mentioned. Thanks again

    ReplyDelete