[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