[R] SQL INSERT using RMySQL

[Chris Stubben]

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.