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.
Getting Genetics Done by Stephen Turner is licensed under a Creative Commons Attribution-NonCommercial 3.0 Unported License.
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.
ReplyDeleteGeorge, 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.
ReplyDeleteHello Stephen!
ReplyDeleteCan 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.
Try something like:
ReplyDeletequery("SELECT * FROM table;")$yourvariable
or
query("SELECT * FROM table;")[[1]]
Hi Stephen!
ReplyDeleteI'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