in Ram database?

[Gerhard Häring]

Tim Roberts wrote:
> William Trenker <wtrenker at shaw.ca> wrote:
>>Here is a simple example:
>>[example of how NOT to use the DB-API]
> This fails for me. 

No surprise.

> There seems to be quite a bit of confusion in sqlite.py
> regarding the transaction state. 

No.

> The Connection object maintains a flag
> called inTransaction that doesn't get cleared when you call "commit"
> through the Cursor, 

Yeah, because that's not the way the Python DB-API is to be used. You're 
supposed to use the methods .commit() and .rollback() on the cursor 
object for transaction management.

> 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.

Yeah, maybe you should consider reading the DB-API spec or the PySQLite 
docs? See above for *NOT* issuing BEGIN/ROLLBACK/COMMIT yourself through 
.execute(), unless you know exactly what you're doing, which you are 
apparently not ;)

If you know exactly what you're doing, you can use the parameter 
autocommit=1 (not =0, like I wrote in my other response) in the 
.connect() call.

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

PySQLite has 7 tests in its test suite for transaction handling. Now 
hacking the PySQLite source code seems like a smart idea to make a 
b0rken example code work :-/

> 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.

I'd be happy to help you get a better understanding. But I'm currently 
/a little bit/ énervé because of the same old b. s. "solutions" for some 
problems being repeatedly posted on c.l.py. So people please remember:

1) the DB-API module handles transactions just fine for you, there is NO 
need to mess around with this yourself. Use .commit() or .rollback() on 
the connection object and everybody will be happy. Especially me ;)

2) the DB-API module quotes parameters just fine for you, there is NO 
need to mess around with this yourself. Just use a tuple of the 
parameters for the second parameter of the .execute() call.

-- Gerhard