[R] File to MYSQL

[Phil Spector]

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 with 
dbGetQuery, which may be more suited to your needs.

 					- Phil Spector
 					 Statistical Computing Facility
 					 Department of Statistics
 					 UC Berkeley
 					 spector at 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.stat at 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
>> 1
>>>
>>>
>>> "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]]
>
>