Re: [R] SQL INSERT using RMySQL

From: Chris Stubben <stubben_at_lanl.gov>
Date: Fri, 11 Apr 2008 19:57:17 -0700 (PDT)

Greg,

If you have a MySQL table with an auto_increment field, you could just insert a NULL value into that column and the database will increment the key (it may not work in SQL STRICT mode, I'm not sure). I don't think there's any way to specify which columns you want to load data into using dbWriteTable yet, but that would be a nice feature since LOAD data now allows that (and SET syntax and other options).

Try this code below - I used cbind(NA, x) to insert a null into the first column.

Chris

> dbSendQuery(con, "create table tmp (id int not null auto_increment primary
> key, a char(1), b char(1))")

<MySQLResult:(369,1,67)>
> x<-data.frame( a=letters[1:3], b=letters[4:6])
> x
  a b
1 a d
2 b e
3 c f
> dbWriteTable(con, "tmp", cbind(NA, x), row.name=FALSE, append=TRUE)
[1] TRUE
> dbWriteTable(con, "tmp", cbind(NA, x), row.name=FALSE, append=TRUE)
[1] TRUE
> dbReadTable(con, "tmp")

  id a b
1 1 a d
2 2 b e
3 3 c f
4 4 a d
5 5 b e
6 6 c f

Gregory. R. Warnes wrote:
>
> 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
>
>
>
>

-- 
View this message in context: http://www.nabble.com/SQL-INSERT-using-RMySQL-tp16640280p16644954.html
Sent from the R help mailing list archive at Nabble.com.

______________________________________________
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 Sat 12 Apr 2008 - 03:01:16 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 Mon 14 Apr 2008 - 02:30:28 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