R: Summary of SQL Tables

Question

I am working with the R programming language.

Normally, when I want to get the summary of a table, I can use something like the "str()" function or the "summary()" function:

str(my_table)

summary(my_table)

However, now I am trying to do this with tables on a server.

For instance, I am trying to get the summaries of variable types for a specific table (e.g. "my_table") on a server. I found a very indirect way to do this:

#load libraries
library(OBDC)
library(RODBC)
library(dbi)

#establish a connection and name it as "dbhandle"

rs <- dbSendQuery(dbhandle, 'select * from my_table limit 1')

dbColumnInfo(rs)

My Question: Is there a more "direct" way to do this? For example, can I get information about each column (e.g. whether the column is integer, character, date, etc.) in a table without first sending the query and then requesting the information? Can I do this directly?

Thanks!

Answer 1

You could try using fetch() from "RMySQL" to turn your SQL query into an R object (e.g. data frame)

library(RMySQL)
rs <- dbSendQuery(dbhandle, 'select * from my_table limit 1')
# Get the results from MySQL into R
my_table = fetch(rs, n=-1)
# clear result
dbClearResult(rs)
rm(rs)

Then use the functions you describe.

str(my_table)

summary(my_table)