Append R dataframe to MySQL table

Question

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)

rustymarmot · Accepted Answer · 2022-04-27 04:44:00Z

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)

answered Apr 27, 2022 at 4:44

rustymarmot

1211 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Append R dataframe to MySQL table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related