table create from data.frame?
Wow. That's awesome! Thanks so much. Respectfully, Jeff. Jeff Hamann, PhD PO Box 1421 Corvallis, Oregon 97339-1421 541-754-2457 jeff.hamann[at]forestinformatics[dot]com http://www.forestinformatics.com http://forufus.blogspot.com/
On Feb 9, 2011, at 11:31 AM, Dirk Eddelbuettel wrote:
On 9 February 2011 at 11:18, Jeff Hamann wrote:
| Is there some function in any of the db tools for R to generate an SQL table create statement (with optional insert statements) from a data.frame object?
Yes, DBI has had dbWriteTable() for that for a long time. Here is a complete
example (from the regression tests in RPostgreSQL) writing rock from the
datasets package:
## try to load our module and abort if this fails
stopifnot(require(RPostgreSQL))
stopifnot(require(datasets))
## load the PostgresSQL driver
drv <- dbDriver("PostgreSQL")
## connect to the default db
con <- dbConnect(drv,
user=Sys.getenv("POSTGRES_USER"),
password=Sys.getenv("POSTGRES_PASSWD"),
host=Sys.getenv("POSTGRES_HOST"),
dbname=Sys.getenv("POSTGRES_DATABASE"),
port=ifelse((p<-Sys.getenv("POSTGRES_PORT"))!="", p, 5432))
if (dbExistsTable(con, "rockdata")) {
print("Removing rockdata\n")
dbRemoveTable(con, "rockdata")
}
dbWriteTable(con, "rockdata", rock)
## run a simple query and show the query result
res <- dbGetQuery(con, "select * from rockdata limit 10")
print(res)
## cleanup
if (dbExistsTable(con, "rockdata")) {
print("Removing rockdata\n")
dbRemoveTable(con, "rockdata")
}
## and disconnect
dbDisconnect(con)
(The dbConnect is overly verbose / complicated because we want this scripted
without writing down users and passwords, that way we can automate tests in
different places.)
Dirk
--
Dirk Eddelbuettel | edd at debian.org | http://dirk.eddelbuettel.com