ie, this will work: isolation_level = None and explicitly "BEGIN" transactions
...
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', isolation_level=None)
c = conn.cursor()
c.execute("drop table if exists PERSON")
c.execute("create table PERSON (PID char(4),Name char(10))")
c.execute('BEGIN')
c.execute("insert into PERSON values ('p1','Smith'), ('p2','Dermiez')")
conn.commit()
# We check the contents of table PERSON
displayDBcontents()
# We insert Jones and we check the contents of PERSON
c.execute('BEGIN')
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()
displayDBcontents()
# Surprise: Jones still is in the DB
c.close()
conn.close()
>test
p1 Smith
p2 Dermiez
p1 Smith
p2 Dermiez
p3 Jones
1
2
p1 Smith
p2 Dermiez
>-----Original Message-----
>From: [email protected] [mailto:sqlite-users-
>[email protected]] On Behalf Of Jean-Luc Hainaut
>Sent: Saturday, 5 July, 2014 13:56
>To: [email protected]
>Subject: [sqlite] Autocommit in "with" query: bug or feature?
>
>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.
>
>Best regards
>
>Jean-Luc Hainaut
>
>
># -*- 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()
>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()
>
># 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()
>displayDBcontents()
>
># Surprise: Jones still is in the DB
>
>c.close()
>conn.close()
>
>Prof. Jean-Luc Hainaut
>Faculté d'Informatique
>University of Namur
>Rue Grandgagnage, 21
>B-5000 - Namur (Belgium)
>Phone (direct) : +32 (81) 72 49 96
>Phone (secret.): +32 (81) 72 49 64
>Fax : +32 (81) 72 49 67
>E-mail : [email protected]
>http://www.info.fundp.ac.be/libd
>
>_______________________________________________
>sqlite-users mailing list
>[email protected]
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
