[R] export from R to MySQL

[bogdan romocea]

> Sean Davis wrote:
> but you will have to create the table by hand

There's no need for manual steps. To take advantage of MySQL's
extremely fast 'load data infile' you could dump the data in CSV
format, write a script for mysql (the command line tool), for example

q <- function(table,infile)
{
query <- paste("
create table ",table," (col1 float, col2 float);
load data infile '",infile,"'
into table ",table,"
fields terminated by ','
lines terminated by '\\r\\n'
ignore 0 lines;
show warnings;
",sep="")
query
}
sink("mysql_script.sql")
cat(q("db.table","infile"),"\n")
sink()

then run the script from R with system(). The mysql command looks like
mysql -u user --password=pswd -v < mysql_script.sql >> log.txt 2>&1


> -----Original Message-----
> From: r-help-bounces at stat.math.ethz.ch
> [mailto:r-help-bounces at stat.math.ethz.ch] On Behalf Of Sean Davis
> Sent: Monday, December 12, 2005 8:51 AM
> To: Meinhard Ploner; r-help
> Subject: Re: [R] export from R to MySQL
>
>
>
>
>
> On 12/12/05 8:33 AM, "Meinhard Ploner" <meinhardploner at gmx.net> wrote:
>
> > Hi R user!
> >
> > What is the fastest way to export a large matrix or vector
> to a MySQL
> > database? The use of data.frame() and dbWriteTable() makes
> the process
> > slow, so is there any <direct> alternative?
>
> Probably dumping to a text file and then using mysqlimport
> will be fastest,
> in terms of computation time, but you will have to create the
> table by hand
> (using SQL CREATE TABLE), so it might take just as much user time.
>
> Sean
>
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide!
> http://www.R-project.org/posting-guide.html
>