473,722 Members | 2,240 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

- Are there any peculiarities with using curs.executeman y(...) 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.clos e() - 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('C REATE 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('A LTER TABLE "%s" ADD "%s" TEXT' % (self.text_name ,
curs.executeman y('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?
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 4080

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 new ...": """ # Create table c.execute('''create table stocks (date timestamp, trans varchar, symbol varchar, qty decimal, price decimal)''')
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 has been upgraded, I can go ahead and install Python 2.5. So I open the manual to Section 13.13 where I find the first example of how to use Sqlite3:
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 = sqlite3.connect(':memory:') conn.execute("create table foo (a int,b int)") conn.execute('create view bar as select * from foo')
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 empty list. Anyone know the reason ??
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 is 1, so when I do; .... print s .... (1, u'Spikedrivers Blues', u'Mississippi John Hurt')
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 strange.... Why do these data just keep in memory and discarded? All things that really store in file is the table. If I create a table, it would appears in the sqlite file.
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 postgresql), and read in batches of say 1000, which you save to the database with a single executemany. It sounds like your Python code has some serious (algorithm or data
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 controlled by changing the connection's text_factory. I have a database that stores strings in 8-bit ISO-8859. So, I set the text_factory to do no conversion. In my database I use user defined functions. I noticed that even when I set...
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:') c = conn.cursor() c.execute('''create table stocks
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.