Pyodbc on M1 Macs

user_11319112 0
Viewed 8211

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.

apple-m1 pymssql pyodbc odbc sql-server
3 Answers

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:

user_115156100

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 the docs from pymssql to more details.

user_162749380

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

user_62287500
Related