[R] SQL INSERT using RMySQL

From: Gregory. R. Warnes <greg_at_warnes.net>
Date: Fri, 11 Apr 2008 15:40:13 -0400


Hi All,

I've finally gotten around to database access using R. I'm happily extracting rows from a MySQL database using RMySQL, but am having problems appending rows to an existing table.

What I *want* to do is to append rows to the table, allowing the database to automatically generate primary key values. I've only managed to add rows by using

        dbWriteTable( con, "past_purchases", newRecords, overwrite=FALSE, append=TRUE, ...)

And this only appears to properly append rows (as opposed to overwriting them) IFF
1) the row names for newRecords are new unique primary key values, 2) the argument row.names is TRUE.

If row.names is FALSE, the records will not be appended, even if newRecords contains a column (named 'id') of unique values that corresponding to the primary key (named 'id').

It appears that in this case, the row names on the data frame are still being used for the primary key, and since overwrite is FALSE, the new records are being silently dropped.

I did manage to get things working by doing the following:

## get the last used id value (primary key) maxId <- dbGetQuery(con, "SELECT MAX(id) FROM past_purchases")[1,1] maxId
if(is.na(maxId)) maxId <- -1

## add the new unique primary keys as row names rownames(fulldata) <- maxId + 1:nrow(fulldata)

## now write out the data
dbWriteTable(con, "past_purchases", value=fulldata, overwrite=FALSE, append=TRUE, row.names=TRUE)

Is there a better way to accomplish this task? (Session info is below)

Thanks!,

-Greg

Gregory R. Warnes, Ph.D.
Associate Professor
Center for Biodefence Immune Modeling

    and
Department of Biostatistics and Computational Biology University of Rochester

> sessionInfo()

R version 2.6.2 (2008-02-08)
i386-apple-darwin8.10.1

locale:
C

attached base packages:
[1] stats graphics grDevices utils datasets methods base

other attached packages:
[1] chron_2.3-15 RMySQL_0.6-0 DBI_0.2-4
>


MySQL client version: 5.0.41

        [[alternative HTML version deleted]]



R-help_at_r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code. Received on Fri 11 Apr 2008 - 19:51:42 GMT

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.2.0, at Sat 12 Apr 2008 - 03:30:27 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.

list of date sections of archive