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

How to put a list into a SQLite datbase

80 views
Skip to first unread message

Cecil Westerhof

unread,
Dec 4, 2017, 1:44:06 PM12/4/17
to
I have the following to open a process:
set vmstat [open {|vmstat -n 60}]

I want to read the values with:
set values [gets $vmstat]

This gives for example:
1 0 0 611788 1474304 5033000 0 0 0 88 3234 5258 25 3 72 0 0

Is there a smart way to put those 17 values into the database?

I understood that I have to do something like:
db eval "
INSERT INTO vmstat (
.
.
.
) VALUES (
:val01,
:val02,
:val03,
.
.
.
:val15,
:val16,
:val17
};
"

But then I have to fill the 17 variables from the list. So I was just
wondering if that could be done better.

--
Cecil Westerhof
Senior Software Engineer
LinkedIn: http://www.linkedin.com/in/cecilwesterhof

Mike Griffiths

unread,
Dec 4, 2017, 3:29:09 PM12/4/17
to
Something like
VALUES('[join $list "','"]')
should do the trick (assuming the list is guaranteed to not contain apostrophes).

Cecil Westerhof

unread,
Dec 4, 2017, 5:14:06 PM12/4/17
to
lassign does what I need. I now have the following:

#!/usr/bin/env tclsh

### Improvements
# Get database from conf-file


package require sqlite3


if {$argc != 1} {
error "Error: ${argv0} DATABASE"
}
sqlite db [lindex $argv 0]
db timeout 10000
set insertVmstat "
INSERT INTO vmstat (
runlength,
-- procs
runable, uninteruptable,
-- memory
swap, free, buffers, cache,
-- swap
swapIn, swapOut,
-- io
blockIn, blockOut,
-- system
interuptsPerSec, contextSwitchesPerSec,
-- cpu
userTime, systemTime, idleTime, waitTime, stolenTime
) VALUES (
:runLength,
:runable, :uninteruptable,
:swap, :free, :buffers, :cache,
:swapIn, :swapOut,
:blockIn, :blockOut,
:interuptsPerSec, :contextSwitchesPerSec,
:userTime, :systemTime, :idleTime, :waitTime, :stolenTime
);
"
set runLength 60
puts "Using an interval of ${runLength} seconds"
after [expr {1000 * (60 - [clock seconds] % 60)}]
set vmstat [open "|vmstat -n ${runLength}"]
# The first three lines need to be skipped
for {set i 0} {${i} < 3} {incr i} {
gets ${vmstat}
}
while {true} {
lassign [gets ${vmstat}] \
runable uninteruptable \
swap free buffers cache \
swapIn swapOut \
blockIn blockOut \
interuptsPerSec contextSwitchesPerSec \
userTime systemTime idleTime waitTime stolenTime
db eval ${insertVmstat}
}
# Not really necessary because the above loop never ends
# But I find this more clear and is robuster against change
close vmstat
db close

Cecil Westerhof

unread,
Dec 4, 2017, 5:14:06 PM12/4/17
to
> Something like
> VALUES('[join $list "','"]')
> should do the trick (assuming the list is guaranteed to not contain
> apostrophes).

I did it with lassign. See my other reply.

Rich

unread,
Dec 4, 2017, 6:11:24 PM12/4/17
to
Cecil Westerhof <Ce...@decebal.nl> wrote:
> Cecil Westerhof <Ce...@decebal.nl> writes:
>
>> This gives for example:
>> 1 0 0 611788 1474304 5033000 0 0 0 88 3234 5258 25 3 72 0 0
>>
>> Is there a smart way to put those 17 values into the database?
>
> lassign does what I need. I now have the following:

Be careful here. The lassign command will work, in this specific
instance, but you are also playing with fire and should be well aware
of the fact that you are playing with fire here.

The lassign command works because Tcl, when asked to perform a "list"
operation upon a string, will attempt to parse the string into a list.

For separating whitespace separated items that are simply digits (as
above) or ascii letters, this will work as you would expect.

But, if there happen to be any special characters (special to the
parsing of lists from strings) in the input string, you'll get an error
instead of a clean extract.

Compare:

$ rlwrap tclsh
% lindex { a b c } 1
b
% lindex { a "b c } 1
unmatched open quote in list
% lindex " a {b c " 1
unmatched open brace in list

Note that I changed to quotes around the entire string in that third
try.

Feeding 'random' strings to the list operators is a way to create a
data dependent error. One that often sits hidden for years, until one
day, the input includes a special character and things break.

So, while what you are doing is likely safe, because vmstat is unlikely
to ever include double quotes or curly brackets in its output, it is
not a safe thing to do in general, and it is often best to leave a loud
comment to your future self in the code for the day the data input
changes enough to break something.


Cecil Westerhof

unread,
Dec 4, 2017, 7:28:06 PM12/4/17
to
Rich <ri...@example.invalid> writes:

> Cecil Westerhof <Ce...@decebal.nl> wrote:
>> Cecil Westerhof <Ce...@decebal.nl> writes:
>>
>>> This gives for example:
>>> 1 0 0 611788 1474304 5033000 0 0 0 88 3234 5258 25 3 72 0 0
>>>
>>> Is there a smart way to put those 17 values into the database?
>>
>> lassign does what I need. I now have the following:
>
> Be careful here. The lassign command will work, in this specific
> instance, but you are also playing with fire and should be well aware
> of the fact that you are playing with fire here.
>
> The lassign command works because Tcl, when asked to perform a "list"
> operation upon a string, will attempt to parse the string into a list.
>
> For separating whitespace separated items that are simply digits (as
> above) or ascii letters, this will work as you would expect.

OK, I will not worry in this case then. But I will look into it, so
that when I have a more complex case I will be prepared.

Thanks.


> But, if there happen to be any special characters (special to the
> parsing of lists from strings) in the input string, you'll get an error
> instead of a clean extract.
>
> Compare:
>
> $ rlwrap tclsh
> % lindex { a b c } 1
> b
> % lindex { a "b c } 1
> unmatched open quote in list
> % lindex " a {b c " 1
> unmatched open brace in list
>
> Note that I changed to quotes around the entire string in that third
> try.
>
> Feeding 'random' strings to the list operators is a way to create a
> data dependent error. One that often sits hidden for years, until one
> day, the input includes a special character and things break.
>
> So, while what you are doing is likely safe, because vmstat is unlikely
> to ever include double quotes or curly brackets in its output, it is
> not a safe thing to do in general, and it is often best to leave a loud
> comment to your future self in the code for the day the data input
> changes enough to break something.
>
>

Cecil Westerhof

unread,
Dec 4, 2017, 7:59:05 PM12/4/17
to
Cecil Westerhof <Ce...@decebal.nl> writes:

> Rich <ri...@example.invalid> writes:
>
>> Cecil Westerhof <Ce...@decebal.nl> wrote:
>>> Cecil Westerhof <Ce...@decebal.nl> writes:
>>>
>>>> This gives for example:
>>>> 1 0 0 611788 1474304 5033000 0 0 0 88 3234 5258 25 3 72 0 0
>>>>
>>>> Is there a smart way to put those 17 values into the database?
>>>
>>> lassign does what I need. I now have the following:
>>
>> Be careful here. The lassign command will work, in this specific
>> instance, but you are also playing with fire and should be well aware
>> of the fact that you are playing with fire here.
>>
>> The lassign command works because Tcl, when asked to perform a "list"
>> operation upon a string, will attempt to parse the string into a list.
>>
>> For separating whitespace separated items that are simply digits (as
>> above) or ascii letters, this will work as you would expect.
>
> OK, I will not worry in this case then. But I will look into it, so
> that when I have a more complex case I will be prepared.

I just made it a little bit more robust.
I added:
set checkLine {^ +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+)$}

And the beginning of the loop is now:
while {true} {
set currentLine [gets ${vmstat}]
if {[regexp -all -line ${checkLine} ${currentLine}] != 1} {
error "ERROR: vmstat gave back an unexpected value"
}
lassign ${currentLine} \

That should take care of things.

Cecil Westerhof

unread,
Dec 4, 2017, 8:28:04 PM12/4/17
to
Cecil Westerhof <Ce...@decebal.nl> writes:

> I added:
> set checkLine {^ +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+)$}

Better to use:
set checkLine [string cat {^} [string repeat { +([0-9]+)} 17] {$}]

Gerald Lester

unread,
Dec 4, 2017, 8:47:20 PM12/4/17
to
On 12/04/2017 07:24 PM, Cecil Westerhof wrote:
> Cecil Westerhof <Ce...@decebal.nl> writes:
>
>> I added:
>> set checkLine {^ +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+)$}
>
> Better to use:
> set checkLine [string cat {^} [string repeat { +([0-9]+)} 17] {$}]
>

You could also use scan.

--
+----------------------------------------------------------------------+
| Gerald W. Lester, President, KNG Consulting LLC |
| Email: Gerald...@kng-consulting.net |
+----------------------------------------------------------------------+

Rich

unread,
Dec 4, 2017, 9:22:00 PM12/4/17
to
That does validate that things are as you expect (although the "-all"
is redundant with a fully spelled out regex). However if you just use
regexp to validate you don't need the capturing parentheses.

Or, if you want to let regexp capture the values too (which is what the
parens do) the -inline switch would return each found number as a
separate list element. Then you could check the list length to see if
it is as expected, and if so lassign it out to variables to feed into
sqlite.

Rich

unread,
Dec 4, 2017, 9:27:08 PM12/4/17
to
Cecil Westerhof <Ce...@decebal.nl> wrote:
> Cecil Westerhof <Ce...@decebal.nl> writes:
>
>> I added:
>> set checkLine {^ +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+)$}
>
> Better to use:
> set checkLine [string cat {^} [string repeat { +([0-9]+)} 17] {$}]

If you go with regex, it might be better to use (presuming there is
always at least one space up front, and none at the end):

{^(?: +([0-9]+)){17}$}

This is a capturing version. Dropping the inner parenthesis would make
it non-capturing.

Cecil Westerhof

unread,
Dec 5, 2017, 1:59:06 AM12/5/17
to
When using:
regexp -line ${checkLine} ${currentRow} -> values

It looks like only the last match is put into values.

When using:
regexp -line ${checkLine} ${currentRow} values

Values is an exact copy of currentRow.

So it seems to me that capturing is not very useful, or am I missing
something?

Cecil Westerhof

unread,
Dec 5, 2017, 2:14:05 AM12/5/17
to
Cecil Westerhof <Ce...@decebal.nl> writes:

> Rich <ri...@example.invalid> writes:
>
>> Cecil Westerhof <Ce...@decebal.nl> wrote:
>>> Cecil Westerhof <Ce...@decebal.nl> writes:
>>>
>>>> I added:
>>>> set checkLine {^ +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+)
>>>> +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+)
>>>> +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+)$}
>>>
>>> Better to use:
>>> set checkLine [string cat {^} [string repeat { +([0-9]+)} 17] {$}]
>>
>> If you go with regex, it might be better to use (presuming there is
>> always at least one space up front, and none at the end):
>>
>> {^(?: +([0-9]+)){17}$}
>>
>> This is a capturing version. Dropping the inner parenthesis would make
>> it non-capturing.

For the moment I have:
set checkLine {^(?: +[0-9]+){17}$}
set runLength 60
puts "Using an interval of ${runLength} seconds"
after [expr {1000 * (60 - [clock seconds] % 60)}]
set vmstat [open "|vmstat -n ${runLength}"]
# The first three lines need to be skipped
for {set i 0} {${i} < 3} {incr i} {
gets ${vmstat}
}
while {true} {
set currentLine [gets ${vmstat}]
if {[regexp ${checkLine} ${currentLine}] != 1} {
error "ERROR: vmstat gave back an unexpected value"
}
lassign ${currentLine} \
runable uninteruptable \

Maybe better to change:
set vmstat [open "|vmstat -n ${runLength}"]

to:
set vmstat [open "|vmstat -n ${runLength}" r]

It is the same, but clearer.

Cecil Westerhof

unread,
Dec 5, 2017, 2:14:05 AM12/5/17
to
Rich <ri...@example.invalid> writes:

>> I just made it a little bit more robust.
>> I added:
>> set checkLine {^ +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+)$}
>>
>> And the beginning of the loop is now:
>> while {true} {
>> set currentLine [gets ${vmstat}]
>> if {[regexp -all -line ${checkLine} ${currentLine}] != 1} {
>> error "ERROR: vmstat gave back an unexpected value"
>> }
>> lassign ${currentLine} \
>>
>> That should take care of things.
>
> That does validate that things are as you expect (although the "-all"
> is redundant with a fully spelled out regex). However if you just use
> regexp to validate you don't need the capturing parentheses.

And the -line is also redundant.
Optimising all the time.

rene

unread,
Dec 5, 2017, 2:20:32 AM12/5/17
to
Am Montag, 4. Dezember 2017 19:44:06 UTC+1 schrieb Cecil Westerhof:
> I have the following to open a process:
> set vmstat [open {|vmstat -n 60}]
>
> I want to read the values with:
> set values [gets $vmstat]
>
> This gives for example:
> 1 0 0 611788 1474304 5033000 0 0 0 88 3234 5258 25 3 72 0 0
>
> Is there a smart way to put those 17 values into the database?
>
Use the list command:

package req sqlite3
3.20.0
sqlite3 db :memory:
db eval {create table t1 (list)}
set a [list 1 a {some text}]
1 a {some text}
db eval {insert into t1 values($a)}
set b [db eval {select * from t1 where rowid=1}]
{1 a {some text}}


HTH
rene

Rich

unread,
Dec 5, 2017, 5:21:49 AM12/5/17
to
Cecil Westerhof <Ce...@decebal.nl> wrote:
> Rich <ri...@example.invalid> writes:
>
>> Cecil Westerhof <Ce...@decebal.nl> wrote:
>>> Cecil Westerhof <Ce...@decebal.nl> writes:
>>>
>>>> I added:
>>>> set checkLine {^ +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+)
>>>> +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+)
>>>> +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+)$}
>>>
>>> Better to use:
>>> set checkLine [string cat {^} [string repeat { +([0-9]+)} 17] {$}]
>>
>> If you go with regex, it might be better to use (presuming there is
>> always at least one space up front, and none at the end):
>>
>> {^(?: +([0-9]+)){17}$}
>>
>> This is a capturing version. Dropping the inner parenthesis would make
>> it non-capturing.
>
> When using:
> regexp -line ${checkLine} ${currentRow} -> values
>
> It looks like only the last match is put into values.

Yes, that is exactly what you asked for, given that you have 17
sub-matches but only one submatchvar.

> When using:
> regexp -line ${checkLine} ${currentRow} values
>
> Values is an exact copy of currentRow.

The first variable after the string to match receives the entire match.
Also exactly as documented.

> So it seems to me that capturing is not very useful, or am I missing
> something?

Missing something. Note the manpage for regexp:

regexp ?switches? exp string ?matchVar? ?subMatchVar subMatchVar ...?

It allows plural submatchvars. You have plural sub-matches (17 of
them, each one of the parens.), you need 17 variables after the
"matchVar" to receive all of them from the regex engine.

You'd want:

regexp -line ${checkLine} ${currentRow} -> v1 v2 v3 v4 v5 v6 v7 v8 v9 v10 v11 v12 v13 v14 v15 v16 v17

to capture all of the submatches.

Pick your own names instead of v1 ... v17.

Rich

unread,
Dec 5, 2017, 5:25:17 AM12/5/17
to
Cecil Westerhof <Ce...@decebal.nl> wrote:
> Maybe better to change:
> set vmstat [open "|vmstat -n ${runLength}"]
>
> to:
> set vmstat [open "|vmstat -n ${runLength}" r]
>
> It is the same, but clearer.

It does mean that a subsequent reader does not need to know the default
in order to understand the code.

Myself (this is a style thing) I always use the POSIX variant, largely
because there are a few esoteric variants that can't be expressed with
the single letter variant. So it would be:

[open "..." RDONLY]

Rich

unread,
Dec 5, 2017, 5:27:07 AM12/5/17
to
That works, but it puts a string serialization of the list into one
row.

I interpreted Cecil's request that he had 17 columns in his DB, and
wanted to put the appropriate values into the correct columns.

Cecil Westerhof

unread,
Dec 5, 2017, 7:28:07 AM12/5/17
to
Thank you very much. I am going to use that.

Maybe I should learn to crawl before I try to run. ;-)

Cecil Westerhof

unread,
Dec 5, 2017, 7:28:07 AM12/5/17
to
Correct and I managed that with the help of this list.
Still some tweaks to go, but otherwise not bad.

Cecil Westerhof

unread,
Dec 5, 2017, 7:28:07 AM12/5/17
to
That is a good idea. If you do not mind I will copy that. ;-)

Cecil Westerhof

unread,
Dec 5, 2017, 8:14:06 AM12/5/17
to
It looks something is not quite right. I tried the following:
set runLength 60
puts "Using an interval of ${runLength} seconds"
after [expr {1000 * (60 - [clock seconds] % 60)}]
set vmstat [open "|vmstat -n ${runLength}"]
# The first three lines need to be skipped
for {set i 0} {${i} < 3} {incr i} {
gets ${vmstat}
}

set checkLine [string cat {^} [string repeat { +([0-9]+)} 17] {$}]
if {[regexp ${checkLine} [gets ${vmstat}] compleet \
runable uninteruptable \
swap free buffers cache \
swapIn swapOut \
blockIn blockOut \
interuptsPerSec contextSwitchesPerSec \
userTime systemTime idleTime waitTime stolenTime] != 1} {
error "ERROR: vmstat gave back an unexpected value"
}
puts ${checkLine}
puts $compleet
puts $runable
puts $uninteruptable
puts $userTime
puts $systemTime
puts $idleTime
puts $waitTime
puts $stolenTime
set checkLine {^(?: +([0-9]+)){17}$}
if {[regexp ${checkLine} [gets ${vmstat}] compleet \
runable uninteruptable \
swap free buffers cache \
swapIn swapOut \
blockIn blockOut \
interuptsPerSec contextSwitchesPerSec \
userTime systemTime idleTime waitTime stolenTime] != 1} {
error "ERROR: vmstat gave back an unexpected value"
}
puts ${checkLine}
puts $compleet
puts $runable
puts $uninteruptable
puts $userTime
puts $systemTime
puts $idleTime
puts $waitTime
puts $stolenTime

And this gives as output:
Using an interval of 60 seconds
^ +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+) +([0-9]+)$
1 0 7080 462668 579204 5558228 0 0 288 81 3691 6468 16 4 76 3 0
1
0
16
4
76
3
0
^(?: +([0-9]+)){17}$
0 0 7080 432272 579400 5562732 0 0 71 114 3777 6288 17 4 78 0 0
0







So something is going wrong. Or are I doing something wrong again?


By the way. The backslashes work work in a script, but not in the
interactive mode. Can I make them work in the interactive mode also?

Rich

unread,
Dec 5, 2017, 12:55:17 PM12/5/17
to
The second one failed to match. Probably because it does not account
for spaces at the start of the line like the first one does.

> By the way. The backslashes work work in a script, but not in the
> interactive mode. Can I make them work in the interactive mode also?

They should work the same in interactive mode. Be sure you don't
accidentally type a space after the backslash and before pressing
return in interactive mode. backslash space is different from
backslash newline.

But, generally, when I get to the point of needing backslashes for any
interactive experimenting I give up and use a script file instead, much
easier than all the typing shenagians in interactive mode.

Cecil Westerhof

unread,
Dec 5, 2017, 1:28:06 PM12/5/17
to
No, it does match, otherwise the error routine would be called. And
compleet is correctly filled. So I think it matches.


>> By the way. The backslashes work work in a script, but not in the
>> interactive mode. Can I make them work in the interactive mode also?
>
> They should work the same in interactive mode. Be sure you don't
> accidentally type a space after the backslash and before pressing
> return in interactive mode. backslash space is different from
> backslash newline.
>
> But, generally, when I get to the point of needing backslashes for any
> interactive experimenting I give up and use a script file instead, much
> easier than all the typing shenagians in interactive mode.

I agree, but I did a copy paste from my script. Script works, shell
does not.

Rich

unread,
Dec 5, 2017, 4:19:45 PM12/5/17
to
Cecil Westerhof <Ce...@decebal.nl> wrote:
>>> Rich <ri...@example.invalid> writes:
>> The second one failed to match. Probably because it does not
>> account for spaces at the start of the line like the first one does.
>
> No, it does match, otherwise the error routine would be called. And
> compleet is correctly filled. So I think it matches.

Ah, yes, you are right on that one. It looks like the repeat operator
({}) does not repeat the capturing. So that variant looks like it
won't work, even though it is shorter.

>>> By the way. The backslashes work work in a script, but not in the
>>> interactive mode. Can I make them work in the interactive mode
>>> also?
>>
>> They should work the same in interactive mode. Be sure you don't
>> accidentally type a space after the backslash and before pressing
>> return in interactive mode. backslash space is different from
>> backslash newline.
>>
>> But, generally, when I get to the point of needing backslashes for
>> any interactive experimenting I give up and use a script file
>> instead, much easier than all the typing shenagians in interactive
>> mode.
>
> I agree, but I did a copy paste from my script. Script works, shell
> does not.

Are you 100% sure that during the paste that some code somewhere did
not add a space at the end of each line. I've had trouble with mswin
and outlook liking (too much) to add one space to stuff copy/pasted out
of an email.

Beyond that (the source or the destination adding something) I have no
more ideas to suggest.

Cecil Westerhof

unread,
Dec 6, 2017, 4:59:06 AM12/6/17
to
I did it in the past also with Bash scripts and there I did not have
this problem. I need to look into it a bit better. But probably it is
not something that I will use a lot.
Still nice to know what is happening.
0 new messages