Python SQLAlchemy: Data source name not found and no default driver specified

Question

Using Python: when connecting to SQL Server using pyodbc, everything works fine, but when I switch to sqlalchemy, the connection fails, giving me the error message:

('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

My code:

cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=servername;DATABASE=dbname;UID=username;PWD=password')
engine = sqlalchemy.create_engine("mssql+pyodbc://username:password@servername/dbname")

I can't find the error in my code, and don't understand why the first options works, but the second doesn't.

Help is highly appreciated!

Answer 1

Ran into this problem as well, appending a driver query string to the end of my connection path worked:

"mssql+pyodbc://" + uname + ":" + pword + "@" + server + "/" + dbname + "?driver=SQL+Server"

Update (July 2021) – As above, just modernized (Python 3.6+):

f"mssql+pyodbc://{uname}:{pword}@{server}:{port}/{dbname}?driver=ODBC+Driver+17+for+SQL+Server"

Note that driver= must be all lowercase.

Answer 2

It works using pymssql, instead of pyodbc.

Install pymssql using pip, then change your code to:

engine = sqlalchemy.create_engine("mssql+pymssql://username:password@servername/dbname")