PythonのPyodbcでSQLServerに接続する

Pythonで保守を行っているシステムのデータベースに接続してデータを抽出したいことなどがよくあると思います。そんな時に便利なのがpyodbcというライブラリです。pyodbcは、odbc経由でデータベースに接続することが可能なライブラリで、MySQLやsqlite3などのデータベースに簡単に接続することが可能です。私は仕事で保守しているシステムのほとんどがSQLServerでシステムのデータベースを管理していることが多いので今回はpyodbcでSQL Serverに接続してみたいと思います。

pyodbcの使い方
まとめ

pyodbcの使い方

ライブラリのインポート

Pyodbcを使って、データベースに接続するときは、必ずpyodbcをインポートします。

import pyodbc as pdb

pythonでSQL Serverに接続する場合は、pyodbcの他にもSQLAlchemyというORM(ORマッパー)が有名ですが、私個人は普段保守でSQLを書くことが多いのでPyodbcを使っています。

SQLServerに接続する文字列

SQLServerに接続するには,ODBCで接続するための接続文字列が必要です。SQLServerでは以下のようになります。

connection = "DRIVER={SQL Server};SERVER=" + インスタンス名 + ";uid=" + ユーザー名 + \
";pwd=" + パスワード + ";DATABASE=" + データベース名

DRIVER={SQL Server};
SERVER=" + インスタンス名 + ";
uid=" + ユーザー名 + \";
pwd=" + パスワード + ";
DATABASE=" + データベース名

プログラムに直接接続情報を記載する場合

pythonのプログラムに直接接続文字列を書く場合は以下の通りです。

import pyodbc　as pdb
connection = "DRIVER={SQL Server};SERVER=" + instance + ";uid=" + user + ";pwd=" + pasword + ";DATABASE=" + db
pdb.connect(connection)

データベースの接続関数を作る

データベースを接続する際は、基本的に流用することが多いので関数化しておくと便利だと思います。関数化しておけば、接続先が変わった場合も、基本的にこの関数だけを修正すればいいので便利です。

import pyodbc as pdb
"""
DB接続関数(SQLServer)
"""
def login_con():
    instance = "xxxxxxx\xxxxx"
    user = "xxxxx"
    pasword = "xxxxxx"
    db = "xxxxxx"
    connection = "DRIVER={SQL Server};SERVER=" + instance + ";uid=" + user + ";pwd=" + pasword + ";DATABASE=" + db
    return pdb.connect(connection)

設定ファイルからDB情報を取得する場合

DBの接続情報をconfigファイルに記載し、そこから接続情報を取得することもできます。
この場合は、configparserというライブラリを使います。ここではconfig.iniというファイルをC:\Python_Sorceというフォルダに配置した場合のサンプルです。

1.config.iniファイルを作成

C:\Python_Sorce（任意でOK)にconfig.iniという名前のファイルを作成して以下のように
記載して保存する。

[TEST_DB]
instance = xxxxx\xxxx
user = xxxxx
pasword = xxxxxx
db = xxxx
[TEST2_DB]
instance = xxxxx\xxxx
user = xxxxx
pasword = xxxxxx
db = xxxx

[](大かっこ)で囲まれた部分は、セクションと呼ぶ。
セクションの下に記載されているオプション名=値はエントリと呼ぶ。

上記設定ファイルでは、TEST_DBとTEST2_DBという２つのセクションがある状態です。

２．configファイルからDB接続情報を取得する関数を作成

#パラメータに指定されたセクションのconfig.ini、ファイルを読み込む
def DB_CON(sect):
    configifile = (r'C:\Python_Sorce\config.ini')
    config_ini = configparser.ConfigParser()
    config_ini.read(configifile,encoding='utf-8')

    instance = config_ini[sect]['instance']
    user = config_ini[sect]['user']
    pasword = config_ini[sect]['pasword']
    db = config_ini[sect]['db']
    connection = "DRIVER={SQL Server};SERVER=" + instance + ";uid=" + user + \
    ";pwd=" + pasword + ";DATABASE=" + db
    return pdb.connect(connection)

上記関数は、C:\Python_Sorce\config.iniに記載されている、セクション名を指定すると
指定されたセクションのDB情報を取得し、接続文字列を作成する関数です。上記の関数を呼び出すときは以下のように記載すればよいです。

＃TEST_DBに接続したい場合
con = DB_CON("TEST_DB")
＃TEST2_DBに接続したい場合con = DB_CON("TEST2_DB")

このように記載できるのでデータベースの接続情報を変更したり、追加したい場合はわざわざプログラムを直さなくても、config.iniのファイルのみを修正すればよいので便利です。

SELECT結果を出力するサンプルプログラム

以下はpyodbcでSQLServerに接続してSQLServerのバージョンを標準出力するサンプルプログラムです。ここでは説明していませんがpandasという、ライブラリを使用すると実行したSQLを簡単にデータフレーム形式で格納することができます。また、出力先をExcelなどにすることもできるので非常に使いやすいです。SQLも取得したいSQLを記載すればその情報が簡単に取得できます。

import configparser
import pyodbc as pdb
import pandas as pd

#パラメータに指定されたセクションのconfig.ini、ファイルを読み込む
def DB_CON(sect):
    configifile = (r'C:\Python_Sorce\config.ini')
    config_ini = configparser.ConfigParser()
    config_ini.read(configifile,encoding='utf-8')
    instance = config_ini[sect]['instance']
    user = config_ini[sect]['user']
    pasword = config_ini[sect]['pasword']
    db = config_ini[sect]['db']
    connection = "DRIVER={SQL Server};SERVER=" + instance + ";uid=" + user + \
                 ";pwd=" + pasword + ";DATABASE=" + db
    return pdb.connect(connection)

#SQLServerに接続
con = DB_CON("TEST_DB")

sql =  '''
    SELECT @@version
    '''

df = pd.read_sql(sql,con)

print(df)

まとめ

pyodbcだと簡単にデータベースに接続できる。
pyodbcは、ODBC経由でデータベースに接続するためのライブラリ
接続情報をconfig化したい場合はConfigParser を使う。

【Python】pyodbcでSQL Serverに接続する