sqlite3 is non-transactional??

[Peter Otten]

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
$