DB Driver for azure SQL Data ware house via python notebook in ML Studio

Question

I am trying to access my data warehouse azure, fetch some data in mlstudio-attached-notebook in python. Simple connection says driver not found.

[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 13 for SQL Server' : file not found (0) (SQLDriverConnect).

Now, I need to know, is it a firewall issue or the not right driver name issue.

I have tried multiple driver name/string. No effect.

Answer 1

By looking at issue with php, one of the guy suggested the driver name.

https://github.com/Microsoft/msphpsql/issues/526

Driver is "ODBC Driver 17 for SQL Server"

Answer 2

1. Install ODBC driver manager from here.
2. Download ODBC Driver for Linux from here.
3. Install the ODBC driver as explained here.

4. Install pyodbc and the required packages

   sudo apt-get -y install python-pip

   sudo pip install --upgrade pip

   sudo apt-get install unixodbc-dev

   sudo apt-get install python-dev

   sudo pip install pyodbc

Finally, run the following Python script to test.

import pyodbc
server = 'tcp:yourserver.database.windows.net'
database = 'mydb'
username = 'myuser'
password = 'mypass'
cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()
cursor.execute("SELECT @@version;")
row = cursor.fetchone()
while row:
    print row
    row = cursor.fetchone()