Docker内にSQL Serverを立ててPythonでアクセスする

# はじめに
とあるご縁でちょっとしたデータ分析の仕事をやらせて頂いているのですが、今回SQLを触ることになり、先方から頂いたデータは.bakファイル(Mirosoft SQL独自のバックアップ形式)でした。これを復元してMacOSで環境構築するまでにかなり手こずり、情報も散乱・錯綜している気がしたので、ここに一連の流れをメモとしてまとめたいと思います。

#環境
PC: MacBook Air (Retina, 13-inch, 2018)
OS: macOS Mojave
メモリ: 16GB

# SQL Serverを立てる
まず.bakファイルをインポートしようと調べると、Azure Data Studio(旧 SQL Operations Studio )というGUIでrestoreできるっぽい。そこで[公式ページ](https://docs.microsoft.com/ja-jp/sql/?view=sql-server-2017)に飛ぶと、Microsoft SQLはMacOSでは使えない! でも大丈夫、代わりにDockerイメージを公式が配布していて、この中にSQL Serverを立てて使えということらしいです。

具体的な手順としては、以下に従っていけばOKです。
[Quickstart: Run SQL Server container images with Docker](
https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker?view=sql-server-2017&pivots=cs1-bash)
[How to Restore a SQL Server Database on a Mac using SQL Operations Studio](https://database.guide/how-to-restore-a-sql-server-database-on-a-mac-using-sql-operations-studio/)

###  Docker Desktopを入れる
[公式](https://hub.docker.com/editions/community/docker-ce-desktop-mac)からdmgファイルをダウンロードして実行します。Docker Hubへの会員登録が必要です。

インストールが完了したら、Docker Desktopを立ち上げて以下のように`Docker Desktop is running`と表示が出ていればOKです。(画像は公式より)

<img width="249" alt="スクリーンショット 2019-05-30 1.59.59.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/258910/304c7908-03e4-85fa-c95e-b42b6b9b9ce8.png">


### コンテナを立てる
ここら辺は以下の記事を参考にしました。
[Dockerを使用してMacからSQL Serverに接続する（方法編）](https://qiita.com/m_rn/items/5c1c5523146d964ff9e4#3-docker%E3%82%92%E4%BD%BF%E7%94%A8%E3%81%97%E3%81%A6-sql-server-%E3%82%B3%E3%83%B3%E3%83%86%E3%83%8A%E3%82%A4%E3%83%A1%E3%83%BC%E3%82%B8%E3%82%92pull%E3%81%97%E3%81%A6%E5%AE%9F%E8%A1%8C)
[Linux Docker コンテナーでの SQL Server データベースを復元します。](https://docs.microsoft.com/ja-jp/sql/linux/tutorial-restore-backup-in-sql-server-container?view=sql-server-2017)

SQL Serverコンテナイメージをpull

```bash
sudo docker pull mcr.microsoft.com/mssql/server:2017-latest
```

以下のコマンドで、コンテナイメージにパスワードを設定して実行することができます。`username`はデフォルトで`sa`になります。

```bash
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=<YourStrong!Passw0rd>' \
   -p 1433:1433 \
   -d mcr.microsoft.com/mssql/server:2017-latest
```

`docker ps`で以下のような出力が得られたら大丈夫です。

```console
$ docker ps
CONTAINER ID        IMAGE                          COMMAND                  CREATED             STATUS              PORTS                    NAMES
bbcf81bcad49        microsoft/mssql-server-linux   "/opt/mssql/bin/sqls…"   9 days ago          Up 15 hours         0.0.0.0:1433->1433/tcp   sql1
```

次はローカルにある.bakファイルをイメージ内にコピーしましょう。
ここではコンテナ名が`sql1`であるとします。

```bash
sudo docker exec -it sql1 mkdir /var/opt/mssql/backup
sudo docker cp PATH_TO_BAK_FILE sql1:/var/opt/mssql/backup
```

これでコンテナが起動している状態でSQL Serverにアクセスすることができます。

もしPCを再起動するなどしてコンテナが止まってしまった場合、

```bash
docker ps -a #container idを確認
docker restart <container id>
```

でコンテナをリスタートすることができます。

## Azure Data Studioを入れる
[公式](https://docs.microsoft.com/en-us/sql/azure-data-studio/download?view=sql-server-2017#get-azure-data-studio-for-macos)からインストール。

インストールが終了したら、必要な情報を入力して、コンテナに接続します。

<img width="1380" alt="スクリーンショット 2019-05-31 14.41.55.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/258910/f047230c-d78b-b15c-569d-d39bb3452cee.png">

restoreから進んで、コンテナ内の.bakファイルへのパスを指定します。

<img width="1380" alt="tmp.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/258910/9c4dd9ec-d8d1-6b75-e3bd-e4f50879092f.png">

<img width="1380" alt="スクリーンショット 2019-05-31 17.01.29.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/258910/814974f8-d408-a88b-0efd-1ba51cd2ebd0.png">


上手くいけば、左の`SERVERS`のカラムにrestoreしたDBが追加されます! Visual Studioライクでなかなか良さげ。

# Pythonでアクセスする
次はPythonでテーブルにアクセスしたいので、pyodbcを導入します。他のライブラリとしてpymysqlも試したのですが、自分の場合はうまく行きませんでした...

具体的な手順としては、Microsoft公式の以下がかなり分かりやすいです。
[Create Python apps using SQL Server on macOS](https://www.microsoft.com/en-us/sql-server/developer-get-started/python/mac/)

```bash
# Homebrewのインストール
/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
# Pythonが入っていなければ入れる
brew install python

# ODBCドライバ・SQLサーバー用コマンドラインツールのインストール
brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
brew install msodbcsql17 mssql-tools

# pyodbcのインストール
pip3 install pyodbc
```

接続を確認するプログラムとして、簡単なプログラムを書いて実行します。

```python:Connect.py
import pyodbc

server = '127.0.0.1'
database = 'DB_NAME' #データベース名
username = 'sa'
password = 'PASSWORD' #サーバーに設定したパスワード

cnxn = pyodbc.connect(
'DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password
)
cursor = cnxn.cursor()

rows = cursor.execute('select * from sys.objects;').fetchall()
cnxn.close()
print(rows)

```

ここの`server`の値についてですが、`server='localhost'`としていると名前解決がうまくいかない場合があるみたいなので、ホスト名でなくIPアドレスで指定しておくと良いみたいです(自分も動かなくなりました)。

# Jupyter Notebookで触る
やっぱりJupyter Notebook上でPandasとか使いたいですよね...?　でも、notebook上で読み込もうとするとエラーが出る場合があります。その場合は、ターミナルとJupyter notebook上で以下のコマンドの出力を確認してみてください。


```shell
!odbcinst -j

unixODBC 2.3.7
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /Users/ricky/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
```

パスが違っていたらとりあえずそれがエラーの原因なので、愚直にコピーします。root権限が必要なのでJupyter上ではなくターミナルから`sudo`で実行しましょう。シンボリックリンクを貼っても良いと思います。


これで、jupyter notebookからもテーブルにアクセスできるようになりました!