Go to Qiita Advent Calendar 2024 Top
LoginSignup
10
10

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@arachan@github(Yusuke Arakawa)

PowerShellでOdbcのDBを扱うModuleを作る

Last updated at Posted at 2016-02-19

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 からデータベースにアクセスする

10
10
2

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
10
10

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?