4

I've been at this for many hours, and cannot figure out what's wrong with my approach. I'm trying to read a table into pandas using sqlalchemy (from a SQL server 2012 instance) and getting the following error:

DBAPIError: (Error) ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found (0) (SQLDriverConnect)") None None

I'm using the following code: 

import sqlalchemy as sql

from sqlalchemy import create_engine

import pyodbc

pyodbc.connect('DSN=MYDSN;UID=User;PWD=Password')

Which returns: 

<pyodbc.Connection at 0x10a26a420>

Which I think is good. Then when I run the following: 

connectionString = 'mssql+pyodbc://User:Password@IPAdress/Database'
engine = sql.create_engine(connectionString)

pd.read_sql("ecodata", engine)

I get the following error mentioned above.

Is there something wrong with my driver setup? I've been wrestling with the driver setup for days and thought I had it beat.

Any help is greatly appreciated.

RJH2
  • 399
  • 1
  • 6
  • 16
  • See here http://docs.sqlalchemy.org/en/rel_0_9/dialects/mssql.html#module-sqlalchemy.dialects.mssql.pyodbc for the docs on connecting to sql server with sqlalchemy. Seems you should be able to do something like `create_engine("mssql+pyodbc://User:Password@MYDSN")`. – joris Nov 05 '14 at 22:58
  • But from the error message it seems there is something wrong with your driver. Do you know which driver you use? (on what platform are you?) – joris Nov 05 '14 at 23:05
  • I'm on OSX, believe I'm using unixODBC. Is there a way to check that, either from terminal or in python? – RJH2 Nov 05 '14 at 23:41
  • Ok, I just tried using the create_engine info you suggested, and I think I'm past that error! (and that's a big deal!) It looks like I'm not referencing the database and table correctly, now. Should that be after the MYDSN? Like this: `create_engine("mssql+pyodbc://User:Password@MYDSN/Database")` – RJH2 Nov 05 '14 at 23:54
  • What you need to know is what driver you have installed that unixODBC uses to connect with SQL Server (eg FreeTDS) – joris Nov 05 '14 at 23:54
  • It is definitely FreeTDS. Edit: Using version 8.0 – RJH2 Nov 05 '14 at 23:57
  • See the last example in the list here: http://docs.sqlalchemy.org/en/rel_0_9/dialects/mssql.html#additional-connection-examples to specify a specific driver in sqlalchemy. Something like `create_engine("mssql+pyodbc://User:Password@host/db?driver=FreeTDS")` – joris Nov 05 '14 at 23:59
  • See maybe also http://stackoverflow.com/questions/4493614/sqlalchemy-equivalent-of-pyodbc-connect-string-using-freetds – joris Nov 06 '14 at 00:04
  • Hey, that did it!!! Thanks so much for the help! For the record, here's the syntax that worked: `connectionString = 'mssql+pyodbc://[User]:[Password]@[IPAddress]:[Port]/[Database]?driver=FreeTDS'` – RJH2 Nov 06 '14 at 00:17

2 Answers2

4

For the record, the answer to my question was figured out by joris:

My syntax for the connection string was wrong, and when I changed it from this

connectionString = 'mssql+pyodbc://User:Password@IPAdress/Database'

to this

connectionString = 'mssql+pyodbc://User:Password@IPAddress:Port/Database?driver=FreeTDS'

It worked!

Thanks again for the help!

RJH2
  • 399
  • 1
  • 6
  • 16
0

Try forming your connection like this. You need a few more parameters.

con = pyodbc.connect('DRIVER={FreeTDS};SERVER="yourserver";PORT=1433;DATABASE=yourdb;UID=youruser;PWD=yourpassword;TDS_Version=7.2;')

To figure out which TDS Version to use:

http://www.freetds.org/userguide/choosingtdsprotocol.htm

Hopefully, this helps!

FlipperPA
  • 13,607
  • 4
  • 39
  • 71
  • The thing is, the OP wants to use pandas `read_sql`, and for this an sqlalchemy engine is needed. – joris Nov 05 '14 at 23:00