I know that CREATE queries are non-transactional in sqlite, as documented, but
I finding something really strange in INSERT queries.
Consider this example:
$ cat example.py
import os
import shutil
import sqlite3
script0 = '''\
CREATE TABLE test (
id SERIAL PRIMARY KEY,
description TEXT NOT NULL);
'''
script1 = '''\
INSERT INTO test (id, description)
VALUES (1, 'First');
INSERT INTO test (id, description)
VALUES (2, 'Second');
'''
script2 = '''\
INSERT INTO test (id, description)
VALUES (1, 'Conflicting with the First');
'''
def main(test_dir):
if os.path.exists(test_dir):
shutil.rmtree(test_dir)
os.mkdir(test_dir)
path = os.path.join(test_dir, 'db.sqlite')
conn = sqlite3.connect(path)
conn.executescript(script0) # this is committing implicitly
try:
conn.executescript(script1) # this should not be committing
conn.executescript(script2) # this one has an error
except:
conn.rollback()
curs = conn.execute('select * from test')
for row in curs: # no rows should have been inserted
print(row)
if __name__ == '__main__':
main('/tmp/test')
I am creating the test table in script0, populating it in script1, then trying
to insert another row with a primary key violation. I would have expected the
rollback to remove the rows inserted in script1, since they are part of the
same transaction. Instead they are not removed!
Can somebody share some light on this? I discover the issue while porting some
code from PostgreSQL to sqlite3, with Postgres doing the right thing and sqlite
failing.
I am puzzled,
Michele Simionato
--
https://mail.python.org/mailman/listinfo/python-list
