R and MySQL

Sometimes you need to talk to a database using R. I’ve put together a R script that shows you how to query data from a MySQL database and insert data back.

library(RMySQL)

# loads the MySQL driver
drv = dbDriver("MySQL") 

# create a connection to the database
con = dbConnect(drv, dbname = "your database name", 
                host = "where your DB is hosted", 
                port = 5432, # number, change to your port
                user="your username", password="your password")

# list all tables in the database
dbListTables(con)

# list all fileds in a particular table
dbListFields(con, "table")

# remove a table
dbRemoveTable(con, "table")

# delete all rows from a table
dbGetQuery(con, "DELETE FROM table")

# fetch all records and fields from a table
res = dbSendQuery(con, "SELECT * FROM table")
df = dbFetch(res) # returns a data frame
dbClearResult(res) # frees all resources associated with a result set

# create a table 'mydata' of 2 fields: name and number of type text and double respectively
dbGetQuery(con, "CREATE TABLE mydata (name text, number double)")  

# insert a row of data into the table mydata
dbGetQuery(con, "INSERT INTO mydata VALUES('fred',7)") 

# you can also change the order of the fields when inserting 
dbGetQuery(con, "INSERT INTO mydata (number, name) VALUES(7,'fred')")

# you can also add multiple records at one time
dbGetQuery(con, "INSERT INTO mydata VALUES('tim',12),('sue',9)")

# close the connection and unload the driver
dbDisconnect(con)
dbUnloadDriver(drv)

Categories