pyodbcを使ってPythonでDBを操作しよう　～SQL Server編～

本日はPythonでODBC経由でDBを操作する方法を紹介したいと思います。RDBMSといってもOracleやMySQLなど色々あるかと思いますが、今回はSQL Serverを使ってみようと思います。

使用するライブラリ

pyodbcというライブラリをインストールする必要があります。

pip install pyodbc　でインストールできると思いますが、インストールがうまくいかない場合は以下サイトでwheelをダウンロードしてそれを使ってインストールしてください。
pypi.org

実行環境

Windows10
SQL Server2016
Python3.6.5

GISとRDBMS

余談にはなるのですが、GISを使われている方の中でどのくらいの方がGISデータの管理にRDBMSを使われているのかすごく気になります。

データはあくまでShapeやGDBのような形式でのみ管理している
ShapeやGDBなどのGISデータとそれに紐づく属性情報などをRDBMS上で管理している
ShapeやGDBなどのGISデータとそれに紐づく属性情報などをRDBMSではなく、MongoDBなどのNoSQLで管理している
ArcGIS Enterprise Geodatabaseで図形も属性もRDBMS上管理している

大雑把ではあるのですが、このようにカテゴライズできるかと思います。皆様はどのような形でGISデータを管理されているのでしょうか？ちなみに私は「2」です。

テストデータ

以下のようなデータを使用します。

DB・・・Sample
テーブル・・・TEST

f:id:sanvarie:20190629160312p:plain

レコードは一つだけ作りました。
f:id:sanvarie:20190629161353p:plain

pyodbcの使用方法

pyodbcを使用して以下のような操作をするための方法を書いてみました。

ログイン
検索
更新
挿入
削除

# -*- coding: utf-8 -*-
import pyodbc

def login():
    # インスタンス
    instance = "インスタンス"

    # ユーザー
    user = "ユーザー"

    #パスワード
    pasword = "パスワード"

    #DB
    db = "Sample"

    connection = "DRIVER={SQL Server};SERVER=" + instance + ";uid=" + user + \
                 ";pwd=" + pasword + ";DATABASE=" + db


    return pyodbc.connect(connection)

if __name__ == '__main__':
    con = login()

これでOKです。簡単ですね

検索

検索はログイン処理で取得したコネクションとSQLをselect_executeメソッドで処理しています。そして、取得したレコードをループし（１行しかありませんが）、各カラムの中身をprintしています。

def select_execute(con, slq):
        cursor = con.cursor()
        cursor.execute(slq)
        rows = cursor.fetchall()
        cursor.close()

        return rows

if __name__ == '__main__':
    con = login()
    sql =  '''select *
                from TEST'''
    res = select_execute(con, sql)
    for r in res:
        print(r[0])
        print(r[1])
        print(r[2])
        print(r[3])

このような結果になりました。

f:id:sanvarie:20190629162754p:plain

更新

NAMEカラムの中身を「ライン」から「ラインテスト」となるようにデータを更新します。要領は検索と同じですが、更新処理なので最後にcommitする必要があります。

def updatet_execute(con, slq):
    cursor = con.cursor()
    cursor.execute(slq)
    con.commit()

if __name__ == '__main__':
    con = login()
    sql = """UPDATE TEST
                SET NAME = NAME + 'テスト'"""

    # データ更新
    updatet_execute(con, sql)

想定通りの結果になりました。
f:id:sanvarie:20190629165149p:plain

挿入

１レコード挿入する処理を書いています。

def insert_execute(con, slq):
    con.execute(sql,
                2,
                "ポイント",
                "マルチ",
                "特になし"
                )

    con.commit()

if __name__ == '__main__':
    con = login()
    sql = """INSERT
             INTO TEST(FEATURE_NUM,
                       NAME,
                       TYPE,
                       NOTE
                       )
                  VALUES (?,
                          ?,
                          ?,
                          ?
                          )"""

    # データ登録
    insert_execute(con, sql)

ちゃんと挿入した分のレコードがDB側に反映されていました。
f:id:sanvarie:20190629164159p:plain

削除

上記処理で挿入したレコードを削除しています。

def delete_execute(con, slq):
    cursor = con.cursor()
    cursor.execute(slq)
    con.commit()

if __name__ == '__main__':
    con = login()
    sql = """DELETE
               FROM TEST
              WHERE FEATURE_NUM = 2"""

    # データ削除
    delete_execute(con, sql)

想定通りレコードが削除されました。
f:id:sanvarie:20190629165718p:plain

pyodbcを使えばとても簡単にPythonからSQL Serverを操作できることがわかりました。今回はあくまでサンプルなので、クラスは作りませんでしたが、実際にDB操作をする際はDBアクセス用のクラスなどを作ってそこに各処理を記載するのがいいかと思います。今後はGISデータとDBのデータを一緒に扱って何かしらの処理をするようなことも紹介できればと思います。

本日は以上です。

GIS奮闘記

元GISエンジニアの技術紹介ブログ。主にPythonを使用。