CODEWITHSUNDEEP
rrmysql
tumultous_rooster
tumultous_rooster

Reputation: 12570

dbSendQuery only returning 500 rows when using RMySQL in R

I am using RMySQL to query a database.

I used

df <- fetch(dbSendQuery(con, "SELECT some_col from some_table"))

and got back this message:

Error in fetch(dbSendQuery(con, "SELECT some_col from some_table")) : 
  error in evaluating the argument 'res' in selecting a method for function 'fetch': Error in .local(conn, statement, ...) : 
  connection with pending rows, close resultSet before continuing

Strangely, df was actually created; 500 obs. of 1 variables, and it did give me back what I expected for these 500.

What does this error mean, and how can I get all the rows in my table?

FOLLOWUP

I found out that I can slurp up a whole table by using

dbReadTable(con, 'some_table')

Upvotes: 5

Views: 7030

Answers (2)

sarah
sarah

Reputation: 229

This link helped so much to use the RMySQL package in a useful way : Accessing MySQL through R

It includes also that you have to pass the parameter n=-1 to the fetch function to retrieve all data and not only the first 500 rows which is set by default in the fetch function.

I hope that helps you like It did for me.

Other useful links:

R interface to the MySQL database

Accessing MySQL through R

Upvotes: 0

scribbles
scribbles

Reputation: 4349

What you're seeing is normal behavior. The RMySQL implementations retrieves only n records, and if n is missing it only returns up to fetch.default.rec as specified in the call to MySQL (500 by default).

To return all of the rows use n = -1.

Also, it's useful to assign a name to your entire query string and then just pass that name to your fetch() statement. 

drv <- dbDriver("MySQL")
con <- dbConnect(drv, user = "username", password="password1", 
                 host = "localhost", dbname="database")
res <- dbSendQuery(con, statement = paste(
                      "SELECT some_column1, some_column2",
                      "FROM some_table", 
                      "WHERE some_column2 >= x",
                      "ORDER BY some_column1"))
data1 <- fetch(res, n = -1)

Upvotes: 11

Related Questions