> > 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.
