On Sun, Jan 31, 2016 at 6:25 PM, James K. Lowden
<jklowden at schemamania.org> wrote:
> On Sat, 30 Jan 2016 20:50:17 -0500
> Jim Callahan <jim.callahan.orlando at gmail.com> wrote:
> But you already have pandas.read_sql_query. While that function
> isn't really what I'd call simple, the complexity afaict -- dates,
> floats, and chunks -- can be laid at Python's feet.
I use R rather than python but the problem of dates is significant and
I assume the same problem as in python. Consider this query:
select myDate + 1 from myTable
Assume that myTable comes from an R data frame with a myDate column
that has R class of "Date". Internally R stores the myDate column as
days since 1970-01-01 and since SQLite has no Date class it sends that
number to SQLite. The select statement then adds 1 giving a number
representing the next day but when one gets it back to R how can R
know that that number is intended to represent a Date and so convert
it to R's Date class? In fact it can't. With databases that have a
Date type this is not a problem but it is a significant problem with
SQLite.
There are some hackish workarounds. For example, consider this
self-contained reproducible R code:
library(sqldf) # load package and dependencies
myTable <- data.frame(myDate = as.Date("2000-01-31")) # define myTable
sqldf("select myDate + 1 myDate from myTable")
sqldf will use the heuristic of assuming that the myDate in the output
has the same class as the myDate in the input (because they have the
same name) and so will convert the output myDate column to the R
"Date" class but the ability of such a heuristic to work is fairly
limited.
It is also possible to specify to sqldf the class of each output
column but this is tedious and puts SQLite at a disadvantage since it
is unnecessary if used with a different backend database that is able
to return a Date class column.
With numeric and character columns there is not much problem but as
soon as one gets to date and date/time columns then this impedence
mismatch appears and is one fo the main reasons an R user might decide
to use a different backend.
