This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#Install the package if you've never done so | |
install.packages("RMySQL") | |
#Load the package | |
library(RMySQL) | |
# Set up a connection to your database management system. | |
# I'm using the public MySQL server for the UCSC genome browser (no password) | |
mychannel <- dbConnect(MySQL(), user="genome", host="genome-mysql.cse.ucsc.edu") | |
# Function to make it easier to query | |
query <- function(...) dbGetQuery(mychannel, ...) | |
# Get the UCSC gene name, start and end sites for the first 10 genes on Chromosome 12 | |
query("SELECT name, chrom, txStart, txEnd FROM mm9.knownGene WHERE chrom='chr12' LIMIT 10;") | |
# Results are returned as a data.frame: | |
# name chrom txStart txEnd | |
# 1 uc007mwj.2 chr12 3235525 3250374 | |
# 2 uc007mwg.2 chr12 3235790 3239112 | |
# 3 uc007mwh.2 chr12 3235790 3239288 | |
# 4 uc007mwi.2 chr12 3235790 3250374 | |
# 5 uc007mwk.1 chr12 3236610 3249997 | |
# 6 uc011yjq.1 chr12 3237284 3241410 | |
# 7 uc007mwl.2 chr12 3247427 3309969 | |
# 8 uc007mwm.1 chr12 3365131 3406494 | |
# 9 uc007mwn.1 chr12 3365131 3406494 | |
# 10 uc007mwp.2 chr12 3403882 3426747 |
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