cursor connection closed before reading result with SyncDriver.MSSQL_PYODBC about prefect-sqlalchemy HOT 5 CLOSED

@ahuang11 #24 did fix this issue. Thank you.

from prefect-sqlalchemy.

I have investigated further and it appears that SqlAlchemy closes the connection when used within a python context manager. It is the closing of the connection that is killing the CursorResult.

https://docs.sqlalchemy.org/en/14/core/connections.html
the Engine.connect() method returns a Connection object, and by using it in a Python context manager (e.g. the with: statement) the Connection.close() method is automatically invoked at the end of the block.

Removing use of context manager resolves the issue

        if sqlalchemy_credentials._async_supported:
            connection = engine.connect()
            result = await connection.execute(*execute_args)
            await connection.commit()
        else:
            connection =  engine.connect()
            result = connection.execute(*execute_args)

from prefect-sqlalchemy.

Thank you. I think #24 should fix this

from prefect-sqlalchemy.

Okay released a new version that should fix this; can you try updating and closing this issue if it's fixed? Thanks!

from prefect-sqlalchemy.

Will close for now; please feel free to re-open if the issue persists!

from prefect-sqlalchemy.