LinuxでTerminal生活を営んでいると、
WindowsでもPowershellのあの青い画面で何でもやりたくなるものです。

Databaseを扱う自分の関数というかCmdletを作っておいて、
青い画面をさらっと読んでSQLを実行して結果を得るみたいなものが作りたい。
簡単なDBを扱うModuleを作ってみました。

環境

Windows 7 SP1 (x64) ja_JP
PowerShell v4.0

条件を整理

まずは条件を整理します。

関数として重要な機能

データを表示するSQLクエリーを実行
更新系SQLクエリーの実行
テーブル一覧の取得

これらの機能を持った関数を作ります。
順番にこんな名前にします。

Get-OdbcData
Invoke-OdbcQuery
OdbcTableList

あればうれしい機能

呼び出すDBは決まっている
結果をcsvに出力
Cmdletですぐに呼び出せるようにしたい

これらの関数をDataTool.psm1というファイル名にして保存します。
まずは。どんな関数が出来たか見て下さい。
後で少しずつ説明していきます。

全体像

DataTools.psm1

function Get-OdbcData{
<#
.Synopsis
    SQL文でデータを取得
.DESCRIPTION
    SQLのSelect文でデータを取得する。
    ODBCのDSN名やConnectionStringを指定して他のDatabaseでも使えます。
    ODBC専用
.EXAMPLE
    Get-OdbcData -q "SELECT * FROM items" | Format-Table
    id name   price
    -- ----   -----
    0  apple   100
    1  orange   80
    2  grape   120

    # SELECT文を実行
.EXAMPLE
    Get-OdbcData -q "SELECT * FROM items" | Export-Csv -Path items.csv -NoTypeInformation
    # 取得したデータをCsvファイルに出力

    PS1 > Get-Content .\item.csv
    "id","name","price"
    "0","apple","100"
    "1","orange","80"
    "2","grape","120"

    # 出力したCsvファイルの確認
.EXAMPLE
    Get-OdbcData -connectionString "DSN=stationer" -q "SELECT * FROM items"
    id name   price
    -- ----   -----
    0  pencil  100
    1  eraser   80
    2  pencase 120

    # 既定以外のデータベースでSELECT文を実行

.NOTES
    System.Data.Odbc      ODBC
    System.Data.OleDb     OleDb
    System.Data.SqlClient SQL Server
    使う.Netのオブジェクトによって、
    OleDb用やSqlClient用のCmdletも作れます。
#>
    [CmdletBinding()]
    param(
        # 接続文字列 ConnectionStrings
        [string]$connectionString="DSN=DSNNAME",
        # クエリー SQL Query
        [string]$q 
    )
    Begin{
        $Con = New-Object System.Data.Odbc.OdbcConnection($connectionString)

        # コマンドオブジェクト作成
        $Cmd=New-Object System.Data.Odbc.OdbcCommand
        $Cmd.Connection=$Con
        $Cmd.CommandText=$q
    }
    Process{    
        # DataAdapter
        $da=New-Object System.Data.Odbc.OdbcDataAdapter
        $da.SelectCommand=$Cmd

        # DataSetに格納
        $DataSet=New-Object System.Data.DataSet
        $nRecs=$da.Fill($DataSet)
        $nRecs | Out-Null
        # データ表示
        $DataSet.Tables[0]
    }
    End{
        $Con.Close()

    }
}

function Invoke-OdbcQuery{
<#
.Synopsis
   SQLクエリーでデータを更新
.DESCRIPTION
    ODBCで設定しているDBに対してデータの更新や削除が行えます。
    ODBCのDSN名やConnectionStringを指定して他のDatabaseでも使えます。
    ODBC専用
.EXAMPLE
    Invoke-OdbcQuery -q "UPDATE tables SET date=null WHERE id=1"
    #
.EXAMPLE
    Invoke-OdbcQuery -q "UPDATE tables SET date=null WHERE id=1" -connectionString="DSN=DSNNAME"
.NOTES
#>
    [CmdletBinding()]
    param(
        # 接続文字列 ConnectionString
        [string]$connectionString="DSN=DSNNAME",
        # クエリー SQL Query
        [string]$q
    )
    begin{
        $Con = New-Object System.Data.Odbc.OdbcConnection($connectionString)

        # コマンドオブジェクト作成
        $Cmd=New-Object System.Data.Odbc.OdbcCommand
        $Cmd.Connection=$Con
        $Cmd.CommandText=$q

    }
    process{
        # 接続オープン
        $Con.Open()
        # 実行
        $Cmd.ExecuteNonQuery()
        # 切断
        $Con.close()
    }
}

function OdbcTableList{
<#
.Synopsis
   テーブル一覧を表示
.DESCRIPTION
    該当するODBCでConnectionStringのテーブル一覧を表示する。
    ODBCのDSN名やConnectionStringを指定して他のDatabaseでも使えます。
    ODBC専用
.EXAMPLE
    OdbcTableList
    items
    sales
    stocks
    customers
    # テーブル一覧を実行
.EXAMPLE
    OdbcTableList | Select-String items
    items
    # テーブル一覧から TableName を検索
.EXAMPLE
    OdbcTableList -connectionString "DSN=stationer"
    stationer_items
    stationer_sales
    # 規定のデータベース以外でテーブル一覧を実行
.NOTES
#>
    [CmdletBinding()]
    param(
        # 接続文字列 ConnectionString
        [string]$connectionString="DSN=DSNNAME"
    )
    Begin{
        # 接続
        $Conn = New-Object System.Data.Odbc.OdbcConnection($connectionString)
          }
    Process{
        # 接続オープン    
        $Conn.Open()
        # テーブル一覧
        $Tables = $Conn.GetSchema("Tables")
        $Tables.TABLE_NAME
    }
    End{
        $Conn.Close()
    }
}

いつでも呼び出せるようにする

これらのCmdletを作り自分のCmdletとしていつでも呼び出せるようにする。
ファイル名を DataTools.psm1 にして、

%USERPROFILES%\Documents\WindowsPowerShell\Modules\DataTools\DataTools.psm1

として保存する。
そして、

load_module

PS1> Import-Module DataTools

と実行する。

次回からはLoad Moduleの実行は不要で何時でも自分の作ったCmdletを呼び出せる。

使い方

Get-OdbcData

# Dataを表示する
PS1> Get-OdbcData -q "SELECT * FROM items" | Format-Table
id name   price
-- ----   -----
0  apple   100
1  orange   80
2  grape   120

# Csvファイルとして出力
PS1> Get-OdbcData -q "SELECT * FROM items" | Export-Csv -Path .\item.csv -NoTypeInformation -Encoding UTF8

# 出力したCsvファイルを表示
PS1> Get-Content .\item.csv
"id","name","price"
"0","apple","100"
"1","orange","80"
"2","grape","120"

# ConnectionStringを指定することで他のDBにも繋ぐことができます。
PS1> Get-OdbcData -q "SELECT * FROM items" -connectionString "DSN=stationer"
id name   price
-- ----   -----
0  pencil  100
1  eraser   80
2  pencase 120

Invoke-OdbcQuery

# grapeのpriceを130に変更
PS1> Invoke-OdbcQuery -q "UPDATE items SET PRICE=130 WHERE ID=2"

# 更新が成功すると更新された行数が引数として帰ります。
PS1> $rows=Invoke-OdbcQuery -q "UPDATE items SET PRICE=130 WHERE ID=2"
PS1> $rows
1

OdbcTableList

# テーブル一覧
PS1> OdbcTableList
items
sales
stocks
customers

# Select-Stringによって絞り込む
PS1> OdbcTableList | Select-String items
items

# 他のDatabaseへの実行も可能
PS1> OdbcTableList -connectionString "DSN=stationer"
stationer_items
stationer_sales

解説

Get-OdbcData

データを表示するSQLクエリーの実行します。
僕の会社ではODBCしか使わないので、
下記の名前空間のオブジェクトを使っています。

呼び出すライブラリー

System.Data.Odbc

.Net FrameworksのSystem.Data.Odbcオブジェクトです。
結果をDataSetに格納して出力します。

もし、OleDbやSqlClient(SQL Serverネイティブ)が欲しければ、
別のCmdletを実装した方が分かり易いです。

呼び出すDBは決まっている

僕の会社の環境下では殆ど特定のDBしか扱わない。
だからConnectionStringの部分を決め打ちで書いて置くのがいいだろう。
時々は別のDBを弄ることもあるだろう。
下記のようにDefaultのparameter(引数)としてCmdlet内で下記のように指定しておく。

defaultのparameter

[CmdletBinding()]
    param(
        [string]$connectionString="DSN=vegetable_store", # 良く使うDSN名を仕込んでおく
    )

こうすればConnectionStringの指定がない場合は、
Defaultに指定したDatabaseに接続する。

別のDBに接続したいときは下記のようにConnectionStringを指定すればいい。

別のDBに繋ぐ場合

PS1 > Get-OdbcData -connectionString "DSN=OtherDNSNAME" -q "SELECT * FROM table"

これは後で説明する下記の関数の場合でも同じようにConnectionStringを指定すれば、
別のDatabaseに接続できるようにしてある。

Invoke-OdbcQuery
OdbcTableList

結果をcsvに出力

Get-OdbcDataの結果はCustomObjectで出力されます。
Csvに出力するのは簡単です。
下記のようにパイプに通してExport-Csvを実行すればいい。

csvファイルに出力

PS1> Get-OdbcData -q "SELECT * FROM items" | Export-Csv -Path .\item.csv -NoTypeInformation -Encoding UTF8

Invoke-OdbcQuery

更新系のSQLクエリの実行
更新系のSQLクエリーの場合はDataSetは不要。

OdbcCommand
OdbcConnection

この2つさえあればいい。

更新系のSQLクエリーとは

UPDATE
DELETE
INSERT

のことである。
これくらい実行できれば通常業務は何とかなるだろう。

下記のように実行する。

grapeのpriceを130に変更

PS1> Invoke-OdbcQuery -q "UPDATE items SET PRICE=130 WHERE ID=2"

ExecuteNonQuery()を実行しているので、
更新が成功すると更新された行数が返ってくる。

更新が成功すると更新された行数が返る。

PS1> $rows=Invoke-OdbcQuery -q "UPDATE items SET PRICE=130 WHERE ID=2"
PS1> $rows
1

OdbcTableList

テーブル一覧
このデータベースにどんなテーブルがあるのか。
テーブル名が分からなければ、SQLクエリーは実行できません。
テーブル一覧は必要でしょう。

下記のように実行すれば、テーブル一覧が出せる

テーブル一覧

PS1> OdbcTableList
items
sales
stocks
customers

そのテーブル一覧を Select-String で検索を掛ければ、
さくっとお目当てのテーブル名を見つけることができるはず。

テーブル一覧を検索

PS1> OdbcTableList | Select-String items
items

参考文献

Windows PowerShell: Windows PowerShell からデータベースにアクセスする