Getting and cleaning data - MySQL

Question 1

ESTABLISH & NAME A CONNECTION

Answer 1

Use a variable and assign the DB connection to it while establishing it::

a <- dbConnect(MySQL(), user= “ “, db =” “, host=” “)

MySQL() ; the type of DB

db ; the database aimed at (optional)

host ; the database name

Question 2

QUERYING THE MySQL INSTANCE FOR ITS DATABASES

Answer 2

The variable to which the dbConnect is assigned is the connection handle and is used as a pointer:

result <- dbQuery(a, “show databases;”)

The queries are in SQL specifically the MySQL form.

The query can be assigned to a variable which will server to launch it:

query <- dbSendQuery(b, “select * from … where …”)
affyMis <- fetch(query)

Question 3

EXPLORING TABLES STRUCTURE OF A SPECIFIC DATABASE

Answer 3

From the database listing select whatever database strikes your fancy.

b <- dbConnect(MySQL(), user= “ “ , db=”hg19”, host= “ “)

List the DB’s tables:

allTables <- dbListTables(b)

Remember to use the variable assigned to the connection; its handle.

allTables is a vector so its length should give use the number of tables available:

length(allTables)

allTables[1:10] # extracts the first ten tables names

Question 4

DISCOVERING A TABLE’S FIELDS

Answer 4

Listing the fields of a given table is the next step to extracting specific values:

dbListFields(b, “name_of_ftable”)

Question 5

READING FROM TABLES INTO MEMORY

Answer 5

Classic approach; read the table content to create a dat set/ frame:

df <- dbReadTable(b, “name_of_table”)

head (df)

Question 6

ALWAYS DISCONNECT WHEN YOU ARE FINISHED

Answer 6

dbDisconnect(b)

Question 7

REFERENCES FOR MySQL COMMANDS

REFERENCES FOR R -MySQL COMMANDS

Answer 7

essential for commands to work in R:

install.packages(“RMySQL”)

References:

http: //www.pantz.org/software/mysql/mysqlcommands.html
http: //www.r-bloggers.com/mysql-and-r/

Only use SELECT, never add, join or delete.