SQlite and dataframes

[jondan99]

Hello

I am reading a sqlite table into a dataframe, where one column is
r real
and all values are missing. I use
df=DBInterface.execute(db, sql) |> DataFrame;
and get df.r to be of type missing (think it should be Union{Missing,Float64} )
SQLite.jl returns the correct type, but it does not make it when converted to a dataframe.

not sure if this is a bug, but thought I would let you all know

best, jack

[nalimilan]

This is probably due to:

SQLite.jl/src/tables.jl

Line 33 in f9c7d62

return nothing # fallback to the actual column types of the result

@quinnj What's the reason for using the actual content types instead of the ones declared in the SQL table?

[quinnj]

This is the default definition because in sqlite, declared column types are just "suggestions" instead of any kind of real constraint. Indeed, you can declare a column type to be DOUBLE, but then insert as many strings as you want and it won't complain. Hence we ran into too many issues where this "flexibility" caused issues when trying to query data from a table.

I think we should bring back the stricttypes::Bool=false keyword argument though, so you could do DBInterface.execute(db, sql; stricttypes=true) and it would produce a Tables.Schema that returns the declared column types.