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

9683 ワード
SQLServer MacOSX Python pyodbc Docker 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できるっぽい。そこで公式ページに飛ぶと、Microsoft SQLはMacOSでは使えない! でも大丈夫、代わりにDockerイメージを公式が配布していて、この中にSQL Serverを立てて使えということらしいです。

具体的な手順としては、以下に従っていけばOKです。
Quickstart: Run SQL Server container images with Docker
How to Restore a SQL Server Database on a Mac using SQL Operations Studio

Docker Desktopを入れる

公式からdmgファイルをダウンロードして実行します。Docker Hubへの会員登録が必要です。

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

コンテナを立てる

ここら辺は以下の記事を参考にしました。
Dockerを使用してMacからSQL Serverに接続する（方法編）
Linux Docker コンテナーでの SQL Server データベースを復元します。

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

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

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

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

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

$ 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であるとします。

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を再起動するなどしてコンテナが止まってしまった場合、

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

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

Azure Data Studioを入れる

公式からインストール。

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

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

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

Pythonでアクセスする

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

具体的な手順としては、Microsoft公式の以下がかなり分かりやすいです。
Create Python apps using SQL Server on macOS

# 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

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

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上で以下のコマンドの出力を確認してみてください。

!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からもテーブルにアクセスできるようになりました!