SQLAlchemy - SQLEndTran - PyODBC vs. Engine

This should be an easy one but driving me crazy.

I cannot share all the details but lets say I have the following:

import pandas as pd
import sqlalchemy
from sqlalchemy.engine import URL

driver='ODBC Driver 17 for SQL Server'
server='MyServer'
database='MyDB'
username = 'MyUser'
password = 'MyPWD'

# pyodbc connection string
connection_string = f'DRIVER={driver};SERVER={server};PORT=17001;DATABASE={database};UID={username};PWD={password}'

# create sqlalchemy engine connection URL
engine = sqlalchemy.create_engine(URL.create("mssql+pyodbc", query={"odbc_connect": connection_string}))

pd.read_sql(sql=Query, con=engine)

ProgrammingError: (pyodbc.ProgrammingError) ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]111214;An attempt to complete a transaction has failed. No corresponding transaction found. (111214) (SQLEndTran)')
(Background on this error at: https://sqlalche.me/e/14/f405)

The link reads (I bolded the interesting bit):

ProgrammingError Exception raised for programming errors, e.g. table not found or already exists, syntax error in the SQL statement, wrong number of parameters specified, etc.

This error is a DBAPI Error and originates from the database driver (DBAPI), not SQLAlchemy itself.

The ProgrammingError is sometimes raised by drivers in the context of the database connection being dropped, or not being able to connect to the database. For tips on how to deal with this, see the section Dealing with Disconnects.

I suppose the connection is to blame and not my Query (not shown) because if I do:

import pyodbc    
cnxn = pyodbc.connect(connection_string)
pd.read_sql(sql=Query, con=cnxn)

The query works fine, but I get the Pandas warning for not using SQLAlchemy:

C:\Users\AppData\Local\Temp\ipykernel_9716\2299942033.py:1: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.

Any idea what I am doing wrong when creating the engine?

Solution

Found it... the following worked:

    import pandas as pd
    import sqlalchemy
    from sqlalchemy.engine import URL
    
    driver='ODBC Driver 17 for SQL Server'
    server='MyServer'
    database='MyDB'
    username = 'MyUser'
    password = 'MyPWD'
    
    # create sqlalchemy engine connection URL
    connection_url = connection_url = URL.create("mssql+pyodbc", 
                                                  username=username, 
                                                  password=password, 
                                                  host=server, 
                                                  database=database, 
                     query={"driver": "ODBC Driver 17 for SQL Server", "autocommit": "True"})
    
    pd.read_sql(sql=Query, con=engine)