Re: Transaction isolation

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Pawel Lewicki wrote:
> Hi,
> I tried to find information about my problem but no clear solution so far.
> What should I do to keep transactions isolated?

That's easy. Use pysqlite2 the standard way and don't try to issue BEGIN,
COMMIT or ROLLBACK statements via execute calls.

Connections are started implicitly once you execute() a DML (data
modification language) command like DELETE, INSERT, UPDATE, REPLACE.

To make your data modifications permament, use the .commit() method of the
connection object.

To throw away the changes of the current transation, use the .rollback()
method of the connection object.

There are quite a few problems in the following code of yours ...

> I have a script like one copied from post from February:
> 
>   >>> from pysqlite2 import dbapi2 as sqlite
>   >>> con = C=sqlite.connect("/path")

Erm. first C is a connection object.

>   >>> C = con.cursor()

Then it is a cursor object. But that's only cosmetic.

>   >>> C.execute("begin")

Don't do that. If, and only if, you don't want to use the DB-API way of
implicit transactions and commit() and rollback methods, you first have to
set isolation_level to None:

con = sqlite.connect(..., isolation_level=None)

>   >>> C.execute("delete from t;")

Only cosmetic but good practise: get rid of trailing semicolons. They're
useless.

>   >>> C.executemany("insert into t values(?);", (1,2,3))

Two errors here.

I assume you really wanted executemany here. I. e. do the equivalent of

C.execute("insert into t values (?)", (1,))
C.execute("insert into t values (?)", (2,))
C.execute("insert into t values (?)", (3,))

The problem here is that executemany needs a *list* of parameter *tuples*.
So you'd need this instead:

C.executemany("insert into t values (?)", [(1,), (2,), (3,)])

> Traceback (most recent call last):
>     File "<interactive input>", line 1, in <module>
> OperationalError: SQL logic error or missing database
>   >>>

That's why SQLite becomes so confused here. The input doesn't make any
sense for executemany().

> When I give up "C.execute("begin")" everything is fine except for 
> transaction isolation.

I believe you're misinterpreting something here. pysqlite starts
transactions just fine if you don't execute BEGIN statements yourself.

> I have another concurrent connection (ODBC) and it reads empty set if I 
> query table between DELETE and INSERT from previous connection.
> What should I do?

I hope my tips above help you achieving what you want to do.

- -- Gerhard
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGcF9DdIO4ozGCH14RAjm2AJsHDNC/QcdUwuKzSz7Ped5tuaQ7SQCfTwPX
OJUvJvS4NXIlBTc2glAnjdw=
=Z7Tw
-----END PGP SIGNATURE-----