SQLAlchemy can't connect to an mssql database

Question

Here's my simple test script. Just trying to do a basic select statement. Found the basic bits on a tutorial.

from sqlalchemy import *  db = create_engine('mssql+pyodbc://user:pass@ip_address/database_name')      db.echo = True  metadata = MetaData(db)  users = Table('member', metadata, autoload=True)  def run(stmt):     rs = stmt.execute()     for row in rs:         print row  s = users.select(users.c.fname == 'Bill') run(s) 

After an hour of searching around and trying a few solutions, I'm no closer to solving it than when I started. Hopefully I've just made a simple error somewhere, but I'm unable to find it...

Here's the error I'm getting

sqlalchemy.exc.DBAPIError: (Error) ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)') None None 

Any help would be much appreciated!

Answer 1

If not specified in the URL, the default driver for the mssql+pyodbc dialect would be "SQL Server" [1]. That means you need to have a section that reads like this in /etc/unixODBC/odbcinst.ini:

[SQL Server] Driver=/path/to/library.so 

It works "automatically" on Windows, because if you open Administrator Tools -> Data Sources (ODBC), you would most likely find an entry named "SQL Server" under the Drivers tab.

On Linux, you can either use the FreeTDS driver, or the official driver from Microsoft (I recommend this).

After installing the driver, you should have something like this in /etc/unixODBC/odbcinst.ini:

[FreeTDS] Driver=/usr/lib/libtdsodbc.so Threading=1  [ODBC Driver 11 for SQL Server] Description=Microsoft ODBC Driver 11 for SQL Server Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0 Threading=1 UsageCount=1 

Then, you just have to add a driver query string parameter to the URL, with value that matches the section name.

Sample URL with FreeTDS:

mssql+pyodbc://user:pass@ip_address/database_name?driver=FreeTDS 

Sample URL with the official driver:

mssql+pyodbc://user:pass@ip_address/database_name?driver=ODBC+Driver+11+for+SQL+Server 

[1] https://bitbucket.org/sqlalchemy/sqlalchemy/src/aa3a8f016f3e4396d125b18b0510abdf72aa8af2/lib/sqlalchemy/dialects/mssql/pyodbc.py?at=default#cl-236