472,954 Members | 1,735 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,954 software developers and data experts.

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

- 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
How many times are you calling execute vs a single executemany? The
python call overhead will add up for thousands of calls.

The relevant source code is here if you're interested:

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.
Standard usage is here:


If the database supports transactions then cursors automatically use
transactions. Your changes only get committed when you call .commit().
Otherwise your changes are lost.

In the specific case of sqllite, some statements (like CREATE TABLE,
ALTER TABLE, etc) also cause a commit. This is probably where your
confusion comes from. Since this isn't part of the python DB API spec
(http://www.python.org/dev/peps/pep-0249/) I wouldn't rely on it.
Otherwise you will have problems with other databases.

Also, in your specific case you're using an 'in memory' sqllite db. So
there are less concerns with losing data between db sessions, etc. But
with most databases (on disk, running across the network on a server)
this becomes important.
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)?
I suggest that you use the standard cursor methods instead, so you can
run your code against non-sqllite databases. The performance etc
should be the same as using the direct method. Like the page says,
it's main benefit is consiseness.
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 (
); 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?
As a general rule, always use .commit() and .close(). Otherwise:

- No .commit() - you will lose db changes since the last commit or
"non-DML, non-query statement" (in the case of sqllite)
- No .close() - Your database connection will only close when your db
objects are garbage collected.
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,
curs.executemany('INSERT OR REPLACE INTO "%s" VALUES (%s)' %
(self.text_name, question_marks), tags_seq)

Are there maybe any comments or hints on a more elegant/efficient solution?
I think that dynamically creating schema (tables, based on text file
structure is a bad idea. A few reasons:

- This forces you to dynamically generate all your queries dynamically
- Not all strings are valid table/column names
- This forces the app to run as database administrator (maybe not
important for sqllite, but definitely an issue if you change to
another dbm).
- Potentially huge stability/security problems - text files can
potentially break system tables, overwrite users, etc, etc.

You're violating several rules on db design/usage.

I strongly recommend that you use a better database logic. ie, create
tables and records in advance (setup script, as db admin user if
applicable), then only use delete/insert/update/select statements (as
restricted user, if applicable).

If this is too much trouble, then I suggest storing your database in
regular Python structures instead, and use pickle/yaml/etc to write to
disk. Your current version uses a 'in memory' database, so the end
result is the same. You'll get a large performance boost also.
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.)
It depends. If your app is simple, single threaded, then a single
connection (global or passed through args) should be fine. Only use
multiple cursors if you need them (multiple threads, multiple
databases, multiple transaction/db isolation levels, etc).

Jun 27 '08 #1
0 3792

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: John Machin | last post by:
Apologies in advance if this is a bit bloggy, but I'd like to get comments on whether I've lost the plot (or, more likely, failed to acquire it) before I start reporting bugs etc. From "What's...
by: mensanator | last post by:
Probably just me. I've only been using Access and SQL Server for 12 years, so I'm sure my opinions don't count for anything. I was, nevertheless, looking forward to Sqlite3. And now that gmpy...
by: Josh | last post by:
Hi, I'm running into a problem when trying to create a view in my sqlite database in python. I think its a bug in the sqlite3 api that comes with python 2.5. This works as expected: conn =...
by: jim-on-linux | last post by:
Python help, I just started working with SQLite3 and ran into this problem. Below, the first select produces results but, after closing then re-opening the database the select produces an...
by: Jorgen Bodde | last post by:
Hi all, I am using sqlite3 in python, and I wonder if there is a way to know if there are valid rows returned or not. For example I have a table song with one entry in it. The ID of that entry...
by: Victor Lin | last post by:
Now I am now developing a program that base on sqlite3 in python. But there is a strange problem. That is, all data I insert into sqlite database do not goes into file in disk. It is really...
by: David | last post by:
You're welcome. executemany is probably a good idea here. If memory becomes a problem at some point (eg: millions of lines) you'll probably want to use an on-disk database (I suggest...
by: jeff_d_harper | last post by:
I've run into a problem with text encoding in the Sqlite3 module. I think it may be a bug. By default sqlite3 converts strings in the database from UTF-8 to unicode. This conversion can be...
by: Charles V. | last post by:
Hi, I hope this is not already known. But Google wasn't any help. So here begins a script to explain my problem. ------------------------- import sqlite3 conn = sqlite3.connect(':memory:')...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.