Advanced usage of R with RMySQL

R called inside other applications

One thing that occasionally bothers me is how to refer a variable inside a function call, now it becomes more prominent when I try to use a parameter passed into a function from a RMySQL query. Here is the deal:

An example in RMySQL from RBlog

#Load the package

# 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="")

# 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;")

My own database

mirList <- c("Adrenal", "Brainstem")
numOfTis <- length(mirList)	
query <- function(...) dbGetQuery(mydb, ...)
query("select rnMiR as mir, tissue as tis from TisSpeMiRs where tissue in ("Adrenal", "Brainstem") and ctrCode = 2 limit 2 ;") ## this works
query("select rnMiR as mir, tissue as tis from TisSpeMiRs where tissue in eval(mirList) and ctrCode = 2 limit 2 ;") ## this does NOT work!!

Well, some “expert” posted Advanced R usage
Single quote vs. double quote is here

Learning step 1

  • Option 1
  • min.v<-5 max.v<-10 cat("You entered ", """, min.v, " ", max.v,""", sep="")
  • Option 2
  • cat("You entered ", '"', min.v, " ", max.v,'"', sep="")
  • Option 3
  • options(useFancyQuotes=FALSE) cat("You entered ", dQuote(paste(min.v, max.v)), sep="")

    Learning step 2

    Posted in Uncategorized | Leave a comment

    MongoDB with Java and Python

    In order to work on project MongoDB with Java, there are a few set up

  • Install MongoDB
  • Make sure Maven is installed
  • Maven needs JAVA_HOME is set up property.
  • Posted in Uncategorized | Leave a comment

    Virtualenv and Virtualwrapper in MacOX

    I learned this is good doc

    Posted in Uncategorized | Leave a comment

    Graphical Database Schema

    In order to get approval, we need a graphical diagram. Pierre suggest schemaspy. It turns out that a couple of dependencies were required:

  • Apparently, SchemaSpy
  • It needs Graphviz installed
  • Also, database driver in our case JDBC-MySQL driver
  • Luckily, I have it installed on my windows machine with ColdFusion earlier, located at
  • C:ColdFusion2016cfusionlibmysql-connector-java-5.1.38-bin.jar
  • Migrate mysql DB from linux (wine) to windows desktop. Before doing this, I needed to drop all tables in the database first. It turns out a scripting way works for linux. For windows, it is easier to drop the database and re-create the database.

    [li11@ehscmplp11/wine ~]$ mysqldump -u li11 -ppassword mirDB > ~/mirDB.sql
    C:Usersli11>mysql -u root -ppassword ratemirs < x:mirDB.sql

    My command to run SchemaSpy was:

    java -jar DownloadsschemaSpy_5.0.0.jar -dp C:ColdFusion2016cfusionlibmysql-connector-java-5.1.38-bin.jar -t mysql -db RATEmiRs -host localhost -u li11 -p nopassword -o X:project2016microRNADBdiagram

    Interesting enough, the command involves both “windows” local and “network” drive.

    Posted in Uncategorized | Leave a comment

    Protected: LIMS development

    This content is password protected. To view it please enter your password below:

    Posted in Uncategorized | Enter your password to view comments.

    Learning Python Web programming

    The most common frame work Django offers great features for web-programming with python.

    I have to take care of pip installation on my mac

    easy_install is the way to go, yeh.

    Here is another help link for django web-development

    It is not surprising that nothing will work as simple as it seems. Well, here the first verification failed:

    python runserver


    maximum recursion depth exceeded

    Someone kindly posted answer. Well, my is empty.

    The answer is “python” had bugs (working with django), so upgrading python installation solved the problem.

    Using MySQL in Django

    My database is mysql, so I was tying to incorporate MySQL for django. As usual, nothing works as I expected. When I ran python manage migrate, I got error

      File "/Library/Python/2.7/site-packages/django/db/backends/mysql/", line 28, in 
        raise ImproperlyConfigured("Error loading MySQLdb module: %s" % e)
    django.core.exceptions.ImproperlyConfigured: Error loading MySQLdb module: No module named MySQLdb

    So, the problem was the “MySQLdb” module as someone pointed out. Well, again the installation itself reached error

    RuntimeError: maximum recursion depth exceeded
    Traceback (most recent call last):
      File "", line 7, in 
      File "/Users/li11/django/mysql/MySQL-python-1.2.4b4/", line 145, in use_setuptools
        return _do_download(version, download_base, to_dir, download_delay)
      File "/Users/li11/django/mysql/MySQL-python-1.2.4b4/", line 125, in _do_download
        _build_egg(egg, tarball, to_dir)
      File "/Users/li11/django/mysql/MySQL-python-1.2.4b4/", line 116, in _build_egg
        raise IOError('Could not build the egg.')
    IOError: Could not build the egg.

    Using PostgreSQL in Django

    It turns out that hooking up django with mysql is not that easy, I have got help from Andy who gave me their project on github repository. Well, they sticked with postgresql version 9.4+.

    My mac has 9.0, so I have to upgrade it to 9.4+. It seems that provides the easiest solution to mac user.

    Next, I need to set path for postgresql

    Using the Python virtualenv and virtualenvwrapper

    Andy’s project needs these prerequisite, and I found this help doc for virtual environment.

    Missing llvm-gcc-4.2 on Mac OS

    My configuration failed at

    pip install -r requirements/dev.txt

    With the following error message

        llvm-gcc-4.2 -fno-strict-aliasing -fno-common -dynamic -g -Os -pipe -fno-common -fno-strict-aliasing -fwrapv -mno-fused-madd -DENABLE_DTRACE -DMACOSX -DNDEBUG -Wall -Wstrict-prototypes -Wshorten-64-to-32 -DNDEBUG -g -fwrapv -Os -Wall -Wstrict-prototypes -DENABLE_DTRACE -arch i386 -arch x86_64 -pipe -DPSYCOPG_DEFAULT_PYDATETIME=1 -DPSYCOPG_VERSION="2.5.5 (dt dec pq3 ext)" -DPSYCOPG_EXTENSIONS=1 -DPSYCOPG_NEW_BOOLEAN=1 -DHAVE_PQFREEMEM=1 -DPG_VERSION_HEX=0x09000D -DPSYCOPG_EXTENSIONS=1 -DPSYCOPG_NEW_BOOLEAN=1 -DHAVE_PQFREEMEM=1 -I/System/Library/Frameworks/Python.framework/Versions/2.7/include/python2.7 -I. -I/usr/include -I/usr/include/postgresql/server -c psycopg/psycopgmodule.c -o build/temp.macosx-10.7-intel-2.7/psycopg/psycopgmodule.o
        unable to execute llvm-gcc-4.2: No such file or directory
        error: command 'llvm-gcc-4.2' failed with exit status 1

    Found a bunch of posting. After upgrading ox-10.11, all works out just fine.

    People reported same errors
    This one needs compiling from github source
    Apple Store is the best answer
    Posted in Uncategorized | Leave a comment

    My learning notes

    Note 1: 2/12/2016

    Today,I was looking at the SNP association analysis. I found this write up by Eric Minikel is very helpful.

    Matti Pirinen from Finland has a good website also.

    Posted in Uncategorized | Leave a comment

    Working with miRBase v21

    This is an embarrassed note as I could not download miRBase using ftp. Hopefully, I can figure it out somehow and keep a note for future use.

    Scenario I: I wanted to download database files from mirbase ftp server

    I was at miRBase download page
    Here is the README file
    I clicked Go to the FTP site to download al file.
    From my linux window, " ftp" gets me "unknown host" error
    What did I do wrong??

    Well, the fix is simple, the serve name was only haha

    user: anonymous

    Frank created a database called miRBaseV21 on the “wine” server

    mysql> show grants for li11;
    | GRANT ALL PRIVILEGES ON `mirDB`.* TO ‘li11’@’%’
    | GRANT ALL PRIVILEGES ON `methylSNPdb`.* TO ‘li11’@’%’
    | GRANT ALL PRIVILEGES ON `miRBaseV21`.* TO ‘li11’@’%’
    | GRANT ALL PRIVILEGES ON `mus_musculus_core_73_38`.* TO ‘li11’@’%’

    Scenario II: I need to create the schema downloaded from mirbase.

    It turns out that "mysqldump" does not work following my earlier post, it could be that I do NOT have root privillege
    Followed this link, I used "mysql> source ~/mysql_jyl/miRBase/miRBaseV21/tables.sql" ; 

    In the end, I imported all the data into the database:

    mysql> load data local infile ‘~/mysql_jyl/miRBase/miRBaseV21/wikipedia.txt’ into table wikipedia ;

    Now, I am working on R scripts for the analytical components. At first, I have to work on connecting to the mysql database. Here are a few lines of code to connect:

    mydb = dbConnect(MySQL(), user='li11', password='password', dbname='mirDB', host='')
    dbListFields(mydb, 'resCenter')

    It turns out that it is quite simple and straightforward with the following help links:

    Main link at R-blogger
    A brief tutorial post by Manoj Kumar
    Posted in Uncategorized | Leave a comment

    Working with my outlook client

    With the new computer, I could NOT find the “MyOwnArchive”.

    Through very hard effort, I got it done

  • Go to "File"
  • Choose "Account Settings"
  • Choose "Data Files"
  • Choose "Add..."
  • Choose default location "MyOwnArchive.pst"
  • It turns out, this “MyOwnArchive.pst” has been backed up by the server and all my “local” folders are recovered. Therefore, I decided to document this with a successful screenshot.outlook-myownarchive

    Posted in Uncategorized | Leave a comment

    Protected: Python note

    This content is password protected. To view it please enter your password below:

    Posted in Uncategorized | Enter your password to view comments.