OK, thanks.
I think I will code something along similar lines, so that I have the
declared column type (the custom types, eg int_date) always easily
available.
Of course with casting and UDF's etc. it will get a bit tricky.
RBS
On Sat, Jan 6, 2018 at 11:26 PM, Keith Medcalf <kmedc...@dessus.com> wrote:
>
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
