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
