The problem…
I am trying to connect to a MSSql server via SQLAlchemy. Here is my code with fake credentials (not my real credentials obviously).
The code…
credentials = { 'username' : 'SPOTTER_xyz_ACC', 'password' : '123Goodbye2016!@#', 'host' : 'MARYLQLT01', 'database' : 'LRS_DUS', 'port' : '1560'} connect_url = sqlalchemy.engine.url.URL( 'mssql+pyodbc', username=credentials['username'], password=credentials['password'], host=credentials['host'], port=credentials['port'], query=dict(service_name=credentials['database'])) engine = create_engine(connect_url) connection=engine.connect()
And this is the .pyodbc error that I am getting.
(pyodbc.Error) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')
Additional Details
But, here is what is weird… I if make a pyodbc connection and use Pandas.read_sql, then I can get data without an error. So I do not understand why I am getting a ‘pyodbc’ error????
connection=pyodbc.connect( 'Driver={SQL Server}; Server=MARYLQLT01; Database=LRS_DUS; UID=SPOTTER_xyz_ACC; PWD=123Goodbye2016!@#') stmt='select * from PD_SC_All' df=pd.read_sql(stmt,connection)
And then I can see the dataframe.
So, why am I getting the (pyodbc.Error)
when I try to connect with SQLAlchemy?
System Information
Windows 7 SQLAlchemy: 1.1.11 pyodbc: 4.0.16 Python 3.6.1 |Anaconda custom (64-bit)| (default, May 11 2017, 13:25:24) [MSC v.1900 64 bit (AMD64)] Type "copyright", "credits" or "license" for more information. IPython 5.3.0 -- An enhanced Interactive Python. ? -> Introduction and overview of IPython's features. %quickref -> Quick reference. help -> Python's own help system. object? -> Details about 'object', use 'object??' for extra details.
Advertisement
Answer
As stated in the SQLAlchemy documentation, for mssql+pyodbc
connections
When using a hostname connection, the driver name must also be specified in the query parameters of the URL.
engine = create_engine("mssql+pyodbc://scott:tiger@myhost:port/databasename?driver=ODBC+Driver+17+for+SQL+Server")
For
credentials = { 'username': 'scott', 'password': 'tiger', 'host': 'myhost', 'database': 'databasename', 'port': '1560'}
your statement produces the connect_url
mssql+pyodbc://scott:tiger@myhost:1560?service_name=databasename
What you really need is
from sqlalchemy.engine import URL connect_url = URL.create( 'mssql+pyodbc', username=credentials['username'], password=credentials['password'], host=credentials['host'], port=credentials['port'], database=credentials['database'], query=dict(driver='ODBC Driver 17 for SQL Server'))
which produces
mssql+pyodbc://scott:tiger@myhost:1560/databasename?driver=ODBC+Driver+17+for+SQL+Server