[R-sig-DB] Importing data into a MySQL table from a data.frame with R

[Luis Aparicio]

 Greetings.  I'm trying to update a MySQL table with new data from a data
frame using R. The data frame has several thousand rows, but only three
columns.  The table has approximately five attributes (columns) so I only
want to update the specific columns that the data frame provides.  I've
been attempting to use the RMySQLDB or RMariaDB package and the dbx
package.  I'm posting the relevant sections of the code.  The lines that
connect to the database are fine.

I'm having trouble because of the different number of columns between the
data frame and the target table.  Also, note that the order the data frame
columns are different than the location of the same columns in the table.
The five columns in the table are:  INTERNAL_ID,  AA_CLONOTYPE,
NT_CLONOTYPE, COUNT, and SAMPLE_ID.  The headers in the data frame are
INTERNAL_ID, NT_CLONOTYPE, and SAMPLE_ID.

The target table may be empty or may have already had rows totally
completed or partially completed with prior data.

I'm posting the code snippet, the error message, and an example of what I
expect to be the result below.  Any ideas how I can approach this problem?
Any help is appreciated.  Thank you. Regards.

*Code snippet*:

table <- "TCR"  #name of the MySQL table
records <- newdf  # data frame with new records

# Insert or update new rows with specific column data
dbxUpsert(conx, table, records, where_cols = c("INTERNAL_ID"))

*The error message:*
Error in .local(conn, statement, ...) :
  could not run statement: Field 'COUNT' doesn't have a default value

*Example of expected output:*

INTERNAL_ID    AA_CLONOTYPE   NT_CLONOTYPE   COUNT   SAMPLE_ID
P28.10.1
ACCGTCTCCTT                  P28.10
P28.10.2
CGGTTTAAAG                   P28.10

-Luis

	[[alternative HTML version deleted]]