in Ram database?

[Tim Roberts]

William Trenker <wtrenker at shaw.ca> wrote:
>
>Here is a simple example:
>
>import sqlite   #module name exposed by the PySQLite wrapper
>conn = sqlite.connect(db=':memory:')   #special connect string to open an in-memory db
>cursor = conn.cursor()  #standard DB-API-2 from this point
>cursor.execute('create table test (a varchar, b int);')
>cursor.execute('commit;') #end PySQLite implicit transaction
>cursor.execute('begin transaction;')  #wrap the inserts in an explicit transaction for speed
>for i in range(0,10000):
>    cursor.execute('insert into test (a,b) values (%s,%i);'%(str(i),i))
>cursor.execute('commit;')
>cursor.execute('select sum(b) from test;')
>print cursor.fetchall()[0][0]

This fails for me.  There seems to be quite a bit of confusion in sqlite.py
regarding the transaction state.  The Connection object maintains a flag
called inTransaction that doesn't get cleared when you call "commit"
through the Cursor, so the script fails during termination because the
Connection tries to rollback what IT thinks is an open transaction.

So, I tried changing it to use conn.commit() instead of
cursor.execute("commit"), but that causes a new problem: the
cursor.execute("begin transaction") fails beause cursor.execute
automatically opens a new transaction before sending the SQL to the engine,
and nested transactions aren't allowed.

Deleting the call to "begin transaction" solves that problem, because the
first "execute" in the loop causes a transaction to be opened
automatically.

Hmmm; this may be an opportunity for me to actually contribute to pysqlite,
which is one of the more useful things I've come across in Python.
-- 
- Tim Roberts, timr at probo.com
  Providenza & Boekelheide, Inc.