473,714 Members | 2,453 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:

http://svn.python.org/projects/pytho...qlite/cursor.c
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:

http://docs.python.org/lib/module-sqlite3.html

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 (
http://docs.python.org/lib/sqlite3-C...g-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 ,
new_col))
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).

David.
Jun 27 '08 #1
0 4078

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

Similar topics

2
1970
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)''')
66
7066
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:
2
4950
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')
0
994
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 ??
6
3229
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')
2
1716
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.
0
239
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
1
3100
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...
0
1227
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
0
8797
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9309
Oralloy
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...
0
9171
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9071
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
6629
isladogs
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...
0
4462
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...
0
4717
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3156
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
3
2107
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.