CODEWITHSUNDEEP
mysqlsqlrrodbc
developer365
developer365

Reputation: 3

Insert data frame on a single table of a data Base using R and ORDBC

how could i insert a full dataframe into a single table of mysql database

this is my table 

CREATE TABLE `data` (
`iddata` int(11) NOT NULL,
`x` varchar(45) DEFAULT NULL,
`y` varchar(45) DEFAULT NULL,
 PRIMARY KEY (`iddata`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

this is my R code :

file <- paste("C:\\Users\\zakaria\\Documents\\RDMZ\\test_rr.csv")
table <- read.table(file,sep=",",header=T)
channel <-odbcConnect(dsn="RSQL",uid="root",pwd="toor")
x <-table[,1]
y<-table[,2]



data<-sqlQuery(channel,paste("INSERT INTO 'data'.'dbwin'('x','y') VALUES (", x,", ", y, ");"))
sqlQuery(channel,data)

i have this error 

[1] "42000 1064 [MySQL][ODBC 5.2(w) Driver][mysqld-5.6.17]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '42000 1064 [MySQL][ODBC 5.2(w) Driver][mysqld-5.6.17]You have an error in your S' at line 1"                   
[2] "[RODBC] ERROR: Could not SQLExecDirect '42000 1064 [MySQL][ODBC 5.2(w) Driver][mysqld-5.6.17]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''data'.'dbwin'('x','y') VALUES ( 0.708007801949862 ,  84 )' at line 1'"

thank u for your help

Upvotes: 0

Views: 1341

Answers (1)

jlhoward
jlhoward

Reputation: 59355

The best way to do this is with @joran's suggestion: use sqlSave(...), which is designed for this.

There are a couple of reasons your code is not working. One problem is that you define x and y as varchar, but you don't quote the strings in the sql.

x<-"A"
y<-"B"
sql <- paste("INSERT INTO 'data'.'dbwin'('x','y') VALUES (", x,", ", y, ");")
cat(sql)
# INSERT INTO 'data'.'dbwin'('x','y') VALUES ( A ,  B );

Rather, use this:

sql <- paste0("INSERT INTO 'data'.'dbwin'('x','y') VALUES (\"", x,"\", \"", y, "\");")
cat(sql)
# INSERT INTO 'data'.'dbwin'('x','y') VALUES ("A", "B");

The second is that you do not need to include the database name in the sql, since that information is embedded in the connection. Rather, use something like:

sql <- "INSERT INTO data (x,y) values (\"A\",\"B\")"

Finally, and most important, this form of INSERT adds one row to the database table, so you cannot use vectors for x and y (as your code does). Here's a work-around:

## not tested
apply(table[,1:2], 1, function(values) {
  x    <- values[1]
  y    <- values[2]
  sql  <- paste0("INSERT INTO data (x,y) VALUES (\"", x,"\", \"", y, "\");")
  data <-sqlQuery(channel,sql)
  sqlQuery(channel,data)
})

This will (should..) execute an sql INSERT for each row of table. It's likely to be slower then sqlSave(...) though.

Finally, you don't need two calls to sqlQuery(...).

Upvotes: 1

Related Questions