I am trying to connect to a MSSql server via SQLAlchemy. Here is my code with fake credentials (not my real credentials obviously).
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)')
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?
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.
As stated in the SQLAlchemy documentation, for mssql+pyodbc
connections
Hostname-based connections are not preferred, however are supported. The ODBC driver name must be explicitly specified:
engine = create_engine("mssql+pyodbc://scott:tiger@myhost:port/databasename?driver=SQL+Server+Native+Client+10.0")
For
credentials = {
'username': 'scott',
'password': 'tiger',
'host': 'myhost',
'database': 'databasename',
'port': '1560'}
your statement
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']))
produces the connect_url
mssql+pyodbc://scott:tiger@myhost:1560?service_name=databasename
What you really need is
connect_url = sqlalchemy.engine.url.URL(
'mssql+pyodbc',
username=credentials['username'],
password=credentials['password'],
host=credentials['host'],
port=credentials['port'],
database=credentials['database'],
query=dict(driver='SQL+Server'))
which produces
mssql+pyodbc://scott:tiger@myhost:1560/databasename?driver=SQL+Server
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With