20

Hi I am trying to read a dataframe from sql server from a linux(ubuntu server). The file(gscm.ppk) has the admin keys in it

I have used the following format to connect to sql server:

import urllib.parse
import pandas as pd
import sqlalchemy as sa

params = urllib.parse.quote_plus(
    "DRIVER={SQL Server Native Client 11.0};"
    "SERVER=dagger;"
    "DATABASE=test;"
    "UID=ubuntu;"
)
engine = sa.create_engine("mssql+pyodbc:///?odbc_connect={}".format(params))
df = pd.read_sql("SELECT * FROM ROBALS", engine)

I keep getting this error:

sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('01000', "[01000] [unixODBC] 
[Driver Manager]Can't open lib 'SQL Server' : file not found (0) 
(SQLDriverConnect)")
(Background on this error at: https://sqlalche.me/e/14/dbapi)

I have downloaded the driver that goes with Ubuntu 20.04

Improve this question

1 Answer 1

Reset to default
2

Your code looks mostly fine except that Driver parameter looks like it's being ignored/ isn't quite right. I couldn't tell if it had to do with white spacing in code, but if I run this code on my instance I'll get a similar error, but it will refer to the right instance.

I updated Driver to be the path, per this answer and I didn't have a lookup failure, so something like the below, if you installed the odbcsql18 drivers would probably work as a substitute.

"DRIVER=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.3.so.1.1;"

I've installed these newer drivers on a container using

curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/debian/11/prod.list > /etc/apt/sources.list.d/mssql-release.list
apt-get update && ACCEPT_EULA=Y apt install unixodbc jq msodbcsql18 mssql-tools18 unixodbc-dev -y
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.