R Language I/O for database tables Reading Data from MySQL Databases


Example

General

Using the package RMySQL we can easily query MySQL as well as MariaDB databases and store the result in an R dataframe:

library(RMySQL)

mydb <- dbConnect(MySQL(), user='user', password='password', dbname='dbname',host='127.0.0.1')

queryString <- "SELECT * FROM table1 t1 JOIN table2 t2 on t1.id=t2.id"
query <- dbSendQuery(mydb, queryString)
data <- fetch(query, n=-1) # n=-1 to return all results

Using limits

It is also possible to define a limit, e.g. getting only the first 100,000 rows. In order to do so, just change the SQL query regarding the desired limit. The mentioned package will consider these options. Example:

queryString <- "SELECT * FROM table1 limit 100000"