sqlobject performance problems (really)

[qvx]

I'm writing a small project and I decided to try pysqlite. The database
consists of one master table with five columns and two detail tables
with one and two columns each (not counting foreign key columns). The
program scans an input file and inserts data into those three tables.

First I used pysqlite (ver 2). It took a few seconds to parse and
populate one thousand of main record and five-six thousand detail
records (including a print statement for each main record). This is
acceptable.

Then I decided to give a try to sqlobject (I had to revert to pysqlite
1.x). I created something like this:

class T1(SQLObject):
col1 = StringCol()
col2 = StringCol(length=5)
...
det1 = MultipleJoin('T2')
det2 = MultipleJoin('T3')

class T2(SQLObject):
...
t1 = ForeignKey('T1')

class T2(SQLObject):
...
t1 = ForeignKey('T1')

My main loop looks like this:

for r1 in par.parse(filename):
# r1 is an intermediary object because I didn't
# know how to instantiate an instance without
# creating a record automatically, especially
# because I didn't have all mandatory values
# up until the end of the parse so I had to
# keep the values in paralel instead of
# storing them directly to my brand new class
print r
t1 = T1(col1=r.col1, ...)
for r2 in r1.det1:
t2 = T2(..., t1=t1)
for r3 in r1.det2:
t2 = T3(..., t1=t1)

Now this takes around half a second for ONE master record!!!

When I turned on the debug mode of connection I could see a lots of
*select* and *commit* statements. I tried to disable autocommit but
with no success. I also tried to explicitly provide ID column (hoping
to avoid select) but also with no success.

"autocommit off" attempt:

connection_string = 'sqlite:/' + db_filename +'?autoCommit=0'

"no select" attempt:

t1 = T1(id=t1id, col1=r.col1, ...)

Any ideas how to make sqlobject work as fast as plain pysqlite.

P.S. I used 0.6.1 version of sqlobject, but later I downloaded fresh
version from SVN (upgraded pysqlite to 2.x, downloaded formencode,
ez_setup, setuptools and maybe others) but it still doesn't work any
better.


qvx

 

[qvx]

"autocommit off" attempt:

connection_string = 'sqlite:/' + db_filename +'?autoCommit=0'

"no select" attempt:

t1 = T1(id=t1id, col1=r.col1, ...)

I changed :

conn_string = 'sqlite:/' + db_datoteka +'?autoCommit=0'
conn = connectionForURI(conn_string)
SQLObject._connection = conn

into:

conn_string = 'sqlite:/' + db_datoteka +'?autoCommit=0'
conn = connectionForURI(conn_string)
conn.autoCommit = False
tran = conn.transaction()
sqlhub.threadConnection = tran

This seems to help in performance department. But it now fails with
exception:


* when input is unicode
for ex: t1.col1 = unicode(col1, encoding='dbcs')

=> UnicodeEncodeError: 'ascii' codec can't encode character
u'\u0107' ...
File "sqlobject\col.py", line 498, in from_python
return value.encode("ascii")

* when input is 'utf8'
for ex: t1.col1 = unicode(col1, encoding='dbcs').encode('utf8')

=> UnicodeEncodeError: 'ascii' codec can't encode character
u'\u017d' ...
File "sqlobject\col.py", line 489, in to_python
return value.encode("ascii")

* when input is 'dbcs'
for ex: t1.col1 = col1_var # col1_var it is already in 'dbcs'

=> UnicodeDecodeError: 'utf8' codec can't decode bytes ...
File "sqlobject\dbconnection.py", line 295, in _executeRetry
return cursor.execute(query)


P.S. I'm a Windows Central European (windows-1250) user and my
sys.setdefaultencoding is 'dbcs'.