Re: [sqlite] Elegant printout of table (with header) via TCL

[rod]

>On 6 Mar 2012, rod wrote:
>> On 2/9/12, rod  wrote:
>> >
>> > I'm looking for a better way to printout the:
>> > Column Headers,
>> > followed by the table contents -
>> > in comma separated value (tsv) format
>> > from a SELECT statement:

>>   By setting a trace I am
>> able to reset a variable called counter to  -0-  every time a new sql
>> statment is compiled.  In addition I also save the sql  statement to
>> be evaluated  to a variable called  ::last_sql via:
>>
>> sql trace {set ::count 0; set ::last_sql }
>>
>> proc fqry {row {sep "\t"} {header 1}} {
>>  upvar $row g_row
>>  set val {}
>>  set col {}
>> set col_name {}
>>  foreach {col_name } $g_row(*) {
>>  lappend val $g_row($col_name)
>>  lappend col $col_name
>>  set len [string length $col_name]
>>  set dash [string repeat - $len]
>>  lappend hd $dash
>> }
>> #always prints the vals; only print headers and "---"'s  if counter==0
>> if {$::count==0 && $header } {puts [join $col $sep]; puts $hd}
>>  puts [join $val $sep]
>> incr ::count
>> }

>> Usage:
>> %sql eval "Select  col2, col4, col6 as Results from table 1"  q_array
>> {fqry q_array}
>>
>>
>
>Personally, your first methods seemed just as good, and no more
>inelegant.

By using the trace the counter automatically gets reset.  I think in
my first case
I was having to code in a counter reset prior to each sql statement.

When you posted earlier, I looked for a way, using the TCL
>interface, to get the column names once SQLite has compiled the
>statement but before it starts fetching rows.  The column names are
>available then, and it solves the problem that I am about to pose.
>Unfortunately, the TCL interface provides no access, as now written.
>
>What I find very unsatisfactory about both of your solutions, and about
>anything that looks reasonably doable through the present TCL interface,
>is that if the result set is empty, no header will be emitted.  I find
>that for things I do, the possibility of an empty table cannot be
>precluded.


I'm likely missing some important nuance but the trace command did
capture the full sql statement.
When the select statement produces an empty table could you the then
reuse the text of that
select statement to create a table (albeit an empty table) in memory?
Then one could analyze
that temp table for the headers in question?
Last night I think I confirmed that a
CREATE table AS  select ;
Does actually produce a table even when the select statement returns
an empty set.
Using the ::last_sql  variable from my trace command above:


CREATE TEMP TABLE table_that_is_only_used_to_get_headers  AS $::last_sql;

Then the headers in this empty table could be identified via the
PRAGMA table_info??
PRAGMA table_info (table_that_is_only_used_to_get_headers)

Granted this is unelegant but better than tinkering with the shell?

Thanks for the reply, note that I am quite new to both sqlite and
particularly to tcl.

I'm excited anyway and I'm going to attempt to modify my
proc fqry
to do some formating and possibly some post processing hopefully to
produce output to
stdout or to a specified results file.  Maybe output like this:


SELECT
col2,
col4,
col6 as Results
FROM table 1

col2col4Results
---
val val val 
val val val
..  ..  ..  
val val val

=== === ==
##  Sum
##  Avg
Max
Min



later rod.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Elegant printout of table (with header) via TCL (solved)

[Larry Brasfield]

On 3 Mar 2012, rod wrote:

On 2/9/12, rod  wrote:
> My apologies if this question should be directed to some other list.
>
> I'm looking for a better way to printout the:
> Column Headers,
> followed by the table contents -
> in comma separated value (tsv) format
> from a SELECT statement:
>
>
> So far I have two solutions, neither seems elegant enough.
>
> The second solution makes use of a test of count

I'm excited, I may have solved my problem.  By setting a trace I am
able to reset a variable called counter to  -0-  every time a new sql
statment is compiled.  In addition I also save the sql  statement to
be evaluated  to a variable called  ::last_sql via:

# this trace is executed at the end of the compiling of an sql statement
sql trace {set ::count 0; set ::last_sql }

Then this proc will print the headers if  ::count=0  (ie only prior to
processing the the first row of the select results.


proc fqry {row {sep "\t"} {header 1}} {
# you can't pass the value in the array to the proc
# you need to upvar it to another var
upvar $row g_row
set val {}
set col {}
set col_name {}
foreach {col_name } $g_row(*) {
lappend val $g_row($col_name)
lappend col $col_name
set len [string length $col_name]
set dash [string repeat - $len]
lappend hd $dash
}
#always prints the vals; only print headers and "---"'s  if counter==0

if {$::count==0 && $header } {puts [join $col $sep]; puts $hd}
puts [join $val $sep]
incr ::count
}


Usage:

%sql eval "Select  col2, col4, col6 as Results from table 1"  q_array
{fqry q_array}


Q)  Does this seem like the way to accomplish this???  Are there other
typical uses of trace that I should be aware of prior to expanding on
this solution.



Personally, your first methods seemed just as good, and no more 
inelegant.  When you posted earlier, I looked for a way, using the TCL 
interface, to get the column names once SQLite has compiled the 
statement but before it starts fetching rows.  The column names are 
available then, and it solves the problem that I am about to pose. 
Unfortunately, the TCL interface provides no access, as now written.


What I find very unsatisfactory about both of your solutions, and about 
anything that looks reasonably doable through the present TCL interface, 
is that if the result set is empty, no header will be emitted.  I find 
that for things I do, the possibility of an empty table cannot be 
precluded.  Nevertheless, the process that uses the table still has to 
"query" it, unless there is special logic to see that it is missing or 
completely empty (including the header).  Such special-casing is very 
distasteful to programmers who think in terms of sets.


This issue is what has led me to create a variant of the SQLite3 shell 
that has no more trouble doing the "right" header output when the result 
set is empty than when there is at least one row (that would have 
sufficed to trigger header emission with the original code).


Best regards,
--
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Elegant printout of table (with header) via TCL (solved)

[rod]

On 2/9/12, rod  wrote:
> My apologies if this question should be directed to some other list.
>
> I'm looking for a better way to printout the:
> Column Headers,
> followed by the table contents -
> in comma separated value (tsv) format
> from a SELECT statement:
>
>
> So far I have two solutions, neither seems elegant enough.
>
> The second solution makes use of a test of count

I'm excited, I may have solved my problem.  By setting a trace I am
able to reset a variable called counter to  -0-  every time a new sql
statment is compiled.  In addition I also save the sql  statement to
be evaluated  to a variable called  ::last_sql via:

# this trace is executed at the end of the compiling of an sql statement
sql trace {set ::count 0; set ::last_sql }

Then this proc will print the headers if  ::count=0  (ie only prior to
processing the the first row of the select results.


proc fqry {row {sep "\t"} {header 1}} {
# you can't pass the value in the array to the proc
# you need to upvar it to another var
upvar $row g_row
set val {}
set col {}
set col_name {}
foreach {col_name } $g_row(*) {
lappend val $g_row($col_name)
lappend col $col_name
set len [string length $col_name]
set dash [string repeat - $len]
lappend hd $dash
}
#always prints the vals; only print headers and "---"'s  if counter==0

if {$::count==0 && $header } {puts [join $col $sep]; puts $hd}
puts [join $val $sep]
incr ::count
}


Usage:

%sql eval "Select  col2, col4, col6 as Results from table 1"  q_array
{fqry q_array}


Q)  Does this seem like the way to accomplish this???  Are there other
typical uses of trace that I should be aware of prior to expanding on
this solution.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Elegant printout of table (with header) via TCL

[rod]

2012/2/9 rod :
> My apologies if this question should be directed to some other list.
>
> I'm looking for a better way to printout the:
> Column Headers,
> followed by the table contents -
> in comma separated value (csv) format
> from a SELECT statement:
>

On Fri, Feb 10, 2012 at 3:35 PM, Alexey Pechnikov wrote:

> See pragma table_info
>
>

First  a few typo  corrections, I had written comma separated values(csv) I
 had meant  "tab sep. val. (TSV)" hence the TCL code:
join  $list  "\t"

Alexey suggest  I research table_info.

I think I already have all the info about the table, as produced by the
select statement,  that I need.  This info is contained within row(*): the
column names for the select columns being executed by 'select'.   I don't
see any added benefit of
pragma table_info.


I should have presented a more generic case  of a select statement, one
that contains a subset of the available columns not * (ie all the columns)
Eventually I would like a to be able to add a procedure to the select
script that prints out a tab separated table of   column headers and table
values for a generic select statement.

Something like:

sql eval {Select col_1, col_2, col_8, col_4  from generic_table } rows {
procedure_that_prints_tsv_and_the_column_names $rows
}

Thanks
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Elegant printout of table (with header) via TCL

[Alexey Pechnikov]

See pragma table_info

2012/2/9 rod :
> My apologies if this question should be directed to some other list.
>
> I'm looking for a better way to printout the:
> Column Headers,
> followed by the table contents -
> in comma separated value (csv) format
> from a SELECT statement:
>
>
> So far I have two solutions, neither seems elegant enough.
>
> First solution I add the rowid tag after the *  then check to see if
> rowid==1 in the output and if so print the headers first
>
> 
>
> # the following code should print the headers and the
> # results from the SELECT command in CSV format
> sql eval {
> SELECT *, rowid \
> FROM Fxyz_max_min limit 10} row {
> # if at first row print headers first
> if $row(rowid)==1 {puts  [join $row(*) "\t"] }
> # define an EMPTY list
> set b_list {}
> foreach col $row(*) {lappend b_list $row($col)}
> set b_list [join $b_list "\t"]
> #write list
> puts   $b_list
> }
>
> 
>
>
> The second solution makes use of a test of count
> (not really a counter just gets set to 1 instead of 0)
>
> 
> set count 0
> sql eval {
> SELECT * \
> FROM Fxyz_max_min limit 10} row {
> # define an EMPTY list
> if $count==0 {puts  [join $row(*) "\t"] }
> set count 1
> set b_list {}
> foreach col $row(*) {lappend b_list $row($col)}
> set b_list [join $b_list "\t"]
> #write list
> puts   $b_list
> }
>
> 
>
>
>
>
> Each of these needs something added; either the rowid to the output or
> a counter test.
> is there a better way??
>
>
> Thanks
>
> P.S.   can the TCL sqlite3 statement open the database using command switchs
> (ie    --cvs  -headers)
> --
> -Rod
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Elegant printout of table (with header) via TCL

[rod]

My apologies if this question should be directed to some other list.

I'm looking for a better way to printout the:
Column Headers,
followed by the table contents -
in comma separated value (csv) format
from a SELECT statement:


So far I have two solutions, neither seems elegant enough.

First solution I add the rowid tag after the *  then check to see if
rowid==1 in the output and if so print the headers first



# the following code should print the headers and the
# results from the SELECT command in CSV format
sql eval {
SELECT *, rowid \
FROM Fxyz_max_min limit 10} row {
# if at first row print headers first
if $row(rowid)==1 {puts  [join $row(*) "\t"] }
# define an EMPTY list  
set b_list {}
foreach col $row(*) {lappend b_list $row($col)}
set b_list [join $b_list "\t"]
#write list
puts   $b_list
}




The second solution makes use of a test of count
(not really a counter just gets set to 1 instead of 0)


set count 0
sql eval {
SELECT * \
FROM Fxyz_max_min limit 10} row {
# define an EMPTY list  
if $count==0 {puts  [join $row(*) "\t"] }
set count 1
set b_list {}
foreach col $row(*) {lappend b_list $row($col)}
set b_list [join $b_list "\t"]
#write list
puts   $b_list
}






Each of these needs something added; either the rowid to the output or
a counter test.
is there a better way??


Thanks

P.S.   can the TCL sqlite3 statement open the database using command switchs
(ie--cvs  -headers)
-- 
-Rod
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users