R help - Jul 2010 - Troubles with DBI's dbWriteTable in RMySQL

I am feeling rather dumb right now.

I created what I thought was a data.frame as follows:

aaa <- lapply(split(moreinfo,list(moreinfo$m_id),drop = TRUE), fun_m_id)
m_id_default_res <- do.call(rbind, aaa)
print("==========================================")
m_id_default_res
print("==========================================")
ndf <- m_id_default_res[, c('mid', 'estimate', 'sd',
'loglik', 'aic','bic',
'chisq', 'chisqpvalue', 'chisqdf')]
ndf

The data in NDF is perfect, exactly what I expected when I print the
contents as shown in the last statement above.

On the asumption tha tthat is a data frame, I tried

dbWriteTable(con,"test1",ndf);

But I received the following error:

Error in function (classes, fdef, mtable)  :
  unable to find an inherited method for function "dbWriteTable", for
signature "MySQLConnection", "character", "matrix"

Then, on the assumption it is trivial to convert a matrix into a data.frame,
i tried:

dbWriteTable(con,"test2",as.data.frame(ndf));

But this produced the following error:
Error in write.table(x, file, nrow(x), p, rnames, sep, eol, na, dec,
as.integer(quote),  :
  unimplemented type 'list' in 'EncodeElement'

The silly, and frustrating, thing is that I used dbWriteTable before, and
that worked adequately.  But that was with a simple data frame (within a for
loop, element by element - res$var[[i]] = expression), not the result of
do.call(rbind(...))  The principle limitation I saw in my previous use of
dbWriteTable is that all fields are given the type 'TEXT', and that it
insists on creating a new table.  What I'd prefer is a kind of bulk interset
that just makes extra records for an existing table.

So, given my past experience with dbWriteTable, it is a question of what
do.call(rbind(..)) did to produce ndf that has the effect that dbWriteTable
doesn't like that data.frame.

So, then, what is the bext way to either get dbWriteTable working (ideally
in a way that works around the limitations I mention above) or to do a bulk
insert into my MySQL table (yes, I already have a table in the relevant
schema with all the right data types for each field, and I load RMySQL at
the start of my program.)  In a worst case, I can live with an insertion one
record at a time.

Thanks

Ted

PS: If it helps, here is the the contents of ndf - as shown by entering
'ndf' at the R prompt:
> ndf
    mid estimate   sd           loglik    aic      bic      chisq
chisqpvalue   chisqdf
206 206 0.1147528  0.04336918   -22.15483 46.30965 46.25556 4.433502
0.03524013    1
229 229 0.07999936 0.01999671   -56.41179 114.8236 115.5962 195307.1
0             2
251 251 0.074421   0.002171616  -4224.072 8450.144 8455.212 593302.2
0             18
252 252 0.03710208 0.0004556731 -28426.82 56855.65 56862.45 3543373
0             38
253 253 0.01397349 0.0005900857 -2925.179 5852.358 5856.677 283.9848
5.232282e-51  16
254 254 0.09043846 0.01528502   -119.108  240.216  241.7713 23.52441
3.139385e-05  3
255 255 0.05078883 0.0006021373 -28294.38 56590.76 56597.63 1988844
0             35
260 260 0.03392846 0.005499136  -166.5730 335.1461 336.7837 10.83060
0.05484413    5
268 268 0.05357114 0.01785082   -35.3407  72.6814  72.87863 82995.79
0             2
286 286 0.09321947 0.01987217   -74.20157 150.4031 151.4942 1.698603
0.6372445     3
290 290 0.03841793 0.006584153  -144.8139 291.6277 293.1541 135.8937
2.902434e-29  3
292 292 0.06289269 0.01988338   -37.66325 77.32651 77.6291  143099.8
0             2
297 297 0.01674874 0.004047625  -86.52035 175.0407 175.8739 47.27713
3.034432e-10  3
302 302 0.02878066 0.003876092  -250.1428 502.2857 504.293  9.22447
0.2369393     7
306 306 0.07904849 0.0004164051 -127449.0 254899.9 254908.4 111574416
0             40
307 307 0.01655872 0.001320903  -795.7314 1593.463 1596.513 57.38622
1.127804e-08  10
308 308 0.02631102 0.000884155  -4095.149 8192.298 8197.081 142.8876
3.904898e-20  21
309 309 0.09891599 0.0084501    -453.9474 909.8947 912.8147 357135.5
0             8
310 310 0.09332047 0.004580396  -1399.262 2800.524 2804.552 217126
0             13
311 311 0.06378327 0.0005049166 -59848.62 119699.2 119706.9 59481893
0             34
313 313 0.06203001 0.0006486936 -34546.67 69095.34 69102.46 18207698
0             32
316 316 0.2222173  0.07026985   -25.04100 52.08199 52.38458 18002.22
0             2
317 317 0.04405086 0.0005949207 -22578.44 45158.88 45165.49 8923236
0             33
320 320 0.05747093 0.006634162  -289.2357 580.4714 582.7889 8.641322
0.2794433     7
321 321 0.06365155 0.003692525  -1115.037 2232.073 2235.767 19.10553
0.08601337    12
322 322 0.05737672 0.01532991   -54.01363 110.0273 110.6663 9.597753
0.008238998   2
323 323 0.03116934 0.001909146  -1188.573 2379.146 2382.73  109.7663
6.656046e-18  12
324 324 0.03027327 0.0004146385 -23922.15 47846.3  47852.88 47330365
0             32
325 325 0.06047783 0.00922026   -163.6356 329.2711 331.0323 1695781
0             3
326 326 0.05627898 0.0008642285 -16432.57 32867.13 32873.48 3405089
0             29
327 327 0.07052627 0.001144912  -13851.16 27704.33 27710.57 6344780
0             26
329 329 0.04571883 0.003557502  -674.0654 1350.131 1353.237 45.31164
1.910501e-06  10
331 331 0.0728699  0.000962558  -20733.71 41469.42 41476.07 16860414
0             26
333 333 0.06777295 0.001090324  -14256.93 28515.86 28522.12 8260494
0             23
335 335 0.03928983 0.00279747   -834.6475 1671.295 1674.578 26.41114
0.003224856   10
336 336 0.01300952 0.003339059  -80.1311  162.2622 162.9703 33.12369
3.032883e-07  3
339 339 0.01881150 0.00181342   -532.1417 1066.283 1068.956 54.56913
1.471288e-08  9
340 340 0.05145835 0.0005917994 -29970.55 59943.1  59950.03 9414130
0             34
342 342 0.0499304  0.008318394  -143.8965 289.793  291.3765 272596.5
0             5
343 343 0.09286674 0.001970758  -7496.029 14994.06 14999.76 5806685
0             18
345 345 0.09734419 0.02075166   -73.24905 148.4981 149.5891 378227
0             2
348 348 0.1214271  0.0294484    -52.8435  107.687  108.5202 77506.98
0             3
349 349 0.03902424 0.01378808   -33.94858 69.89716 69.9766  128122.4
0             2
351 351 0.08562618 0.01617962   -96.8174  195.6348 196.967  600605.1
0             3
353 353 0.07881848 0.001481375  -10019.92 20041.84 20047.79 757587.9
0             23
355 355 0.1700651  0.02405001   -138.5787 279.1574 281.0694 117588.7
0             5
357 357 0.279062   0.08055722   -27.31586 56.63171 57.11662 146315.7
NULL          0
359 359 0.1107370  0.01927527   -105.6197 213.2394 214.7359 700526.1
0             2
361 361 0.07228863 0.02950606   -21.76253 45.52506 45.31682 0.05699268
NULL          0
364 364 0.09285628 0.02575071   -43.89713 89.79426 90.35921 207100.4
0             1
365 365 0.04475289 0.005873406  -238.1828 478.3655 480.426  26.28362
2.773893e-05  4
366 366 0.05853624 0.01194517   -92.11462 186.2292 187.4073 18.7308
0.0008876563  4
372 372 0.1681826  0.001565875  -32098.5  64199    64206.35 12222618
0             20
373 373 0.1861721  0.009691454  -989.3199 1980.640 1984.551 1026991
0             9
374 374 0.1908545  0.001808531  -29579.93 59161.86 59169.18 30091104
0             17
376 376 0.05068101 0.00253942   -1584.917 3171.834 3175.821 53.73581
6.726159e-07  13
377 377 0.05139656 0.002815447  -1321.405 2644.811 2648.619 636256.1
0             12
379 379 0.03694568 0.006740381  -128.9492 259.8984 261.2996 20.98622
0.0008149303  5
381 381 0.07612399 0.01622689   -78.65862 159.3172 160.4083 483650.9
0             2
382 382 0.07759566 0.00672728   -472.9804 947.9608 950.8512 3.636577
0.8883354     8
383 383 0.0839288  0.003826281  -1672.815 3347.631 3351.806 1468604
0             12
386 386 0.3076828  0.1087812    -17.42949 36.85897 36.93841 146249.3
NULL          0
388 388 0.08411514 0.002038605  -5915.418 11832.84 11838.28 3567333
0             19
389 389 0.2652991  0.07357968   -30.24967 62.49933 63.06428 3.671548
0.05534874    1
400 400 0.06066988 0.0005452555 -47049.76 94101.52 94108.94 15136253
0             34
401 401 0.07686127 0.001361266  -11364.06 22730.11 22736.18 2356.645
0             17
403 403 0.07934795 0.002621314  -3237.064 6476.128 6480.948 596.5487
2.885649e-118 14
406 406 0.1463095  0.005977766  -1750.296 3502.593 3506.988 134.6048
6.848968e-26  7
408 408 0.05098418 0.000751513  -18286.73 36575.46 36581.89 4644034
0             30
409 409 0.0498832  0.002783089  -1283.381 2568.762 2572.533 455252.2
0             12
410 410 0.04319069 0.0009440177 -8661.194 17324.39 17330.03 1757742
0             26
411 411 0.03459361 0.003043249  -562.9672 1127.934 1130.794 647584.6
0             9
412 412 0.06024919 0.004633276  -643.766  1289.532 1292.662 81.82442
7.009833e-14  9
413 413 0.06335487 0.001731585  -5029.547 10061.09 10066.29 1479695
0             21
415 415 0.05850944 0.002173851  -2779.123 5560.245 5564.83  1189948
0             16
416 416 0.2221953  0.006624451  -2817.223 5636.446 5641.472 1115447
0             10
417 417 0.2708675  0.004846135  -7204.336 14410.67 14416.72 4794338
0             11

	[[alternative HTML version deleted]]

Hi Ted,
  This is difficult to answer without a self-contained example -- one
that can be cut and pasted into an R session and run.  You might also
try coercing each element of aaa to a dataframe before calling rbind.
--Gray

On Fri, Jul 16, 2010 at 4:34 PM, Ted Byers <r.ted.byers at gmail.com>
wrote:
> I am feeling rather dumb right now.
>
> I created what I thought was a data.frame as follows:
>
> aaa <- lapply(split(moreinfo,list(moreinfo$m_id),drop = TRUE), fun_m_id)
> m_id_default_res <- do.call(rbind, aaa)
> print("==========================================")
> m_id_default_res
> print("==========================================")
> ndf <- m_id_default_res[, c('mid', 'estimate',
'sd', 'loglik', 'aic','bic',
> 'chisq', 'chisqpvalue', 'chisqdf')]
> ndf
>
> The data in NDF is perfect, exactly what I expected when I print the
> contents as shown in the last statement above.
>
> On the asumption tha tthat is a data frame, I tried
>
> dbWriteTable(con,"test1",ndf);
>
> But I received the following error:
>
> Error in function (classes, fdef, mtable) ?:
> ?unable to find an inherited method for function "dbWriteTable",
for
> signature "MySQLConnection", "character",
"matrix"
>
> Then, on the assumption it is trivial to convert a matrix into a
data.frame,
> i tried:
>
> dbWriteTable(con,"test2",as.data.frame(ndf));
>
> But this produced the following error:
> Error in write.table(x, file, nrow(x), p, rnames, sep, eol, na, dec,
> as.integer(quote), ?:
> ?unimplemented type 'list' in 'EncodeElement'
>
> The silly, and frustrating, thing is that I used dbWriteTable before, and
> that worked adequately. ?But that was with a simple data frame (within a
for
> loop, element by element - res$var[[i]] = expression), not the result of
> do.call(rbind(...)) ?The principle limitation I saw in my previous use of
> dbWriteTable is that all fields are given the type 'TEXT', and that
it
> insists on creating a new table. ?What I'd prefer is a kind of bulk
interset
> that just makes extra records for an existing table.
>
> So, given my past experience with dbWriteTable, it is a question of what
> do.call(rbind(..)) did to produce ndf that has the effect that dbWriteTable
> doesn't like that data.frame.
>
> So, then, what is the bext way to either get dbWriteTable working (ideally
> in a way that works around the limitations I mention above) or to do a bulk
> insert into my MySQL table (yes, I already have a table in the relevant
> schema with all the right data types for each field, and I load RMySQL at
> the start of my program.) ?In a worst case, I can live with an insertion
one
> record at a time.
>
> Thanks
>
> Ted
>
> PS: If it helps, here is the the contents of ndf - as shown by entering
> 'ndf' at the R prompt:
>> ndf
> ? ?mid estimate ? sd ? ? ? ? ? loglik ? ?aic ? ? ?bic ? ? ?chisq
> chisqpvalue ? chisqdf
> 206 206 0.1147528 ?0.04336918 ? -22.15483 46.30965 46.25556 4.433502
> 0.03524013 ? ?1
> 229 229 0.07999936 0.01999671 ? -56.41179 114.8236 115.5962 195307.1
> 0 ? ? ? ? ? ? 2
> 251 251 0.074421 ? 0.002171616 ?-4224.072 8450.144 8455.212 593302.2
> 0 ? ? ? ? ? ? 18
> 252 252 0.03710208 0.0004556731 -28426.82 56855.65 56862.45 3543373
> 0 ? ? ? ? ? ? 38
> 253 253 0.01397349 0.0005900857 -2925.179 5852.358 5856.677 283.9848
> 5.232282e-51 ?16
> 254 254 0.09043846 0.01528502 ? -119.108 ?240.216 ?241.7713 23.52441
> 3.139385e-05 ?3
> 255 255 0.05078883 0.0006021373 -28294.38 56590.76 56597.63 1988844
> 0 ? ? ? ? ? ? 35
> 260 260 0.03392846 0.005499136 ?-166.5730 335.1461 336.7837 10.83060
> 0.05484413 ? ?5
> 268 268 0.05357114 0.01785082 ? -35.3407 ?72.6814 ?72.87863 82995.79
> 0 ? ? ? ? ? ? 2
> 286 286 0.09321947 0.01987217 ? -74.20157 150.4031 151.4942 1.698603
> 0.6372445 ? ? 3
> 290 290 0.03841793 0.006584153 ?-144.8139 291.6277 293.1541 135.8937
> 2.902434e-29 ?3
> 292 292 0.06289269 0.01988338 ? -37.66325 77.32651 77.6291 ?143099.8
> 0 ? ? ? ? ? ? 2
> 297 297 0.01674874 0.004047625 ?-86.52035 175.0407 175.8739 47.27713
> 3.034432e-10 ?3
> 302 302 0.02878066 0.003876092 ?-250.1428 502.2857 504.293 ?9.22447
> 0.2369393 ? ? 7
> 306 306 0.07904849 0.0004164051 -127449.0 254899.9 254908.4 111574416
> 0 ? ? ? ? ? ? 40
> 307 307 0.01655872 0.001320903 ?-795.7314 1593.463 1596.513 57.38622
> 1.127804e-08 ?10
> 308 308 0.02631102 0.000884155 ?-4095.149 8192.298 8197.081 142.8876
> 3.904898e-20 ?21
> 309 309 0.09891599 0.0084501 ? ?-453.9474 909.8947 912.8147 357135.5
> 0 ? ? ? ? ? ? 8
> 310 310 0.09332047 0.004580396 ?-1399.262 2800.524 2804.552 217126
> 0 ? ? ? ? ? ? 13
> 311 311 0.06378327 0.0005049166 -59848.62 119699.2 119706.9 59481893
> 0 ? ? ? ? ? ? 34
> 313 313 0.06203001 0.0006486936 -34546.67 69095.34 69102.46 18207698
> 0 ? ? ? ? ? ? 32
> 316 316 0.2222173 ?0.07026985 ? -25.04100 52.08199 52.38458 18002.22
> 0 ? ? ? ? ? ? 2
> 317 317 0.04405086 0.0005949207 -22578.44 45158.88 45165.49 8923236
> 0 ? ? ? ? ? ? 33
> 320 320 0.05747093 0.006634162 ?-289.2357 580.4714 582.7889 8.641322
> 0.2794433 ? ? 7
> 321 321 0.06365155 0.003692525 ?-1115.037 2232.073 2235.767 19.10553
> 0.08601337 ? ?12
> 322 322 0.05737672 0.01532991 ? -54.01363 110.0273 110.6663 9.597753
> 0.008238998 ? 2
> 323 323 0.03116934 0.001909146 ?-1188.573 2379.146 2382.73 ?109.7663
> 6.656046e-18 ?12
> 324 324 0.03027327 0.0004146385 -23922.15 47846.3 ?47852.88 47330365
> 0 ? ? ? ? ? ? 32
> 325 325 0.06047783 0.00922026 ? -163.6356 329.2711 331.0323 1695781
> 0 ? ? ? ? ? ? 3
> 326 326 0.05627898 0.0008642285 -16432.57 32867.13 32873.48 3405089
> 0 ? ? ? ? ? ? 29
> 327 327 0.07052627 0.001144912 ?-13851.16 27704.33 27710.57 6344780
> 0 ? ? ? ? ? ? 26
> 329 329 0.04571883 0.003557502 ?-674.0654 1350.131 1353.237 45.31164
> 1.910501e-06 ?10
> 331 331 0.0728699 ?0.000962558 ?-20733.71 41469.42 41476.07 16860414
> 0 ? ? ? ? ? ? 26
> 333 333 0.06777295 0.001090324 ?-14256.93 28515.86 28522.12 8260494
> 0 ? ? ? ? ? ? 23
> 335 335 0.03928983 0.00279747 ? -834.6475 1671.295 1674.578 26.41114
> 0.003224856 ? 10
> 336 336 0.01300952 0.003339059 ?-80.1311 ?162.2622 162.9703 33.12369
> 3.032883e-07 ?3
> 339 339 0.01881150 0.00181342 ? -532.1417 1066.283 1068.956 54.56913
> 1.471288e-08 ?9
> 340 340 0.05145835 0.0005917994 -29970.55 59943.1 ?59950.03 9414130
> 0 ? ? ? ? ? ? 34
> 342 342 0.0499304 ?0.008318394 ?-143.8965 289.793 ?291.3765 272596.5
> 0 ? ? ? ? ? ? 5
> 343 343 0.09286674 0.001970758 ?-7496.029 14994.06 14999.76 5806685
> 0 ? ? ? ? ? ? 18
> 345 345 0.09734419 0.02075166 ? -73.24905 148.4981 149.5891 378227
> 0 ? ? ? ? ? ? 2
> 348 348 0.1214271 ?0.0294484 ? ?-52.8435 ?107.687 ?108.5202 77506.98
> 0 ? ? ? ? ? ? 3
> 349 349 0.03902424 0.01378808 ? -33.94858 69.89716 69.9766 ?128122.4
> 0 ? ? ? ? ? ? 2
> 351 351 0.08562618 0.01617962 ? -96.8174 ?195.6348 196.967 ?600605.1
> 0 ? ? ? ? ? ? 3
> 353 353 0.07881848 0.001481375 ?-10019.92 20041.84 20047.79 757587.9
> 0 ? ? ? ? ? ? 23
> 355 355 0.1700651 ?0.02405001 ? -138.5787 279.1574 281.0694 117588.7
> 0 ? ? ? ? ? ? 5
> 357 357 0.279062 ? 0.08055722 ? -27.31586 56.63171 57.11662 146315.7
> NULL ? ? ? ? ?0
> 359 359 0.1107370 ?0.01927527 ? -105.6197 213.2394 214.7359 700526.1
> 0 ? ? ? ? ? ? 2
> 361 361 0.07228863 0.02950606 ? -21.76253 45.52506 45.31682 0.05699268
> NULL ? ? ? ? ?0
> 364 364 0.09285628 0.02575071 ? -43.89713 89.79426 90.35921 207100.4
> 0 ? ? ? ? ? ? 1
> 365 365 0.04475289 0.005873406 ?-238.1828 478.3655 480.426 ?26.28362
> 2.773893e-05 ?4
> 366 366 0.05853624 0.01194517 ? -92.11462 186.2292 187.4073 18.7308
> 0.0008876563 ?4
> 372 372 0.1681826 ?0.001565875 ?-32098.5 ?64199 ? ?64206.35 12222618
> 0 ? ? ? ? ? ? 20
> 373 373 0.1861721 ?0.009691454 ?-989.3199 1980.640 1984.551 1026991
> 0 ? ? ? ? ? ? 9
> 374 374 0.1908545 ?0.001808531 ?-29579.93 59161.86 59169.18 30091104
> 0 ? ? ? ? ? ? 17
> 376 376 0.05068101 0.00253942 ? -1584.917 3171.834 3175.821 53.73581
> 6.726159e-07 ?13
> 377 377 0.05139656 0.002815447 ?-1321.405 2644.811 2648.619 636256.1
> 0 ? ? ? ? ? ? 12
> 379 379 0.03694568 0.006740381 ?-128.9492 259.8984 261.2996 20.98622
> 0.0008149303 ?5
> 381 381 0.07612399 0.01622689 ? -78.65862 159.3172 160.4083 483650.9
> 0 ? ? ? ? ? ? 2
> 382 382 0.07759566 0.00672728 ? -472.9804 947.9608 950.8512 3.636577
> 0.8883354 ? ? 8
> 383 383 0.0839288 ?0.003826281 ?-1672.815 3347.631 3351.806 1468604
> 0 ? ? ? ? ? ? 12
> 386 386 0.3076828 ?0.1087812 ? ?-17.42949 36.85897 36.93841 146249.3
> NULL ? ? ? ? ?0
> 388 388 0.08411514 0.002038605 ?-5915.418 11832.84 11838.28 3567333
> 0 ? ? ? ? ? ? 19
> 389 389 0.2652991 ?0.07357968 ? -30.24967 62.49933 63.06428 3.671548
> 0.05534874 ? ?1
> 400 400 0.06066988 0.0005452555 -47049.76 94101.52 94108.94 15136253
> 0 ? ? ? ? ? ? 34
> 401 401 0.07686127 0.001361266 ?-11364.06 22730.11 22736.18 2356.645
> 0 ? ? ? ? ? ? 17
> 403 403 0.07934795 0.002621314 ?-3237.064 6476.128 6480.948 596.5487
> 2.885649e-118 14
> 406 406 0.1463095 ?0.005977766 ?-1750.296 3502.593 3506.988 134.6048
> 6.848968e-26 ?7
> 408 408 0.05098418 0.000751513 ?-18286.73 36575.46 36581.89 4644034
> 0 ? ? ? ? ? ? 30
> 409 409 0.0498832 ?0.002783089 ?-1283.381 2568.762 2572.533 455252.2
> 0 ? ? ? ? ? ? 12
> 410 410 0.04319069 0.0009440177 -8661.194 17324.39 17330.03 1757742
> 0 ? ? ? ? ? ? 26
> 411 411 0.03459361 0.003043249 ?-562.9672 1127.934 1130.794 647584.6
> 0 ? ? ? ? ? ? 9
> 412 412 0.06024919 0.004633276 ?-643.766 ?1289.532 1292.662 81.82442
> 7.009833e-14 ?9
> 413 413 0.06335487 0.001731585 ?-5029.547 10061.09 10066.29 1479695
> 0 ? ? ? ? ? ? 21
> 415 415 0.05850944 0.002173851 ?-2779.123 5560.245 5564.83 ?1189948
> 0 ? ? ? ? ? ? 16
> 416 416 0.2221953 ?0.006624451 ?-2817.223 5636.446 5641.472 1115447
> 0 ? ? ? ? ? ? 10
> 417 417 0.2708675 ?0.004846135 ?-7204.336 14410.67 14416.72 4794338
> 0 ? ? ? ? ? ? 11
>
> ? ? ? ?[[alternative HTML version deleted]]
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>


-- 
Gray Calhoun

Assistant Professor of Economics, Iowa State University
http://www.econ.iastate.edu/~gcalhoun/