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

#はじめに

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

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

１、WebサイトからLinux用のSQL Server ODBCドライバの試用版をダウンロードします。

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

mkdir datadirect

mv PROGRESS_DATADIRECT_ODBC_SQLSERVER_LINUX_XX.tar.Z datadirect/

cd datadirect/

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

tar -xvf PROGRESS_DATADIRECT_ODBC_SQLSERVER_LINUX_64.tar.Z

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

Debian の場合

sudo apt-get install ksh

CentOS/RHELの場合

sudo yum install ksh

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

./unixmi.ksh

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

７、使用許諾契約書に「yes」で同意します。

８、プロンプトに名前や会社名などを入力します。シリアル番号とキーに EVAL と入力します。

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

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

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

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

#SQL Server ODBCドライバの設定

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

sudo chmod +x odbc.sh

./odbc.sh

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

echo $ODBCINI

./odbc.sh

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

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

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

source ~/.bashrc

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

[ODBC Data Sources]

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

[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

[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パッケージをインストールします。

pip install pyodbc

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

Debain:

sudo apt-get install unixodbc unixodbc-dev

CentOS/Redhat:

sudo apt-get install unixODBC unixODBC-devel

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

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への接続に成功したら、上記のファイルに以下のスクリプトを追加して、データベース内のテーブルを出力できます。

##Print TableNames

for row in cursor.tables():

                if row.table_type == "TABLE":

                                print (row.table_name)

                                print (row)

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

##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 もサポートしていますので、そちらは別の機会に。