SQLalchemy fails after version 1.4.15 to connect to SQL Server

user_10159466 0
Viewed 422

I have a python script which has worked fine, but if I upgrade SQLalchemy to a version newer than 1.4.15 (or that is the last working I have) then I get the error:

sqlalchemy.exc.InterfaceError: (pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

My connection in python looks like this:

import sqlalchemy
from sqlalchemy import create_engine


#Create connection to SQL
NAV = 'mssql://<server>/<database>?driver=SQL+Server+Native+Client+11.0?trusted_connection=yes'
engine = create_engine(NAV, fast_executemany=True)

If I downgrade SQLalchemy to 1.4.15 it works just fine again.

Any ideas as I would like not to be stuck on an old version forever :-)

sqlalchemy python
1 Answers

SQLAlchemy 1.4.16 fixed a long-standing but subtle bug in the parsing of connection URIs. Previous versions would accept

…/dbname?driver=SQL+Server+Native+Client+11.0?trusted_connection=yes

and essentially ignore anything after (and including) the second ?. Now SQLAlchemy requires that subsequent (2nd, 3rd, …) arguments be separated with &, not ?, so the "driver=" is being interpreted as

SQL+Server+Native+Client+11.0?trusted_connection=yes

instead of just

SQL+Server+Native+Client+11.0

and the former (with '+' unescaped to space) does not match any known ODBC driver name.

As noted in the comments to the question, the fix is to simply omit the ?trusted_connection=yes part since it is not needed anyway.

user_21443900
Related