Create Table in transaction in SQLite in-memory database not being rolled back

Question

Does SQLite in-memory support transactions around table creations? I would expect the code below to begin the transaction, create the table, rollback the transaction, then have nothing left. Instead, the table appears to still be around.

from sqlalchemy import create_engine, inspect

engine = create_engine('sqlite://', echo=True)
conn = engine.connect()

try:
    with conn.begin():
        conn.execute('''CREATE TABLE test (
            "index" BIGINT,
            a BIGINT,
            b BIGINT
        )''')
        assert False  # rollback
except AssertionError:
    pass

tables = inspect(engine).get_table_names()
assert tables == [], tables

The assertion fails:

Traceback (most recent call last):
    File "C:\...\lib\site-packages\IPython\core\interactiveshell.py", line 3418, in run_code
        exec(code_obj, self.user_global_ns, self.user_ns)
    File "<ipython-input-2-9e170787a857>", line 21, in <module>
        assert tables == [], tables
AssertionError: ['test']

Here's the engine output:

2021-03-01 12:16:16,872 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2021-03-01 12:16:16,872 INFO sqlalchemy.engine.base.Engine ()
2021-03-01 12:16:16,872 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2021-03-01 12:16:16,873 INFO sqlalchemy.engine.base.Engine ()
2021-03-01 12:16:16,873 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2021-03-01 12:16:16,873 INFO sqlalchemy.engine.base.Engine CREATE TABLE test (
    "index" BIGINT,
    a BIGINT,
    b BIGINT
)
2021-03-01 12:16:16,873 INFO sqlalchemy.engine.base.Engine ()
2021-03-01 12:16:16,874 INFO sqlalchemy.engine.base.Engine ROLLBACK
2021-03-01 12:16:16,874 INFO sqlalchemy.engine.base.Engine SELECT name FROM sqlite_master WHERE type='table' ORDER BY name
2021-03-01 12:16:16,874 INFO sqlalchemy.engine.base.Engine ()

Edit: SQLAlchemy version is 1.3.23

Answer 1

The pysqlite DBAPI has several known issues with transaction processing, including transactional DDL. They are discussed in the SQLAlchemy documentation for the SQLite dialect:

Transactional DDL