PythonでODBC接続を行えるpyodbcをインストールする

2019.12.18

はじめに

データアナリティクス事業本部のkobayashiです。

前回AmazonLinux,macOS環境でSQL Serverからデータを取得するためにsqlcmd、bcpをインストールしコマンドラインからデータをエクスポートする方法を紹介しました。

その後工程の加工処理をPythonで行っていましたがSQL Serverからデータを取得する処理もPythonで行いすべての工程をPythonで実行することにしSQL Serverへの接続にpyodbcを利用しました。またpyodbcはSQL Server以外のデータソースへもODBCドライバを用意し設定することで接続できるので合わせてそちらも設定しましたのでまとめます。

前回の記事

SQL Serverからデータを取得するためにsqlcmdをインストールする

環境

  • macOS Mojave
  • AmazonLinux2
  • Python 3.6.5
  • pyodbc 4.0.27

pydobc

pyodbcはODBCを利用して様々なデータベースにPythonから接続して操作を行えます。

pyodbc is an open source Python module that makes accessing ODBC databases simple. It implements the DB API 2.0 specification but is packed with even more Pythonic convenience.

インストール方法

pipがインストールしてあればインストールは以下のコマンドだけでpyodbcをインストールできます。

$ pip install pyodbc

ドライバファイルの設定

macOSとAmazonLinuxでpyodbcが接続に使うドライバの設定ファイルの場所が違います。

macOS

pyodbcが利用するドライバの設定ファイルは以下の場所にあります。

$ cat  /usr/local/etc/odbcinst.ini 

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/usr/local/lib/libmsodbcsql.17.dylib
UsageCount=1

SQL Serverのコマンドラインツールをインストールすると自動的にこのファイルに設定が書き込まれているのでSQL Serverに接続する場合は特にこのファイルの記述を変更する必要はありません。

PostgreSQLの設定

PostgreSQLの設定を行います。 まずはドライバをインストールします。

$ brew install psqlodbc

インストールが完了したら設定ファイルに追記します。

$ cat  /usr/local/etc/odbcinst.ini 

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/usr/local/lib/libmsodbcsql.17.dylib
UsageCount=1

[PostgreSQL]
Driver=/usr/local/lib/psqlodbcw.so

Redshiftの設定

次にRedshiftの設定を行います。 ODBCドライバのインストールはAWSの公式ページに従って行えば特に問題なく行えます。

公式ページの手順に従ってインストールを終えると

/opt/amazon/redshift/lib/libamazonredshiftodbc.dylib

にドライバファイルができるので設定ファイルに追記します。

$ cat  /usr/local/etc/odbcinst.ini 

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/usr/local/lib/libmsodbcsql.17.dylib
UsageCount=1

[PostgreSQL]
Driver=/usr/local/lib/psqlodbcw.so

[Redshift]
Driver=/opt/amazon/redshift/lib/libamazonredshiftodbc.dylib

AmazonLinux

pyodbcが利用するドライバの設定ファイルは以下の場所にあります。

$ cat  /etc/odbcinst.ini

# Example driver definitions

# Driver from the postgresql-odbc package
# Setup from the unixODBC package
[PostgreSQL]
Description     = ODBC for PostgreSQL
Driver          = /usr/lib/psqlodbcw.so
Setup           = /usr/lib/libodbcpsqlS.so
Driver64        = /usr/lib64/psqlodbcw.so
Setup64         = /usr/lib64/libodbcpsqlS.so
FileUsage       = 1


# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
[MySQL]
Description     = ODBC for MySQL
Driver          = /usr/lib/libmyodbc5.so
Setup           = /usr/lib/libodbcmyS.so
Driver64        = /usr/lib64/libmyodbc5.so
Setup64         = /usr/lib64/libodbcmyS.so
FileUsage       = 1

すでにPostgreSQLとMySQLの設定はされているのでここにSQL ServerとRedshiftの設定を追記します。

SQL Serverの設定

前回でインストールは終わっているのでドライバファイルもインストールされていますので設定ファイルに追記します。

$ cat  /etc/odbcinst.ini

# Example driver definitions

# Driver from the postgresql-odbc package
# Setup from the unixODBC package
[PostgreSQL]
Description     = ODBC for PostgreSQL
Driver          = /usr/lib/psqlodbcw.so
Setup           = /usr/lib/libodbcpsqlS.so
Driver64        = /usr/lib64/psqlodbcw.so
Setup64         = /usr/lib64/libodbcpsqlS.so
FileUsage       = 1


# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
[MySQL]
Description     = ODBC for MySQL
Driver          = /usr/lib/libmyodbc5.so
Setup           = /usr/lib/libodbcmyS.so
Driver64        = /usr/lib64/libmyodbc5.so
Setup64         = /usr/lib64/libodbcmyS.so
FileUsage       = 1

[ODBC Driver 17 for SQL Server]
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.4.so.2.1
UsageCount=1

Redshiftの設定

次にRedshiftの設定を行います。 ODBCドライバのインストールはAWSの公式ページに従って行えば特に問題なく行えます。 RHELの64 ビットをインストールします。

公式ページの手順に従ってインストールを終えると

/opt/amazon/redshiftodbc/lib/64/libamazonredshiftodbc64.so

にドライバファイルができるので設定ファイルに追記します。

$ cat  /etc/odbcinst.ini

# Example driver definitions

# Driver from the postgresql-odbc package
# Setup from the unixODBC package
[PostgreSQL]
Description     = ODBC for PostgreSQL
Driver          = /usr/lib/psqlodbcw.so
Setup           = /usr/lib/libodbcpsqlS.so
Driver64        = /usr/lib64/psqlodbcw.so
Setup64         = /usr/lib64/libodbcpsqlS.so
FileUsage       = 1


# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
[MySQL]
Description     = ODBC for MySQL
Driver          = /usr/lib/libmyodbc5.so
Setup           = /usr/lib/libodbcmyS.so
Driver64        = /usr/lib64/libmyodbc5.so
Setup64         = /usr/lib64/libodbcmyS.so
FileUsage       = 1

[ODBC Driver 17 for SQL Server]
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.4.so.2.1
UsageCount=1

[Redshift]
Driver=/opt/amazon/redshiftodbc/lib/64/libamazonredshiftodbc64.so

以上で設定は終わりです。

pyodbcでデータソースに接続する

ここまでの設定を行えばpydobcを使って各データソースに接続できるようになります。

import pyodbc 
cnn_sqlserfer = pyodbc.connect('DRIVER=ODBC Driver 17 for SQL Server;UID=ユーザ;PWD=パスワード;DATABASE=データベース;SERVER=ホスト;')
cnn_postgres = pyodbc.connect('DRIVER=PostgreSQL;UID=ユーザ;PWD=パスワード;DATABASE=データベース;SERVER=ホスト;')
cnn_redshift = pyodbc.connect('DRIVER=Redshift;UID=ユーザ;PWD=パスワード;DATABASE=データベース;SERVER=ホスト;')

あとはコネクションを張ってカーソルを作成してといった方法で操作が可能です。詳しい使い方は公式ドキュメントが非常にわかりやすのでそちらをご確認ください。

まとめ

Linux、macOSでSQL Serverを含めた様々なデータソースに接続でき操作を行えるpyodbcの設定をまとめました。 これでデータソースに接続してデータを取得して加工するといった処理がPythonで行えるようになります。Pythonを利用すればPandasやPySparkを使い大規模なデータ処理も簡単に行えるようになり非常に効率が良いです。

最後まで読んで頂いてありがとうございました。