1

I am using a macOs BigSur, and I would like to use python to connect to azure sql database.

I followed Microsoft documentation:

https://learn.microsoft.com/en-us/python/api/overview/azure/sql?view=azure-python

to set all the configuration and installed all the requirement for the Mac OS following this documentation.

https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/install-microsoft-odbc-driver-sql-server-macos?view=sql-server-ver15

but when I run my python script I get this error:

cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)")

I install mssql-tools and unixodbc

and if I run the command odbcinst -j

I get back this output:

unixODBC 2.3.9
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /Users/<my-user>/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Anyone has a clue about this error? and please if you need more infos just ask me. Thank you so much

EDIT: Following the documentation, Microsoft suggests to make a sim link out of the odic.ini and odbcinst.ini. I run the code to create those symlinks but I realised that if I try to open those files, they are empty, and if I try to go to the folder and open them manually rather than with terminal, I get the error that it can't be done because the source doesn't exist. Did anyone ever occurred in this error?

LATEST UPDATE: I installed FreeTDS and updated my .odbc.ini with the following configuration:

[my_server]
Description = my_server
TDS_Version = 7.4
Driver = /usr/local/lib/libtdsodbc.so
Server = YOUR.SERVERNAME.HERE.com
Port = 1433

When I run my python script to connect to the database, I get the following Traceback:

Traceback (most recent call last):
  File "database.py", line 11, in <module>
    cnxn = pyodbc.connect('dsn=my_server;'
pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/usr/local/lib/libtdsodbc.so' : file not found (0) (SQLDriverConnect)")

And in fact there is no libtdsodbc.so in that location. In another topic they suggest to install freeTDS with the flag --with-unixodbc but when I try to do so, I get the error that the command doesn't exist

Nayden Van
  • 1,133
  • 1
  • 23
  • 70

2 Answers2

1

UPDATE

If your macos is on the x64 architecture, you can use below method to solve issues.

If you use M1, it's not support now.

enter image description here

Solution for x64 architecture.

Run below code, it should useful to you.

brew update
brew install unixodbc
brew install FreeTDS

Then update the .odbc.ini file.

For more details, please refer below blogs.

How to set up ODBC in Mac OS to connect to MS SQL Server for use with Python and R

Jason Pan
  • 15,263
  • 1
  • 14
  • 29
  • Hello, thank you so much for your help. But unfortunately this doesn't solve my problem. I installed freetds but I get the error that `libtdsodbc.so` doesn't exist, and in fact it doesn't. and if I try to install freetds using `--with-uniodbc` I get the error that the command doesn't exist – Nayden Van Jun 23 '21 at 07:41
  • @NaydenVan Can you update your post with error pic what you meet ? – Jason Pan Jun 23 '21 at 07:44
  • @NaydenVan https://stackoverflow.com/questions/47180843/python-cant-open-lib-libtdsodbc-so-file-not-found – Jason Pan Jun 23 '21 at 07:58
  • @NaydenVan Try to use other version for macos. – Jason Pan Jun 23 '21 at 07:59
  • Having the same issue. :( – Nayden Van Jun 23 '21 at 08:45
  • 1
    I missed an important bit. On the Microsoft documentation, they state that the macOS M1 are not supported. I need to find another solution – Nayden Van Jun 23 '21 at 09:01
  • @NaydenVan Can I update my answer with M1 are not supported ? I think it will help more forum users. – Jason Pan Jun 23 '21 at 09:04
  • 1
    [M1 is now supported](https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/install-microsoft-odbc-driver-sql-server-macos?view=sql-server-ver16) (from v17.8 onwards). – ScriptKiddieOnAComputer Aug 10 '22 at 12:12
0

Starting ODBC version 17.8, Apple M1 is supported.

You can install ODBC 18 on macOS with the following commands:

/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)"

brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update

HOMEBREW_NO_ENV_FILTERING=1 ACCEPT_EULA=Y brew install msodbcsql18 mssql-tools18
ScriptKiddieOnAComputer
  • 925
  • 12
  • 19