SQlite and dataframes #275

Activity

1. nalimilan commented on Nov 28, 2021

   

   nalimilan

   on Nov 28, 2021

   More actions

   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?
2. quinnj commented on Dec 2, 2021

   

   quinnj

   on Dec 2, 2021

   Member

   More actions

   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.

   👍React with 👍1Reacted by Milan Bouchet-Valat
3. quinnj commented on Oct 4, 2026

   

   quinnj

   on Oct 4, 2026

   Member

   More actions

   This is supported through the strict result option added in #304, included in v1.6.0. I verified registered SQLite.jl 1.9.0 on Julia 1.9.4 and 1.13.1: an all-NULL REAL column becomes Union{Missing,Float64} when using the prepared-statement form:

   stmt = DBInterface.prepare(db, sql)
   df = try
       DataFrame(DBInterface.execute(stmt, (); strict=true))
   finally
       DBInterface.close!(stmt)
   end

   The explicit () is the parameter collection. It lets strict=true reach SQLite's result option; the connection/SQL keyword convenience form treats keywords as bound query parameters instead. Default result reading still infers types from values to handle SQLite's flexible column typing.

   Both runtimes pass seven checks for row counts, all-missing values, strict column types, and an empty result's declared type. Closing this as implemented without changing the default behavior.

   AI disclosure: This work was prepared with assistance from OpenAI Codex.
4. quinnj

   closed this as completedon Oct 4, 2026