CODEWITHSUNDEEP
pythonsql-servergoogle-colaboratorypyodbc
Kamil Oster
Kamil Oster

Reputation: 51

pyodbc on google colab

I am trying to connect to my SQL server on google colab by using pyodbc. However, it tells me that I cannot find the driver.

Code to install packages (I replaced IP, port and password with x)

``` !sudo apt-get install unixodbc-dev
!pip install pyodbc
!pip install chart_studio ```

``` import pyodbc
conn = pyodbc.connect(DRIVER = '{ODBC Driver 17 for SQL Server}',
                      SERVER = 'xxx.xxx.x.xx, xxxx',
                      DATABASE = 'Database',
                      UID = 'sa',
                      PWD = 'xxxxxx')

cursor = conn.cursor()
```

I get this error:

```---------------------------------------------------------------------------
Error                                     Traceback (most recent call last)
<ipython-input-12-3e54dc10e278> in <module>()
      3                       DATABASE = 'Database_PIL',
      4                       UID = 'sa',
----> 5                       PWD = 'mbdxwko2')
      6 
      7 cursor = conn.cursor()

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

Do you have any suggestion how to make it work?

Thanks!

Upvotes: 3

Views: 9681

Answers (3)

Bharat Raj Saya
Bharat Raj Saya

Reputation: 39

This worked for me

# install msodbcsql17
%%sh
apt-get install -y unixodbc-dev
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
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get -q -y install msodbcsql17

Followed by

!pip install pyodbc

Upvotes: 2

Mauro Del Nook
Mauro Del Nook

Reputation: 114

Thanks both of you, this worked for me:

First chunk

%%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

Second chunk

!sudo apt-get install unixodbc-dev
!pip install pyodbc
!pip install chart_studio

Third chunk

import pyodbc

Upvotes: 1

Chris Arnold
Chris Arnold

Reputation: 51

I had the same problem, but finally figured out that installing the ODBC package direct from microsoft could solve it. Run the following code as a single batch in colab.

%%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

Upvotes: 5

Related Questions