32

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!

Scott
  • 887
  • 2
  • 10
  • 10

2 Answers2

34

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

sayap
  • 6,169
  • 2
  • 36
  • 40
  • +1 Great explanation. I tried so many different things until I stumbled upon this. My problem was I could not change the code by adding the driver name in the connection string as it is working fine on a windows server without that extra parameter. So I went ahead and created another entry in my `/etc/odbcinst.ini` called `[SQL Server]` and copied everything that was under `[SQL Server Native Client 11.0]` to my entry and voila! everything works... – Karthic Raghupathi Dec 04 '13 at 05:37
  • 4
    SQLAlchemy could not find the default odbc driver even on Windows. Adding "?driver=SQL+Server" solved my issue. Note that the name right of the equal sign must match your driver name found under Administrative Tools/Data Sources/Drivers – Matej Sep 16 '15 at 18:51
  • The latest MS driver (v13) can be found here: https://msdn.microsoft.com/en-us/library/hh568451%28v=sql.110%29.aspx Install the Driver Manager, make sure you have all the dependencies, and then install the driver. – iled Jan 15 '16 at 19:07
  • 1
    @Matej your little comment to use "?driver=SQL+Server" worked for me after 3 hrs searching for the correct string! Why isn't this documented? – whytheq May 25 '16 at 07:37
1

The error you are receiving may indicate there is no DSN setup with the name IM002. Have you tried testing the ODBC connection directly to validate that it is setup properly? Do you have the appropriate Microsoft SQL Server database drivers installed?

Registered User
  • 8,357
  • 8
  • 49
  • 65
  • 1
    How would I go about testing the ODBC connection directly? I've connected to it before with a python script under windows with just pyodbc and it's worked fine. I also believe the mssql db drivers are present on my linux system, but I may be mistaken. Is this pyodbc and unixodbc? – Scott Mar 25 '13 at 23:13