How to install PYODBC in Databricks

Question

I have to install pyodbc module in Databricks. I have tried using this command (pip install pyodbc) but it is failed due to below error.

Error message

Answer 1

I was having the same issue for installation. This is what I tried and it worked.

• Databricks does not have default ODBC Driver. Run following commands in a single cell to install MS SQL ODBC driver

%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

• Run this in notebook

dbutils.fs.put("/databricks/init/<YourClusterName>/pyodbc-install.sh","""
#!/bin/bash
sudo apt-get update
sudo apt-get -q -y install unixodbc unixodbc-dev
sudo apt-get -q -y install python3-dev
/databricks/python/bin/pip install pyodbc
""", True)

• Restart the cluster

• Import pyodbc in Code

Answer 2

I had some problems a while back with connecting using pyobdc, details of my fix are here: https://datathirst.net/blog/2018/10/12/executing-sql-server-stored-procedures-on-databricks-pyspark

I think the problem stems from PYTHONPATH on the databricks clusters being set to the Python 2 install.

I suspect the lines:

%sh    
apt-get -y install unixodbc-dev
/databricks/python/bin/pip install pyodbc

Will work for you.

Update: Even simpler (though you will still need unixodbc-dev from above):

%sh 
sudo apt-get install python3-pip -y
pip3 install --upgrade pyodbc