Michele Simionato wrote:
> 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,
executescript() is trying to be helpful...
"""
executescript(sql_script)
This is a nonstandard convenience method for executing multiple SQL
statements at once. It issues a COMMIT statement first, then executes the
SQL script it gets as a parameter.
"""
...and failing. When you use execute() things work as expected:
$ cat sqlite_trans_demo.py
import os
import shutil
import sqlite3
import sys
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 executescript(conn, script):
for sql in script.split(";"):
conn.execute(sql)
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:
executescript(conn, script1) # this should not be committing
if "--conflict" in sys.argv:
executescript(conn, script2) # this one has an error
except Exception as err:
print(err)
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_sqlite')
$ python3 sqlite_trans_demo.py
(1, 'First')
(2, 'Second')
$ python3 sqlite_trans_demo.py --conflict
UNIQUE constraint failed: test.id
$
--
https://mail.python.org/mailman/listinfo/python-list
