Question:
I had asked before a question about MySQL database using the RODBC library and due to an error in the types, I saw in one of the answers they told me about the RMySQL library and I decided to try it.
But 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 doing:
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 execute the last line (write) my database creates a new column and all types become 'text':
+-----------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------+------+-----+---------+-------+
| row_names | text | YES | | NULL | |
| codigo | text | YES | | NULL | |
| nome | text | YES | | NULL | |
+-----------+------+------+-----+---------+-------+
How could I solve this and keep mysql types?
Answer:
Put overwrite=FALSE. You are overwriting the table, so you lose the types.