On Mon, Feb 1, 2016 at 8:27 AM, Keith Medcalf <kmedcalf at dessus.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.
>
> What is the problem exactly? Sounds like the R thingy is broken. I do this
> all the time using Python. You simply have to compile the library with
> column_metadata and then use it. Sheesh. Even pysqlite has done this just
> peachyu fine for about a decade.
>
> 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.
>
> There is no impedence mismatch. Simply inadequate wattage by the person(s)
> solving the problem. As I said, this problem has been solved with SQLite and
> Python for a decade. So I would suggest the problem is that the wattage was
> so low, the lights were completely out.
>
The impedence in the example is that on the sqlite side the type is
numeric and on the R side it is of Date class. It is impossible to
know which sqlite numeric types are intended to represent dates and
which are intended to represent numbers so when they are sent back to
R there is no automatic assignment to Date class. Either the
programmer has to specify it (which is tedious) or else one has to
rely on heuristics such as assuming that any output field having the
same name as input field must also have the same type (but such
heuristics do not cover all cases).
If you believe that python can know that a date is being returned from
sqlite without the programmer specifying it even though sqlite has no
date class, I suggest providing a self contained reproducible example
of python code to illustrate it as I did with R.
