WSL2で起動したUbuntu22.04のPython環境でpyodbcモジュールを使用してDockerコンテナ内のSQLServerに接続する方法をまとめました。
SQLServerの環境構築に難航しましたが、何が必要かを理解すればDocker環境の方がテストや開発に利用する時に簡単にできそうです。
やりたいこと
- UbuntuにPython環境を作成
- Ubuntu内にSQLServer（Dockerコンテナ）を作成
- Ubuntu内にSQLServerに接続するための環境を作成
- Ubuntuからpyodbcモジュールを使用してSQLServer（Dockerコンテナ）に接続する。
動作環境
| OS | Ubuntu |
| バージョン | 22.04.3 |
| 動作環境 | WSL2 |
前提条件
- Linux 用 Windows サブシステム (WSL)でUbuntuをインストールしていること
- Dockerをインストールしていること
環境構築（Ubuntu+Python）
Python3のインストール確認
python3 -Vversionが表示されたらOK
Python 3.10.12
プログラム上からSQLServerに接続・操作をするために、pyodbcモジュールをインストール
pip install pyodbcインストール後にPython3を実行すると、以下のエラーが出る場合があります。
ImportError: libodbc.so.2: cannot open shared object file: No such file or directoryこれはpyodbcモジュールを読み込むための環境が整っていない場合に起こります。
次の環境構築（SQLServer＋Docker）でSQLServerの環境を構築するとpyodbcモジュールを読み込むことができるようになります。「unixodbc-dev」パッケージが足りていないためです。
環境構築（SQLServer＋Docker）
SQLServerをプログラム上で利用するための環境構築
Ubuntu:Linux 上に SQL Server をインストールする – SQL Server | Microsoft Learn
GPGキーをインポート
curl https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc必要なパッケージが入ったリポジトリを登録
curl https://packages.microsoft.com/config/ubuntu/22.04/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.listSQLServerの実行に必要なパッケージをインストール
- mssql-tools18 sqlcmdコマンドとODBCドライバを利用するために必要
- unixodbc-dev DBと接続するときに必要
sudo apt-get update
sudo apt-get install mssql-tools18 unixodbc-dev環境変数に追加
echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bash_profile
source ~/.bash_profile確認
vi ~/.bash_profileDockerでSQLServerを構築
Docker:SQL Server on Linux 用のコンテナーのインストール – SQL Server | Microsoft Learn
SQLServerの公式imageをダウンロード
sudo docker pull mcr.microsoft.com/mssql/server:2022-latestSQLServerコンテナを作成
※MSSQL_SA_PASSWORDは任意のパスワード（パスワードポリシーあり）
sudo docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=xxxxxxxxxxxxxx" \
-p 1433:1433 --name sql1 --hostname sql1 \
-d \
mcr.microsoft.com/mssql/server:2022-latestSQLServerコンテナに入る
sudo docker exec -it sql1 "bash"SQLServerに接続
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "xxxxxxxxxxxxxxxxxxx"とてもわかりにくいが以下のように1>と表示されていればログイン成功
1>DBとデータ作成
注意点はSQLServerはクエリの最後に必ずGOを入力する。GOがクエリ実行という意味。
DB作成
CREATE DATABASE Test;
GO作成したDBに切り替える
use Test;
GOテーブル作成
CREATE TABLE Inventory (id INT, name NVARCHAR(50), quantity INT);
GOデータ挿入
INSERT INTO Inventory VALUES (1, 'banana', 150); INSERT INTO Inventory VALUES (2, 'orange', 154);
GOプログラム
pyodbcのドキュメントを参考に実装
Home · mkleehammer/pyodbc Wiki · GitHub
DbTest.py
import pyodbc
def sql_connection():
connectionstring = "DRIVER={ODBC Driver 18 for SQL Server};SERVER=127.0.0.1,1433;DATABASE=Test;UID=sa;PWD=@xxxxxxxxxxxxxxx;Encrypt=no"
con = pyodbc.connect(connectionstring)
cursor = con.cursor()
cursor.execute("SELECT * FROM Inventory")
rows = cursor.fetchall()
for row in rows:
print(row)
con.close
sql_connection()実装内容の説明
connectionstringにSQLServer構築時の接続情報を設定する。
- DRIVER={ODBC Driver 18 for SQL Server}
- SERVER=127.0.0.1,1433;
- DATABASE=Test;
- UID=sa;
- PWD=@xxxxxxxxxxxxxxx;
- Encrypt=no
DRIVER={ODBC Driver 18 for SQL Server}
ODBC Driverの確認
cat /etc/odbcinst.ini実行結果
[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.3.so.2.1
UsageCount=1Encrypt=no
デフォルト「yes」で接続時にサーバ証明書の検証が必要となるため「no」にしておく。
実行
python3 DbTest.py実行結果
(1, 'banana', 150)
(2, 'orange', 154)エラーについて
ドライバーが読み込めない場合のエラー
pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)")適切なドライバーを設定する
/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.3.so.2.1
接続情報が異なる場合のエラー
pyodbc.OperationalError: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')接続情報を見直す。
サーバ、ユーザ、パスワードが間違っていないか？？
ローカルからsqlcmdコマンドでSQLサーバーに接続できるか？？
sqlcmd -S localhost -U sa -P "xxxxxxxxxxxxxxxxxxx" -C-Cオプションで証明書の検証を行わない。
encryptエラー
接続時の証明書検証を行わないように、Encrypt=noにする。
pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: error:0A000086:SSL routines::certificate verify failed:self-signed certificate (SQLDriverConnect)')
