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



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


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

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"