SQLite Forum

Views, Casting vs and converters
Login

Views, Casting vs and converters

(1) By bleepbloop on 2021-09-28 04:34:54 [link] [source]

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

(2) By Gunter Hick (gunter_hick) on 2021-09-28 08:58:16 in reply to 1 [link] [source]

I think you are not properly separating SQL issues from Python issues.

For SQLite data types see https://sqlite.org/datatype3.html. The builtin types are NULL, INTEGER, REAL, TEXT and BLOB. It is not possible to define additional types in SQLite. Any type name you provide is mapped to one of the builtin types.

For SQL Syntax see https://sqlite.org/lang.html, https://sqlite.org/lang_select.html and https://sqlite.org/lang_expr.html.

The CAST SQL function attempts to convert whatever actual value is provided to a value of the builtin types the provided type-name is mapped to.

An integer value of 1 stored in column my_col with a declared type of INTEGER is stored as an integer value of 1.

my_col thus returns the integer value 1,
CAST(my_col as FLOAT) returns real value 1.0,
CAST(my_col as BOOLEAN) leaves the integer value 1 as the integer value 1

SQLite does not attach any meaning to the column name. It seems you are attempting to convey information to Python through this channel. IMHO this violates the layer model. SQLite is in the data layer; SQL Queries should focus on retrieval of the data. Your Python script handles the presentation layer, so it should already know how the data is returned, and not rely on abusing column names as data.

Python questions are better posed on a Python orieanted plattform.

(3) By David Raymond (dvdraymond) on 2021-09-28 13:36:33 in reply to 1 [source]

I believe when you set detect_types to both PARSE_DELCTYPES and PARSE_COLNAMES it prioritizes the column names first, then the declared types.

Since your declared type of my_col is INTEGER, and you only declared a converter for "BOOLEAN"

my_col as "my_col float [FLOAT]"
Because of the column name it looks for a converter for "FLOAT" and doesn't find it.
Since "my_col" is a bare column it checks for a converter for "INTEGER" and doesn't find it.
So it returns what it got from the query, which is 1

CAST(my_col as FLOAT) as [my_col cast float]
The column name "my_col cast float" doesn't conform to the "name [type]" format, so no check is done for a converter on the column name.
CAST(my_col as FLOAT) is not a bare column any more, so there is no declared type for it to look up a converter for.
So it returns what it got from the query, which is my_col cast as a float, or 1.0

my_col as "my_col bol [BOOLEAN]"
The column name of the correct format, so it checks for a converter for "BOOLEAN" and finds it.
It passes what it got from the query (1) to your registered function for "BOOLEAN" and gets back True

CAST(my_col as BOOLEAN) as [my_col cast bol]
The column name "my_col cast bol" doesn't conform to the "name [type]" format, so no check is done for a converter on the column name.
CAST(my_col as BOOLEAN) isn't a bare column any more, so there is no declared type for it to look up a converter for.
So it returns what it got from the query, which is my_col cast to the SQLite type of "BOOLEAN", which following the rules results in numeric affinity. 1 as numeric is still 1, so that's what you get back.


So the important things for you is that for PARSE_DECLTYPES when you do...
cast(my_col as SOMETHING)
...that "SOMETHING" is not carried through as the declared type. PARSE_DECLTYPES only works if it's selecting just a bare column from a table.
And for PARSE_COLNAMES, your column names need to follow the format of "name [type]"