Why is the SQL server DB engine not connecting with SQLAlchemy engine?

Question

I am trying to connect to the SQL server database on python platform using SqlAlchemy. I am using windows authentication to connect to my the SQL server. On connecting the server the SqlAlchemy engine is throwing an error:

Below is the code I have implemented:

import os
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
import pyodbc


              Driver         Server Name    Instance        Database
DATABASE_URL='mssql+pyodbc://DESKTOP-N32LSOV\PRANAV/AdventureworksDW2016CTP3?trusted_connection=yes'
Engine = create_engine(DATABASE_URL)

cn = Engine.connect()

When the above code is run, this error is produced:

Error:sqlalchemy.exc.InterfaceError: (pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)') (Background on this error at: http://sqlalche.me/e/rvf5)

I tried using pymssql driver inplace of pyodbc driver but still the error persists. On contrary when i try to connect to the using the below syntax it connects. I guess i am missing some attribute in the mssql url.

pyodbc.connect(r'Driver={SQL Server};Server=DESKTOP-N32LSOV\PRANAV;Database=master;Trusted_Connection=yes;')

Any help will be appreciated.

Answer 1

You need to specify both that you want to use ODBC and what ODBC driver to use.

engine = sqlalchemy.create_engine('mssql+pyodbc://localhost/Sandbox?driver=SQL+Server+Native+Client+11.0')

If you add the driver= part to your database url, it should work.

Answer 2

If all else fails, I would try using the creator argument to create_engine (documentation):

def creator():
    return pyodbc.connect(r'Driver={SQL Server};Server=DESKTOP-N32LSOV\PRANAV;Database=master;Trusted_Connection=yes;')

Engine = create_engine('mssql://', creator=creator)

Using creator= bypasses all connection parameters specified in the URL, so you should only pass information to specify the DB dialect in the URL.

Looking at the mssql+pyodbc dialect/driver documentation, there is also the ?odbc_connect option:

import urllib.parse
CONNECTION_STRING = r'Driver={SQL Server};Server=DESKTOP-N32LSOV\PRANAV;Database=master;Trusted_Connection=yes;'
Engine = create_engine('mssql+pyodbc:///?odbc_connect=' + urllib.parse.quote_plus(CONNECTION_STRING))