R loop to query all tables within a database

Question

I am new to R and have a database, DatabaseX which contains multiple tables a,b,c,d,etc. I want to use R to count number of rows for a common attribute message_id among all these tables and store it separately.

I can count message_id for all tables using below code:

list<-dbListTables(con)
# print list of tables for testing
print(list)
for (i in 1:length(list)){
  query <- paste("SELECT COUNT(message_id) FROM ",list[i], sep = "")
  t <- dbGetQuery(con,query)
}
print(t)

This prints :

### COUNT(message_id)
## 1  21519

but I want to keep record of count(message_id) for each individual table. So for example table a = 200, b = 300, c = 500, and etc.

any suggestions how to do this?

Have you tried using a nested loop. I would suggest writing a function that does the query and then putting that function in a recursive loop. — prog_24
– prog_24, Commented Jan 15, 2016 at 9:23
@TimBiegeleisen I mean to store the value in order to use it for future, I want to be able to draw a histogram to show number of message_id count in each table — Rain Man
– Rain Man, Commented Jan 15, 2016 at 9:29
@kaiten65 can you please explain a little more? I am new to R and not sure how to do this — Rain Man
– Rain Man, Commented Jan 15, 2016 at 9:32
@RainMan check my answer below. I have slightly modified your code but the principle is the same. You can achieve more with R when using functions. — prog_24
– prog_24, Commented Jan 15, 2016 at 9:48

Tim Biegeleisen · Accepted Answer · 2016-01-15 09:47:00Z

1

As @kaiten65 suggested, one option would be to create a helper function which executes the COUNT query. Outside of your loop I have defined a numeric vector counts which will store the number of records for each table in your database. You can then perform descriptive stats on the tables along with this vector of record counts.

doCountQuery <- function(con, table) {
    query <- paste("SELECT COUNT(message_id) FROM ", table, sep = "")
    t     <- dbGetQuery(con, query)

    return(t)
}

list <- dbListTables(con)
counts <- numeric(0)       # this will store the counts for all tables

for (i in 1:length(list)) {
    count <- doCountQuery(con, list[i])
    counts[i] <- count[[1]]
}

edited Jan 15, 2016 at 9:47

answered Jan 15, 2016 at 9:36

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Rain Man Over a year ago

so the value of message_id count is stored in counts[i] for each table? I tried to do a print(counts[i] statement in the loop and I get this output ## [[1]] ## [1] 3 ## ## [[1]] ## [1] 12172 ## ## [[1]] ## [1] 2308 ## ## [[1]] ## [1] 3273 ## ## [[1]] ## [1] 21519 so there is no way to access the count for each individual table in future?

Tim Biegeleisen Over a year ago

Try using count[[1]], which should give you the (single) field coming back from the SQL query.

Rain Man Over a year ago

when printing the value, I get this ## [1] 3 ## [1] 12172 ## [1] 2308 ## [1] 3273 ## [1] 21519I think I am confusing this with arrays, doesn't this mean value of [1] is changing ? can I output [1] 3 [2] 12172 [3] 2308?

Tim Biegeleisen Over a year ago

[1] is just an index, the number which comes after it is the number of records for that particular table. You can plot the vector of tables names against counts to get the output you want.

Rain Man Over a year ago

I added a few more tables and when I tried to run the query again I got this error : error in .local(conn, statement,...) : could not run statement. The error occurs in the for loop, any suggestion?

|

prog_24 · Accepted Answer · 2016-01-15 09:46:19Z

0

You can use functions to achieve what you need. For instance

readDB <- function(db, i){
     query <- paste("SELECT COUNT(message_id) FROM ",db, sep = "")
     t <- dbGetQuery(con,query)
     return(print(paste("Table ", i, " count:", t)
     }

list<-dbListTables(con)
for (i in 1:length(list)){
    readDB(list[i]);
}

This should print your list recursively but the actual code is in a nice editable function. Your output will be

 "Table 1 count: 2519"
 "Table 2 count: ---- "

More information on R functions here: http://www.statmethods.net/management/userfunctions.html

answered Jan 15, 2016 at 9:46

prog_24

7991 gold badge13 silver badges27 bronze badges

Collectives™ on Stack Overflow

R loop to query all tables within a database

2 Answers 2

10 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related