Re: [R] File to MYSQL

Phil Spector Mon, 16 May 2011 13:32:56 -0700

If you're using the RMySQL package, the obvious choice
for writing a data frame to a table is dbWriteTable.
Note the append= argument, which if set to TRUE, will
allow you to write as much or as little to the database
as you need.
But before you do that, you should probably try to understand
how loops work in R.  When you write


for (i in length(Query)) {
 rs1<-dbSendQuery(con,Query[i])
 }

you overwrite the value of rs1 each time you go through the
loop.  After the loop, rs1 will be a query object that you
could examine using

fetch(rs1)

but, of course, it will only be the result of the final

query. You might also familiarize yourself withdbGetQuery, which may be more suited to your needs.


                                        - Phil Spector
                                         Statistical Computing Facility
                                         Department of Statistics
                                         UC Berkeley
                                         spec...@stat.berkeley.edu


On Mon, 16 May 2011, Nilza BARROS wrote:

Hi, Jerome

I was trying to use RMYSQL

for (i in length(Query)) {
rs1<-dbSendQuery(con,Query[i])
}

But although the Query have several  lines the command above just feed my
database with the first one.

Query

[1] "INSERT INTO OBS
(date,T_2M,TMAX_2M,TMIN_2M,TD_2M,PS,FF_10M,DD_10M,TOT_PRCP,CLCL,Station_NO)
VALUES (2011051312,26,NULL,20.6,19.4,1014.8,2.91,220,0.00,6,836490);"
[2] "INSERT INTO OBS
(date,T_2M,TMAX_2M,TMIN_2M,TD_2M,PS,FF_10M,DD_10M,TOT_PRCP,CLCL,Station_NO)
VALUES (2011051312,17,NULL,15.5,15.7,912.8,9.91,180,0.00,8,836920);"
[3] "INSERT INTO OBS
(date,T_2M,TMAX_2M,TMIN_2M,TD_2M,PS,FF_10M,DD_10M,TOT_PRCP,CLCL,Station_NO)
VALUES (2011051312,21.6,NULL,18.1,19.7,1003,1.94,140,0.00,2,836950);"

Thanks,


On Mon, May 16, 2011 at 4:46 PM, Jerome Asselin <
jerome.asselin.s...@gmail.com> wrote:

On Mon, 2011-05-16 at 14:55 -0300, Nilza BARROS wrote:

Dear R-user,

I have to feed my database using some SQL commands. I have already read a
data frame with the data I need but
after that these data should be write in a file wtih SQL commands.

1)  My dataframe:

dput(Alldados)

structure(list(Station_NO = c(836490, 836920, 836950, 836980,
837380, 837460), TMAX_2M = c("NULL", "NULL", "NULL", "NULL",
"NULL", "NULL"), TMIN_2M = c("20.6", "15.5", "18.1", "19.9",
"17", "21.5"), TD_2M = c("19.4", "15.7", "19.7", "20.1", "17.5",
"20.4"), PS = c("1014.8", "912.8", "1003", "1014.4", "967.8",
"NULL"), FF_10M = c("2.91", "9.91", "1.94", "4.08", "0", "6.02"
), DD_10M = c(220, 180, 140, 180, 0, 320), date2 = c("2011051312",
"2011051312", "2011051312", "2011051312", "2011051312", "2011051312"
)), .Names = c("Station_NO", "TMAX_2M", "TMIN_2M", "TD_2M", "PS",
"FF_10M", "DD_10M", "date2"), row.names = c(108L, 112L, 113L,
114L, 119L, 120L), class = "data.frame")


2) My script

outfile<- with(as.data.frame(Alldados),sprintf("INSERT INTO OBS
(date,TMAX_2M,TMIN_2M,TD_2M,PS,FF_10M,DD_10M,Station_NO) VALUES (%s, %s,

%s,

%s,%s,%s,%s,%s)",date2,TMAX_2M,TMIN_2M,TD_2M,PS,Station_NO))

write.table(outfile,file=paste(dat.dir,"outfile_13mai.txt",sep=""),append=FALSE,col.names=FALSE,

row.names=FALSE)


3)  The commands above works but the problem is that the oufile_13mai.txt
have several quotes (") . So  when I am going to feed my database using

the

command
/usr/bin/mysql -uxx -pxxx ormverif < ~/ormverif/syn/outfile_13mai.txt
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check
the manual that corresponds to your MySQL server version for the right
syntax to use near '"INSERT INTO OBS
(date,T_2M,TMAX_2M,TMIN_2M,TD_2M,PS,FF_10M,DD_10M,TOT_PRCP,CLCL' at line



"INSERT INTO OBS (date,TMAX_2M,TMIN_2M,TD_2M,PS,Station_NO) VALUES
(2011051312, NULL, 20.6,19.4,1014.8,836490)"
"INSERT INTO OBS (date,TMAX_2M,TMIN_2M,TD_2M,PS,Station_NO) VALUES
(2011051312, NULL, 15.5,15.7,912.8,836920)"


So I need a file like below:


INSERT INTO OBS (date,TMAX_2M,TMIN_2M,TD_2M,PS,Station_NO) VALUES
(2011051312, NULL, 20.6,19.4,1014.8,836490)
INSERT INTO OBS (date,TMAX_2M,TMIN_2M,TD_2M,PS,Station_NO) VALUES
(2011051312, NULL, 15.5,15.7,912.8,836920)


Have you considered the "quote=FALSE" option in write.table?

Another option would be to use the sqlUpdate() command from the RODBC
package. That would allow you to insert data into your SQL server
directly from R.

HTH,
Jerome



--
Abra?o,
Nilza Barros

        [[alternative HTML version deleted]]


______________________________________________
R-help@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.

Re: [R] File to MYSQL

Reply via email to