Building a connection URL for mssql+pyodbc with sqlalchemy.engine.url.URL

Question

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.

Your connection URL is missing the ODBC driver name. You need something like mssql+pyodbc://scott:tiger@myhost:port/databasename?driver=SQL+Server (ref: here). — Gord Thompson
– Gord Thompson, Commented Sep 6, 2017 at 23:42
This is still not working. the sqlalchemy.engine.url.URL() does not have a place to put driver = SQL+Server'. — Clay
– Clay, Commented Sep 7, 2017 at 14:58

Gord Thompson · Accepted Answer · 2022-08-03 12:28:19Z

39

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

edited Aug 3, 2022 at 12:28

answered Sep 7, 2017 at 16:31

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Lucas Andrade Over a year ago

Since version 1.4.17 sqlalchemy requires a sqlalchemy.engine.url.URL to create the engine, we can't use just a string anymore (which was my case) this answer save me after 2 days searching about that.

nlhnt Over a year ago

Well, this is a headache... Can't get it to work, I'll start considering a career change for a java developer soon if this goes on.

nlhnt Over a year ago

Finally, using sqlalchemy.engine.url.URL.create() did the job.

Collectives™ on Stack Overflow

Building a connection URL for mssql+pyodbc with sqlalchemy.engine.url.URL

The problem...

The code...

Additional Details

System Information

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

The problem...

The code...

Additional Details

System Information

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related