Reading a table in pandas using sqlalchemy from SQL Server
I've been doing this for many hours and can't figure out what's wrong with my approach. I am trying to read a table into pandas using sqlalchemy (from a SQL Server 2012 instance) and get the following error:
DBAPIError: (Error) ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found (0) (SQLDriverConnect)") None None
I am 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>
I think it 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 am getting the following error mentioned above.
Is there something wrong with the driver setting? I've struggled with driver setup for days and thought I was beating it.
Any help is greatly appreciated.
source to share
For the record, the answer to my question was figured out by Joris:
My connection string syntax was wrong and when I changed it from this
connectionString = 'mssql+pyodbc://User:Password@IPAdress/Database'
to that
connectionString = 'mssql+pyodbc://User:Password@IPAddress:Port/Database?driver=FreeTDS'
It worked!
Thanks again for your help!
source to share
Try to shape your connection as follows. 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 determine which version of TDS to use:
http://www.freetds.org/userguide/choosingtdsprotocol.htm
Hope this helps!
source to share