[Python] Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0)...

I have searched a lot for the solution but still struggling with this problem.

I'm trying to connect to a SQL Server instance running on 127.0.0.1:1433. However, I'm getting a sqlalchemy.exc.DBAPIError with the following error message:

sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)")


I think I need to install the ODBC driver, but I'm not sure if it needs to be installed on the SQL Server Docker image or on my local VM. If the answer is the Docker image, then I think my /etc/odbcinst.ini file is correctly configured as follows:

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.so.2.1
UsageCount=1


But if the ODBC driver needs to be installed on my local VM, then my /etc/odbcinst.ini file is empty.

Here's the Python code I used to connect to the SQL Server instance:

from sqlalchemy import create_engine

server = "127.0.0.1,1433"
user = "sa"
password = "Pass@12345"
db_name = "test_database"

engine = create_engine(f'mssql+pyodbc://{user}:{password}@{server}/{db_name}?driver=ODBC Driver 17 for SQL Server')

connection = engine.connect()

print("connected")


Another question is what should i do if there is @ in password?

• sqlserver: sqlserver:2022-latest docker image, runs on 127.0.0.1:1433
• os: Ubuntu 22.04
• python: 3.10.6
• sqlalchemy: 2.0.16

Any help would be greatly appreciated. Thanks!

Continue reading...