When I try to connect to a sql server database with pyodbc (on mac):
import pyodbc
server = '####'
database = '####'
username = '####@####'
password = '#####'
driver='{ODBC Driver 13 for SQL Server}'
pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1443;DATABASE='+database+';UID='+username+';PWD='+password)
I get the following error:
Error: (‘01000’, “[01000] [unixODBC][Driver Manager]Can’t open lib ‘ODBC Driver 13 for SQL Server’ : file not found (0) (SQLDriverConnect)”)
When I path in the actual driver location:
driver='/usr/local/lib/libmsodbcsql.13.dylib'
It starts working!
My odbcinst.ini looks like this:
[ODBC Driver 13 for SQL Server] Description=Microsoft ODBC Driver 13 for SQL Server Driver=/usr/local/lib/libmsodbcsql.13.dylib UsageCount=1
How can I get my reference to driver='{ODBC Driver 13 for SQL Server}' to start working again?
I initially used this guide to install the driver. And I’m using anaconda on Mac Sierra if that helps?
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
Running:
odbcinst -j
It yielded:
unixODBC 2.3.4 DRIVERS............: /etc/odbcinst.ini SYSTEM DATA SOURCES: /etc/odbc.ini FILE DATA SOURCES..: /etc/ODBCDataSources USER DATA SOURCES..: /Users/emehex/.odbc.ini SQLULEN Size.......: 8 SQLLEN Size........: 8 SQLSETPOSIROW Size.: 8
Instead of copying the files to the /etc/ directory (not sure why unixODBC thought they were there) I created a symbolic link to each file:
sudo ln -s /usr/local/etc/odbcinst.ini /etc/odbcinst.ini sudo ln -s /usr/local/etc/odbc.ini /etc/odbc.ini
This solved the problem.
Method 2
In my case, I have a Mac OS and the following commands fixed the problem:
brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release brew update brew install msodbcsql mssql-tools
Note 1:
It might be necessary that you need to install unixodbc in advance to msodbcsql and mssql-tools as the following:
brew install unixodbc
Note 2:
If you dont have already brew, the Missing Package Manager for macOS, then you can install it from here: https://brew.sh/
Note 3: You can verifiy your installation as @emehex already mentioned above with the following commands:
odbcinst -j sudo ln -s /usr/local/etc/odbcinst.ini /etc/odbcinst.ini sudo ln -s /usr/local/etc/odbc.ini /etc/odbc.ini
Method 3
In my case, I had to change the pyodbc database driver string to
driver='{ODBC Driver 17 for SQL Server}'
actually, in my python code pyodbc was expecting ODBC Driver 13 but, as the ODBC Driver version was updated (because of ubuntu update) to current version ODBC Driver 17, the problem had occurred.
Method 4
In my case, I fixed the problem with three steps as follow:
# Step1: install unixodbc brew install unixodbc # Step2: install Microsoft ODBC Driver for SQL Server on MacOS brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release brew update brew install msodbcsql mssql-tools # Step3：verify odbcinst configuration path is correct odbcinst -j sudo ln -s /usr/local/etc/odbcinst.ini /etc/odbcinst.ini sudo ln -s /usr/local/etc/odbc.ini /etc/odbc.ini
Method 5
I was building a custom image on top of Python and this is the configuration that made it work:
FROM python:3.8 as pyodbc COPY . /app WORKDIR /app # Required for msodbcsql17 and mssql-tools RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - RUN curl https://packages.microsoft.com/config/debian/10/prod.list > /etc/apt/sources.list.d/mssql-release.list RUN apt-get update # Key might change in the future, replace with new one on the logs RUN apt-key adv --keyserver keyserver.ubuntu.com --recv-keys BA6932366A755776 RUN apt-get update RUN apt-get install -y software-properties-common RUN add-apt-repository ppa:deadsnakes/ppa RUN apt-get update RUN ACCEPT_EULA=Y apt-get install -y msodbcsql17 mssql-tools RUN apt-get install unixodbc-dev RUN pip install pipenv RUN pipenv install --system --deploy CMD [ "python" , "__init__.py"]
Method 6
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 apt-get update ACCEPT_EULA=Y apt-get install msodbcsql17 apt-get -y install unixodbc-dev sudo apt-get install python3-pip -y pip3 install --upgrade pyodbc
Use above steps to istall the odbc driver correctly and everything will fall in place.
Method 7
I have to add that if you are using a different driver (FreeTDS) and in your connection string you omit to mention it, it will default to driver='{ODBC Driver 17 for SQL Server} or something like like that.
So the solution is not to forget driver, you DB settings will look like this:
'default': {
'ENGINE': 'sql_server.pyodbc',
'HOST': '127.0.0.1',
'NAME': 'mydb',
'PORT': '1433',
'USER': 'sa',
'PASSWORD': '*****',
'OPTIONS':{
'driver': 'FreeTDS',
'host_is_server': True,
}
}
Method 8
I’ve tried to establish a connection to a remote MS SQL Server deployed on a (Windows) machine from an external (Linux) machine. It took me some time to realise you need to first install the drivers on the machine that tries establish connection (i.e. Linux in my case)!
If you’re using macOS/Linux what you need to do is to simply Install the Microsoft ODBC Driver for SQL Server on Linux and macOS and then follow instructions on Connecting to databases for your particular OS.
Method 9
I was struggling with a similar issue.
Firstly, I was following this instruction:
I thought the error was in that I used driver 17 and not driver 13 – as per the connection string visible in the Azure Portal.
After playing around with it for a long time, getting this error for driver 13:
” pyodbc.Error: (‘01000’, “[01000] [unixODBC][Driver Manager]Can’t open lib ‘/usr/local/lib/libmsodbcsql.13.dylib’ : file not found (0) (SQLDriverConnect)”) ”
and getting a timeout error for driver 17.
I finally found the solution in this hack. The problem was OpenSSL where two versions confused up.
I’m running the app in a vertual environment in VS Code on my Mac and deploying it to Azure Web App.
Method 10
I simply built image top of python:3.7-alpine
FROM python:3.7-alpine COPY . /app/. WORKDIR /app # Install curl RUN apk add --no-cache curl RUN apk update && apk add curl # Install the Microsoft ODBC driver Linux.Follow the mssql documentation: https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15 RUN curl -O https://download.microsoft.com/download/e/4/e/e4e67866-dffd-428c-aac7-8d28ddafb39b/msodbcsql17_17.8.1.1-1_amd64.apk RUN curl -O https://download.microsoft.com/download/e/4/e/e4e67866-dffd-428c-aac7-8d28ddafb39b/mssql-tools_17.8.1.1-1_amd64.apk # Install the package(s) RUN apk add --allow-untrusted msodbcsql17_17.8.1.1-1_amd64.apk RUN apk add --allow-untrusted mssql-tools_17.8.1.1-1_amd64.apk # Install other libs RUN pip install --upgrade pip RUN pip install -r requirements.txt RUN apk add python3 python3-dev g++ unixodbc-dev RUN python3 -m ensurepip RUN pip3 install --user pyodbc # Run script ENTRYPOINT [ "python", "-u", "run.py"]
When setting connection inside the code, you need to specify the driver version that you’ve installed. For instance in my case:msdobcversion=17 so driver should be DRIVER={ODBC Driver 17 for SQL Server}
# Make connection
server = os.environ["server"]
database = os.environ["database"]
username = os.environ["UID"]
password = os.environ["PWD"]
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
Method 11
Installation that worked on the Ubuntu 18.04. I’m not sure if two of the ./bash_profile and ./bashrc exports are needed but I didn’t have time to check.
sudo apt-get update ACCEPT_EULA=Y sudo apt-get -y install msodbcsql17 mssql-tools echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc sudo apt-get -y install unixodbc libc6 libstdc++6 libkrb5-3 libcurl3 openssl debconf unixodbc unixodbc-dev
Then as a driver in connection use ODBC Driver 17 for SQL Server which is matching the current Azure version.
Method 12
I know everyone had the same stupid error ones: but I remind, I spent 30 mins just reading this branch… error was odd space at the end of connection string
SQLALCHEMY_DATABASE_URI: str = "mssql+pyodbc://sa:<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="512525113d3e32303d393e2225">[email protected]</a>:1433/babylon_pacemaker?Trusted_connection = no&driver=ODBC+Driver+17+for+SQL+Server " - odd space :(
Funny that Alembic works well, but SQLAlchimy has an error.
Method 13
Thank you Uzzal your solution solved my problem:
Blockquote
In my case, I had to change the pyodbc database driver string to
driver='{ODBC Driver 17 for SQL Server}’
actually, in my python code pyodbc was expecting ODBC Driver 13 but, as the ODBC Driver version was updated (because of ubuntu update) to current version ODBC Driver 17, the problem had occurred.
https://stackoverflow.com/a/48849351/7694643
It’s work for me. I’ve added it as env. variable:
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0