Вставьте значения NA в таблицу Mysql RMySQL

Я пытаюсь вставить строку фрейма данных в таблицу 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 - Столбец 'NA' в списке полей desconocida

Версия MySQL: 8.0.27

Р-версия: 4.03

Пакет RMySQL: 0.10.22

Есть ли причина, по которой вы не используете DBI::dbAppendTable? Поскольку кажется, что payments уже является фреймом, вы можете подмножить свой фрейм (при необходимости) и либо вставить одну строку, либо все сразу. Использование sprintf для формулировки запроса для вставки данных в форме, удобной для SQL, кажется ненужным.

— 15.05.2022 03:42

Но если вы действительно хотите обойти удобство и гарантии, которые обеспечивает dbAppendTable, вам нужно указать значение, где, если это NA, то значение в вашем запросе должно быть null, иначе '%f'. Да, это означает, что вам нужно включать одинарные кавычки, только если вставляемое значение не равно NA.

— 15.05.2022 03:43

Я использую dbSendQuery для каждой строки платежного набора данных, потому что мне нужен вставленный идентификатор для вставки в другую таблицу. id_payment является автоматическим числом в платеже и внешним ключом в другой таблице с именем reservation. Если я использую dbAppenTable, как мне получить идентификатор каждого платежа?

— 15.05.2022 15:56

Ладно, да, это всегда мешает. Тьфу, я помню эту проблему, одну из причин, по которой я не люблю автоматически увеличивающиеся идентификаторы. К сожалению, я думаю, что MySQL делает это сложным, не имея безопасного способа обойти это. В этом случае см. 2-й вариант в моем ответе.

— 15.05.2022 18:23

mysql sql r rmysql r-dbi

15.05.2022 03:26

Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для разработчиков

В последние годы архитектура микросервисов приобрела популярность как способ построения масштабируемых и гибких приложений. Laravel , популярный PHP...

Как построить CRUD-приложение в Laravel

Laravel - это популярный PHP-фреймворк, который позволяет быстро и легко создавать веб-приложения. Одной из наиболее распространенных задач в...

Освоение PHP и управление базами данных: Создание собственной СУБД - часть II

В предыдущем посте мы создали функциональность вставки и чтения для нашей динамической СУБД. В этом посте мы собираемся реализовать функции обновления...

Документирование API с помощью Swagger на Springboot

В предыдущей статье мы уже узнали, как создать Rest API с помощью Springboot и MySql .

Роли и разрешения пользователей без пакета Laravel 9

Этот пост изначально был опубликован на techsolutionstuff.com .

Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM

В предыдущей статье мы завершили установку базы данных, для тех, кто не знает.

Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

оберните 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);

15.05.2022 03:36

Ответ принят как подходящий

Три взгляда на это:

Не добавляйте sprintf/pasteданные в строку запроса. В дополнение к проблемам безопасности, связанным с злонамеренныйSQL-инъекция (например, Подвиги мамы XKCD, также известным как «таблицы Little Bobby»), это также связано с искаженными строками или ошибками Unicode-VS-ANSI, даже если запрос выполняет один аналитик данных.
Удобно, что есть функция, которая заботится о вставке данных из data.frame в таблицу более безопасным способом: dbAppendTable. Вы могли бы сделать просто
```
dbAppendTable(db, "payment", payments[i,])
```
если все столбцы должны быть вставлены, в противном случае необходимо что-то более подробное:
```
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")])
```
Если вы планируете сделать это для более чем 1 строки, то dbAppendTable может без проблем занять несколько строк.
Если вы действительно хотите выполнять одну строку за раз со своим собственным оператором insert, я настоятельно рекомендую вам использовать параметризованные запросы, возможно, что-то вроде:
```
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", ...)])
```
(Это напомнило мне... dbExecute - это хорошая оболочка, которая делает dbSendStatement всегда за которым следует dbClearResult. Есть также dbGetQuery, за которым на самом деле dbSendQuery всегда следует dbClearResult, возвращая данные. Вы не возвращаете строки из таблицы, поэтому первое в любом случае предпочтительнее)
ПРИМЕЧАНИЕ: для этой функции требуется обновленный драйвер для доступа к базе данных. Если вы используете RMySQL, то есть проблема: этот пакет не видел существенных обновлений в течение многих лет (на данный момент) и не поддерживает параметризованные запросы. Я считаю, что пакет RMariaDB полностью совместим с MySQL и, он поддерживает параметризованные запросы.
Если вы действительно должны делать это вручную (и на самом деле, я сильно не одобряю этого, слишком много раз я думал, что смогу обойти риски, только чтобы каждый раз быть укушенным), тогда R's NA переводится в null (без кавычек!). Для этого нужно условно добавить кавычки. Что-то вроде:
```
ifelse(is.na(payments[i,]$date), "null", sQuote(payments[i,]$date))
```
для строкового поля каждый в вашем запросе и обязательно измените '%s' на %s в вашем формате. Почти наверняка есть лучшие способы автоматизировать это, чтобы вы не набирали дюжину или более ifelse, но, на мой взгляд, это действительно не стоит того, чтобы делать это.
(Если вы полагаетесь на другую семантику sprintf("%s", ..) по сравнению с неявной строковой классификацией с sQuote, то вам может понадобиться еще больше усилий.)

Я использую dbSendQuery для каждой строки платежного набора данных, потому что мне нужен вставленный идентификатор для вставки в другую таблицу. id_payment является автоматическим числом в платеже и внешним ключом в другой таблице с именем reservation. С dbAppendTable я получил ошибку в списке полей, но я смог сделать это, используя dbWriteTable с добавлением и параметрами row.names в FALSE. Благодарю вас!

— 15.05.2022 20:33

15.05.2022 03:51