Connect to sql server from linux environment using putty private key, sqlalchemy and pyodbc

flvlnr44 于 5个月前发布在 SQL Server

关注(0)|答案(1)|浏览(47)

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

sql-server

来源：https://stackoverflow.com/questions/69599917/connect-to-sql-server-from-linux-environment-using-putty-private-key-sqlalchemy

1条答案

按热度按时间

hm2xizp91#

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

赞(0）回复(0）举报 5个月前

我来回答

Connect to sql server from linux environment using putty private key, sqlalchemy and pyodbc

1条答案

相关问题

热门标签

最新问答