在mySQL表RMYSQL中插入Na值

发布于 2025-01-29 03:09:11 字数 1490 浏览 4 评论 0原文

我正在尝试将数据框架行插入MySQL表中，但是我的字符和数字列中具有NA值。我会遇到此错误：.local（conn，statement，...）中的错误：无法运行语句：“字段列表”中的未知列'na'

这是我的查询：

sql <- sprintf("insert into payment (amount,payment_type,date,customer_total,base_price, p2c_total, partner_total, pay_online, pay_at_lot,tax,first_name_on_card,last_name_on_card,address)
                  values (%f, %d, '%s',%f,%f,%f,%f,%f,%f,%f,'%s','%s','%s');",
                 payments[i,]$amount,payments[i,]$payment_type,payments[i,]$date, payments[i,]$customer_total,
                 payments[i,]$base_price, payments[i,]$p2c_total, payments[i,]$partner_total,
                 payments[i,]$pay_online,payments[i,]$pay_at_lot,payments[i,]$tax,
                 payments[i,]$first_name_on_card, payments[i,]$last_name_on_card, payments[i,]$address)
rs <- dbSendQuery(db, sql[i])
dbClearResult(rs)

这是SQL代码：

insert into reservation (reservation_number, driver_name, number_passengers, checkin_datetime, checkout_datetime, days, reservation_date, reservation_email,id_reservation_status, id_payment, id_ship, id_facility, id_user) values ('j990j','CB', 4, '2020-01-12 10:00:00', '2020-01-19 10:30:00', 8, 'NA', '[email protected]',NA, 1, 2, 547, 6);

这是MySQL错误：＃1054 -la columna'na'en en Field列表ES DESCONOCIDA

MYSQL版本：8.0.27

R版本：4.03

RMYSQL软件包：0.10.22

原文

I am trying to insert a data frame row in a mysql table, but I have NA values in character and numeric columns. I'm getting this error: Error in .local(conn, statement, ...) :
could not run statement: Unknown column 'NA' in 'field list'

This is my query:

sql <- sprintf("insert into payment (amount,payment_type,date,customer_total,base_price, p2c_total, partner_total, pay_online, pay_at_lot,tax,first_name_on_card,last_name_on_card,address)
                  values (%f, %d, '%s',%f,%f,%f,%f,%f,%f,%f,'%s','%s','%s');",
                 payments[i,]$amount,payments[i,]$payment_type,payments[i,]$date, payments[i,]$customer_total,
                 payments[i,]$base_price, payments[i,]$p2c_total, payments[i,]$partner_total,
                 payments[i,]$pay_online,payments[i,]$pay_at_lot,payments[i,]$tax,
                 payments[i,]$first_name_on_card, payments[i,]$last_name_on_card, payments[i,]$address)
rs <- dbSendQuery(db, sql[i])
dbClearResult(rs)

This is the sql code:

insert into reservation (reservation_number, driver_name, number_passengers, checkin_datetime, checkout_datetime, days, reservation_date, reservation_email,id_reservation_status, id_payment, id_ship, id_facility, id_user) values ('j990j','CB', 4, '2020-01-12 10:00:00', '2020-01-19 10:30:00', 8, 'NA', '[email protected]',NA, 1, 2, 547, 6);

And this is the mysql error:
#1054 - La columna 'NA' en field list es desconocida

MySQL version: 8.0.27

R version: 4.03

RMySQL package: 0.10.22

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

我的鱼塘能养鲲 2025-02-05 03:09:11

查看这一点的三种方法：

不要sprintf/粘贴 data 到查询字符串中。除了对恶意 妈妈的利用关注畸形的字符串或Unicode-VS-ANSI错误，即使是运行查询的数据分析师。
方便地，有一个函数以更安全的方式将数据从data.frame插入到表中的功能：dbappendtable。您可能只能做
在
如果需要插入所有列，则需要更多的详细内容：
dbappendtable（db，“付款”，付款[i，c（“金额”，“ peays_type”，“ date”，“ date”，“ customer_total”，“ base_price”，“ base_price”，“ base_price”， “ p2c_total”，“ panters_total”，“ pay_online”，“ pay_at_lot”，“税”，“ first_name_on_card”，“ last_name_on_card”，“地址”））
如果您打算进行1行以上，那么dbappendtable可以在没有任何问题的情况下进行多行。
如果您真的想一次使用自己的插入语句进行一行，那么我强烈敦促您使用参数化查询，也许是类似：
```
qry <- "insert into payment (amount,payment_type,date,customer_total,base_price, p2c_total, partner_total, pay_online, pay_at_lot,tax,first_name_on_card,last_name_on_card,address ）
                  值（？，？，？，？，？，？，？，？，？，？，？，？）;
dbexecute（db，qry，params =付款[i，c（“量”，“ peays_type”，...））））））
 
```
（这提醒我... dbexecute是一个不错的包装器，可以做dbsendStatement始终后面dbclearresult。还有dbgetQuery真正的dbsendquery始终是dbclearresult，返回数据。 /p>
注意：此功能需要一个最新的驱动程序来访问数据库。如果您正在使用rmysql，则存在一个问题：该软件包在几年（到目前为止）尚未看到实质性更新，并且不支持参数化查询。我相信 rmariadb软件包都与mysql和它支持参数化查询。
如果您必须真正手动执行此操作（实际上，我强烈地劝阻它，我以为我可以解决风险，只能每次被咬伤），然后R的na翻译进入null（无引号！）。为此，您需要有条件地添加报价。类似：
```
  ifelse（is.na（付款[i，] $ date），“ null”，squote（付款[i，] $ date））
 
```
对于您查询中的每个类似字符串的字段，并确保将'％s'更改为％s以您的格式中的。几乎可以肯定有更好的方法可以自动化此功能，以免您输入十几个或更多ifelse s，但我认为这确实不值得这样做。
（如果您依靠sprintf的不同语义（“％s”，..）与带有sque> squote的隐式字符串化，那么您可能需要那里还有更多肘部。）

Three ways to look at this:

Don't sprintf/paste data into a query string. In addition to security concerns about malicious SQL injection (e.g., XKCD's Exploits of a Mom aka "Little Bobby Tables"), it is also a concern for malformed strings or Unicode-vs-ANSI mistakes, even if it's one data analyst running the query.
Conveniently, there's a function that takes care of inserting data from a data.frame into the table in a safer way: dbAppendTable. You might be able to do just
```
dbAppendTable(db, "payment", payments[i,])
```
if all of the columns need to be inserted, otherwise something more verbose is necessary:
```
dbAppendTable(db, "payment", payments[i,c("amount", "payment_type", "date", "customer_total", "base_price", "p2c_total", "partner_total", "pay_online", "pay_at_lot", "tax", "first_name_on_card", "last_name_on_card", "address")])
```
If you're planning on doing this for more than 1 row, then dbAppendTable can take multiple rows with no issue.
If you really want to do one row at a time with your own insert statement, then I strongly urge you to use parameterized queries, perhaps something like:
```
qry <- "insert into payment (amount,payment_type,date,customer_total,base_price, p2c_total, partner_total, pay_online, pay_at_lot,tax,first_name_on_card,last_name_on_card,address)
                  values (?, ?, ?,?,?,?,?,?,?,?,?,?,?);"
dbExecute(db, qry, params = payments[i, c("amount", "payment_type", ...)])
```
(That reminds me ... dbExecute is a nice wrapper that does dbSendStatement always followed by dbClearResult. There's also dbGetQuery which is really dbSendQuery always followed by dbClearResult, returning the data. You aren't returning rows from the table, so the first is preferred anyway.)
NOTE: this feature requires an up-to-date driver for accessing the database. If you're using RMySQL then there is a problem: that package has not seen substantive updates in years (as of now) and does not support parameterized queries. I believe the RMariaDB package is both fully compatible with MySQL and it supports parameterized queries.
If you must really do this manually (and really, I discourage it strongly, too many times I thought I could work around the risks, only to be bitten each time), then R's NA translates into null (no quotes!). To do this, you need to conditionally add quotes. Something like:
```
ifelse(is.na(payments[i,]$date), "null", sQuote(payments[i,]$date))
```
for each string-like field in your query, and make sure to change '%s' to %s in your format. There are almost certainly better ways to automate this so that you aren't typing a dozen or more ifelses, but in my opinion it really is not worth the pain of doing that.
(If you're relying on different semantics of sprintf("%s", ..) versus the implicit string-ification with sQuote, then you may need even more elbow-grease there.)

回复收藏 0 原文

刘备忘录 2025-02-05 03:09:11

将您的NA包装到'NA''

insert into reservation (reservation_number, driver_name, number_passengers, checkin_datetime, checkout_datetime, days, reservation_date, reservation_email,id_reservation_status, id_payment, id_ship, id_facility, id_user) values ('j990j','CB', 4, '2020-01-12 10:00:00', '2020-01-19 10:30:00', 8, 'NA', '[email protected]','NA', 1, 2, 547, 6);

wrap your NA to 'NA'

insert into reservation (reservation_number, driver_name, number_passengers, checkin_datetime, checkout_datetime, days, reservation_date, reservation_email,id_reservation_status, id_payment, id_ship, id_facility, id_user) values ('j990j','CB', 4, '2020-01-12 10:00:00', '2020-01-19 10:30:00', 8, 'NA', '[email protected]','NA', 1, 2, 547, 6);

回复收藏 0 原文

~没有更多了~