Pandas DataFrame.to_sql() doesn't work anymore with an sqlalchemy 2.0.1 engine.connect() as a context manager and doesn't throw any error

Question

This code with pandas 1.5.3 and sqlalchemy 2.0.1 is not working anymore and surprisingly, it doesn't raises any error, the code passes silently:

# python 3.10.6
import pandas as pd # 1.5.3
import psycopg2 # '2.9.5 (dt dec pq3 ext lo64)'
from sqlalchemy import create_engine # 2.0.1


def connector():
    return psycopg2.connect(**DB_PARAMS)

engine = create_engine('postgresql+psycopg2://', creator=connector)

with engine.connect() as connection:
    df.to_sql(
        name='my_table',
        con=connection,
        if_exists='replace',
        index=False,
    )

Currently, with sqlalchemy 2.0.1 my table is no more populated with the DataFrame content.

Whereas it was correctly populated with sqlalchemy version 1.4.45.

Edit

Apparently, it works when I don't use a context manager:

connection = engine.connect()

res.to_sql(
    name='my_table',
    con=connection,
    if_exists='replace',
    index=False
)
Out[2]: 133 # <- wondering what is this return code '133' here?

connection.commit()
connection.close()

How could I get it to work with a context manager (aka a with statement)?

Answer 1

The context manager that you are using rolls back on exit. Instead, use engine.begin(), which will commit.

with engine.begin() as connection:
    df.to_sql(
        name='my_table',
        con=connection,
        if_exists='replace',
        index=False,
    )