pyodbc - error while running application within a container

Question

I am having a python application that uses mssql - instead of using pymssql, i was trying pyodbc. There seems to be no problems while running the application in a Windows local machine. While deploying the application in a dev env packaged as a container, I see the following errors.

from pyodbc import Error
2017-04-14T13:58:28.858638588Z ImportError: Error loading shared library libodbc.so.2: No such file or directory (needed by /usr/local/lib/python3.5/site-packages/pyodbc.cpython-35m-x86_64-linux-gnu.so)

The docs require me to install the database drivers along with pyodbc.

initial check looks good to me - images shared object dependencies and sym links

Millpond

Your container does not have the required ODBC support files installed. For example, in a Ubuntu Linux environment you would need to sudo apt install unixodbc-dev to get the missing file(s). – Depository

@GordThompson - i have unixodbc-dev installed as well. the confusing part is i see this when I ssh into the container -- /usr/lib/x86_64-linux-gnu/libodbc.so.2 , which i assume is the correct path – Millpond

That looks right. When I do ldd pyodbc.cpython-35m-i386-linux-gnu.so, one of the lines is libodbc.so.2 => /usr/lib/i386-linux-gnu/libodbc.so.2 (0xb7670000), which is the equivalent location on a 32-bit install of Xubuntu. However, libodbc.so.2 is a symlink to the actual file libodbc.so.2.0.0 in the same directory. Have you verified that your libodbc.so.2 symlink is valid? – Depository

Yes. exact same thing. let me add those details in the question itself – Millpond

Answer 1

The ubuntu environment is not having the odbc library, so it need to be installed using

sudo apt install unixodbc-dev 

once installed update the ubuntu using

sudo apt-get update

It will resolve the issue.

For further reading go to this link

Parmesan

Thanks for sharing the solution. This helped me resolve the issue. – Thundercloud

Answer 2

This is an old question, so here's an updated (year 2023) answer.

The error

ImportError: Error loading shared library libodbc.so.2: No such file or directory

requires one to install the Microsoft ODBC driver for SQL Server. At first, I tried the solution provided above, namely

# This solution is incomplete!
sudo apt install unixodbc-dev 

however this solution is incomplete, as it will NOT install all the required drivers. For instance, the Python script

import pyodbc                                                                                           
                                                                                                        
print(pyodbc.drivers()) 

will still return the empty list [].

The solution instead is to follow the instructions given by Microsoft itself. For Ubuntu:

if ! [[ "18.04 20.04 22.04 23.04" == *"$(lsb_release -rs)"* ]];
then
    echo "Ubuntu $(lsb_release -rs) is not currently supported.";
    exit;
fi

curl https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc

curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list

sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql18
# optional: for bcp and sqlcmd
sudo ACCEPT_EULA=Y apt-get install -y mssql-tools18
echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc
source ~/.bashrc
# optional: for unixODBC development headers
sudo apt-get install -y unixodbc-dev

This should solve all the problems, and the aforementioned Python script should now output ['ODBC Driver 18 for SQL Server'].

WARNING: If you try the incomplete solution mentioned above, and then follow the instructions provided by Microsoft (like I did at first) you'll run into nasty problems related to cyclic and incomplete installations and related dependencies. If you accidentally executed the incomplete solution first, it is best to sudo apt purge unixodbc-dev before following the steps recommended by Microsoft.

Finally, note that Microsoft now fully supports ODBC, so the instructions above should be considered the way to install ODBC, and therefore the correct answer to the original question by OP.

Kairouan

Answer 3

With pyodbc, it has lot of issues as you need to download compatible drivers that can be very messy. Instead use pymssql, it doesn't need additional drivers.

For more details : pymssql versus pyodbc versus adodbapi versus...

For sample code : https://pythonhosted.org/pymssql/pymssql_examples.html

Nikianikita