SQLite Forum

Views, Casting vs and converters
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.