[R] Troubles with DBI's dbWriteTable in RMySQL

Gray Calhoun gray.calhoun at gmail.com
Mon Jul 19 22:35:15 CEST 2010


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/



More information about the R-help mailing list