'Append R dataframe to MySQL table

Im trying to append a dataframe I have manipulated in R to a simple MySQL database with an existing table (table_a) with four columns as follows:

id. Primary key and auto incrementing.
name. varchar(45).
value_a. varchar(45).
value_b. varchar(45).

The dataframe (data) has three columns.
name.
value_a.
value_b.

I can connect to the database OK using RMySQL and dbConnect. But when I use the dbWriteTable command to append the dataframe "data" I get the following error.

Error in .local(conn, statement, ...) :
could not run statement: Unknown column 'row_names' in 'field list'

I know it connects ok because i can get it to create a new table but i want to continually append to an existing one in MySQL.

Any ideas anyone.

con <- dbConnect(MySQL(),
                 user = 'root',
                 password = '???????',
                 host = 'localhost',
                 dbname='rdbtest')
dbWriteTable(conn = con, name = 'table_a', value = data, append = TRUE, header = TRUE)
mysqlrappend


Solution 1:[1]

Ok solved.. needed to add row.names=FALSE

con <- dbConnect(MySQL(),
                 user = 'root',
                 password = '???????',
                 host = 'localhost',
                 dbname='rdbtest')
dbWriteTable(conn = con, name = 'table_a', value = data, append = TRUE, header = TRUE, row.names = FALSE)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 rustymarmot

Related Questions