[R] Data type problem when extract data from SQLite to R by using RSQLite

[Seth Falcon]

Hi Jia,

On Mon, Feb 28, 2011 at 12:37 PM, chen jia <chen_1002 at fisher.osu.edu> wrote:
> When I extract data from SQLite to R, the data types (or modes) of the
> extracted data seems to be determined by the value of the first row.
> Please see the following example.

It would help to provide the output of sessionInfo() as well as the
schema definition for the table in SQLite (or at least description of
how it was created).

Here's an example that works as you'd like:

    > library(RSQLite)
    > db = dbConnect(SQLite(), dbname = ":memory:")
    > dbGetQuery(db, "create table t (a int, b real, c text)")
    > df = data.frame(a=c(NA, 1L, 2L), b=c(NA, 1.1, 2.2), c=c(NA, "x",
"y"),stringsAsFactors=FALSE)
    > df
       a   b    c
    1 NA  NA <NA>
    2  1 1.1    x
    3  2 2.2    y
    > dbGetPreparedQuery(db, "insert into t values (?, ?, ?)", df)
    > dbGetQuery(db, "select * from t")
       a   b    c
    1 NA  NA <NA>
    2  1 1.1    x
    3  2 2.2    y
    > sapply(dbGetQuery(db, "select * from t"), typeof)
              a           b           c
      "integer"    "double" "character"
    > sapply(dbGetQuery(db, "select * from t limit 1"), typeof)
              a           b           c
      "integer"    "double" "character"
    > sapply(dbGetQuery(db, "select a from t limit 1"), typeof)
            a
    "integer"
    > sapply(dbGetQuery(db, "select a from t limit 2"), typeof)
            a
    "integer"
    > sapply(dbGetQuery(db, "select a from t limit 1"), typeof)
            a
    "integer"


> sessionInfo()
R version 2.11.1 (2010-05-31)
x86_64-apple-darwin9.8.0

locale:
[1] en_US.UTF-8/en_US.UTF-8/C/C/en_US.UTF-8/en_US.UTF-8

attached base packages:
[1] stats     graphics  grDevices datasets  utils     methods   base

other attached packages:
[1] RSQLite_0.9-4 DBI_0.2-5

loaded via a namespace (and not attached):
[1] tools_2.11.1




-- 
Seth Falcon | @sfalcon | http://userprimary.net/