Difficulty with getting rollback to work in python3 with sqlite, pandas, and sqlalchemy

I am using sqlite with sqlalchemy to write a pandas DataFrame to a database. I am having a hard time understanding how transactions and rollbacks work with this combination. I followed the instructions from sqlalchemy's website regarding workarounds for sqlite, but that doesn't seem to help. Consider the code below.

I first create an empty database.

from sqlalchemy import create_engine, event
import pandas as pd

url = r'sqlite:///'
engine = create_engine(url)

@event.listens_for(engine, "connect")
def do_connect(dbapi_connection, connection_record):
    # disable pysqlite's emitting of the BEGIN statement entirely.
    # also stops it from emitting COMMIT before any DDL.
    dbapi_connection.isolation_level = None

@event.listens_for(engine, "begin")
def do_begin(conn):
    # emit our own BEGIN
    conn.execute("BEGIN")

pd.read_sql("SELECT name FROM sqlite_master", engine)

The output, as expected is.

Out[0]: 
Empty DataFrame
Columns: [name]
Index: []

Now I write a DataFrame using to_sql method inside a transaction and purposefully raise an exception, and catch it. I expect the following code to rollback whatever to_sql did.

data = pd.DataFrame()

# Start a transaction so we can roll back in case of issues
connection = engine.connect()
trans = connection.begin_nested()

try:
    # Save data and script
    data.to_sql('data', connection.connection, if_exists='replace')

    raise Exception

    # Commit transaction
    trans.commit()

except Exception as err:
    trans.rollback()

connection.close()
pd.read_sql("SELECT name FROM sqlite_master", engine)

However, from the output we can see that the database was not rolled back to when we started the transaction. I was expecting to see the output of this step to be identical to the one above.

Out[1]: 
            name
0           data
1  ix_data_index

What am I missing, and is there a way to fix this?

Versions:

sys.version
Out[13]: '3.4.1 |Anaconda 2.3.0 (64-bit)| (default, Sep 24 2014, 18:32:42) [MSC v.1600 64 bit (AMD64)]'

pd.__version__
Out[14]: '0.16.2'

sqlalchemy.__version__
Out[15]: '1.0.8'