Docker内からホストのSQL Serverへアクセスする

# はじめに
Windows 10上のDockerコンテナに収めたPythonプログラムからホスト側(Windows 10)に構築したSQL Serverへアクセスする方法について記載します。
そもそもDockerとの組み合わせでSQL Serverを使うケースはあまり多くないかもしれません。
実際にそのあたりを記載した内容はネット上にあまり見つけられません。
しかし、動作環境としてオンプレでWindowsが求められ、Databaseが必要となった場合には、SQL Serverが選択肢に上がりますし、他のプログラムとの兼ね合いで、例えば、Power BIを使いたいといった要望に応えようとすると、SQL Serverが選択されるケースがあるかと思います。
DockerイメージとしてのSQL Serverも存在しますが、運用上SQL ServerはホストOS上に構築したいという要望もあるかと思います。
そうなると、これから記述する内容は多少なりとも参考になるかと思います。
Dockerは「Docker Desktop for Windows」で、ホストOSにWindows 10 Pro 64bitを使用しました。
「Docker Desktop for Windows」のインストールや操作については別のサイトを参照してください。
「Docker Desktop for Windows」のバージョンは、現時点では2.3.0.2です。
SQL Serverは、SQL Server 2019 Express を用いました。

# SQL Serverの設定を行います

## SQL Serverをインストールします
ホストOSにSQL Serverをインストールします。
合わせてSQL Server Management Studioもインストールします。
テスト用にdatabaseを作成し、その中にtableを生成して、recordを2,3追加しておいてください。

例：
データベース名: Test
テーブル名: Table_1

|id(int,NULL以外) | name(varchar(50),NULL) |
|----------------:|:-----------------------|
| 1               |                  Suzuki|
| 2               |                  Tanaka|
| 3               |                  Yamada|

## SQL Serverに接続できるようにするための設定
SQL Serverに接続できるようにFirewallなどの設定を確認・修正してください。
私は下記を参照しました。
[Python3 SQL Server 2016 接続設定](https://ninigishi.hatenablog.com/entry/2019/09/11/163259)

# Docker側の設定
## Dockerfileの設定

Dockerfileの設定は、pyodbcのインストールだけでは駄目で、ODBC Driverのインストールが必要です。
ODBC Driverのインストールについては下記を参考にしました。
[Microsoft ODBC Driver for SQL Server をインストールする (Linux)](https://docs.microsoft.com/ja-jp/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15#microsoft-odbc-17)
※上記のDebian 10に関する記述を参考にしましたが、OS毎に異なります。
実際にはDockerfileに以下のような記述を追加します。


```Dockerfile:Dockerfile

From python:3.7.7-buster

USER root
# 省略
RUN pip install pipenv

COPY Pipfile ./
COPY Pipfile.lock ./

RUN pipenv install --system

# 省略

# 環境変数
ENV DEBIAN_FRONTEND noninteractive
ENV DEBCONF_NOWARNINGS yes

RUN apt-get update && \
    apt-get install -y apt-utils apt-transport-https

RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
# python:3.7.7-busterを使用したので、debian 10用の記述を引っ張ってくる
RUN curl https://packages.microsoft.com/config/debian/10/prod.list > /etc/apt/sources.list.d/mssql-release.list

RUN apt-get update

# pyodbc動かすのに必要なライブラリ
RUN ACCEPT_EULA=Y apt-get install -y msodbcsql17
RUN ACCEPT_EULA=Y apt-get install -y mssql-tools
RUN ACCEPT_EULA=Y apt-get install -y unixodbc-dev

RUN pip install --upgrade pip

RUN pip install pyodbc
```


## Python プログラムの記述

SQL ServerへアクセスするPythonプログラムは以下のような感じです。
ホストに対してはlocalhostとか127.0.0.1だと繋がらないので、注意してください。
ローカルループバックでないIPアドレスかhost.docker.internalを使用します。

```Python:test.py

import pyodbc

# サーバー
# localhostとか127.0.0.1だと繋がらない
server = 'host.docker.internal'
#データベース
database = 'Test'
# ユーザー
username = 'sa'
#パスワード
password = 'saのパスワード'

# 色々書いてみたが、この記述だと接続できる
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

# 以下あくまでも例
cursor = cnxn.cursor()

sql =  '''select * from Table_1'''
cursor.execute(sql)

rows = cursor.fetchall()

for r in rows:
    print(r[0])
    print(r[1])

cursor.close()
cnxn.close()
```
docker-compose.ymlなどは特別に記述することはありません。
`docker-compose build`
`docker-compose up`
を順次実行すると、下記のような結果が表示されると思います。

```shell:PowerShell
PS C:\> docker-compose build
Building test
Step 1/x : From python:3.7.7-buster
...
Successfully built xxxxxxxxxxxx
Successfully tagged sample_test:latest
PS C:\> docker-compose up
Creating network "sample_test_default" with the default driver
Creating sample_test_1 ... done
Attaching to sample_test_1
PS C:\> test_1  | 1
PS C:\> test_1  | Suzuki
PS C:\> test_1  | 2
PS C:\> test_1  | Tanaka
PS C:\> test_1  | 3
PS C:\> test_1  | Yamada
```

以上ですが、うまくいかない場合など下記も参照してください。

# 補足

どうも接続がうまくいかないという場合には、下記を参考にして確認してみてください。

## Dockerコンテナを起動したままにします

Dockerコンテナを起動したままにします。
例えば、下記のサイトを参照。
[Dockerのコンテナを起動したままにする](https://qiita.com/reflet/items/dd65f9ffef2a2fcfcf30)

コンテナを起動させて、下記のようなコマンドでコンテナ内に入ります。

```shell:PowerShell
PS C:\ docker exec -i -t  [コンテナ名] bash
```
## odbcinst.iniが存在するか

dockerの中に、odbcinst.iniが存在するか確認してください。

```shell:bash
root@xxx:~# cat /etc/odbcinst.ini
```

を実行すると、

```shell:bash
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.5.so.2.1
UsageCount=1

```
上記のような内容が表示されるはずです。
`No such file or directory`になったり、何も表示されない場合、
ODBC Driverがうまくインストールされていない可能性があります。

## sqlcmdで接続確認

bashからsqlcmdにアクセスできるようにします。

```shell:bash
root@xxx:~# echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
root@xxx:~# source ~/.bashrc
```

sqlcmdを実行します

```shell:bash
root@xxx:~# sqlcmd -S host.docker.internal -U sa -P 'saのパスワード'
```
これで、以下のように表示されれば接続はできているはずです。

```sql:sqlcmd
1>
```

念のため、DBを操作できるか確認します。

```sql:sqlcmd
1> use [データベース名]
2> go
データベース コンテキストが '[データベース名]' に変更されました。
1> select * from Table_1
2> go
id          name
----------- --------------------------------------------------
          1 Suzuki
          2 Tanaka
          3 Yamada
```
## isqlで接続確認

isqlでも確認したい場合には、/etc/odbc.iniの中身を確認してください。
```shell:bash
root@xxx:~# cat /etc/odbc.ini
```
中身が空になっているかもしれません。
その場合には、下記に記載されているように編集する必要があります。
[SQL Server への接続](https://docs.microsoft.com/ja-jp/sql/connect/odbc/linux-mac/connection-string-keywords-and-data-source-names-dsns?view=sql-server-ver15)

```ini:odbc.ini
[MSSQLTest]  
Driver = ODBC Driver 17 for SQL Server  
# Server = [protocol:]server[,port]  
# Server = tcp:localhost,1433
```
しかしながら、使えるエディタがないので、vimなどをインストールする必要があります。

```shell:bash
root@xxx:~# apt-get install vim
```

# おわりに

ここまで、うまくいきましたでしょうか！
うまくいかない場合には、先に登場したいくつかの設定が正しいか確認してみてください。
以上になりますが、皆様の快い開発の一助になれば幸いです。