Reading table into pandas using sqlalchemy from SQL Server

Question

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.

See here docs.sqlalchemy.org/en/rel_0_9/dialects/… 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
– joris, Commented Nov 5, 2014 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
– joris, Commented Nov 5, 2014 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
– RJH2, Commented Nov 5, 2014 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
– RJH2, Commented Nov 5, 2014 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
– joris, Commented Nov 5, 2014 at 23:54

RJH2 · Accepted Answer · 2014-11-06 00:23:50Z

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!

answered Nov 6, 2014 at 0:23

RJH2

4391 gold badge7 silver badges16 bronze badges

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

Comments

FlipperPA · Accepted Answer · 2014-11-05 22:06:42Z

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!

answered Nov 5, 2014 at 22:06

FlipperPA

14.4k5 gold badges46 silver badges76 bronze badges

1 Comment

joris Over a year ago

The thing is, the OP wants to use pandas read_sql, and for this an sqlalchemy engine is needed.

Collectives™ on Stack Overflow

Reading table into pandas using sqlalchemy from SQL Server

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related