Thursday, December 15, 2011

Query a MySQL Database from R using RMySQL

I use this all the time, and the setup is dead simple. Follow the code below to load the RMySQL package, connect to a database (here the UCSC genome browser's public MySQL instance), set up a function to make querying easier, and query the database to return results as a data frame.

5 comments:

  1. Hello Stephen, I´d like to talk to you about how you installed RMySQL, I´m a windows user and this packege is not compiled for windows yet. Could you help me please? My name is George.

    ReplyDelete
  2. George, see if this helps. If not, I'll see if I can get Will (a Windoze guy) to post about how to do this. You'll have to use the RODBC package, and set up a connection to your database server through My Computer --> Administrative Tools --> Data Sources. But it's been way too long since I've done this, and I don't have a PC to test it out on or give you any screenshots. RMySQL is definitely easier and more "portable" if you can get it to work.

    ReplyDelete
  3. Hello Stephen!
    Can I ask you a question?
    When you do a query to a database, like you did above, is it possible to pass the data directly to a vector?
    I wanna use the data and create a plot/graphic.

    ReplyDelete
  4. Try something like:

    query("SELECT * FROM table;")$yourvariable

    or


    query("SELECT * FROM table;")[[1]]

    ReplyDelete
  5. Hi Stephen!

    I'm working on a project to regress NFL data in R but we have a database we are pulling from in MySQL. I'm following your instructions, but what do I use instead of "genome" and "genome...edu" for my user and host?

    Thanks!
    Andrew

    ReplyDelete

Note: Only a member of this blog may post a comment.

Creative Commons License
Getting Genetics Done by Stephen Turner is licensed under a Creative Commons Attribution-NonCommercial 3.0 Unported License.