Database in R?

Question

Database in R?

Navigation

#1 by (2 votes)
#2 by (1 votes)

7

Greetings, I have problems with the insertion of new records to a Database.

I use the library RODBC to connect to the database from R, all right there, but when I want to add a record that I have stored in a data frame like:

datos<- data.frame(nombre="juan",edad=22)
sqlSave(canal,tablename="alumnos",datos,append=TRUE)

I get an error, what can I do? Do you have any suggestions? I am looking forward to your response. Thank you very much.

Update:

My code is simple:

library(RODBC) 
canal <- odbcConnect("PrMYSQL",uid = "root") 
datos<-data.frame(nombre='alberto', edad=31,sexo= 'masculino') 
guardar<- sqlQuery(canal,"INSERT INTO alumnos (nombre,edad,sexo) VALUES (",paste(datos[1,]),")")

When executing I get the following error:

Error in if (errors) return(odbcGetErrMsg(channel)) else
return(invisible(stat)) : argument is not interpretable as logical

I hope you can help me thanks

r rodbc

asked by ElFenix Soy 16.12.2015 в 00:23

source

2 answers

Change of id in form Create android photo gallery

score 2 · Answer 1

I see that you modified your data, this would be the correct format:

canal <- odbcConnect("PrMYSQL",uid = "root") 
datos<-data.frame(codigo=1, nombre='alberto', edad=31,sexo= 'masculino') 
guardar<-sqlQuery(canal, paste0("INSERT INTO alumnos (codigo,nombre,edad,sexo) VALUES ( '",datos$codigo,"', '",datos$nombre,"', '",datos$edad,"', '",datos$sexo,"' )" ) )

this error:

Error in if (errors) return(odbcGetErrMsg(channel)) else
return(invisible(stat)) : argument is not interpretable as logical

is generated mainly by the incorrect use of paste or paste0

score 1 · Answer 2

I agree with Elenasys that the error is in the paste () , but it is possible to use the line for the query. However you have two problems, when extracting from a data.frame created by as.data.frame , factors are generated and converted to number when using paste , on the other hand there is the problem of concatenating columns. With the following example:

datos<-data.frame(nombre='alberto', edad=31,sexo= 'masculino')

You can concatenate your chain with do.call ()

 do.call(paste, c(datos,sep=","))
 [1] "alberto\",\"31\",\"masculino"

The first and last quotes do not count because they are part of the output of R and only those that are preceded by \

count.

Remaining then as:

canal <- odbcConnect("PrMYSQL",uid = "root") 
datos<-data.frame(codigo=1, nombre='alberto', edad=31,
                  sexo= 'masculino') 
guardar<-sqlQuery(canal, paste0('INSERT INTO alumnos
                  (codigo,nombre,edad,sexo) VALUES 
                  ("', do.call(paste, c(datos[1,],sep='","')),'")')

I hope this can help you.