'Pandas and SQLAlchemy: df.to_sql() with SQLAlchemy 2.0 fututre=True throws an error when using a connection from engine.begin()

I tried to upsert a table in MS SQL Server from a pandas DataFrame.

As I understand it, it's a two step process:

  • do a pandas df.to_sql() into a temp table
  • execute a magic sql to merger the temp table in the existing final table

this works, but only if i set future=False in the create_engine call.

   self.engine = create_engine(self.url, echo=True, future=False)
  with self.engine.begin() as conn:
            df.to_sql("#update_table", conn, if_exists="append", index=False)
            # merge...
            sql = f"""\
            -- magic sql
            """
            result = conn.execute(text(sql)).fetchone()

When using future=True i get this error:

Traceback (most recent call last):
  File "/usr/local/Caskroom/miniconda/base/envs/dc_uk/lib/python3.9/site-packages/pandas/io/sql.py", line 1340, in insert_records
    table.insert(chunksize=chunksize, method=method)
  File "/usr/local/Caskroom/miniconda/base/envs/dc_uk/lib/python3.9/site-packages/pandas/io/sql.py", line 959, in insert
    with self.pd_sql.run_transaction() as conn:
  File "/usr/local/Caskroom/miniconda/base/envs/dc_uk/lib/python3.9/contextlib.py", line 119, in __enter__
    return next(self.gen)
  File "/usr/local/Caskroom/miniconda/base/envs/dc_uk/lib/python3.9/site-packages/pandas/io/sql.py", line 1416, in run_transaction
    with self.connectable.begin() as tx:
  File "/usr/local/Caskroom/miniconda/base/envs/dc_uk/lib/python3.9/site-packages/sqlalchemy/future/engine.py", line 144, in begin
    return super(Connection, self).begin()
  File "/usr/local/Caskroom/miniconda/base/envs/dc_uk/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 766, in begin
    raise exc.InvalidRequestError(
sqlalchemy.exc.InvalidRequestError: This connection has already initialized a SQLAlchemy Transaction() object via begin() or autobegin; can't call begin() here unless rollback() or commit() is called first.

seems like something in pandas can't deal (yet) with the changes in SA2. :(

There are some questions about SQLAlchemy 2.0 from earlier this year but all closed.

My Question: Anything i did wrong? or is that still an open issue...?

PS: This is on a mac, python 3.9.7, pandas 1.3.4, and SQLAlchemy 1.4.27.

sql-serverpandassqlalchemy


Solution 1:[1]

Per the docs "future" causes a transaction to start automatically, so you don't call .begin().

eg

with engine.connect() as conn:
    conn.execute(...)
    conn.execute(...)
    conn.commit()

    conn.execute(...)
    conn.execute(...)
    conn.commit()

method sqlalchemy.future.Connection.begin()

Solution 2:[2]

you need to update the pandas version

pip install pandas==1.4.1

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 David Browne - Microsoft
Solution 2 Rostyslav

Related Questions