SQL Server, Python, on Mac OS

Question

First of all, I know this question was asked/answered once here, but I followed the steps suggested here, and got stucked in the middle of the steps.

Before the question, I have a Python script which runs well on a CentOS server:

import pyodbc 

server = 'tcp:192.168.1.1' 
database = 'MYDB' 
username = 'username' 
password = 'password'

sqlQuery = "SELECT * FROM dbo.DB1;"

cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()
cursor.execute(sqlQuery)

The same script gives me error on macOS, so I followed the nstruction in the previous link step by step, I had FreeTDS installed, and tested with tsql -S 192.168.1.1 -U username -P password. Things were fine so far.

Then I installed unixODBC with brew install unixodbc, and did the configuration as suggested:

/usr/local/Cellar/unixodbc/2.3.4/etc/odbcinst.ini

[FreeTDS]
Description=FreeTDS Driver for Linux & MSSQL on Win32
Driver=/usr/local/lib/libtdsodbc.so
Setup=/usr/local/lib/libtdsodbc.so
UsageCount=1

and..

/usr/local/Cellar/unixodbc/2.3.4/etc/odbc.ini

[MYSERVER]
Description         = Test to SQLServer
Driver              = FreeTDS
Trace               = Yes
TraceFile           = /tmp/sql.log
Database            = 'MYDB'
Servername          = 'tcp:192.168.1.1'
UserName            = 'username'
Password            = 'password'
Port                = 1433
Protocol            = 8.0
ReadOnly            = No
RowVersioning       = No
ShowSystemTables    = No
ShowOidColumn       = No
FakeOidIndex        = No

Then I tested..

$ isql -v MYSERVER
[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[08S01][unixODBC][FreeTDS][SQL Server]Unable to connect: Adaptive Server is unavailable or does not exist
[01000][unixODBC][FreeTDS][SQL Server]Unknown host machine name.
[ISQL]ERROR: Could not SQLConnect

also..

$ isql -v tcp:192.168.1.1 username password
[IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified
[ISQL]ERROR: Could not SQLConnect

Back to my python script on macOS, I changed a little bit, and still could not connect to SQL server..

cnxn = pyodbc.connect('DRIVER=FreeTDS;SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()
cursor.execute(sqlQuery)

Any ideas?

I'm running into the same issue? Did you ever get this sorted? — Jachin, Jul 24 '17 at 04:48

score 0 · Answer 1 · answered May 08 '23 at 23:41

make sure you follow the next steps, should be work with Mac M2, Ventura +

brew install unixodbc
pip uninstall pyodbc
pip install --no-binary :all: pyodbc
install drivers for 18 (copy and paste bash code), https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/install-microsoft-odbc-driver-sql-server-macos?view=sql-server-ver15
run python code and get connected

  import pyodbc 
  server = 'xxxxx.database.windows.net' 
  database = 'xxx' 
  username = 'xxx@xx.x.com' 
  password = 'xxxxx' 
  cnxn = pyodbc.connect('DRIVER={ODBC Driver 18 for SQL Server};SERVER='+server+';DATABASE='+database+';ENCRYPT=yes;UID='+username+';PWD='+ password )
  cursor = cnxn.cursor()

if you have different settings, like Active Directory User, make sure you add at the end of the connection string

Authentication=ActiveDirectoryPassword;Encrypt=yes;

Check more settings here:

https://learn.microsoft.com/en-us/sql/connect/odbc/using-azure-active-directory?view=sql-server-ver16

SQL Server, Python, on Mac OS

1 Answers1