transactions of an sqlite3 connection used as a context manager are not atomic

Question

According to the documentation,

Connection objects can be used as context managers that automatically commit or rollback transactions. In the event of an exception, the transaction is rolled back; otherwise, the transaction is committed:

I understand that everything within a with statement should be an atomic transaction. Now consider this code

import sqlite3
con = sqlite3.connect(':memory:')

try:
  with con:
    con.execute('create table foo (id integer primary key)')
    con.execute('insert into foo values (1)')
    con.execute('insert into foo values (1)')
except sqlite3.Error:
  print('transaction failed')

try:
  rec = con.execute('select count(*) from foo')
  print('number of records: {}'.format(rec.fetchone()[0]))
except sqlite3.Error as e:
  print(e)

which returns

transaction failed
number of records: 0

On one hand, the transaction failed, due to the duplicated value. On the other hand, table foo exists, even though it is empty, which means that the first insert has been rolled back. Shouldn't the table creation be rolled back as well?

Doing the transaction "by hand" produces the expected result:

import sqlite3
con = sqlite3.connect(':memory:')

con.execute('begin')
try:
  con.execute('create table foo (id integer primary key)')
  con.execute('insert into foo values (1)')
  con.execute('insert into foo values (1)')
  con.execute('commit')
except sqlite3.Error:
  con.execute('rollback')
  print('transaction failed')

try:
  rec = con.execute('select count(*) from foo')
  print('number of records: {}'.format(rec.fetchone()[0]))
except sqlite3.Error as e:
  print(e)

returns

transaction failed
no such table: foo

Why the discrepency?

Answer 1

As of Python 3.6, DDL, or Data Defitinion Language statements, like CREATE TABLE, do not start a transaction. This means that any such statement is automatically committed the moment you execute one.

See the Controlling transactions section:

By default, the sqlite3 module opens transactions implicitly before a Data Modification Language (DML) statement (i.e. INSERT/UPDATE/DELETE/REPLACE).

[...]

Changed in version 3.6: sqlite3 used to implicitly commit an open transaction before DDL statements. This is no longer the case.

This means that you'll have to start a transaction explicitly if you want DDL statements to be part of a transaction.

Using the connection as a context manager still only issues a commit or rollback when exiting, it does not start a transaction; instead the first DML statement encountered will start one. If you want DDL to be part of a transaction, add a begin statement at the top:

try:
    with con:
        con.execute('begin')  # explicit, rather than implicit, transaction start
        con.execute('create table foo (id integer primary key)')
        con.execute('insert into foo values (1)')
        con.execute('insert into foo values (1)')
except sqlite3.Error:
    print('transaction failed')