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…
JavaScript
x
18
18
1
credentials = {
2
'username' : 'SPOTTER_xyz_ACC',
3
'password' : '123Goodbye2016!@#',
4
'host' : 'MARYLQLT01',
5
'database' : 'LRS_DUS',
6
'port' : '1560'}
7
8
connect_url = sqlalchemy.engine.url.URL(
9
'mssql+pyodbc',
10
username=credentials['username'],
11
password=credentials['password'],
12
host=credentials['host'],
13
port=credentials['port'],
14
query=dict(service_name=credentials['database']))
15
16
engine = create_engine(connect_url)
17
connection=engine.connect()
18
And this is the .pyodbc error that I am getting.
JavaScript
1
3
1
(pyodbc.Error) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source
2
name not found and no default driver specified (0) (SQLDriverConnect)')
3
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????
JavaScript
1
10
10
1
connection=pyodbc.connect(
2
'Driver={SQL Server};
3
Server=MARYLQLT01;
4
Database=LRS_DUS;
5
UID=SPOTTER_xyz_ACC;
6
PWD=123Goodbye2016!@#')
7
8
stmt='select * from PD_SC_All'
9
df=pd.read_sql(stmt,connection)
10
And then I can see the dataframe.
So, why am I getting the (pyodbc.Error)
when I try to connect with SQLAlchemy?
System Information
JavaScript
1
13
13
1
Windows 7
2
SQLAlchemy: 1.1.11
3
pyodbc: 4.0.16
4
5
Python 3.6.1 |Anaconda custom (64-bit)| (default, May 11 2017, 13:25:24) [MSC v.1900 64 bit (AMD64)]
6
Type "copyright", "credits" or "license" for more information.
7
8
IPython 5.3.0 -- An enhanced Interactive Python.
9
? -> Introduction and overview of IPython's features.
10
%quickref -> Quick reference.
11
help -> Python's own help system.
12
object? -> Details about 'object', use 'object??' for extra details.
13
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.
JavaScript121engine = create_engine("mssql+pyodbc://scott:tiger@myhost:port/databasename?driver=ODBC+Driver+17+for+SQL+Server")
2
For
JavaScript
1
7
1
credentials = {
2
'username': 'scott',
3
'password': 'tiger',
4
'host': 'myhost',
5
'database': 'databasename',
6
'port': '1560'}
7
your statement produces the connect_url
JavaScript
1
2
1
mssql+pyodbc://scott:tiger@myhost:1560?service_name=databasename
2
What you really need is
JavaScript
1
11
11
1
from sqlalchemy.engine import URL
2
3
connect_url = URL.create(
4
'mssql+pyodbc',
5
username=credentials['username'],
6
password=credentials['password'],
7
host=credentials['host'],
8
port=credentials['port'],
9
database=credentials['database'],
10
query=dict(driver='ODBC Driver 17 for SQL Server'))
11
which produces
JavaScript
1
2
1
mssql+pyodbc://scott:tiger@myhost:1560/databasename?driver=ODBC+Driver+17+for+SQL+Server
2