Insert into MySQL from R

Question

I am using DBI package to insert data to MySQL. Here is the code:

ch <- DBI::dbConnect(MySQL())
dbSendQuery(ch, 'set character set "utf8"')
dbSendQuery(ch, 'SET NAMES utf8')
for (i in 1:nrow(test)) {
  query <- paste0("INSERT INTO trade_data VALUES('0', '", test[i, 1], "', '",
                  test[i, 2], "', ", test[i, 3], "')")
  dbSendQuery(ch, query)
}

The problem is in 3td column, which is numeric, but have NA values. When loop comes to row which has NA value it returns an error:

Error in .local(conn, statement, ...) : could not run statement: Unknown column 'NA' in 'field list'

I tried to change NA to NaN, "NULL", and some other types, but nothing works. If I change NA to 0 it works.

Check if the 3rd column equals to NA and convert to 0 if it does. — Shadow, Feb 15 '19 at 19:07
Btw, there seems to be an extra single quote before the closing ) — Shadow, Feb 15 '19 at 19:08

Parfait · Answer 1 · 2019-02-16T18:27:38.637

2

Consider the programming industry standard of parameterization for any application layer like R that runs SQL. With this approach, you avoid any needs of string interpolation or messy quote enclosures. R's DBI standard has several ways, one of which is sqlInterpolate:

# PREPARED STATEMENT (NO DATA) QMARKS REQUIRED BUT NAMES CAN CHANGE
sql <- "INSERT INTO trade_data (Col1, Col2, Col3, col4) 
        VALUES (?param1, ?param2, ?param3, ?param4)"

ch <- DBI::dbConnect(MySQL())
dbSendQuery(ch, 'set character set "utf8"')
dbSendQuery(ch, 'SET NAMES utf8')

for (i in 1:nrow(test)) {
  # BIND PARAMS
  query <- sqlInterpolate(conn, sql, param1 = "0", param2 = test[i, 1], 
                          param3 = test[i, 2], param4 = test[i, 3])
  # EXECUTE QUERY
  dbSendQuery(ch, query)
}

edited Feb 16 '19 at 18:27

answered Feb 15 '19 at 19:28

Parfait

104,375
17
94
125

I tried your code, but it returns an error: ` Error in .local(conn, statement, ...) : could not run statement: 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 ' 101.2, 101.2, ` 0.05, 101.2, 101.2, 101.2, 101.2, 250000, 253000, 02.01.2008)' at line 1 ` – Mislav Feb 16 '19 at 09:50
Afer I change "" to NA, it have problems with dots inside characters: Error in .local(conn, statement, ...) : could not run statement: 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 '.2008)' at line 2 – Mislav Feb 16 '19 at 10:07
1

You appear to have more than 4 columns in *trade_data* which this `INSERT` assumes if you omit explicit columns. Be sure to include **all** columns you intend to update data: `INSERT INTO trade_data (Col1, Col2, Col3, ...) VALUES(...` – Parfait Feb 16 '19 at 18:27

score 0 · Answer 2 · answered Feb 15 '19 at 19:18

0

If you are comfortable changing NA to 0, then your best bet is to do the following.

test[is.na(test)] <- 0

This will replace all NAs in the data.frame test with 0. You can do the same and change to string 'NULL' if you please as well.

test[is.na(test)] <- 'NULL'

If you are looking to replace only a column, you can do the following:

test$col3[is.na(test$col3)] <- 0

answered Feb 15 '19 at 19:18

Robele Baker

91
5

I don't want to set NA to zero because than I don't know which are 0 and which Na. – Mislav Feb 15 '19 at 19:24
Change to string 'NULL' – Robele Baker Feb 15 '19 at 19:55
For `NA`s they want the substring `NULL` to be in the query string between `VALUES` commas. Not `'NULL'`. – philipxy Jul 01 '19 at 20:08

score 0 · Answer 3 · answered Feb 16 '19 at 10:30

I got it right. I had to change "" to "NULL" and NA to NULL, and then used ifelse statement in insert. Like this:

ch <- DBI::dbConnect(MySQL())
dbSendQuery(ch, 'set character set "utf8"')
dbSendQuery(ch, 'SET NAMES utf8')
test[test == ""] <- "NULL"
test[is.na(test)] <- "NULL"
for (i in 1:nrow(test)) {
  query <- paste0("INSERT INTO trade_data VALUES('0', '", test[i, 1], "', ",
                  ifelse(test[i, 2] == "NULL", test[i, 2], paste0("'", test[i, 2], "'")), ", ", 
                  ifelse(test[i, 3] == "NULL", test[i, 3], paste0("'", test[i, 3], "'")), ", ",
                  # test[i, 3],", ", 
                  test[i, 4], ", ",
                  test[i, 5], ", ",
                  test[i, 6], ", ", test[i, 7] , ", ",
                  test[i, 8], ", ", test[i, 9] , ", ",
                  test[i, 10], ", ", test[i, 11] , ", '",
                  test[i, 12], "')")
  dbSendQuery(ch, query)
}
DBI::dbDisconnect(ch)

Your question doesn't mention wanting empty strings to become nulls. In SQL the empty string is not null. (Only Oracle equates the empty string to null, it's not standard SQL.) — philipxy, Jul 01 '19 at 20:39
Some people have a hard time distinguishing between the empty string (`""`) and `NULL` - hint: there's a difference ;) — quickshiftin, Apr 15 '22 at 20:02

Insert into MySQL from R

3 Answers3

Related