Unable to connect to SQL Server ODBC using python

Question

I am trying to connect to SQL Server from python to read synapse views. I have server name, user name and password. I am using the below code.

import pyodbc 
server = '' 
database = '' 
username = '' 
password = '' 

cnxn = pyodbc.connect('DRIVER={ODBC Driver 18 for SQL Server};SERVER='+server+';DATABASE='+database+';ENCRYPT=yes;UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

Below is the error message I receive.

Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 18 for SQL Server' : file not found (0) (SQLDriverConnect)")

Should I use JDBC connection from Python?

Answer 1

You can try this alternative approach 17 version which is working for me. try to down grade version 18 to 17.

I tried to reproduce the same in my environment and got the below results with 17:

Run following command to install ODBC drivers on the Azure Databricks cluster

%sh
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get -q -y install msodbcsql17

Now, you can check Azure Databricks connected to SQL server

Code:

import pyodbc
server = '<server_name>'
database = '<Database_name>'
username = '<User_name>'
password = '<SQL_password>'
    
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=' + server + ';DATABASE='+ database +';UID=' + username + ';PWD='+ password)