How to write entire dataframe into mySql table in R

Question

I have a data frame containing columns 'Quarter' having values like "16/17 Q1", "16/17 Q2"... and 'Vendor' having values like "a", "b"... .

I am trying to write this data frame into database using

query <- paste("INSERT INTO cc_demo (Quarter,Vendor) VALUES(dd$FY_QUARTER,dd$VENDOR.x)")

but it is throwing error :

Error in .local(conn, statement, ...) : 

could not run statement: Unknown column 'dd$FY_QUARTER' in 'field list'

I am new to Rmysql, Please provide me some solution to write entire dataframe?

Answer 1

I would advise against writing sql query when you can actually use very handy functions such as dbWriteTable from the RMySQL package. But for the sake of practice, below is an example of how you should go about writing the sql query that does multiple inserts for a MySQL database:

# Set up a data.frame
dd <- data.frame(Quarter = c("16/17 Q1", "16/17 Q2"), Vendors = c("a","b"))

# Begin the query
sql_qry <- "insert into cc_demo (Quarter,Vendor) VALUES"

# Finish it with
sql_qry <- paste0(sql_qry, paste(sprintf("('%s', '%s')", dd$Quarter, dd$Vendors), collapse = ","))

You should get:

"insert into cc_demo (Quarter,Vendor) VALUES('16/17 Q1', 'a'),('16/17 Q2', 'b')"

You can provide this query to your database connection in order to run it.

I hope this helps.

Answer 2

To write a data frame to mySQL DB you need to:

1. Create a connection to your database, you need to specify:

   • MySQL connection
   • User
   • Password
   • Host
   • Database name

---

library("RMySQL")
connection <- dbConnect(MySQL(), user = 'root', password = 'password', host = 'localhost', dbname = 'TheDB')

---

2. Using the connection create a table and then export data to the database

---

dbWriteTable(connection, "testTable", testTable)

You can overwrite an existing table like this:

dbWriteTable(connection, "testTable", testTable_2, overwrite=TRUE)