CODEWITHSUNDEEP
mysqlrrmysql
user6559913
user6559913

Reputation: 513

How to write entire dataframe into mySql table in R

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?

Upvotes: 2

Views: 7013

Answers (2)

USER_1
USER_1

Reputation: 2469

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')
  1. 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)

Upvotes: 7

Abdou
Abdou

Reputation: 13274

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.

Upvotes: 3

Related Questions