CODEWITHSUNDEEP
azure-sql-databasepyodbc
Nayden Van
Nayden Van

Reputation: 1569

Pyodbc Azure sql database

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

Upvotes: 1

Views: 449

Answers (2)

ScriptKiddieOnAComputer
ScriptKiddieOnAComputer

Reputation: 984

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

Upvotes: 0

Jason Pan
Jason Pan

Reputation: 21977

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

Upvotes: 1

Related Questions