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-
>[email protected]] 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 <[email protected]>
>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-
>> >[email protected]] 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
><[email protected]>
>> >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-
>> >> >[email protected]] 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
>> >> >[email protected]
>> >> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>> >users
>> >>
>> >>
>> >>
>> >> _______________________________________________
>> >> sqlite-users mailing list
>> >> [email protected]
>> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>> >users
>> >>
>> >_______________________________________________
>> >sqlite-users mailing list
>> >[email protected]
>> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [email protected]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>_______________________________________________
>sqlite-users mailing list
>[email protected]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
