LinuxよりSQL ServerにPythonでODBCアクセスする

#はじめに

機械学習以外でこんな用途あるのかなぁ。。。
ニッチだなぁ。。と思いつつLinuxよりMicrosoft SQL ServerにODBC経由で接続する方法を説明してみます。テストはPythonで実施してみます。

#Linux環境にDataDirect SQL Server ODBC ドライバをインストール

１、Webサイトから<a href="https://www.ashisuto.co.jp/datadirect/app_download/" target=#_blank>Linux用のSQL Server ODBCドライバ</a>の試用版をダウンロードします。

２、ダウンロード後、以下のLinuxコマンドを実行してパッケージを新しいフォルダに移動します。

```console
mkdir datadirect

mv PROGRESS_DATADIRECT_ODBC_SQLSERVER_LINUX_XX.tar.Z datadirect/

cd datadirect/
```

３、次のコマンドでパッケージを展開します。

```console
tar -xvf PROGRESS_DATADIRECT_ODBC_SQLSERVER_LINUX_64.tar.Z
```

４、ターミナルで以下のコマンドを実行し、KSHインタプリタをインストールします。

Debian の場合

```console
sudo apt-get install ksh
```

CentOS/RHELの場合

```console
sudo yum install ksh
```

５、ターミナルで以下のスクリプトを実行し、インストールを実行します。

```console
./unixmi.ksh
```

６、プロンプトが表示されたら、Enterまたは「Y」を入力し、OSを確認してください。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/556649/7f657b51-dccb-f149-3954-c50625e0f928.png)

７、使用許諾契約書に「yes」で同意します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/556649/b8980a3e-2d11-ac40-3caa-bc9cc82ff8e2.png)

８、プロンプトに名前や会社名などを入力します。シリアル番号とキーに EVAL と入力します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/556649/1420a690-f094-6563-459f-20ee6b7bdb12.png)

９、付属するすべてのドライバーをインストールするか、1 つのドライバーを選択してインストールするかを選択する画面が表示されますが、ここではオプション 2 を選択します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/556649/792e11b4-5722-3133-2ff2-e0ce1ec71833.png)

１０、インストールするドライバのリストが表示されます。オプション11の'SQL Server Wire Protocol'を選択します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/556649/db013298-3e0b-c69e-ec75-627f4d31c114.png)

１１、パスを確認します。確認できなかった場合はパスを入力してください。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/556649/49764b35-bbad-ab98-c754-36d11640f1fd.png)

１２、インストールが終了するまでお茶でも飲んで待ちます。別の Linux ドライバをインストールしたい場合は「Y」で色々試せますが、今回は「N」でインストーラーを終了させます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/556649/58a9dfdc-e5d4-f9e5-ebb1-eb123f003ddd.png)

#SQL Server ODBCドライバの設定

１、インストレーションパス
/Progress/DataDirect/Connect_64_for_ODBC_71
（バージョンにより末尾が異なります）
に移動し、ターミナルで以下のコマンドを実行。環境変数ODBCINI、ODBCINST、LD_LIBRARY_PATHを追加します。

```console
sudo chmod +x odbc.sh

./odbc.sh
```

２、以下のコマンドで変数ODBCINI、ODBCINST、LD_LIBRARY_PATHが正常にインストールされているかどうかを確認します。ファイル odbc.ini のパスが表示されているはずです。

```console
echo $ODBCINI

./odbc.sh
```

３、環境変数が正しく追加されていない場合は、これらの値を ~/.bashrc に追加してください。

```console
export LD_LIBRARY_PATH=/install_path/Progress/DataDirect/Connect64_for_ODBC_71/lib

export ODBCINI=/ install_path/Progress/DataDirect/Connect64_for_ODBC_71/odbc.ini

export ODBCINST=/ install_path/Progress/DataDirect/Connect64_for_ODBC_71/odbcinst.ini
```

４、以下のコマンドでターミナルの設定をリフレッシュします。

```console
source ~/.bashrc
```

５、パス /install_path/Progress/DataDirect/Connect64_for_ODBC_71/odbc.ini 
にある odbc.ini ファイルを開き、次のサンプル odbc.ini に示すように SQL Server 接続の詳細を設定します。

```console
[ODBC Data Sources]

SQL Server Legacy Wire Protocol=DataDirect 7.1 SQL Server Legacy Wire Protocol

```


```console
[ODBC]
IANAAppCodePage=4

InstallDir=/home/progress/Progress/DataDirect/Connect64_for_ODBC_71

Trace=0

TraceFile=odbctrace.out

TraceDll=/home/progress/Progress/DataDirect/Connect64_for_ODBC_71/lib/ddtrc27.so

```

```console
[SQL Server Wire Protocol]
Driver=/home/progress/Progress/DataDirect/Connect64_for_ODBC_71/lib/ddsqls27.so

Description=DataDirect 7.1 SQL Server Wire Protocol

AlternateServers=

AlwaysReportTriggerResults=0

AnsiNPW=1

ApplicationName=

ApplicationUsingThreads=1

AuthenticationMethod=1

BulkBinaryThreshold=32

BulkCharacterThreshold=-1

BulkLoadBatchSize=1024

BulkLoadFieldDelimiter=

BulkLoadOptions=2

BulkLoadRecordDelimiter=

ConnectionReset=0

ConnectionRetryCount=0

ConnectionRetryDelay=3

Database=<database_name>

EnableBulkLoad=0

EnableQuotedIdentifiers=0

EncryptionMethod=0

FailoverGranularity=0

FailoverMode=0

FailoverPreconnect=0

FetchTSWTZasTimestamp=0

FetchTWFSasTime=1

GSSClient=native

HostName=<server_name>

HostNameInCertificate=

InitializationString=

Language=

LoadBalanceTimeout=0

LoadBalancing=0

LoginTimeout=15

LogonID=<logon id> (optional)

MaxPoolSize=100

MinPoolSize=0

PacketSize=-1

Password=<password> (optional)

Pooling=0

PortNumber=1433

QueryTimeout=0

ReportCodePageConversionErrors=0

SnapshotSerializable=0

TrustStore=

TrustStorePassword=

ValidateServerCertificate=1

WorkStationID=

XMLDescribeType=-10

```


６、odbc.iniファイルを保存して終了します。

#SQL Server ODBC 接続のテスト

１、ODBCドライバをテストしてみます。今回、簡単なPythonスクリプトで実施してみます。以下のコマンドを実行してpyodbcパッケージをインストールします。

```console

pip install pyodbc
```

<sql.h> ライブラリが見つからないという例外が発生した場合は、unixODBC パッケージをインストールする必要があるかもしれません。以下のコマンドを実行することでインストールできます。

Debain:

```console
sudo apt-get install unixodbc unixodbc-dev
```

CentOS/Redhat:

```console
sudo apt-get install unixODBC unixODBC-devel
```

２、以下のスクリプトをpythonファイルに保存して実行。SQL Serverに正常に接続できるかどうかを確認します。

```console
import pyodbc

 

##connecting to your database through ODBC

cnxn = pyodbc.connect('DSN=SQL Server Wire Protocol;UID=sa;PWD=pass', autocommit=True)

cursor = cnxn.cursor()
```

３、SQL Serverへの接続に成功したら、上記のファイルに以下のスクリプトを追加して、データベース内のテーブルを出力できます。

```console
##Print TableNames

for row in cursor.tables():

                if row.table_type == "TABLE":

                                print (row.table_name)

                                print (row)
```

４、また、以下のスクリプトを追加してスクリプトを実行してみることもできます。クエリを実行しようとしているNorthwindデータベースが存在することに注意してください。

```console
##Executing a simple query and printing the records

cursor.execute("SELECT EmployeeID, LastName, FirstName, Address, City, Region from Northwind.dbo.Employees")

for row in cursor.fetchall():

    print (row)
```

この記事ではSQL Server 認証モードを使用していますが、DataDirect SQL Server ODBC および JDBC ドライバは、Windows 認証および Kerberos もサポートしていますので、そちらは別の機会に。