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@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.
