On 2014/07/05 21:55, Jean-Luc Hainaut wrote:
Hi,
Context: Python 2.7.6, Windows XP, SQLite v3.8.5.
The following test program suggest that "with" queries automatically execute a
commit, as if they were DDL statements. I hope this is a bug, otherwise, this side effect
considerably reduces the interest of this query.
Bonjour Jean-Luc,
The assumption is not true I think, there is nothing in a "with" query that
causes committal - but let's see the example.
# -*- coding: UTF8 -*-
import sqlite3
def displayDBcontents():
query = "select * from PERSON"
c.execute(query)
print
for row in c: print '%-4s %-10s' % (row[0],row[1])
# We create and fill the STAFF database
conn = sqlite3.connect('STAFF.db')
c = conn.cursor()
Right about here should be the start of a transaction somewhere... but I do not see any, where did you want to roll back to? If you
are using pysqlite it will probably have an auto-open transaction, but that should be avoided, I'm not sure exactly how since I
don't use it. Either way, you should try do it manually.
c.execute("drop table if exists PERSON")
c.execute("create table PERSON (PID char(4),Name char(10))")
c.execute("insert into PERSON values ('p1','Smith'),('p2','Dermiez')")
conn.commit()
This Commit is a no-op... no explicit transaction was opened, so every one of the above "c.execute()" commands started AND ended an
implicit transaction.
And so we continue without an open transaction... or... maybe a transaction was opened in the background, but now you committed it,
and so it is not open anymore.. or maybe a new one is opened automatically - you need to find out how the wrapper works exactly.
# We check the contents of table PERSON
displayDBcontents()
# We insert Jones and we check the contents of PERSON
c.execute("insert into PERSON values('p3','Jones')")
displayDBcontents()
# We execute a simple "with" query
c.execute("with CTE(A) as (values (1),(2)) select A from CTE")
print
for row in c: print row[0]
# We cancel the last insertion (Jones should disappear)
# and we check the contents of PERSON
conn.rollback()
Roll back to where? there is no open transaction still... every single execute
was a transaction by itself.
displayDBcontents()
# Surprise: Jones still is in the DB
Yep, but not a surprise at all, very much expected.
c.close()
conn.close()
May I suggest a slightly altered script:
import sqlite3
def displayDBcontents():
query = "select * from PERSON"
c.execute(query)
print
for row in c: print '%-4s %-10s' % (row[0],row[1])
# We create and fill the STAFF database
conn = sqlite3.connect('STAFF.db')
c = conn.cursor()
c.execute("BEGIN TRANSACTION;") # This might wee have a defined automatic
implemetnation in the object, such as c.beginTransaction() or such.
c.execute("drop table if exists PERSON")
c.execute("create table PERSON (PID char(4),Name char(10))")
c.execute("insert into PERSON values ('p1','Smith'),('p2','Dermiez')")
c.execute("COMMIT;") # btw - Why did you refer back to the "Conn" here in
your version? c.commit should work just fine.
# Now the last open transaction was committed, there is no longer an open
transaction, so we need to start a new one.
c.execute("BEGIN TRANSACTION") # Or c.beginTransaction() etc.
# We check the contents of table PERSON
displayDBcontents()
# We insert Jones and we check the contents of PERSON
c.execute("insert into PERSON values('p3','Jones')")
displayDBcontents()
# We execute a simple "with" query
c.execute("with CTE(A) as (values (1),(2)) select A from CTE")
print
for row in c: print row[0]
# We cancel the last insertion (Jones should disappear)
# and we check the contents of PERSON
c.execute("ROLLBACK;") # or c.rollback() or such...
displayDBcontents()
# NOW if jones is still here, it would be indeed a huge Surprise - but he won't
be, because SQL cannot lie. :)
I still do not know if the transactions can be used explicit like this in the wrapper, but that is not a SQLite question really. The
above in a normal SQLite (or any other SQL engine) will work as advertised and no happy random committing could be taking place.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
