R dbBuildTableDefinition mysql rmysql error writin

问题:

I am working on R installed on Linux/Ubuntu system. I want to write a table to a database. Why doesn't it work?

I was able to drop tables and also able to display data present in a table. In short my dbGetQuery and dbRemoveTable work perfectly.

 employee <- c('John Doe','Peter Gynn','Jolie Hope')
 salary <- c(3, 2, 1)

 employ<- data.frame(employee, salary)

>  employ
    employee salary
1   John Doe      3
2 Peter Gynn      2
3 Jolie Hope      1
> dbBuildTableDefinition(dbh,"ee",employ)
[1] "CREATE TABLE ee \n( row_names text,\n\temployee text,\n\tsalary double \n)"

回答1:

You can directly write a table through the dbWriteTable function. If conn is your connection object, in this case you should try:

    dbWriteTable(conn,"ee",employ)

The command you gave just builds the MySQL command that defines the table. No actual table is created in the database. If you want to create the table, you can take the value of dbBuildTableDefinition and plug it into dbGetQuery. For instance:

    tableDef<-dbBuildTableDefinition(dbh,"ee",employ)
    dbGetQuery(conn,tableDef)

Note that in this way you just create the definition of the table, without putting any value in it.