Pandas DataFrame.to_sql() doesn't work anymore with an sqlalchemy 2.0.1 engine.connect() as a c-CodePudding

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)?

CodePudding user response：

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,
    )