从R插入MySQL [英] Insert into MySQL from R

问题描述

我正在使用DBI包将数据插入MySQL.这是代码:

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)
}

问题出在3td列中，该列是数字，但具有NA值.当循环进入具有NA值的行时，它将返回错误:

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:

.local(conn，statement，...)中的错误:无法运行语句: 字段列表"中的未知列"NA"

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

我试图将NA更改为NaN，"NULL"和其他一些类型，但是没有任何效果.如果我将NA更改为0，它将起作用.

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

推荐答案

为运行SQL的R之类的任何应用程序层考虑参数化的编程行业标准.使用这种方法，可以避免字符串内插或混乱的引号括起来的任何需求. R的DBI标准有几种方法，其中一种是 sqlInterpolate :

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)
}

这篇关于从R插入MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！