Adding values in existing tables with Rmysql

Question

I had asked earlier a question about Mysql database on using the RODBC library and due to an error in the types,I saw in one of the answers told me about the Rmysql library and decided to try it.

However I am not able to add data.frame tables to an existing table.

This is the table I made in mysql:

CREATE TABLE teste ( codigo varchar(5), nome varchar(5) );
+--------+------------+------+-----+---------+-------+
| Field  | Type       | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| codigo | varchar(5) | YES  |     | NULL    |       |
| nome   | varchar(5) | YES  |     | NULL    |       |
+--------+------------+------+-----+---------+-------+

And this is the code I’m making:

library(RMySQL)
#Criando conexão
con = dbConnect(RMySQL::MySQL(),host = "localhost" ,dbname = "Banco",username= "****", password= "****")

#Listando tabelas
dbListTables(con)

#Elementos que serão adicionados
x = list("co1","nome1")
y = list("co2","nome2")

z = rbind(x,y)
colnames(z) = list("codigo","nome")
rownames(z) = NULL

z = data.frame(z)
#==============================

#Escrevendo elementos na tabela
dbWriteTable(con, name="teste", value=z ,overwrite = TRUE,row.names = FALSE)

But when I run the last line (write) my database creates a new column and all types see 'text':

+-----------+------+------+-----+---------+-------+
| Field     | Type | Null | Key | Default | Extra |
+-----------+------+------+-----+---------+-------+
| row_names | text | YES  |     | NULL    |       |
| codigo    | text | YES  |     | NULL    |       |
| nome      | text | YES  |     | NULL    |       |
+-----------+------+------+-----+---------+-------+

How could I resolve this and keep mysql types?

Answer 1

Put overwrite=FALSE. You are overwriting the table, so you lose the types.