RMySQL dbWriteTable with field.types

Question

I have a data frame, called df, that looks like this:

dte, val
2012-01-01, 23.2323
2012-01-02, 34.343

The type on the columns is date and numeric. I would like to write this to a MySQL database using an already open connection. The connection works fine as I am able to query the db fine. I try to run the following:

dbWriteTable(con, name="table_name", value=df, field.types=list("date", "double(20,10)"))

This generates the error:

Error in function (classes, fdef, mtable) : unable to find an inherited method for function "make.db.names", for signature "MySQLConnection", "NULL"

If I do not specify a field.types, and run:

dbWriteTable(con, name="table_name", value=df)

I get the error:

Error in mysqlExecStatement(conn, statement, ...) : RS-DBI driver: (could not run statement: BLOB/TEXT column 'dte' used in key specification without a key length)

Can anyone shed some light on this?

Thanks

I rather doubt your implication that the dte column is of class "Date". It is printing as if it were of class "character". You should post instead the results of str() on head(name-object). — IRTFM, Jan 14 '12 at 18:49
the date column is of class date, i just wrote up the example by hand above. i explicitly do as.Date() on it to make sure of this. — Alex, Jan 15 '12 at 00:27

score 35 · Accepted Answer · edited Dec 05 '18 at 07:11

35

After playing with things, I realized what the problem is: field.types must be a NAMED ~~list~~ vector, not simply a list; additionally the row names must not be included otherwise we need three field types. For the above example, the following works fine:

dbWriteTable(con, name="table_name", value=df,
  field.types = c(dte="date", val="double(20,10)"), row.names=FALSE)

edited Dec 05 '18 at 07:11

BenBarnes

19,114
6
56
74

answered Jan 15 '12 at 18:39

Alex

19,533
37
126
195

2

Thanks @Brooky for the tip with the `field.types` object class change – BenBarnes Dec 05 '18 at 07:11
1

Can´t you add the field for a list of columns instead of doing it one by one? – Laura K Jun 26 '19 at 19:31
What does `"double(20,10)"` mean? I assume one is the length argument. – Adam_G Apr 29 '23 at 14:48

score -2 · Answer 2 · answered Aug 19 '20 at 10:26

-2

thats because of table name. do not use capital letters for mysql table name.

answered Aug 19 '20 at 10:26

SSDN

276
3
8

Where does the OP mention a table name with capital letters? – General Grievance Aug 19 '20 at 11:38

RMySQL dbWriteTable with field.types

2 Answers2

Linked