2014/07/06
PostgreSQL > テスト用ダミーファイル群を作る（2）
PostgreSQL > テスト用ダミーファイル群を作る（2）
昨日の続き。PostgreSQL の COPY コマンドと DO 構文を使ってダミーデータファイルを複数一括作成できたはいいが、出力先ファイル名に日本語など非 ASCII を使うと文字化けしてしまうので、COPY を使わず PL/Python で出力する例。実行環境は 2014/04/17 を、PL/Python のインストール手順は下記を参照。また日本語ドキュメントへのリンクも張っておく。
■ 2013/10/15 PL/Python でラスタをファイル出力（1）
http://kenpg.seesaa.net/article/377573574.html
■ PostgreSQL 9.3.2文書 : PL/Python - Python手続き言語
http://www.postgresql.jp/document/9.3/html/plpython.html
↓ 最初に COPY コマンドでファイル名が文字化けする現象の確認。実行自体はでき、書き込みもクエリどおり行われているが…。
この「テスト → 繝・せ繝・txt」という文字化けパターンをエディタで検証したら、UTF-8 の文字列を Shift_JIS と見なして読み込んだ場合に起きた。自分の環境特有かもしれない。ともかく、DO 構文（無名コードブロック）の言語に PL/Python を使うと、非 ASCII があっても問題なく新規ファイルを作れたので ↓ 当面こちらで代替する。
↓ 昨日の最初の例と同様、全国都道府県コード・名の一覧テーブルを使い、都道府県別のダミーデータファイルを出力する。
↓ クエリ例。大きな流れは昨日と同様、一番目の SQL でファイル一覧を取得し、一行ずつループしながら二番目の SQL でダミーデータをファイル出力する。後者の部分で COPY の代わりに Python で一行ずつ書き込む。ロードが必要なモジュールは特にない。クエリ 2 の中で列と列の連結にタブを使っている。これをカンマに変えれば CSV になる。
↑ 実行し終わった様子。メッセージペインに出力ファイルが出ている。パスに半角空白があっても問題なく書き出せた。下は結果のフォルダと、ファイルの中身。
↑ 今回、行並びもランダムにした。行数は random() * 1000 で各ファイルおおむね数百行ある。参考までファイルを ZIP にして置いておく。
https://kenpg.up.seesaa.net/image/20140706_test.zip
↓ PL/Python は SQL をヒアドキュメントで書けるのが超便利。通常の SQL を丸ごと ''' で囲って変数に入れられる。pgAdmin のクエリツールなら当該 SQL を選択して即実行できる。
ただ今日の方法では、ファイル名とダミーデータを連動させるのが面倒（例えば「北海道.txt」の中に「北海道」という文字を入れたり、北海道内の市区町村リストを入れるなど）。不可能ではないが、Python のコード内でファイルごとに SQL を生成する必要があり、せっかくのヒアドキュメントが使える利点が消えてしまう。
ファイル名に応じたダミーデータを作るには、まず COPY コマンドで ASCII 文字だけの一時ファイルに出力し、直後に PL/Python でリネームする方が良さそう。というわけで（3）に続く。
■ 2013/10/15 PL/Python でラスタをファイル出力（1）
http://kenpg.seesaa.net/article/377573574.html
■ PostgreSQL 9.3.2文書 : PL/Python - Python手続き言語
http://www.postgresql.jp/document/9.3/html/plpython.html
↓ 最初に COPY コマンドでファイル名が文字化けする現象の確認。実行自体はでき、書き込みもクエリどおり行われているが…。
この「テスト → 繝・せ繝・txt」という文字化けパターンをエディタで検証したら、UTF-8 の文字列を Shift_JIS と見なして読み込んだ場合に起きた。自分の環境特有かもしれない。ともかく、DO 構文（無名コードブロック）の言語に PL/Python を使うと、非 ASCII があっても問題なく新規ファイルを作れたので ↓ 当面こちらで代替する。
↓ 昨日の最初の例と同様、全国都道府県コード・名の一覧テーブルを使い、都道府県別のダミーデータファイルを出力する。
↓ クエリ例。大きな流れは昨日と同様、一番目の SQL でファイル一覧を取得し、一行ずつループしながら二番目の SQL でダミーデータをファイル出力する。後者の部分で COPY の代わりに Python で一行ずつ書き込む。ロードが必要なモジュールは特にない。クエリ 2 の中で列と列の連結にタブを使っている。これをカンマに変えれば CSV になる。
DO LANGUAGE plpython3u $PY$
# 出力先フォルダ
dir = 'R:/Test 20140706/'
# クエリ 1 ファイル名用
sq1 = '''
SELECT concat(pcode, ' ', pname, '.txt') fln
FROM "201407"."05_tb_sample_1"
WHERE pname <> '東京都'
'''
# クエリ 2 ダミーデータ用
sq2 = '''
SELECT sub || E'\t' || random() || E'\n' str
FROM generate_series(1, (random() * 1000) :: int) sub
ORDER BY random()
'''
dt1 = plpy.execute(sq1)
for i in range(0, len(dt1)) :
fln = dir + dt1[i]['fln']
plpy.info(fln)
fhd = open(fln, 'w')
dt2 = plpy.execute(sq2)
for j in range(0, len(dt2)) :
fhd.write(dt2[j]['str'])
fhd.close()
$PY$ ;
↑ 実行し終わった様子。メッセージペインに出力ファイルが出ている。パスに半角空白があっても問題なく書き出せた。下は結果のフォルダと、ファイルの中身。
↑ 今回、行並びもランダムにした。行数は random() * 1000 で各ファイルおおむね数百行ある。参考までファイルを ZIP にして置いておく。
https://kenpg.up.seesaa.net/image/20140706_test.zip
↓ PL/Python は SQL をヒアドキュメントで書けるのが超便利。通常の SQL を丸ごと ''' で囲って変数に入れられる。pgAdmin のクエリツールなら当該 SQL を選択して即実行できる。
ただ今日の方法では、ファイル名とダミーデータを連動させるのが面倒（例えば「北海道.txt」の中に「北海道」という文字を入れたり、北海道内の市区町村リストを入れるなど）。不可能ではないが、Python のコード内でファイルごとに SQL を生成する必要があり、せっかくのヒアドキュメントが使える利点が消えてしまう。
ファイル名に応じたダミーデータを作るには、まず COPY コマンドで ASCII 文字だけの一時ファイルに出力し、直後に PL/Python でリネームする方が良さそう。というわけで（3）に続く。