Rollback a pandas to_sql() operation if any error occurs-CodePudding

I want to do a roll-back of a df.to_sql if any error occur, but I cannot find the base exceptions that to_sql-errors inherits from.

From

CodePudding user response：

You can use with engine.begin() (instead of with engine.connect()) to handle the transaction for you. The block will automatically rollback if an error occurs, otherwise it will commit.

For example, this code

import pandas as pd
import sqlalchemy as sa

connection_url = (
    "mssql pyodbc://scott:tiger^[email protected]/test"
    "?driver=ODBC Driver 17 for SQL Server"
)
engine = sa.create_engine(
    connection_url,
    echo=True,
)
cause_error = False

table_name = "thing"
with engine.begin() as conn:
    conn.exec_driver_sql(f"DROP TABLE IF EXISTS {table_name}")
    conn.exec_driver_sql(
        f"CREATE TABLE {table_name} (id int primary key, txt varchar(50))"
    )
    if cause_error:
        conn.exec_driver_sql(
            f"INSERT INTO {table_name} (id, txt) VALUES (1, 'xyz')"
        )

df = pd.DataFrame([(1, "foo"), (2, "bar")], columns=["id", "txt"])
try:
    with engine.begin() as conn:
        df.to_sql(table_name, conn, if_exists="append", index=False)
        print(">>> All good.")
except Exception as e:
    print(">>> Something went wrong!")

shows this in the log

2022-02-01 10:45:01,835 INFO sqlalchemy.engine.Engine INSERT INTO thing (id, txt) VALUES (?, ?)
2022-02-01 10:45:01,835 INFO sqlalchemy.engine.Engine [generated in 0.00053s] ((1, 'foo'), (2, 'bar'))
>>> All good.
2022-02-01 10:45:01,860 INFO sqlalchemy.engine.Engine COMMIT

However, if we use cause_error = True then the log contains

2022-02-01 10:53:29,385 INFO sqlalchemy.engine.Engine INSERT INTO thing (id, txt) VALUES (?, ?)
2022-02-01 10:53:29,385 INFO sqlalchemy.engine.Engine [generated in 0.00046s] ((1, 'foo'), (2, 'bar'))
2022-02-01 10:53:29,423 INFO sqlalchemy.engine.Engine ROLLBACK
>>> Something went wrong!