Pyodbc on M1 Macs

Question

I am trying to connect to a Microsoft sql server database using pyodbc. I keep getting the error

Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)")

Checking pyodbc.drivers() gives no result

I installed the Microsoft ODBC driver according to the instructions provided here:

I ran odbcinst -j which yields

---

DRIVERS............: /etc/odbcinst.ini 
SYSTEM DATA SOURCES: /etc/odbc.ini 
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /Users/pawannandakishore/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

---

but when I got to /etc, I cannot find either odbcinst.ini or odbc.ini. They are seem to be in opt/homebrew/Cellar/

I would really appreciate some help on this.

Tomchay

As per Install the Microsoft ODBC driver for SQL Server (macOS): The Microsoft ODBC driver for SQL Server on macOS is only supported on the x64 architecture. The Apple M1 is not supported. Maybe try FreeTDS until they do? – Matthaeus

@Matthaeus this is factually wrong. The official pages says "... only supported on the x64 arch through version 17.7. Apple M1 (ARM64) support was added starting with version 17.8..." – Lyn

@gmarais, the quote was correct when it was written a year ago. Microsoft didn't even release ODBC Driver 17.8 for SQL Server until four months later in July 2021. – Matthaeus

Answer 1

UPD:

You actually can use pyodbc in the container but the container should be built and executed in the x86_64 arch container. In order to do this, you need to add the platform either to docker-compose.yml or provide an argument during container run (when using docker). You need to make sure that you are building a container using buildx!

Docker:

• docker buildx build --platform linux/amd64 -t myimage .
• docker run --platform linux/amd64 myimage bash

Docker-compose:

version: "3.9"

services:
  web:
    image: myimage:latest
    build:
      context: ./
    platform: linux/amd64

---

Original answer:

It's true that pyodbc does not support ARM architecture. I'm using pymssql on my M1 in order to connect to MSSQL server.

1. You need to install the system requirement freetds-dev. For alpine container, it will be apk add freetds-dev

2. In pip requirements add pymssql package.

3. Test connection with simple script:

import pymssql
conn = pymssql.connect(server='mssql', user='SA', password='Passw@rd', database='master')
cursor = conn.cursor()
cursor.execute("""SELECT 1;""")

4. SqlAlchemy connection should look like this

SQLALCHEMY_DATABASE_URI = (
    f"mssql+pymssql://{MSSQL_USER}:{MSSQL_PASSWORD}@{MSSQL_HOST}/{MSSQL_DB}?"
)

If you need to run MSSQL database on M1 - here my answer on this problem https://mcmap.net/q/448248/-docker-connect-sql-server-container-non-zero-code-1

Related links:

• Adaptive server connection failed (DB-Lib error message 20002, severity 9)

• https://docs.sqlalchemy.org/en/14/dialects/mssql.html#module-sqlalchemy.dialects.mssql.pymssql

• https://learn.microsoft.com/en-us/sql/connect/python/pymssql/step-1-configure-development-environment-for-pymssql-python-development?view=sql-server-ver15

• https://pymssql.readthedocs.io/en/stable/pymssql_examples.html

Dogwatch

There will be a performance hit if you use amd64 container. It's probably better to use FreeTDS. – Exponential

Answer 2

Indeed the ODBC Driver is not yet supported on M1 Macs.

But! you can use the pymssql library in order to connect and make queries. This works perfectly on my M1 MacBook Pro 13"

Install FreeTDS via Homebrew

brew install freetds

Install using pip

Then use pip (or pip3) to install pymssql:

pip3 install pymssql

Start coding!

Then, just import it to your code! Here is an example :)

import os
import pymssql

server = <your_server>
user = <username>
password = <password>

conn = pymssql.connect(server, user, password, "<your_database>")
c1 = conn.cursor()

c1.execute('SELECT * FROM <your_table>')
data = c1.fetchall()

print(data)

conn.close()

Here are the docs from pymssql to more details.

---

Update Mar 16 2023: Updated pymssql docs link

---

Update May 25 2023: From Microsoft official docs:

Apple ARM64 support was added starting with version 17.8. The architecture will be detected and the correct package will be automatically installed by the Homebrew formula.

Indeed ODBC Driver is now supported for ARM architecture laptops (M Chip series)

Stalwart

Thanks for this! Yep it worked with pymssql – Tomchay

Note that you can use FreeTDS with pyodbc too. But your example with pymssql is also good! – Exponential

Tried this and got the error "ERROR: Could not build wheels for pymssql, which is required to install pyproject.toml-based projects" – Finalism

"Indeed the ODBC Driver is not yet supported on M1 Macs" - no longer true? – Letter

Link is dead.... – Letter

Link fixed! sorry – Stalwart

Amazing! I went through a lot of MS/Azure docs and this was the only thing that worked though I used conda install pymssql – Honora

Answer 3

I tried on my M1 2020 Big Sur, it works

brew install unixodbc
export LDFLAGS="-L/opt/homebrew/Cellar/unixodbc/2.3.9/lib"
export CPPFLAGS="-I/opt/homebrew/Cellar/unixodbc/2.3.9/include"

If you are using pycharm, make sure the LDFLAGS and CPPFLAGS is set up correctly

Donndonna