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.


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.


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',

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.


  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

  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.

  3. haha - well said!

  4. Which R version is compatible with RMySQL ?

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

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

  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