Updating Mysql table using sqlSave in R

Question

Updating Mysql table using sqlSave in R

Navigation

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

4

I'm new to R and I'm trying to add new tables in the database, the problem is that it always shows this message:

Error in sqlSave(connection, dado, tablename = "teste", rownames = FALSE,  : 
unable to append to table ‘teste’

Where:

dado = pegarDados(arquivoNome,TAG)
colnames(dado) = pegarNomes(arquivoNome,TAG)
sqlSave(connection, dado, tablename = "teste",rownames = FALSE ,colnames = FALSE,append=TRUE)

Does anyone know what causes this and how to solve it? I'm using the RODBC package.

mysql r odbc

asked by anonymous 17.04.2015 / 14:54

2 answers

1

Once in a job I had to incrementally add tables to a database. The solution I used was this one:

    ## Cria uma conexão com o banco de dados
con <- dbConnect(drv, user="usuario_do_banco", password="senha", dbname="nome_do_banco", host="url_do_banco")

## Adiciona linha a linha se tabela regras existir ou cria uma nova caso contrário
if(dbExistsTable(con, "regras")) {
  dbGetQuery(con, "delete from regras")
  for (i in 1:length(regras[,1])) {
    insere <- paste('insert into regras values (',"'",regras[i,1],"'",",","'",regras[i,2],"'",',',"'",regras[i,3],"'",',',"'",regras[i,4],"'",',',"'",regras[i,5],"'",')', sep="") ## Formatação específica do meu insert
    dbSendQuery(con, insere)
  }
} else {
    dbWriteTable(con, "regras", regras, row.names=F)
  }

In this case I was using Postgres through the RPostgeSQL package, but the RMySQL package has the same commands and should work the same way.

In case you just want to create a table in the database from a data.frame simply use:

dbWriteTable(con, "nome_da_tabela_a_ser_criada", data_frame, row.names=F)

I prefer RMySQL instead of RODBC.

17.04.2015 / 22:13

Import LateX for R Markdown How do I get a resource that is in the same package as the class that will use it?

score 1 · Accepted Answer

Probably this is happening because you had already created the "test" table before, at some point, and now you are trying to write to it again. To find out if this is, run sqlQuery(connection, "drop table teste") before sqlSave() . If you want to update the table, and do not save on top, you can use the sqlUpdate() function, for example.