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

[Vlastimil Brom]

Hi all,
I'd like to ask about some (probably elementary) things about the proper
usage of sqlite3 in python (2.5.2; win).
- Are there any peculiarities with using curs.executemany(...) vs. multiple
curs.execute(...) ? I read a notice, sqlite3 does internally some
caching, hence both should be similarly fast, but in my case executemany(...)
is quite a bit faster (probably due to the function call overhead?). Are
there maybe some caveats with using  executemany? (the obvious
memory consumption for the intermediate list doesn't seem to matter
much in my application).
Further, I am not quite sure about the standard usage of the cursor
object and also the proper commiting the transactions and closing the
connection.
Should one create a cursor of a connection and call the execute ... methods
of the cursor -
or is it better to call the shortcut execute etc. methods of the
Connection object
directly (as suggested in the docs:
http://docs.python.org/lib/node351.html(or are there specific use
cases for both approaches)?

When the transactions should be
commited? (creating, altering a table, or also selecting the results ?)
There seem to be some implicit handling of the transactions (
http://docs.python.org/lib/sqlite3-Controlling-Transactions.html#sqlite3-Controlling-Transactions);
hence I am not
sure about the standard usage of these methods; the same is true of
connection.close() - or are these calls eventually unnecessary?


To give a bit more info about my use case, my (gui) app displays a set texts
along with some informations on the currently visible text segments.
After starting a gui the texts are read from the source files (also
containing a kind of tags) - simultaneously
the tags are parsed and the corresponding values are stored in sqlite
"in memory" - the column names as well as the corresponding values for given
text parts are extracted dynamically from the source files.
During the whole run of the program the db should be available in order to
retrieve the properties of the given text index.

Below is a part of my code dealing with the populating of the db;, I'm
sorry, it's a not runable pseudocode, however, my present code works
somehow, but I'm quite sure, the implementation isn't really standard, hence
I'd like to hear some comments/suggestions. (I asked some time ago about the
possibility of a parametrised input of the table and column names, but this
seems impossible - therefore I used the string interpolation.)

- the variables are defined in other parts of code before; most of
the names might be self explanatory; self - custom class managing the text
with its properties; text_name - name of the table; index_col_name - a name
of the special db column containing the text index; act_db_columns - list of
the db columns; question_marks - a string "?, ?, ? ..." (synchronized with
the length of act_db_columns); tags_seq - a nested list with
the previously retrieved data for the database.

conn_tags_DB = sqlite3.connect(':memory:')
curs = self.conn_tags_DB.cursor()
curs.execute('CREATE TABLE IF NOT EXISTS "%s" ("%s", UNIQUE("%s"))' %
(self.text_name, index_col_name, index_col_name))
curs.execute(u'INSERT OR REPLACE INTO "%s"("%s") VALUES (?)' %
(self.text_name, index_col_name), (0,))
for new_col in act_db_columns[1:]: # adds the needed columns (except of the
first one: index_col_name)
    curs.execute('ALTER TABLE "%s" ADD "%s" TEXT' % (self.text_name,
new_col))
curs.executemany('INSERT OR REPLACE INTO "%s" VALUES (%s)' %
(self.text_name, question_marks), tags_seq)
self.conn_tags_DB.commit()

Are there maybe any comments or hints on a more elegant/efficient solution?

Now, what's
the usual way to access the database? Is it possible/wise/standard ...
to leave the connection open for the subsequent queries
during the whole run of the app; could even the
cursor eventually be present as a class method, or should it rather be
created repeatedly with each call?  (After populating, the db shouldn't be
modified, but only read.)

Many thanks in advance for your help;
 and apologies for this longer post.
   Greetings,
       Vlasta
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-list/attachments/20080503/24434b30/attachment.html>