[R] RMySQL problem

David James dj at research.bell-labs.com
Mon May 24 23:44:58 CEST 2004


Hi,

The method for dbWriteTable() uses the MySQL bulk loading faciliy
"LOAD DATA LOCAL INFILE" to efficiently upload a file into the
server.  So you should be able to issue something like
  > sim.result <- big.simulation(...)
  > dbWriteTable(con, "table_name", sim.results, append = TRUE)
to append the rows in sim.results into the MySQL "table_name".

However, I should mention that the currently implementation
outputs the contents of the data.frame into a temporary file
using the function write.table(), which in the past could be
slow.

Hope this helps,

--
David

Ernesto Jardim wrote:
> Hi,
> 
> I'm using R 1.9.0 with RMySQL 0.5-4 and MySQL 3.23.55 on a suse 8.2 box.
> 
> I have a simulation study and (as usual for newbies in simulation, I
> guess) I have a lot of data that I want to store in MySQL. I want to
> write an R script that reads data from RData files and writes it to a
> MySQL database. 
> 
> I read some R documents (R Data Import/Export and DSC papers) but I'm
> finding differences between the documents and the packages (RMySQL and
> DBI). I don't find the methods to write data like "dbWriteTable"...
> 
> On the other hand I've tryied to build a sql statement to insert data
> but I'm stucked because of ... who knows, my ignorance probably. I want
> to take advantage of MySQl INSERT INTO statement that deals with several
> rows at once to insert a complete data.frame into a table. I've tryied
> to use "paste" to build the sql string but It works "by column" and I
> need it "by row" ...
> 
> The sql systax should be something like:
> 
> INSERT INTO TABLEA(COL1, COL2, COL3) VALUES
> 	(VAL11, VAL12, VAL13),
> 	(VAL21, VAL22, VAL23),
> 	...
> 	(VALN1, VALN2, VALN3);
> 
> and I have a data.frame with 3 columns corresponding to that table
> columns.
> 
> How can I do this ?
> 
> Thanks
> 
> EJ
> 
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list
> https://www.stat.math.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html




More information about the R-help mailing list