M1 MacからpyodbcでSQL Serverへ接続してみた

手元のMacからSQL Serverに繋ぐ必要があり、 pyodbcを使えばできるということはわかっていたのでサクッとできるだろうとたかを括っていたのですが、 思ったよりも難しかったので、やり方を記録しておきます。

検証環境

ComputerとOSの情報

MacBook Air M1, 2020
macOS Ventura 13.4.1

Pythonの情報

asdfによる仮想環境内で動くPythonを使用しました。 とはいえ今回の話では仮想環境であることは特に何も影響はないかと思います。

$ python -V
Python 3.11.3
$ which python
/Users/hirano.shigetoshi/.asdf/shims/python

実行クエリ

SQL Serverに接続してクエリを投げるPythonスクリプトはこちらです。

connect_to_sqlserver.py

import pyodbc

sqlsv_server = "xxx.database.windows.net"
sqlsv_database = "sample_db"
sqlsv_username = "sample_user"
sqlsv_password = "PASSWORD"
sql = """select count(*) from sample_table;"""

sqlsv_cnxn = pyodbc.connect(
    "DRIVER={ODBC Driver 17 for SQL Server};SERVER="
    + sqlsv_server
    + ";DATABASE="
    + sqlsv_database
    + ";UID="
    + sqlsv_username
    + ";PWD="
    + sqlsv_password
)
sqlsv_cursor = sqlsv_cnxn.cursor()
sqlsv_cursor.execute(sql)
rows = sqlsv_cursor.fetchall()
print(rows)

やってみた

まずはpyodbcをインストールします。

$ pip install pyodbc
Collecting pyodbc
  Using cached pyodbc-4.0.39-cp311-cp311-macosx_11_0_arm64.whl (72 kB)
Installing collected packages: pyodbc
Successfully installed pyodbc-4.0.39

pyodbc-4.0.39が入りました。 この状態でPythonスクリプトを実行してみます。

$ python connect_to_sqlserver.py
Traceback (most recent call last):
  File "/Users/hirano.shigetoshi/study/20230810-pyodbc-sqlserver-m1/connect_to_sqlserver.py", line 1, in <module>
    import pyodbc
ImportError: dlopen(/Users/hirano.shigetoshi/.asdf/installs/python/3.11.3/lib/python3.11/site-packages/pyodbc.cpython-311-darwin.so, 0x0002): symbol not found in flat namespace '_SQLAllocHandle'

しょっぱな、pyodbcをimporotしようとした所でエラーになってしまうようです。

エラーの文章で調べてみると、 どうやらインストールされたpyodbcがそもそもMacに適合していないもののようで、パッケージを入れ直す必要があるようです。 やり方としてはpip installする際に --no-binary :all:を指定して、バイナリを使わないインストールを行うと良いようです。

https://github.com/mkleehammer/pyodbc/issues/1124#issuecomment-1318793968

--no-binary :all:は、:がついていて見慣れない感じですが、 本当にそのまま :をつけてコマンドに渡してあげる必要があります。 意味としては、全てのパッケージに対してバイナリを使わない、という命令になるようです。

一度pyodbcをuninstallして、 バイナリを使わないように指定した上でインストールを行ってみます。

$ pip uninstall pyodbc
Found existing installation: pyodbc 4.0.39
Uninstalling pyodbc-4.0.39:
  Would remove:
    /Users/hirano.shigetoshi/.asdf/installs/python/3.11.3/lib/python3.11/site-packages/pyodbc-4.0.39.dist-info/*
    /Users/hirano.shigetoshi/.asdf/installs/python/3.11.3/lib/python3.11/site-packages/pyodbc.cpython-311-darwin.so
    /Users/hirano.shigetoshi/.asdf/installs/python/3.11.3/lib/python3.11/site-packages/pyodbc.pyi
Proceed (Y/n)? Y
  Successfully uninstalled pyodbc-4.0.39
Reshimming asdf python...

$ pip install --no-binary :all: pyodbc
DEPRECATION: --no-binary currently disables reading from the cache of locally built wheels. In the future --no-binary will not influence the wheel cache. pip 23.1 will enforce this behaviour change. A possible replacement is to use the --no-cache-dir option. You can use the flag --use-feature=no-binary-enable-wheel-cache to test the upcoming behaviour. Discussion can be found at https://github.com/pypa/pip/issues/11453
Collecting pyodbc
  Using cached pyodbc-4.0.39.tar.gz (282 kB)
  Installing build dependencies ... done
  Getting requirements to build wheel ... done
  Installing backend dependencies ... done
  Preparing metadata (pyproject.toml) ... done
Building wheels for collected packages: pyodbc
  Building wheel for pyodbc (pyproject.toml) ... done
  Created wheel for pyodbc: filename=pyodbc-4.0.39-cp311-cp311-macosx_12_0_arm64.whl size=74048 sha256=fde3929e6a9823d1ff112924e871cbaa0d18e6a6d1d5766b12e65c3806c66a2f
  Stored in directory: /Users/hirano.shigetoshi/Library/Caches/pip/wheels/aa/8d/1b/5717a0fa126a9dc68c860c6f0be44b2779ff9314925448fc80
Successfully built pyodbc
Installing collected packages: pyodbc
Successfully installed pyodbc-4.0.39

[notice] A new release of pip available: 22.3.1 -> 23.2.1
[notice] To update, run: pip3 install --upgrade pip
Reshimming asdf python...

うまくいきました。 インストールされたバージョンは以前と同じ4.0.39です。

さて、これでうまくいくだろうと実行してみると、また別にエラーになりました。

Traceback (most recent call last):
  File "connect_to_sqlserver.py", line 9, in main
    sqlsv_cnxn = pyodbc.connect(
pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]Client unable to establish connection (0) (SQLDriverConnect)')

何やら接続を確立できないようです。 エラーメッセージを見る限り情報がほとんどありません。

とりあえずエラーメッセージでそのまま検索して見ると、それっぽいことが書いてあるページを見つけました。

https://github.com/mkleehammer/pyodbc/issues/967

正直内容はほとんど理解できていないのですが、openssl@1.1が使われれば良いように読めます。

ちなみに現在のopensslはこのMacに初めからインストールされていたもののようです。

$ openssl version
LibreSSL 3.3.6
$ which -a openssl
/usr/bin/openssl

とりあえずダメ元でbrewでopenssl@1.1を入れてみます。

$ brew install openssl@1.1

インストールはかなり時間がかかりましたが、特に問題は起きずに完了しました。 homebrewによる既存ライブラリのバージョンチェックもあったのかもしれませんが、1時間くらいかかった印象です。

$ which -a openssl
/opt/homebrew/bin/openssl
/usr/bin/openssl
$ openssl version
OpenSSL 3.1.1 30 May 2023 (Library: OpenSSL 3.1.1 30 May 2023)

無事に別のバージョンのopensslが入りました。 PATHの優先度的にもbrewで入れた方が優先的に使われる状態になっていそうです。

この状態で再度Pythonスクリプトを実行してみます。

$ python connect_to_sqlserver.py
[(16828412,)]

うまくいきました！！ ちゃんとテーブルの件数が取れています。

補足

--no-binaryの指定について

pipはwheelがあればそれを利用とするようです。 wheelはbdistと呼ばれるもので、すでにどこかの環境でビルドされた結果物です。 一方ビルドされていないソースコードの状態で配布されるものをsdistというようです。

PurePythonなパッケージであれば、ビルドされた環境に多少差異があっても問題ないのですが、 pyodbcの場合はどうやらAppleSiliconとは適合しない環境でビルドされたbdistがpipで見つかってしまうようです。 --no-binary :all:を指定することでbdistが見つかってもそれを使わず、 自分の環境でビルドを行ってインストールができるようです。

ただしログの中にこのような記述もあるので、 このやり方は将来使えなくなってしまう可能性がありそうです。

DEPRECATION: --no-binary currently disables reading from the cache of locally built wheels. In the future --no-binary will not influence the wheel cache. pip 23.1 will enforce this behaviour change. A possible replacement is to use the --no-cache-dir option. You can use the flag --use-feature=no-binary-enable-wheel-cache to test the upcoming behaviour. Discussion can be found at https://github.com/pypa/pip/issues/11453

opensslについて

今回うまくいったのは、PATHの先頭に来るopensslコマンドが直接影響しているわけではなく、 どうやら、brew install opensslをしたことによってインストールされた内部的なライブラリによってこれがうまくいくようになったようです。

brewでインストールされたopensslの実体である /opt/homebrew/Cellar/openssl@3/3.1.1_1/bin/openssl というファイル自体をリネームして見つからないようにしてみたのですが、 SQL Serverへの接続はうまくいきました。

まとめ

pyodbcを使ってM1 MacからSQL Serverに接続してクエリを投げることができました！ 単純にパッケージをインストールしてプログラムを実行するだけだろうと思っていたのですが、 思ったよりも苦戦してしましました。

pip installをする際にbdistが見つかっても使わないようにするというオプションは、 今後もAppleSiliconのMacでPythonを使っていく上で重要な情報を得ることができたように思います。

またopensslの方については正直詳細はよくわかっていません。 AppleSiliconのマシンであることが理由なのかどうかも不明です。 こちらについてはまた何か分かったら追記したいと思います。

以上、誰かの参考になれば幸いです。