[sqlite] data type from join multiple tables
I need to identify data types extracted from a join between multiple tables without using cross-checking table_info more pragmatic. Is there a faster way to 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: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) -- 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 punk.k...@gmail.com Para: General Discussion of SQLite Database sqlite-users@sqlite.org 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 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) -- 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 punk.k...@gmail.com 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) -- 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 fabiolinos...@gmail.com wrote: hi, 2010/5/14 P Kishor punk.k...@gmail.com 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) -- 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 punk.k...@gmail.com On Fri, May 14, 2010 at 9:49 AM, Fabio Spadaro fabiolinos...@gmail.com wrote: hi, 2010/5/14 P Kishor punk.k...@gmail.com 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) -- 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 fabiolinos...@gmail.com wrote:
Hi,
2010/5/14 P Kishor punk.k...@gmail.com
On Fri, May 14, 2010 at 9:49 AM, Fabio Spadaro fabiolinos...@gmail.com
wrote:
hi,
2010/5/14 P Kishor punk.k...@gmail.com
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
Re: [sqlite] data type from join multiple tables
Hi,
2010/5/14 P Kishor punk.k...@gmail.com
On Fri, May 14, 2010 at 10:11 AM, Fabio Spadaro fabiolinos...@gmail.com
wrote:
Hi,
2010/5/14 P Kishor punk.k...@gmail.com
On Fri, May 14, 2010 at 9:49 AM, Fabio Spadaro fabiolinos...@gmail.com
wrote:
hi,
2010/5/14 P Kishor punk.k...@gmail.com
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 fabiolinos...@gmail.com wrote:
Hi,
2010/5/14 P Kishor punk.k...@gmail.com
On Fri, May 14, 2010 at 10:11 AM, Fabio Spadaro fabiolinos...@gmail.com
wrote:
Hi,
2010/5/14 P Kishor punk.k...@gmail.com
On Fri, May 14, 2010 at 9:49 AM, Fabio Spadaro fabiolinos...@gmail.com
wrote:
hi,
2010/5/14 P Kishor punk.k...@gmail.com
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 fabiolinos...@gmail.comwrote:
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
