Raw Message

Message-ID: <19794.60295.916709.317662@max.nulle.part>
Date: 2011-02-09T19:31:19Z
From: Dirk Eddelbuettel
Subject: table create from data.frame?
In-Reply-To: <1B976F52-8114-4B23-88F9-25219A62FEDD@forestinformatics.com>

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