pythonsql-serverdockersqlalchemyodbc

Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect) while connect with sqlalchemy

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?

Any help would be greatly appreciated. Thanks!

Solution

  • Solved by these steps:

    1- install ODBC driver on local machine using this script for ubuntu official doc:

    if ! [[ "18.04 20.04 22.04 22.10" == *"$(lsb_release -rs)"* ]];
then
    echo "Ubuntu $(lsb_release -rs) is not currently supported.";
    exit;
fi

sudo su
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -

curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list > /etc/apt/sources.list.d/mssql-release.list

exit
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql18
# optional: for bcp and sqlcmd
sudo ACCEPT_EULA=Y apt-get install -y mssql-tools18
echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc
source ~/.bashrc
# optional: for unixODBC development headers
sudo apt-get install -y unixodbc-dev

    Note: DNS filtering may cause issues during installation

    2- Edit script as bellow to if password contains char @

    from sqlalchemy import create_engine
from urllib.parse import quote_plus


server = "127.0.0.1:1433"
user = "sa"
password = "Pass@12345"
db_name = "test_database"
dsn = "ODBC Driver 18 for SQL Server"

engine = create_engine(f"mssql+pyodbc://{user}:%s@{server}/{db_name}?TrustServerCertificate=yes&driver={dsn}" % quote_plus(password))

connection = engine.connect()

print("connected")