This is how pySqlite2 (aka sqlite3) wrapper in Python achieves such data 
manipulations, by tagging the column name specially (since by default rows are 
only retrieved and accessible as positional locations in a row tuple.  If you 
open the database and tell it to "parse column types" then it will retrieve the 
column name from the cursor description then split the column name at the 
space.  The righthand token will be used to lookup a "converter" function in a 
dictionary of "converters", and will then apply the converter function to the 
retrieved data for that column AFTER it is retrieved from SQLite3 and BEFORE it 
is returned to the user.

That means that you could write a function called "JulianToDatetime(number)" 
and write your SQL Query (with column name parsing enabled) as

select column1 as [column1 JulianDateTime] from table;

and add a converter entry {"JulianDateTime": JulianToDateTime} and the field 
would automagically and transparently be "converted" with the JulianToDateTime 
function before it is returned to the user program.  For data going the other 
way, you can write an "adapter" function that converts the Python "datetime" 
type into a JulianDay number that occurs automagically between you passing the 
data binding to the wrapper, and the wrapper passing the data into SQLite3.

pySqlite2 (now sqlite3) Python wrapper can also parse the table declared type 
in the same way to apply automatic coverters on data retrieval (I do not know 
how it gets the declared type -- I'd have to go look in the source for 
pySqlite2.  However, once a returned column is an aggregate or an expression, 
it likely will not carry the origin declaration type anymore and only 
overloading the datatype into the column name (via as) will work.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Bart Smissaert
>Sent: Saturday, 6 January, 2018 15:54
>To: SQLite mailing list
>Subject: Re: [sqlite] sqlite3_column_decltype and max and min
>
>OK, thanks will have a look at that.
>So, how about this one then:
>
>select max(integer_date) as [max_integer_date int_date] from table1
>
>?
>
>RBS
>
>On Sat, Jan 6, 2018 at 10:35 PM, Keith Medcalf <kmedc...@dessus.com>
>wrote:
>
>>
>> Yes.  You can use "pragma table_info(tablename)" to get information
>about
>> a table, the whole table, and nothing but the one table.  Or you
>can use
>> the bunch-o-views which will give you a data dictionary to be able
>to get
>> the same data by treating all the various introspection pragma's as
>tables
>> and letting you query for specific information such as:
>>
>> select affinity from SysColumns where ObjectType='table' and
>ObjectName='myTableThatIAMInterestedIn'
>> and
>ColumnName='TheColumnNameForWhichIWantToKnowTheDeclaredAffinity';
>>
>> or perhaps even
>>
>> select ObjectType, ObjectName, from SysColumns where ColumnName = '
>> GollyJesusHereAreAllTheObjectsHavingColumnsWithThisName';
>>
>>
>> One is simply more selective that the other.  The pragma's use
>> ill-conceived field names (containing spaces and other kaiboshery)
>whereas
>> the views rename such ill-conception to something not quite so ill
>such
>> that "escaping" is not required (which is as God intended when she
>invented
>> the concept of variable naming and so forth).
>>
>> ---
>> The fact that there's a Highway to Hell but only a Stairway to
>Heaven says
>> a lot about anticipated traffic volume.
>>
>>
>> >-----Original Message-----
>> >From: sqlite-users [mailto:sqlite-users-
>> >boun...@mailinglists.sqlite.org] On Behalf Of Bart Smissaert
>> >Sent: Saturday, 6 January, 2018 15:00
>> >To: SQLite mailing list
>> >Subject: Re: [sqlite] sqlite3_column_decltype and max and min
>> >
>> >Not sure how this works.
>> >I had a look at your views.
>> >Are you saying that I should get the data type (only if
>> >sqlite3_column_decltype produces null) by querying these views,
>> >passing it
>> >the column name?
>> >
>> >RBS
>> >
>> >
>> >On Sat, Jan 6, 2018 at 7:27 PM, Keith Medcalf
><kmedc...@dessus.com>
>> >wrote:
>> >
>> >>
>> >> select max(integer_date) as [max_integer_date int_date] from
>table1
>> >>
>> >>
>> >> fetch the cursor.description and split the column name at the
>> >space.  The
>> >> right part is the datatype for your handy dandy use.
>> >>
>> >> You may also be able to use the same method in the table
>> >declaration,
>> >> provided that the type carries through, but I do not remember
>> >offhand.
>> >>
>> >> create table1([integer_date] [integer int_date])
>> >>
>> >> The former is how pySqlite2 links adapters and converters to the
>> >query.
>> >>
>> >> ---
>> >> The fact that there's a Highway to Hell but only a Stairway to
>> >Heaven says
>> >> a lot about anticipated traffic volume.
>> >>
>> >>
>> >> >-----Original Message-----
>> >> >From: sqlite-users [mailto:sqlite-users-
>> >> >boun...@mailinglists.sqlite.org] On Behalf Of Bart Smissaert
>> >> >Sent: Saturday, 6 January, 2018 10:45
>> >> >To: General Discussion of SQLite Database
>> >> >Subject: [sqlite] sqlite3_column_decltype and max and min
>> >> >
>> >> >Is there any way with sqlite3_column_decltype (or otherwise) to
>> >get
>> >> >the
>> >> >declared data type (as in the table create sql in
>SQLite_master)
>> >when
>> >> >it is
>> >> >a simple expression such as max and min?
>> >> >
>> >> >for example we have a table created like this:
>> >> >
>> >> >create table1([integer_date] int_date)
>> >> >
>> >> >and we do:
>> >> >
>> >> >select max(integer_date) from table1
>> >> >
>> >> >I would then like to get returned int_date, rather than
>integer.
>> >> >
>> >> >The custom datatype int_date is needed for formatting purpose
>to
>> >tell
>> >> >the
>> >> >app that receives
>> >> >the data that the column holds dates as integers.
>> >> >
>> >> >To do this in application code is not that simple, so I hope
>there
>> >is
>> >> >some
>> >> >simpler way.
>> >> >
>> >> >
>> >> >RBS
>> >> >_______________________________________________
>> >> >sqlite-users mailing list
>> >> >sqlite-users@mailinglists.sqlite.org
>> >> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>> >users
>> >>
>> >>
>> >>
>> >> _______________________________________________
>> >> sqlite-users mailing list
>> >> sqlite-users@mailinglists.sqlite.org
>> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>> >users
>> >>
>> >_______________________________________________
>> >sqlite-users mailing list
>> >sqlite-users@mailinglists.sqlite.org
>> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to