Updating Mysql table using sqlSave in R

Asked

Viewed 108 times

4

I’m new to R and I’m trying to add new tables to the database,:

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 am using the package RODBC.

mysql r odbc

  • Which package you are using?

  • I’m using the RODBC

  • Probably this is happening because you had already created the "test" table before, at some point, and now you are trying to write on it again. To know if it’s that turn sqlQuery(connection, "drop table teste") before of sqlSave().

  • It worked, thank you.

2 answers

1


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

1

Once in a job I needed to add tables in a database incrementally. 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 only 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 over RODBC.

Browser other questions tagged mysql r odbc

You are not signed in. Login or sign up in order to post.