Re: [sqlite] data type from join multiple tables
On Fri, May 14, 2010 at 9:01 AM, Fabio Spadaro wrote: > I need to identify data types extracted from a > join between multiple tables without using cross-checking table_info more > pragmatic. > Could you clarify what you really want to do? Your question is not clear at all, at least to me. What do you mean by "using cross-checking table_info more pragmatic"? More pragmatic than what? > Is there a faster way to do it? > Faster than what? If you want data_types, you can use SELECT Typeof(column_name) > -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] data type from join multiple tables
Or you can use sqlite3_column_decltype in conjunction with sqlite3_column_table_name, sqlite3_column_database_name, sqlite3_column_origin_name but to use the last three functions you will need compile sqlite with -DSQLITE_ENABLE_COLUMN_METADATA directive on LCFLAGS or something like this! - Mensagem original - De: "P Kishor" Para: "General Discussion of SQLite Database" Enviadas: Sexta-feira, 14 de Maio de 2010 11:05:43 Assunto: Re: [sqlite] data type from join multiple tables On Fri, May 14, 2010 at 9:01 AM, Fabio Spadaro wrote: > I need to identify data types extracted from a > join between multiple tables without using cross-checking table_info more > pragmatic. > Could you clarify what you really want to do? Your question is not clear at all, at least to me. What do you mean by "using cross-checking table_info more pragmatic"? More pragmatic than what? > Is there a faster way to do it? > Faster than what? If you want data_types, you can use SELECT Typeof(column_name) > -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Atenciosamente, Israel Lins Albuquerque Desenvolvimento Polibrás Brasil Software Ltda. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] data type from join multiple tables
hi, 2010/5/14 P Kishor > On Fri, May 14, 2010 at 9:01 AM, Fabio Spadaro > wrote: > > I need to identify data types extracted from a > > join between multiple tables without using cross-checking table_info more > > pragmatic. > > > > Could you clarify what you really want to do? Your question is not > clear at all, at least to me. What do you mean by "using > cross-checking table_info more pragmatic"? More pragmatic than what? > > > Is there a faster way to do it? > > > > Faster than what? > > If you want data_types, you can use SELECT Typeof(column_name) > > > > > > > > > > -- > Puneet Kishor http://www.punkish.org > Carbon Model http://carbonmodel.org > Charter Member, Open Source Geospatial Foundation http://www.osgeo.org > Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor > Nelson Institute, UW-Madison http://www.nelson.wisc.edu > --- > Assertions are politics; backing up assertions with evidence is science > === > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > I use python sqlite embedded and in my application I have a panel where you can type a query and see the results in a panel's grid Now suppose that I type a query like "select a.field1, b.field2 from table1 a, table2 b " such data will be displayed in grid but I need to know what types of data I obtained. How do it? -- Fabio Spadaro www.fabiospadaro.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] data type from join multiple tables
On Fri, May 14, 2010 at 9:49 AM, Fabio Spadaro wrote: > hi, > > 2010/5/14 P Kishor > >> On Fri, May 14, 2010 at 9:01 AM, Fabio Spadaro >> wrote: >> > I need to identify data types extracted from a >> > join between multiple tables without using cross-checking table_info more >> > pragmatic. >> > >> >> Could you clarify what you really want to do? Your question is not >> clear at all, at least to me. What do you mean by "using >> cross-checking table_info more pragmatic"? More pragmatic than what? >> >> > Is there a faster way to do it? >> > >> >> Faster than what? >> >> If you want data_types, you can use SELECT Typeof(column_name) >> >> >> >> >> > >> >> >> >> -- >> Puneet Kishor http://www.punkish.org >> Carbon Model http://carbonmodel.org >> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org >> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor >> Nelson Institute, UW-Madison http://www.nelson.wisc.edu >> --- >> Assertions are politics; backing up assertions with evidence is science >> === >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > I use python sqlite embedded and in my application I have a panel where you > can > type a query and see the results in a panel's grid > Now suppose that I type a query like "select > a.field1, b.field2 from table1 a, table2 b " such data > will be displayed in grid but I need to know what types of data I obtained. > How do it? SELECT a.field1, Typeof(a.field1), b.field2, Typeof(b.field2) FROM table1 a, table2 b... > > -- > Fabio Spadaro > www.fabiospadaro.com > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] data type from join multiple tables
Hi, 2010/5/14 P Kishor > On Fri, May 14, 2010 at 9:49 AM, Fabio Spadaro > wrote: > > hi, > > > > 2010/5/14 P Kishor > > > >> On Fri, May 14, 2010 at 9:01 AM, Fabio Spadaro > > >> wrote: > >> > I need to identify data types extracted from a > >> > join between multiple tables without using cross-checking table_info > more > >> > pragmatic. > >> > > >> > >> Could you clarify what you really want to do? Your question is not > >> clear at all, at least to me. What do you mean by "using > >> cross-checking table_info more pragmatic"? More pragmatic than what? > >> > >> > Is there a faster way to do it? > >> > > >> > >> Faster than what? > >> > >> If you want data_types, you can use SELECT Typeof(column_name) > >> > >> > >> > >> > >> > > >> > >> > >> > >> -- > >> Puneet Kishor http://www.punkish.org > >> Carbon Model http://carbonmodel.org > >> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org > >> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor > >> Nelson Institute, UW-Madison http://www.nelson.wisc.edu > >> --- > >> Assertions are politics; backing up assertions with evidence is science > >> === > >> ___ > >> sqlite-users mailing list > >> sqlite-users@sqlite.org > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> > > > > I use python sqlite embedded and in my application I have a panel where > you > > can > > type a query and see the results in a panel's grid > > Now suppose that I type a query like "select > > a.field1, b.field2 from table1 a, table2 b " such data > > will be displayed in grid but I need to know what types of data I > obtained. > > How do it? > > > SELECT a.field1, Typeof(a.field1), b.field2, Typeof(b.field2) > FROM table1 a, table2 b... > > > > > > -- > > Fabio Spadaro > > www.fabiospadaro.com > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > It does not work, It always return data type text. -- Fabio Spadaro www.fabiospadaro.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] data type from join multiple tables
On Fri, May 14, 2010 at 10:11 AM, Fabio Spadaro wrote:
> Hi,
>
> 2010/5/14 P Kishor
>
>> On Fri, May 14, 2010 at 9:49 AM, Fabio Spadaro
>> wrote:
>> > hi,
>> >
>> > 2010/5/14 P Kishor
>> >
>> >> On Fri, May 14, 2010 at 9:01 AM, Fabio Spadaro > >
>> >> wrote:
>> >> > I need to identify data types extracted from a
>> >> > join between multiple tables without using cross-checking table_info
>> more
>> >> > pragmatic.
>> >> >
>> >>
>> >> Could you clarify what you really want to do? Your question is not
>> >> clear at all, at least to me. What do you mean by "using
>> >> cross-checking table_info more pragmatic"? More pragmatic than what?
>> >>
>> >> > Is there a faster way to do it?
>> >> >
>> >>
>> >> Faster than what?
>> >>
>> >> If you want data_types, you can use SELECT Typeof(column_name)
>> >>
>> >>
>> >>
>> >>
>> >
>> > I use python sqlite embedded and in my application I have a panel where
>> you
>> > can
>> > type a query and see the results in a panel's grid
>> > Now suppose that I type a query like "select
>> > a.field1, b.field2 from table1 a, table2 b " such data
>> > will be displayed in grid but I need to know what types of data I
>> obtained.
>> > How do it?
>>
>>
>> SELECT a.field1, Typeof(a.field1), b.field2, Typeof(b.field2)
>> FROM table1 a, table2 b...
>>
>>
>> >
>
> It does not work, It always return data type text.
>
That is because your table has probably defined those columns as TEXT.
Consider the following --
sqlite> CREATE TABLE t (a TEXT, b INTEGER);
sqlite> INSERT INTO t VALUES ('foo', 5);
sqlite> SELECT a, Typeof(a), b, Typeof(b) FROM t;
a Typeof(a) b Typeof(b)
-- -- -- --
foo text5 integer
sqlite>
--
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] data type from join multiple tables
Hi,
2010/5/14 P Kishor
> On Fri, May 14, 2010 at 10:11 AM, Fabio Spadaro
> wrote:
> > Hi,
> >
> > 2010/5/14 P Kishor
> >
> >> On Fri, May 14, 2010 at 9:49 AM, Fabio Spadaro >
> >> wrote:
> >> > hi,
> >> >
> >> > 2010/5/14 P Kishor
> >> >
> >> >> On Fri, May 14, 2010 at 9:01 AM, Fabio Spadaro <
> fabiolinos...@gmail.com
> >> >
> >> >> wrote:
> >> >> > I need to identify data types extracted from a
> >> >> > join between multiple tables without using cross-checking
> table_info
> >> more
> >> >> > pragmatic.
> >> >> >
> >> >>
> >> >> Could you clarify what you really want to do? Your question is not
> >> >> clear at all, at least to me. What do you mean by "using
> >> >> cross-checking table_info more pragmatic"? More pragmatic than what?
> >> >>
> >> >> > Is there a faster way to do it?
> >> >> >
> >> >>
> >> >> Faster than what?
> >> >>
> >> >> If you want data_types, you can use SELECT Typeof(column_name)
> >> >>
> >> >>
> >> >>
> >> >>
> >> >
> >> > I use python sqlite embedded and in my application I have a panel
> where
> >> you
> >> > can
> >> > type a query and see the results in a panel's grid
> >> > Now suppose that I type a query like "select
> >> > a.field1, b.field2 from table1 a, table2 b " such data
> >> > will be displayed in grid but I need to know what types of data I
> >> obtained.
> >> > How do it?
> >>
> >>
> >> SELECT a.field1, Typeof(a.field1), b.field2, Typeof(b.field2)
> >> FROM table1 a, table2 b...
> >>
> >>
> >> >
> >
> > It does not work, It always return data type text.
> >
>
> That is because your table has probably defined those columns as TEXT.
> Consider the following --
>
> sqlite> CREATE TABLE t (a TEXT, b INTEGER);
> sqlite> INSERT INTO t VALUES ('foo', 5);
> sqlite> SELECT a, Typeof(a), b, Typeof(b) FROM t;
> a Typeof(a) b Typeof(b)
> -- -- -- --
> foo text5 integer
> sqlite>
>
>
>
>
>
>
> --
> Puneet Kishor http://www.punkish.org
> Carbon Model http://carbonmodel.org
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> ---
> Assertions are politics; backing up assertions with evidence is science
> ===
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
No, i'm sure. Probably typeof in python's sqlite is not supported very mell
or there is other explanation.
--
Fabio Spadaro
www.fabiospadaro.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] data type from join multiple tables
On 14 May 2010, at 5:38pm, Fabio Spadaro wrote: > No, i'm sure. Probably typeof in python's sqlite is not supported very mell > or there is other explanation. It works perfectly inside SQLite: sqlite> CREATE TABLE tabA (a INTEGER, B INTEGER); sqlite> INSERT INTO tabA VALUES (1,10); sqlite> INSERT INTO tabA VALUES (2,20); sqlite> CREATE TABLE tabB (theNum INTEGER, theName TEXT); sqlite> INSERT INTO tabB VALUES (10,'albert'); sqlite> INSERT INTO tabB VALUES (20,'bertha'); sqlite> SELECT tabA.a,tabB.theNum,tabB.theName,typeof(tabB.theNum),typeof(tabB.theName) FROM tabA JOIN tabB ON tabB.theNum = tabA.b; 1|10|albert|integer|text 2|20|bertha|integer|text Do the above in your platform and tell us what you get. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] data type from join multiple tables
On Fri, May 14, 2010 at 11:38 AM, Fabio Spadaro wrote:
> Hi,
>
> 2010/5/14 P Kishor
>
>> On Fri, May 14, 2010 at 10:11 AM, Fabio Spadaro
>> wrote:
>> > Hi,
>> >
>> > 2010/5/14 P Kishor
>> >
>> >> On Fri, May 14, 2010 at 9:49 AM, Fabio Spadaro > >
>> >> wrote:
>> >> > hi,
>> >> >
>> >> > 2010/5/14 P Kishor
>> >> >
>> >> >> On Fri, May 14, 2010 at 9:01 AM, Fabio Spadaro <
>> fabiolinos...@gmail.com
>> >> >
>> >> >> wrote:
>> >> >> > I need to identify data types extracted from a
>> >> >> > join between multiple tables without using cross-checking
>> table_info
>> >> more
>> >> >> > pragmatic.
>> >> >> >
>> >> >>
>> >> >> Could you clarify what you really want to do? Your question is not
>> >> >> clear at all, at least to me. What do you mean by "using
>> >> >> cross-checking table_info more pragmatic"? More pragmatic than what?
>> >> >>
>> >> >> > Is there a faster way to do it?
>> >> >> >
>> >> >>
>> >> >> Faster than what?
>> >> >>
>> >> >> If you want data_types, you can use SELECT Typeof(column_name)
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >> >
>> >> > I use python sqlite embedded and in my application I have a panel
>> where
>> >> you
>> >> > can
>> >> > type a query and see the results in a panel's grid
>> >> > Now suppose that I type a query like "select
>> >> > a.field1, b.field2 from table1 a, table2 b " such data
>> >> > will be displayed in grid but I need to know what types of data I
>> >> obtained.
>> >> > How do it?
>> >>
>> >>
>> >> SELECT a.field1, Typeof(a.field1), b.field2, Typeof(b.field2)
>> >> FROM table1 a, table2 b...
>> >>
>> >>
>> >> >
>> >
>> > It does not work, It always return data type text.
>> >
>>
>> That is because your table has probably defined those columns as TEXT.
>> Consider the following --
>>
>> sqlite> CREATE TABLE t (a TEXT, b INTEGER);
>> sqlite> INSERT INTO t VALUES ('foo', 5);
>> sqlite> SELECT a, Typeof(a), b, Typeof(b) FROM t;
>> a Typeof(a) b Typeof(b)
>> -- -- -- --
>> foo text 5 integer
>> sqlite>
>>
>>
>>
>>
>>
>>
>>
>
> No, i'm sure. Probably typeof in python's sqlite is not supported very mell
> or there is other explanation.
>
Typeof() is a sqlite3 function. Maybe the Python implementation is
overriding it. But, given that you are getting back "text" and not an
error, shows that you are using some Typeof() function. Using the
example db that I showed above, and the following Perl script, I get
the desired and expected output
#!/usr/local/bin/perl
use DBI qw(:sql_types);
$dbh = DBI->connect("dbi:SQLite:dbname=/Users/punkish/Data/punkish/foo","","");
$sth = $dbh->prepare("SELECT a, Typeof(a), b, Typeof(b) FROM t");
$sth->execute;
while (my @row = $sth->fetchrow_array) {
print (join ", ", @row) . "\n";
}
prints...
foo, text, 5, integer
--
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] data type from join multiple tables
On Fri, May 14, 2010 at 12:38 PM, Fabio Spadaro wrote:
>
> No, i'm sure. Probably typeof in python's sqlite is not supported very mell
> or there is other explanation.
>
It works perfectly in Python:
In [2]: import sqlite3
In [3]: conn = sqlite3.connect(':memory:')
In [4]: c = conn.cursor()
In [5]: c.execute("CREATE TABLE tabA (a INTEGER, B INTEGER)")
In [6]: c.execute("INSERT INTO tabA VALUES (1,10)")
In [7]: c.execute("INSERT INTO tabA VALUES (2,20)")
In [8]: c.execute("CREATE TABLE tabB (theNum INTEGER, theName TEXT)")
In [9]: c.execute("INSERT INTO tabB VALUES (10,'albert')")
In [10]: c.execute("INSERT INTO tabB VALUES (20,'bertha')")
In [11]: res = c.execute("SELECT
tabA.a,tabB.theNum,tabB.theName,typeof(tabB.theNum),typeof(tabB.theName)
FROM tabA JOIN tabB ON tabB.theNum = tabA.b")
In [12]: for r in res.fetchall(): print r
:
(1, 10, u'albert', u'integer', u'text')
(2, 20, u'bertha', u'integer', u'text')
Regards,
Mikhail Terekhov
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
