CODEWITHSUNDEEP
rrmysql
John Smith
John Smith

Reputation: 2876

SQL query contains 'c(' when being generated from rmysql

I am using the library RMySQL to generate an SQL statement

query <- sprintf("foo", paste(mydf, collapse = "', '",sep = ','))

When i have a look at the query it seems to be putting everything in brackets for example a column would consist of monatory terms 10.50, 20.50, 50.33 but when i look at the SQL it has it as c(10.50,20.50, 50.33) which causes my SQL to crash. Does anyone know why?

Reproducible example

mydf <- data.frame(
   X = sample(1:10),
   Y = sample(c("yes", "no"), 10, replace = TRUE)
 )

 # Construct the update query by looping over the data fields
 query <- sprintf("INSERT INTO feedback (X,Y) VALUES ('%s')",
                  paste(mydf, collapse = "', '",sep = ','))


> cat(query)
INSERT INTO feedback (X,Y) VALUES ('c(8, 6, 10, 9, 3, 4, 5, 7, 2, 1)', 'c(1, 2, 1, 2, 2, 1, 1, 1, 2, 2)')

Thanks

Upvotes: 0

Views: 180

Answers (1)

Parfait
Parfait

Reputation: 107567

As mentioned, consider using dbWriteTable() to import a dataframe to an existing MySQL database table. Dataframe columns should match database table's column names and types (or coercible types) or types specified using a named list in its field.types argument:

dbWriteTable(conn, "MyTable", mydf, row.names = FALSE, append = TRUE)

Also, dataframe does not need to be exhaustive to fill every current column in database table, so leave out any automated fields like autoincrement IDs and timestamps.

Consider even using a staging temp table regularly cleaned out which might be needed to avoid duplicates (w/ NOT IN or NOT EXISTS or LEFT JOIN / IS NULL queries) and/or match different named or additional columns in df not in db table:

dbSendQuery(conn, "DELETE FROM MyTempTable")  # OR USE overwrite = TRUE in next line
dbWriteTable(conn, "MyTempTable", mydf, row.names = FALSE, append = TRUE) 
dbSendQuery(conn, "INSERT INTO MyTable (Col1, Col2, Col3, ...)
                   SELECT Col1, Col2, Col3 ... FROM MyTempTable")

Upvotes: 1

Related Questions