[R-sig-DB] table create from data.frame?

[Jeff Hamann]

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 using debian.org | http://dirk.eddelbuettel.com


	[[alternative HTML version deleted]]