473,246 Members | 1,423 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

sqlite3 db update extremely slow

I am trying to fill a sqlite3 database with records, and to this end I
have written a class that creates the db, the table and adds rows to
the table.

The problem is that the updating process is *extremely* slow, and
occasionally I get the message "database locked".

I tried removing "self.con.commit()" in the add_record method, but
then nothing is saved in the db. I don't know whether this has
anything to do with it, but I have found no option to enable
autocommit.

This is the class that I am using:

class sqliteDB(object):
"Wrapper for SQLite methods"
def __init__(self, db_file="sqlite3.db"):
'Intialize SQLite database, sqlite_db_init("db_file_name.db")'
print 'SQLite db init: ', db_file
self.con = sqlite3.connect(db_file)
self.cur = self.con.cursor()

def create_table(self, table):
"create table (table_name)"

query ='CREATE TABLE %s (hword VARCHAR(256) PRIMARY KEY,
definition TEXT)' % table
try:
self.cur.execute(query)
self.con.commit()
except Exception, e:
print e

def add_record (self, table, headWord, definition):

try:
self.cur.execute('INSERT INTO ' + table + '(hword,
definition) VALUES(?, ?)', (headWord, definition))
self.con.commit()
except Exception, e:
print e

And this is the actual code that I use to write to the db file:

db = sqliteDB()
db.create_table("table_name")

for k, v in myData:
db.add_record(table, k,v)

This works extremely slow (~10KB of data per second) and takes ages to
complete even with small files. Where did I go wrong?

Would it be faster (or possible) to import a text file to sqlite using
something like the mysql's command
LOAD DATA INFILE "myfile.csv"...?

Jul 16 '07 #1
5 10598
coldpizza schreef:
I am trying to fill a sqlite3 database with records, and to this end I
have written a class that creates the db, the table and adds rows to
the table.

The problem is that the updating process is *extremely* slow, and
occasionally I get the message "database locked".

I tried removing "self.con.commit()" in the add_record method, but
then nothing is saved in the db. I don't know whether this has
anything to do with it, but I have found no option to enable
autocommit.
Remove self.con.commit() from add_record(), and do it once after all
records are added.

The reason that the process is slow with a commit after every INSERT is
that sqlite syncs the inserted data to disk before it continues.

--
If I have been able to see further, it was only because I stood
on the shoulders of giants. -- Isaac Newton

Roel Schroeven
Jul 16 '07 #2
Thanks a lot, Roel, adding a single commit() at the end did solve the
speed problem.

Another question is do I have to explicitly close the DB connection,
or is it automatically garbage collected? Is it Ok to no have any
cleanup code?

Another question would be how to define the encoding for newly added
records?
And how do set the encoding for the retrieved records? Is it always
utf-8 by default?

On Jul 16, 11:21 pm, Roel Schroeven <rschroev_nospam...@fastmail.fm>
wrote:
coldpizza schreef:
I am trying to fill a sqlite3 database with records, and to this end I
have written a class that creates the db, the table and adds rows to
the table.
The problem is that the updating process is *extremely* slow, and
occasionally I get the message "database locked".
I tried removing "self.con.commit()" in the add_record method, but
then nothing is saved in the db. I don't know whether this has
anything to do with it, but I have found no option to enable
autocommit.

Remove self.con.commit() from add_record(), and do it once after all
records are added.

The reason that the process is slow with a commit after every INSERT is
that sqlite syncs the inserted data to disk before it continues.

--
If I have been able to see further, it was only because I stood
on the shoulders of giants. -- Isaac Newton

Roel Schroeven

Jul 16 '07 #3
coldpizza wrote:
Thanks a lot, Roel, adding a single commit() at the end did solve the
speed problem.

Another question is do I have to explicitly close the DB connection,
or is it automatically garbage collected? Is it Ok to no have any
cleanup code?
It's generally OK, but you can register a function with atexit() if you
are paranoid about cleanup. Here's a sample with an ugly global variable.

from atexit import register

def close():
global conn
if conn:
conn.close()
print "Database closed"
conn = None

#
# We try to ensure the database is always closed by registering
# the nodule's close() function to be called on program exit
#
register(close)

import psycopg2 as db
conn = db.connect(database="billing", user="steve", password="tadaa!")
curs = conn.cursor()

print "Database opened"
Another question would be how to define the encoding for newly added
records?
And how do set the encoding for the retrieved records? Is it always
utf-8 by default?
Generally speaking each database instance will have an associated
encoding. Trying to establish some other encoding would then be pissing
into the wind.

regards
Steve
--
Steve Holden +1 571 484 6266 +1 800 494 3119
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://del.icio.us/steve.holden
--------------- Asciimercial ------------------
Get on the web: Blog, lens and tag the Internet
Many services currently offer free registration
----------- Thank You for Reading -------------

Jul 17 '07 #4
Steve Holden <st***@holdenweb.comwrites:
# We try to ensure the database is always closed by registering
# the nodule's close() function to be called on program exit
Ooh! Where do I find more about writing Python nodules? Is it related
to cluster programming?

--
\ "If you go parachuting, and your parachute doesn't open, and |
`\ you friends are all watching you fall, I think a funny gag |
_o__) would be to pretend you were swimming." -- Jack Handey |
Ben Finney
Jul 17 '07 #5
coldpizza wrote:
Thanks a lot, Roel, adding a single commit() at the end did solve the
speed problem.

Another question is do I have to explicitly close the DB connection,
or is it automatically garbage collected? Is it Ok to no have any
cleanup code?

Another question would be how to define the encoding for newly added
records?
And how do set the encoding for the retrieved records? Is it always
utf-8 by default?
SQLite databases store text in UTF-8 encoding. If you use pysqlite, and
always use unicode strings, you will never have any problems with that.
pysqlite does not rap on your knuckles if you store arbitrary encodings in
the database, but you will feel sorry once you try to fetch the data:
>>from pysqlite2 import dbapi2 as sqlite
con = sqlite.connect(":memory:")
binary_rubbish = chr(130) + chr(200)
con.execute("create table foo(bar)")
<pysqlite2.dbapi2.Cursor object at 0xb7dc20b0>
>>con.execute("insert into foo(bar) values (?)", (binary_rubbish,))
<pysqlite2.dbapi2.Cursor object at 0xb7dc22f0>
>># so far, so good ...
.... # watch now
....
>>con.execute("select bar from foo")
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
pysqlite2.dbapi2.OperationalError: Could not decode to UTF-8 column 'bar'
with text '��'
>>>
HTH

-- Gerhard

Jul 17 '07 #6

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

Similar topics

0
by: smartin | last post by:
python IIS cgi loading extremely slow I recently uninstalled python 1.5.2 and installed python 2.3 on a Windows 2000 server running IIS 5.0. The issue is that when submitting a cgi request to a...
5
by: garydevstore | last post by:
Hi, I have a table defined as CREATE TABLE ( IDENTITY (1, 1) NOT NULL , NULL , NULL , NOT NULL , NULL , (255) COLLATE SQL_Latin1_General_CP1_CS_AS
83
by: D. Dante Lorenso | last post by:
Trying to use the 'search' in the docs section of PostgreSQL.org is extremely SLOW. Considering this is a website for a database and databases are supposed to be good for indexing content, I'd...
2
by: Chris | last post by:
I'm using a DataTable in my application. I am able to load rows into the DataTable quickly. What's puzzling me, however, is that when I update a set of cells in the DataTable, the update is really...
2
by: gcmf8888 | last post by:
I use socket client to connect to a server written in C++. I found out that the C# socket connection(I didn't use TCPClient instead i used socket) is extremely slow(comparing to Java and C++ one I...
2
by: tormod | last post by:
I'm using Visual Studio 2005 professional edition and try to build a web site built in c#. The solution consists of about 20 class library projects and one web site. The web site references the...
6
by: DCC-700 | last post by:
I am running VB for ASP.Net in VS.Net 2003 and am experiencing extremely slow response in the ide at times and the debugger. Below is additional detail on the problem. Any thoughts are much...
0
by: Carlo Marchesoni | last post by:
I have an application that has a main.aspx page (something like a todo list). Clicking one of the items open an new window (javascript window.open) with a new aspx page. this is extremely slow....
1
by: CSmith | last post by:
Design view moving extremely slow ...HELP!!!
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, youll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

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.