1
import pyodbc    

pyodbc.connect('Driver={SQL SERVER};'
                  'Server=server name;'
                  'Database = database name;'
                  'UID='my uid;'
                  'PWD= 'my password;'
                  'Authentication = ActiveDirectoryPassword')

running above code in databrick notebook i am getting following error 

Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'SQL SERVER' : file not found (0) (SQLDriverConnect)")
CHEEKATLAPRADEEP
  • 12,191
  • 1
  • 19
  • 42
qaiser
  • 2,770
  • 2
  • 17
  • 29

2 Answers2

2

By default, Azure Databricks does not have ODBC Driver installed.

For SQL Server: You can resolve the issue by using the following script

sudo apt-get -q -y install unixodbc unixodbc-dev
sudo apt-get -q -y install python3-dev
sudo pip install --upgrade pip
pip install pyodbc
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 msodbcsql

For Azure SQL Database: Run the following commands in a single cell to install MY SQL ODBC Driver on 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

enter image description here

CHEEKATLAPRADEEP
  • 12,191
  • 1
  • 19
  • 42
  • Are you connecting to Azure SQL DB? Could you please share the error message which you are experiencing? – CHEEKATLAPRADEEP May 26 '20 at 08:59
  • https://datathirst.net/blog/2018/10/12/executing-sql-server-stored-procedures-on-databricks-pyspark , i followed this link and changed Driver={SQL Driver} to Driver={ODBC Driver 17 for SQL Server} – qaiser May 26 '20 at 09:08
  • now i am getting this error OperationalError: ('HYT00', '[HYT00] [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)') – qaiser May 26 '20 at 09:08
  • Problem seems to be network related issue. I would request you to check the connect string passed and rerun. – CHEEKATLAPRADEEP May 26 '20 at 09:12
  • code was correct, functional account need to created for connecting with SQL server.. everything worked fine.. – qaiser Jun 02 '20 at 10:01
  • The SQL SERVER one throws a lot of error, and says it's missing dependencies that can't be installed. Is there a solution to this? – spareTimeCoder Jan 05 '23 at 12:35
1

What you posted looks like straight Python code. In the Databricks environment, things are a little different than they are on your local machine.

Try it like this.

import pyodbc
server = '<server>.database.windows.net'
database = '<database>'
username = '<username>'
password = '<password>'
driver= '{ODBC Driver 17 for SQL Server}'
cnxn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()
cursor.execute("SELECT TOP 20 pc.Name as CategoryName, p.name as ProductName FROM [SalesLT].[ProductCategory] pc JOIN [SalesLT].[Product] p ON pc.productcategoryid = p.productcategoryid")
row = cursor.fetchone()
while row:
    print (str(row[0]) + " " + str(row[1]))
    row = cursor.fetchone()
ASH
  • 20,759
  • 19
  • 87
  • 200