[R] RMySQL problem - SOLVED

[Ernesto Jardim]

Hi,

The problem was the row.names that were sent to the database and created
an primary key duplicate. Now it works fine.

BTW congratulations for the package. It copied 15300 rows by 11 columns
in less than 5 seconds, in my PIII 833 with 1 GB RAM.

> system.time(dbWriteTable(con, "TBL_SIMDATA", TBL.SIMDATA, append =
TRUE, row.names=F))
[1] 3.87 0.05 4.86 0.01 0.00
 

Just one suggestion. It would be usefull to have a more verbose output,
maybe the "LOAD DATA INFILE" output. 

Thanks for your help.

EJ


On Tue, 2004-05-25 at 13:01, Sean Davis wrote:
> I have written tables with 10,000 rows before without incident, so I'm not
> sure what is going on--out of my league.  Is this idiosyncratic to your
> data?  In other words, can you load a simple 2-column table with 10000 rows?
> If not, I would suggest writing R code to construct the 10000  row toy
> example, write the table to MySQL, and then a dbReadTable and show that the
> number of rows is incorrect and post the code and results to the list.
> 
> Sean
> 
> On 5/25/04 7:24 AM, "Ernesto Jardim" <ernesto at ipimar.pt> wrote:
> 
> > On Tue, 2004-05-25 at 11:00, Sean Davis wrote:
> >> The function that you are looking for is:
> >> 
> >> help.search("writetable")
> >> 
> >> Or whatever in quotes you like.  Also, you can try help.start() which will
> >> give you the browser window with HTML versions of docs and a search
> >> facility.  
> >> 
> >> When you say "import", what do you mean?  Reading an Rdata file, a text
> >> file, or reading from mysql?
> > 
> > I'm referring to the number of rows that R sent to MySQL with the
> > dbWriteTable method. The dataframe has 13500 rows but when I do
> > 
> >> dbWriteTable(con, "TBL_SIMDATA", TBL.SIMDATA, append = TRUE)
> > [1] TRUE
> > 
> > the TBL_SIMDATA table in MySQL has only 555 rows ...
> > 
> > EJ
> > 
> >> Sean
> >> 
> >> 
> >> On 5/25/04 5:34 AM, "Ernesto Jardim" <ernesto at ipimar.pt> wrote:
> >> 
> >>> Hi,
> >>> 
> >>> I found the dbWriteTable function.
> >>> 
> >>> Sorry for the work but I'm still trying to understand how the S4
> >>> documentation works. If you do "help(package="RMySQL")" the
> >>> "dbWriteTable" method does not exist. It exists a "dbReadTable" method
> >>> wich has an alias to "dbWriteTable". It should be intuitive but it
> >>> wasn't for me ...
> >>> 
> >>> Now the method: I could only import 555 rows ... Is there some flag
> >>> about the number of rows it can import ?
> >>> 
> >>> Regards
> >>> 
> >>> EJ 
> >>> 
> >>> On Mon, 2004-05-24 at 22:44, David James wrote:
> >>>> 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
> >>> 
> >>> ______________________________________________
> >>> 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
> >>> 
> >