Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

sqlite select question

58 views
Skip to first unread message

Busirane

unread,
Apr 22, 2018, 6:15:49 PM4/22/18
to
Hello:

I've recently started diving into sqlite3, and so far I love it! I have a question about a SELECT that returns more than one row. What I want is to get a *list* of the selected rows. But

db eval {SELECT * FROM t}

returns the *concatenation* of the selected rows. I am aware of the ability to append ?array? ?script? to the db eval command but those approaches require reassembling the vector for each row, accumulating them, and returning that.

At https://groups.google.com/forum/#!topic/comp.lang.tcl/Sm3IjX_CY6c I found this solution:

db function mklist ::list
db eval {SELECT mklist(col1, col2, ...colN) FROM t}

and it works great if you specify the columns, as shown, but doesn't work for

db eval {SELECT mklist(*) FROM t} ;# Doesn't work

That just returns an empty list for each selected row. Why doesn't * work, and is there a way to get a *list* of the selected rows without having to explicitly specify the columns?

Rich

unread,
Apr 22, 2018, 6:52:06 PM4/22/18
to
Busirane <john....@gmail.com> wrote:
> What I want is to get a *list* of the selected rows. But
>
> db eval {SELECT * FROM t}
>
> returns the *concatenation* of the selected rows.

That it does. If you know how many columns are in the table, you can
do this:

set rows [list]
foreach {a b c} [db eval {select * from test;}] {
lappend rows [list $a $b $c]
}

And you can wrap that up in a proc to make it easy to call if you like.

> I am aware of the ability to append ?array? ?script? to the db eval
> command but those approaches require reassembling the vector for each
> row, accumulating them, and returning that.

Yep, which is basically what my code above does as well. Presumably
you don't want to do this.

> At https://groups.google.com/forum/#!topic/comp.lang.tcl/Sm3IjX_CY6c I found this solution:
>
> db function mklist ::list
> db eval {SELECT mklist(col1, col2, ...colN) FROM t}
>
> and it works great if you specify the columns, as shown, but doesn't work for
>
> db eval {SELECT mklist(*) FROM t} ;# Doesn't work
>
> That just returns an empty list for each selected row. Why doesn't *
> work,

Only D. Richard Hipp could answer for why * works the way it works. As
for your why above in regards to the embedded function call, it is
likely related to a difference in how * is handled by the underlying
sql parser and executor vs. how explicit column specifications are
handled in the same.

> and is there a way to get a *list* of the selected rows without
> having to explicitly specify the columns?

Well, general good DB design says you should avoid use of * for final
designed queries. It is ok for test/prototype work, but the final
system should explicitly state the columns it wishes to have back from
the DB. This insulates the query from the addition of new columns to
the table at a later point in the future. If the table grows a new
column (a not unheard of change in the DB field) then a '*' query's
return set changes, and the code consuming it at best aborts, and at
worst creates corrupted data somewhere else in the DB.

With a query asking for explicit rows, adding columns is an invisible
change, the query works the same before and after the addition. Only
removing a column the query was expecting will effect the query, and
then generally by an error return for column not found. Reducing the
likelyhood that code will try to process bad data anyway, and produce
garbage results.

jda...@gmail.com

unread,
Apr 22, 2018, 10:27:02 PM4/22/18
to

>
> db eval {SELECT * FROM t}
>

try:

db eval {select * from t} rr {lappend res [lmap v $rr(*) {set $rr($v)}]}

v will be a list of each lines data as a list.
Each result is stored in the array rr indexed by column name, with an additional member rr(*) containing a list of column names.
The script uses lmap to convert the array to a list of values, which is appended to variable res


another option:

db eval {select * from t} rr {lappend res [dict remove [array get rr] *]}

returns a list of dicts, one per result line, indexed by column number

In both cases, make sure rr is unset, or an empty array before use (normally not a problem if used within a proc).

Dave B

Busirane

unread,
Apr 23, 2018, 9:58:26 AM4/23/18
to
On Sunday, April 22, 2018 at 10:27:02 PM UTC-4, jda...@gmail.com wrote:
> db eval {select * from t} rr {lappend res [lmap v $rr(*) {set $rr($v)}]}

That's a good solution, thanks.

> db eval {select * from t} rr {lappend res [dict remove [array get rr] *]}

I tried that myself, but it's not a good solution because arrays are not order-preserving (like a dict would be).

Thanks to Rich for reminding me not to use * in final queries. Based on that, I think the mklist() solution is probably the best.
0 new messages