SQLite Forum

Timeline
Login

1 forum post by user bleepbloop

2021-09-28
04:34 Post: Views, Casting vs and converters (artifact: 5ab04ddfdf user: bleepbloop)

(First post, sorry if I'm doing anything wrong)

Working on a converter for a view (explanation below) and my starting point was the Python SQLITE doc but I'm getting some behavior I don't understand. Also not quite sure where to look for answers between SQLITE and python.

Specifically, the behavior when accessing a column (my_col INTEGER) differs when using CAST(my_col as SOME_TYPE) from what I see when using my_col as "my_col_some_type [SOME_TYPE]" (assuming that there is a converter defined and registered for the appropriate type).

With a simple converter defined for BOOLEANs, the results were:

  • my_col as "my_col float [FLOAT]" evaluated as 1
  • CAST(my_col as FLOAT) as [my_col cast float] evaluated as 1.0
  • my_col as "my_col bol [BOOLEAN]" evaluated as True
  • CAST(my_col as BOOLEAN) as [my_col cast bol] evaluated as 1

Is there a reason/recommendation I'm missing about why the CAST() approach seems to return the expected results with built-in sqlite types (I tried with FLOAT) but doesn't seem to work with types I've defined. Alternatively, any understanding on why the other approach my_col as "my_col_some_type [SOME_TYPE]" approach seems opposite, working on types I've defined but not with built-in?

Potentially related: from the relevant section in default adapters and converters — link: https://docs.python.org/3/library/sqlite3.html#default-adapters-and-converters — uses both detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES but from what I saw doesn't clarify on why both are included. Could this value for detect_types impact behavior?

Notes on what I'm trying to do: using a view that joins the two tables below, I'm essentially trying to pivot from (using a silly example)

idclothing_name
1shirt
2pants
3dress

and

name clothing_type_id
bob1
bob2
ann3

to

nameclothing
bobshirt, pants
anndress

and I want to implement a converter to return the list of clothing items for each person as a list (rather than the string from group_concat).