9

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.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Pawan nandakishore
  • 111
  • 1
  • 1
  • 4
  • 3
    As per [Install the Microsoft ODBC driver for SQL Server (macOS)](https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/install-microsoft-odbc-driver-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? – AlwaysLearning Mar 31 '21 at 22:33
  • @AlwaysLearning 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..." – gmarais Mar 08 '22 at 19:35
  • @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](https://techcommunity.microsoft.com/t5/sql-server-blog/odbc-driver-17-8-for-sql-server-released/ba-p/2596530). – AlwaysLearning Mar 08 '22 at 22:00

3 Answers3

8

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;""")
  1. 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://stackoverflow.com/a/66919852/11515610

Related links:

Богдан Михайлович
  • 419
  • 3
  • 8
  • There will be a performance hit if you use amd64 container. It's probably better to use FreeTDS. – Nick Jan 20 '22 at 14:58
7

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)

johannstark
  • 71
  • 1
  • 5
2

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

imjuanleonard
  • 130
  • 10