using sqlite3 - execute vs. executemany; committing ...

[Vlastimil Brom]

>
>
> Thank you very much for your comprehensive answer and detailed
informations, David; I really appreciate it!

As for the number of items, there would be approx. 34 000 calls of execute()
in my present code, in the
final version probably more; I think executmany is more efficient
here, if there aren't any drawbacks.
You are right, I should definitely use the standard approaches also usable
in other databases, rather than relying on the specific behaviour of sqlite3
(althought for the current task - an embeded, lightweight, in memory db,
sqlite seems to be an obvious choice).

Thanks for your comments on the concept,that's exactly, what I needed;
actually my
current version using sqlite3 is a reimplementation; the previous
script used nested dicts (as
the most suitable built-in data structure, I knew
of). In this older version the lookups for a tag
values given the text index were ok (as the indices are keys in the main
dict), however the other way (retrieving the index given a certain
combination of tag values)
is much harder and requires nested loops over the entire data on each query (or
alternatively multiple parallel dicts "keyed" on the available tag values).
I thought a database would perform much better in such cases (and the
preliminary tests confirm this); however now I see the possible risks too.
I'll look into other possible approaches. There will be clearly a
finite set of the
column names, hence it is possible to define the db at the beginning
and fill it with values only
after parsing the texts; the problem is, this can't be done untill the
texts are available, I just thought, a more generic
approach would be be more easily extensible and also a bit simpler.

As for the usage of the db in my
app, everything is simple, single threaded,  the parsing the text to get the
tags with their values are done in the program itself, hence it can be
controlled, as well as the text sources themselves; furthermore, there won't
be any explicit user queries of
SQL, all of them are programmatic - e.g. a text is displayed on the
screen and the information about the given line should
be retrieved and displayed (like chapter, verse
nr.) or vice versa - the user selects some property (e.g. beginning of
a certain chapter) and the text should be
scrolled to make the corresponding position visible.

Just for information, what are valid table and
column names in sqlite? (Unfortunately, I couldn't find any reference for
that (maybe except the reserved sqlite_master);  as quoted names, everything
I tried, worked
fine, also whitespace, various unicode characters etc.; of course, I
can imagine, that e.g. supplying a quote would
cause problems ... )

Thanks once more for your help,
                                  Vlasta
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-list/attachments/20080504/ae9800a4/attachment-0001.html>