sqlite3 is sqlite 2?

L

Laszlo Nagy

gandalf@ubuntu:~$ python
Python 2.6.2 (release26-maint, Apr 19 2009, 01:56:41)
[GCC 4.3.3] on linux2
Type "help", "copyright", "credits" or "license" for more information.'2.4.1'

Is it possible to install a real sqlite version 3 somehow? I really need
it because I have to use savepoint/rollback to. That is only supported
from sqlite 3.6.8. Is it enough if I upgrade to Ubuntu Karmic? I know
that is not a question about Python itself.

But still, it is interesting that a module named "sqlite3" can actually
be used for something called "sqlite 2".

Thanks

Laszlo
 
L

Laszlo Nagy

That's the sqlite *bindings* version:
Click to expand...

Thanks. I tried it and RELEASE command didn't work:
.... conn.execute("BEGIN")
.... conn.execute("create table a ( i integer)")
.... conn.execute("insert into a values (1)")
.... conn.execute("savepoint sp1")
.... conn.execute("insert into a values(2)")
.... conn.execute("release sp1")
.... conn.execute("COMMIT")
....
<sqlite3.Cursor object at 0xb7e29b30>
<sqlite3.Cursor object at 0xb7e29b60>
<sqlite3.Cursor object at 0xb7e29b30>
<sqlite3.Cursor object at 0xb7e29b60>
<sqlite3.Cursor object at 0xb7e29b30>
Traceback (most recent call last):


The syntax is correct: http://www.sqlite.org/lang_savepoint.html
The savepoint was really created.
But I get this error, telling "no such savepoint". What is wrong here?
Maybe it has to do something with transaction isolation? :-s

Thank you

Laszlo
 
L

Laszlo Nagy

From memory you can't issue a "CREATE TABLE" statement inside a
transaction, at least not at the default isolation level. Such a
statement will automatically commit the current transaction. Doesn't
help with your current problem but worth pointing out :)
Thank you. I'll keep in mind.
When debugging strange transaction behaviour, I find it easiest to
create the connection with isolation_level=None so that are no implicit
transactions being created behind your back. Not sure why, but setting
this makes your example work for me.
Click to expand...
Yes, same for me. But setting it to None means auto commit mode! See here:

http://docs.python.org/library/sqlite3.html#sqlite3-controlling-transactions


But it does not work that way. Look at this example

import sqlite3

conn = sqlite3.connect(':memory:')
conn.isolation_level = None
with conn:
conn.execute("create table a ( i integer ) ")

with conn:
conn.execute("insert into a values (1)")
conn.execute("SAVEPOINT sp1")
conn.execute("insert into a values (2)")
conn.execute("SAVEPOINT sp2")
conn.execute("insert into a values (3)")
conn.execute("ROLLBACK TO sp2")
conn.execute("insert into a values (4)")
conn.execute("RELEASE sp1")

with conn:
for row in conn.execute("select * from a"):
print row


It prints:

(1,)
(2,)
(4,)

So everything is working. Nothing is auto commited. But if I change it
to "DEFERRED" or "IMMEDIATE" or "EXCLUSIVE" then it won't work. Why?

I'm now confused. Also, I could not find anything about these isolation
levels on the sqlite website. The only think I could find is "PRAGMA
read_uncommited". If that is the same as setting isolation_level to
None, then I don't want it.

L
 
L

Laszlo Nagy

I'm now confused. Also, I could not find anything about these
isolation levels on the sqlite website. The only think I could find is
"PRAGMA read_uncommited". If that is the same as setting
isolation_level to None, then I don't want it.
Click to expand...
Yes, it is. Here is a test:

import os
import sqlite3
import threading
import time

FPATH = '/tmp/test.sqlite'
if os.path.isfile(FPATH):
os.unlink(FPATH)

def getconn():
global FPATH
conn = sqlite3.connect(FPATH)
conn.isolation_level = None
return conn

class Thr1(threading.Thread):
def run(self):
conn = getconn()
print "Thr1: Inserting 0,1,2,3,4,5"
with conn:
for i in range(6):
conn.execute("insert into a values (?)",)
print "Thr1: Commited"
with conn:
print "Thr1: Selecting all rows:"
for row in conn.execute("select * from a"):
print row
print "Thr1: Wait some..."
time.sleep(3)
print "Thr1: Selecting again, in the same transaction"
for row in conn.execute("select * from a"):
print row


class Thr2(threading.Thread):
def run(self):
conn = getconn()
with conn:
print "Thr2: deleting all rows from a"
conn.execute("delete from a")
print "Thr2: Now we wait some BEFORE commiting changes."
time.sleep(3)
print "Thr2: Will roll back!"
raise Exception


def main():
with getconn() as conn:
conn.execute("create table a ( i integer ) ")
thr1 = Thr1()
thr1.start()
time.sleep(1)
thr1 = Thr2()
thr1.start()

main()


And the test result:

Thr1: Inserting 0,1,2,3,4,5
Thr1: Commited
Thr1: Selecting all rows:
(0,)
(1,)
(2,)
(3,)
(4,)
(5,)
Thr1: Wait some...
Thr2: deleting all rows from a
Thr2: Now we wait some BEFORE commiting changes.
Thr1: Selecting again, in the same transaction
Thr2: Will roll back!
Exception in thread Thread-2:
Traceback (most recent call last):
File "/usr/lib/python2.6/threading.py", line 525, in __bootstrap_inner
self.run()
File "test.py", line 44, in run
raise Exception
Exception


It means that setting isolation_level to None will really allow
uncommited changes to be read by other transactions! This is sad, and of
course this is something that I do not want. If I change it to DEFERRED
then I get a correct result:

Thr1: Inserting 0,1,2,3,4,5
Thr1: Commited
Thr1: Selecting all rows:
(0,)
(1,)
(2,)
(3,)
(4,)
(5,)
Thr1: Wait some...
Thr2: deleting all rows from a
Thr2: Now we wait some BEFORE commiting changes.
Thr1: Selecting again, in the same transaction
(0,)
(1,)
(2,)
(3,)
(4,)
(5,)
Thr2: Will roll back!

However, then savepoints won't work. Is there any way to use read
commited (or higher) isolation level, and have savepoints working at the
same time?

I don't see how would savepoints be useful without at least read
commited isolation level. :-(

L
 
L

Laszlo Nagy

No it doesn't. The problem is that using a connection as a context
manager doesn't do what you think.

It does *not* start a new transaction on __enter__ and commit it on
__exit__. As far as I can tell it does nothing on __enter__ and calls
con.commit() or con.rollback() on exit. With isolation_level=None,
these are no-ops.
Click to expand...
Thank you Ryan! You are abolutely right, and thank you for reading the
source. Now everything works as I imagined.

The way the context manager and isolation_level works looks very very
strange to me. Here is a demonstration:

import sqlite3
def getconn():
conn = sqlite3.connect(':memory:')
conn.isolation_level = None
return conn
def main():
with getconn() as conn:
conn.execute("create table a ( i integer ) ")
try:
conn.execute("insert into a values (1)")
with conn:
conn.execute("insert into a values (2)")
raise Exception
except:
print "There was an error"
for row in conn.execute("select * from a"):
print row
main()


Output:

There was an error
(1,)
(2,)


Looks like the context manager did not roll back anything. If I remove
isolation_level=None then I get this:

There was an error

E.g. the context manager rolled back something that was executed outside
the context. I cannot argue with the implementation - it is that way.
But this is not what I would expect. I believe I'm not alone with this.

Using your connection manager, everything is perfect:

There was an error
(1,)


The only thing I have left is to implement a connection manager that
emulates nested transactions, using a stack of savepoints. :)

Suggestions:

Just for clarity, we should put a comment at the end of the
documentation here:

http://docs.python.org/library/sqlite3.html#sqlite3-controlling-transactions

I would add at least these things:

#1. By using isolation_level = None, connection objects (used as a
context manager) WON'T automatically commit or rollback transactions.
#2. Using any isolation level, connection objects WON'T automatically
begin a transaction.
#3. Possibly, include your connection manager class code, to show how to
do it "the expected" way.

Also one should clarify in the documentation, what isolation_level does.
Looks like setting isolation_level to None is not really an "auto commit
mode". It is not even part of sqlite itself. It is part of the python
extension.

Thank you again.

Laszlo
 
J

John Bokma

Laszlo Nagy said:
gandalf@ubuntu:~$ python
Python 2.6.2 (release26-maint, Apr 19 2009, 01:56:41)
[GCC 4.3.3] on linux2
Type "help", "copyright", "credits" or "license" for more information.'2.4.1'

Is it possible to install a real sqlite version 3 somehow? I really
need it because I have to use savepoint/rollback to. That is only
supported from sqlite 3.6.8. Is it enough if I upgrade to Ubuntu
Karmic? I know that is not a question about Python itself.

But still, it is interesting that a module named "sqlite3" can
actually be used for something called "sqlite 2".
Click to expand...

You're mistaking the *module* version with the version of the database,
unless I am mistaken.
 
R

Ryan Kelly

... conn.execute("BEGIN")
... conn.execute("create table a ( i integer)")
... conn.execute("insert into a values (1)")
... conn.execute("savepoint sp1")
... conn.execute("insert into a values(2)")
... conn.execute("release sp1")
... conn.execute("COMMIT")
...
<sqlite3.Cursor object at 0xb7e29b30>
<sqlite3.Cursor object at 0xb7e29b60>
<sqlite3.Cursor object at 0xb7e29b30>
<sqlite3.Cursor object at 0xb7e29b60>
<sqlite3.Cursor object at 0xb7e29b30>
Traceback (most recent call last):


The syntax is correct: http://www.sqlite.org/lang_savepoint.html
The savepoint was really created.
But I get this error, telling "no such savepoint". What is wrong here?
Maybe it has to do something with transaction isolation? :-s
Click to expand...

From memory you can't issue a "CREATE TABLE" statement inside a
transaction, at least not at the default isolation level. Such a
statement will automatically commit the current transaction. Doesn't
help with your current problem but worth pointing out :)

When debugging strange transaction behaviour, I find it easiest to
create the connection with isolation_level=None so that are no implicit
transactions being created behind your back. Not sure why, but setting
this makes your example work for me.

Ryan




--
Ryan Kelly
http://www.rfk.id.au | This message is digitally signed. Please visit
(e-mail address removed) | http://www.rfk.id.au/ramblings/gpg/ for details


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEABECAAYFAkudT00ACgkQfI5S64uP50rlAQCgp0uMuEQsNCXDF58oKYpWVwDs
pIEAnihmCcuYe09GbLwLOqorTmTfLpsE
=VLEN
-----END PGP SIGNATURE-----
 
R

Ryan Kelly

Yes, it is. Here is a test:
Click to expand...

No it isn't. The "magic" behind isolation_level is a creation of the
python sqlite bindings. You can probably tell that I'm not a fan of it.
import os
import sqlite3
import threading
import time

FPATH = '/tmp/test.sqlite'
if os.path.isfile(FPATH):
os.unlink(FPATH)

def getconn():
global FPATH
conn = sqlite3.connect(FPATH)
conn.isolation_level = None
return conn

class Thr1(threading.Thread):
def run(self):
conn = getconn()
print "Thr1: Inserting 0,1,2,3,4,5"
with conn:
for i in range(6):
conn.execute("insert into a values (?)",)
print "Thr1: Commited"
with conn:
print "Thr1: Selecting all rows:"
for row in conn.execute("select * from a"):
print row
print "Thr1: Wait some..."
time.sleep(3)
print "Thr1: Selecting again, in the same transaction"
for row in conn.execute("select * from a"):
print row


class Thr2(threading.Thread):
def run(self):
conn = getconn()
with conn:
print "Thr2: deleting all rows from a"
conn.execute("delete from a")
print "Thr2: Now we wait some BEFORE commiting changes."
time.sleep(3)
print "Thr2: Will roll back!"
raise Exception


def main():
with getconn() as conn:
conn.execute("create table a ( i integer ) ")
thr1 = Thr1()
thr1.start()
time.sleep(1)
thr1 = Thr2()
thr1.start()

main()


And the test result:

Thr1: Inserting 0,1,2,3,4,5
Thr1: Commited
Thr1: Selecting all rows:
(0,)
(1,)
(2,)
(3,)
(4,)
(5,)
Thr1: Wait some...
Thr2: deleting all rows from a
Thr2: Now we wait some BEFORE commiting changes.
Thr1: Selecting again, in the same transaction
Thr2: Will roll back!
Exception in thread Thread-2:
Traceback (most recent call last):
File "/usr/lib/python2.6/threading.py", line 525, in __bootstrap_inner
self.run()
File "test.py", line 44, in run
raise Exception
Exception


It means that setting isolation_level to None will really allow
uncommited changes to be read by other transactions! This is sad, and of
course this is something that I do not want.
Click to expand...



No it doesn't. The problem is that using a connection as a context
manager doesn't do what you think.

It does *not* start a new transaction on __enter__ and commit it on
__exit__. As far as I can tell it does nothing on __enter__ and calls
con.commit() or con.rollback() on exit. With isolation_level=None,
these are no-ops.

If you create your own connection wrapper that explicitly creates and
commits transactions, you example will work fine with
isolation_level=None. Here's the relevant changes:


class MyConn(sqlite3.Connection):
def __enter__(self):
self.execute("BEGIN")
return self
def __exit__(self,exc_type,exc_info,traceback):
if exc_type is None:
self.execute("COMMIT")
else:
self.execute("ROLLBACK")

def getconn():
global FPATH
conn = sqlite3.connect(FPATH,factory=MyConn)
conn.isolation_level = None
return conn


Cheers,

Ryan


--
Ryan Kelly
http://www.rfk.id.au | This message is digitally signed. Please visit
(e-mail address removed) | http://www.rfk.id.au/ramblings/gpg/ for details


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEABECAAYFAkudX5sACgkQfI5S64uP50rvSQCffuzC9vU5mUBTQFAUlrce6HCk
204An09Ody1i2StbP7O8YyqIE/1MEDXn
=UEhE
-----END PGP SIGNATURE-----
 
R

Ryan Kelly

Yes, same for me. But setting it to None means auto commit mode! See here:

http://docs.python.org/library/sqlite3.html#sqlite3-controlling-transactions


But it does not work that way. Look at this example

import sqlite3

conn = sqlite3.connect(':memory:')
conn.isolation_level = None
with conn:
conn.execute("create table a ( i integer ) ")

with conn:
conn.execute("insert into a values (1)")
conn.execute("SAVEPOINT sp1")
conn.execute("insert into a values (2)")
conn.execute("SAVEPOINT sp2")
conn.execute("insert into a values (3)")
conn.execute("ROLLBACK TO sp2")
conn.execute("insert into a values (4)")
conn.execute("RELEASE sp1")

with conn:
for row in conn.execute("select * from a"):
print row


It prints:

(1,)
(2,)
(4,)

So everything is working. Nothing is auto commited. But if I change it
to "DEFERRED" or "IMMEDIATE" or "EXCLUSIVE" then it won't work. Why?
Click to expand...

I have a theory, based on a quick perusal of the sqlite3 bindings
source.

The bindings think that "SAVEPOINT sp1" is a "non-DML, non-query"
statement. So when isolation_level is something other than None, this
statement implicitly commits the current transaction and throws away
your savepoints!

Annotating your example:

# entering this context actually does nothing
with conn:
# a transaction is magically created before this statement
conn.execute("insert into a values (1)")
# and is implicitly committed before this statement
conn.execute("SAVEPOINT sp1")
# a new transaction is magically created
conn.execute("insert into a values (2)")
# and committed, discarding the first savepoint.
conn.execute("SAVEPOINT sp2")
# a new transaction is magically created
conn.execute("insert into a values (3)")
# and committed, discarding the very savepoint we are trying to use.
conn.execute("ROLLBACK TO sp2")
conn.execute("insert into a values (4)")
conn.execute("RELEASE sp1")


In your previous multi-threaded example, try adding a "SAVEPOINT sp1"
statement after deleting the rows in Thread2. You'll see that the
delete is immediately committed and the rows cannot be read back by
Thread1. (modified version attached for convenience).


Cheers,

Ryan

--
Ryan Kelly
http://www.rfk.id.au | This message is digitally signed. Please visit
(e-mail address removed) | http://www.rfk.id.au/ramblings/gpg/ for details


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEABECAAYFAkudbj8ACgkQfI5S64uP50pSQQCfZW+JV0d8ls7PmicH/Ksk+D+j
FswAnj6YygK4WhObMFDK85BeL8pIlNJu
=BV5/
-----END PGP SIGNATURE-----
 
L

Laszlo Nagy

Annotating your example:

# entering this context actually does nothing
with conn:
# a transaction is magically created before this statement
conn.execute("insert into a values (1)")
# and is implicitly committed before this statement
conn.execute("SAVEPOINT sp1")
# a new transaction is magically created
conn.execute("insert into a values (2)")
# and committed, discarding the first savepoint.
conn.execute("SAVEPOINT sp2")
# a new transaction is magically created
conn.execute("insert into a values (3)")
# and committed, discarding the very savepoint we are trying to use.
conn.execute("ROLLBACK TO sp2")
conn.execute("insert into a values (4)")
conn.execute("RELEASE sp1")
Click to expand...

We all know the Zen of Python. Explicit is better than implicit.

There is no point in using a savepoint outside a transaction. There is
no point in using a savepoint if it commits all previous changes
automatically.

Conclusion:

Sqlite's isolation_level is dark magic. It mixes real isolation levels
with behaviour of context managers, and automagical commits in the wrong
places.
Setting isolation_level=None is a must for anyone who want to do any
serious work with sqlite.

L
 
R

Ryan Kelly

Thank you Ryan! You are abolutely right, and thank you for reading the
source. Now everything works as I imagined.
Click to expand...

No problemo - isolation_level has given me my fair share of headaches in
the past, so I couldn't resist the opportunity to understand it a little
better.
The way the context manager and isolation_level works looks very very
strange to me. Here is a demonstration:

import sqlite3
def getconn():
conn = sqlite3.connect(':memory:')
conn.isolation_level = None
return conn
def main():
with getconn() as conn:
conn.execute("create table a ( i integer ) ")
try:
conn.execute("insert into a values (1)")
with conn:
conn.execute("insert into a values (2)")
raise Exception
except:
print "There was an error"
for row in conn.execute("select * from a"):
print row
main()


Output:

There was an error
(1,)
(2,)


Looks like the context manager did not roll back anything.
Click to expand...

Yes, because there were no transactions created so there was nothing to
roll back.
If I remove
isolation_level=None then I get this:

There was an error

E.g. the context manager rolled back something that was executed outside
the context.
Click to expand...

Yes, because the transactions created by the default isolation level do
not nest, so the rollback happens at outermost scope.
I cannot argue with the implementation - it is that way.
But this is not what I would expect. I believe I'm not alone with this.
Click to expand...

That's at least two of us :)
Just for clarity, we should put a comment at the end of the
documentation here:

http://docs.python.org/library/sqlite3.html#sqlite3-controlling-transactions

I would add at least these things:

#1. By using isolation_level = None, connection objects (used as a
context manager) WON'T automatically commit or rollback transactions.
#2. Using any isolation level, connection objects WON'T automatically
begin a transaction.
#3. Possibly, include your connection manager class code, to show how to
do it "the expected" way.

Also one should clarify in the documentation, what isolation_level does.
Looks like setting isolation_level to None is not really an "auto commit
mode". It is not even part of sqlite itself. It is part of the python
extension.
Click to expand...

I think of it as almost the opposite - you have to set
isolation_level=None to get the unadulterated behaviour of the
underlying sqlite library.

I'm sure the devs would appreciate a documentation patch (submission
details at http://python.org/dev/patches/). I'm also pretty confident
that I won't have time to do one up anytime soon :)


Good luck with your project!


Ryan

--
Ryan Kelly
http://www.rfk.id.au | This message is digitally signed. Please visit
(e-mail address removed) | http://www.rfk.id.au/ramblings/gpg/ for details


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEABECAAYFAkud54kACgkQfI5S64uP50pQ1wCghoUcCvVPQ/O/yRwwp3ETKlhW
c6EAnA9QeCFo/8maAZsQGOYQdp25RYUl
=9VL0
-----END PGP SIGNATURE-----
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

sqlite version on windows 0
SQLite + FTS (full text search) 8
expandtabs acts unexpectedly 2
semantics of ** (unexpected/inconsistent?) 2
How to install 64-bit python on Ubuntu 5
sqlite3 and Python 2.5.1 3
sqlite3 bug?? 13
? 'in' operator and fallback to __getitem__ 6

Members online

Total: 31 (members: 1, guests: 30)
Robots: 126

Forum statistics

Threads
473,772
Messages
2,569,593
Members
45,105
Latest member
sheetaldubay7750ync

Latest Threads

Top