dbwritetable removes auto increment field in mysql

Question

I am working with MySql for the first time. I have created a database in mysql with some tables using PHPMYADMIN. One of the table is titled as "City". This table as two column fields 1. City_ID and 2. City. In this City_ID is the primary key with auto increment.

I have the data in CSV format, which I have read into R. From the data frame, I am using the following command to get the City values from R - DF.

City <- as.data.frame(unique(df[,10]))
names(City) <- "City"

I am using the following codes to connect with MySql.

library(RMySQL)
mydb <- dbConnect(MySQL(), user = "root", password = "****", dbname = "pal", host = "localhost")

dbWriteTable(mydb, "City", City, overwrite = TRUE, row.names = FALSE)

This removes the field City_ID and fills the City column of the table with values. Don't know how to resolve this. Most of the work that I do has data in excel or csv formats. And I need to frequently update the tables too. Any thoughts on this.

Possible duplicate of [dbWriteTable(..., append = T) is overwritng in R](http://stackoverflow.com/questions/11719850/dbwritetable-append-t-is-overwritng-in-r) — Panagiotis Kanavos, Mar 31 '16 at 11:37
@PanagiotisKanavos I did try with `overwrite = FALSE` along with append = T but i am getting only zero as values in my sql table. — Apricot, Mar 31 '16 at 12:11

score 0 · Answer 1 · edited May 23 '17 at 12:15

I think, this is the problem at this case too. LAST_INSERT_ID() always returns 0 (RMySQL) - separate connection issue as Illari wrote at different topic.

library("RMySQL")
con <- dbConnect(MySQL())
dbSendQuery(con, "DROP TABLE IF EXISTS t;")
dbSendQuery(con, "CREATE TABLE t (i INT NOT NULL AUTO_INCREMENT PRIMARY KEY);")
res <- dbSendQuery(con, "INSERT INTO t VALUES (NULL);")

doesn't work:
dbGetQuery(con, "SELECT LAST_INSERT_ID();")
  LAST_INSERT_ID()
1                0

# works:
dbClearResult(rs)
dbGetQuery(con, "SELECT LAST_INSERT_ID();")
  LAST_INSERT_ID()
1                1

dbwritetable removes auto increment field in mysql

1 Answers1