Python で openpyxl を使ってみる

こんにちは。masm11 です。

Python で .xlsx ファイルを作成したかったので openpyxl に挑戦してみました。 簡単に使えましたので、その方法を紹介したいと思います。

インストールはコマンドラインで以下のようにします。

pip install openpyxl

では、まずは簡単に A1〜C3 セルに数値を入れる方法から。 なお、以下のコードでは、Python 3.6 以上を想定した書き方をしています。

#!/usr/bin/env python

import openpyxl

wb = openpyxl.Workbook()
ws = wb.active

num = 0
for r in [ '1', '2', '3' ]:
    for c in [ 'A', 'B', 'C' ]:
        ws[f'{c}{r}'] = num
        num += 1

wb.save('sample-1.xlsx')

これで sample-1.xlsx ができます。LibreOffice で開いた画面は以下のようになりました。

次に、各数値を今度は文字列にしてみましょう。

#!/usr/bin/env python

import openpyxl

wb = openpyxl.Workbook()
ws = wb.active

num = 0
for r in [ '1', '2', '3' ]:
    for c in [ 'A', 'B', 'C' ]:
        ws[f'{c}{r}'] = str(num)
        num += 1

wb.save('sample-2.xlsx')

これでもいいと言えばいいのですが、セルの値の先頭に ' が付いて、 '0 などとなってしまいました。

ここは書式設定をするのが良いでしょう。

数値形式は以下のページの FORMAT_*** が使えそうです。

https://openpyxl.readthedocs.io/en/stable/_modules/openpyxl/styles/numbers.html

この中から FORMAT_TEXT を指定してみました。

#!/usr/bin/env python

import openpyxl

wb = openpyxl.Workbook()
ws = wb.active

num = 0
for r in [ '1', '2', '3' ]:
    for c in [ 'A', 'B', 'C' ]:
        ws[f'{c}{r}'].number_format = openpyxl.styles.numbers.FORMAT_TEXT
        ws[f'{c}{r}'] = str(num)
        num += 1

wb.save('sample-3.xlsx')

これで以下のようにテキストになりました。

以上のように、openpyxl はとても使いやすそうです。 今後もお世話になりそうな予感がしています。

ではまた。

プログラムの出力をリダイレクトする

こんにちは、masm11 です。

今回は、プログラムの出力を操作する話を書きたいと思います。 「なんだそんなことか」と思われるかもしれませんが、 なかなか高度なこともできます。

ファイルディスクリプタとは

プログラムの出力を操作するには「ファイルディスクリプタ」というものについて知っておく必要があります。 リダイレクトの前にこれについて軽く勉強してみます。

C言語で

int fd = open("/tmp/foo", O_WRONLY);

などとファイルを開くと、その結果として 0 以上の番号が得られます。 これがファイルディスクリプタです。

ファイルに対して入出力する時には、このファイルディスクリプタを使って、

write(fd, "123", 3);

などとします。fd は先程得られたファイルディスクリプタです。 fd が示すファイル(つまり /tmp/foo)に対して 123 という3バイトを書き込んでいます。

そして、ファイルディスクリプタ 0〜2 は特別な意味を持っていて、

• 0 は標準入力
• 1 は標準出力
• 2 は標準エラー出力

をそれぞれ示すことになっています。

dup2() とは

open() の他に、dup2() というシステムコールがあります。

これは、元々のファイルディスクリプタとは別の、指定したファイルディスクリプタからでも使えるように する機能があります。

例えば

int fd = open("/tmp/foo", O_WRONLY);

とすると、fd を使って /tmp/foo に書き込むことができますが、dup2() を使って

dup2(fd, 1);

とすると、fd の代わりに 1 でも /tmp/foo に書き込むことができるようになります。

リダイレクト

さて、リダイレクトです。

program > log.txt

よく目にする、普通のリダイレクトです。

シェルは、今までに説明した open() と dup2() を使って、

int fd = open("log.txt", O_WRONLY);
dup2(fd, 1);

をした後に program を実行します。すると、program にとって標準出力のファイルディスクリプタ 1 は log.txt につながっているため、program が普通に標準出力に出力するだけで、log.txt への出力となります。

2>&1 とは

次に、

program > log.txt 2>&1

これもよく目にすると思います。

シェルは

// > log.txt の処理
int fd = open("log.txt", O_WRONLY);
dup2(fd, 1);

// 2>&1 の処理
dup2(1, 2);

をした後に program を実行します。

dup2(1, 2); をすることで、1 だけでなく 2 でも log.txt に出力されるようになっているわけです。

なお、間違って

program 2>&1 > log.txt

と実行し、「あれ?」と思った方、いませんか?

// 2>&1 の処理
dup2(1, 2);

// > log.txt の処理
int fd = open("log.txt", O_WRONLY);
dup2(fd, 1);

dup2(1, 2); の時、1 につながっているファイルに 2 からでも出力されるようになるわけですが、 通常では 1 には端末がつながっているため、2 も同じ端末へ出力されるようになります。 ただ、通常では(つまりリダイレクトしなければ) 2 は 1 と同じ端末に出力されるようになっていますので、 このタイミングでの 2>&1 は無意味となります。

そしてその後に > log.txt が処理され、1 が log.txt につながります。

そのため、標準エラー出力は引き続き端末のままとなるわけです。

順序には気をつけましょう。

標準出力と標準エラー出力を入れ替える

では最後に、標準出力と標準エラー出力を入れ替えてみます。

program 3>&2 2>&1 1>&3 3>&-

こうすることで入れ替えられます。順を追って見ていきます。

// 3>&2
dup2(2, 3);

こうすると、2 につながっているファイルに対して、3 からでも出力できるようになります。 3 というファイルディスクリプタは、標準入力でも標準出力でも標準エラー出力でもない、普通のファイルディスクリプタです。

// 2>&1
dup2(1, 2);

こうすると、1 につながっているファイルに対して、2 からでも出力できるようになります。

さて、その上で、

// 1>&3
dup2(3, 1);

です。3 につながっているファイルに対して、1 からでも出力できるようになります。 ここで 3 は、上に書いたとおり、元々 2 につながっていたファイルです。 ですので、1 からは元々 2 につながっていたファイルに出力されることになります。

以上で、program を実行すると、標準出力は標準エラー出力へ、標準エラー出力は標準出力へ出力されるようになるわけです。

ただ、3 は、1 と 2 を入れ替えるために一時的に割り当てたもので、 program には不要です。ですので、

// 3>&-
close(3);

で 3 を閉じています。

実際に試してみましょう。

bash-3.2$ (
>   (
>     echo stdout
>     echo stderr >&2
>   ) 3>&2 2>&1 1>&3 3>&-
> ) > stdout.txt 2>stderr.txt
bash-3.2$ cat stdout.txt 
stderr
bash-3.2$ cat stderr.txt 
stdout
bash-3.2$ 

ちゃんと入れ替わっているようです。

この例では、この記事で説明しなかった書き方もしています。 >&2 と 2>stderr.txt の部分ですね。 どんな操作なのかは考えてみて下さい。

では。

Let's Encrypt でワイルドカード証明書を取得する

(2018/07/23 追記しました)

こんにちは、masm11 です。

弊社では社内でいくつものサブドメインを使っています。abc.example.com, def.example.com, ghi.example.com, ... そしていずれも HTTPS でアクセスできるようにするため、Let's Encrypt で証明書を取得しています。 しかし、サブドメインは時々増え、そのたびに証明書を新たに作っていました。 わりと手間です。

その手間を軽減するため、Let's Encrypt でワイルドカード証明書を取得することにしました。 ワイルドカード証明書があれば、*.example.com 全てに使えるため、 サブドメインが増えても証明書を新たに作らなくてすみます。

そこで今回は、Let's Encrypt を使ったワイルドカード証明書の取得方法について書きたいと思います。 ただ、今回の作業が、certbot-auto が既に動いていて、それを使ってワイルドカード証明書を取得する、 というものだったため、 この記事は「今まで certbot-auto で取得・更新していて、それをワイルドカード証明書に移行したい」 という人向けとなります。

さて、Let's Encrypt でワイルドカード証明書を取得するには、DNS を使った認証をする必要があります。 そして、せっかく Let's Encrypt を使うのですから、更新を自動化したいものです。 そこで、DNS による認証を自動化します。

DNS サービスは世の中にいくつもあり、certbot(-auto) はそれらの中のいくつかの方式に対応しています。 弊社では Route53 を使っていますので、Route53 用のプラグインを使いました。

1. certbot-auto について

certbot-auto や、それが使っている Python のライブラリは、自動更新されているはずです。 従って、改めて更新する必要はありません。

2. certbot-dns-route53 プラグインのインストール

/opt/eff.org/certbot/venv/ に certbot-auto が使っている python の virtualenv がありますので、 ここに certbot-dns-route53 をインストールします。

sudo /opt/eff.org/certbot/venv/bin/pip install certbot-dns-route53

./certbot-auto plugins と実行すると、使えるプラグインのリストが表示され、 その中に dns-route53 が存在するのが確認できます。

* dns-route53
Description: Obtain certificates using a DNS TXT record (if you are using AWS
Route53 for DNS).
Interfaces: IAuthenticator, IPlugin
Entry point: dns-route53 = certbot_dns_route53.dns_route53:Authenticator

3. AWS 的な準備

AWS に新たなユーザ(IAM)を作ります。このユーザの権限で DNS を操作することになります。

このユこのユーザに与える権限は、JSON で与えるのが簡単です。 JSON は以下のようになります (/opt/eff.org/certbot/venv/lib/python2.7/site-packages/certbot_dns_route53/__init__.py そのままです)。

{
    "Version": "2012-10-17",
    "Id": "certbot-dns-route53 sample policy",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "route53:ListHostedZones",
                "route53:GetChange"
            ],
            "Resource": [
                "*"
            ]
        },
        {
            "Effect" : "Allow",
            "Action" : [
                "route53:ChangeResourceRecordSets"
            ],
            "Resource" : [
                "arn:aws:route53:::hostedzone/YOURHOSTEDZONEID"
            ]
        }
    ]
}

これの YOURHOSTEDZONEID の部分を書き換えてアップロードすると、 権限を設定できます。 hostedzone ID は aws console に表示されていますので、それに書き換えて下さい。

このユーザの credentials を /root/.aws/credentials に保存します。 ファイルの中身は以下のようになります。

[default]
aws_access_key_id = ...
aws_secret_access_key = ...

4. 証明書を取得

いよいよ証明書の取得です。

sudo ./certbot-auto certonly \
  --server https://acme-v02.api.letsencrypt.org/directory \
  --email メールアドレス \
  --agree-tos \
  --dns-route53 \
  -d \*.ドメイン

ワイルドカード証明書を取得するには、letsencrypt のサーバを default とは異なる acme-v02 にする必要があるので、 それを指定しています。

また、サーバを変更するとメールアドレスも再度登録する必要があるので、--email を指定しています。 再登録となるので、同意確認もあります。同意確認のために --agree-tos を付けています。

--dns-route53 は Route53 を使った認証をする指定です。

実行すると、少し時間がかかります。DNS を使うため、TXT レコードを設定した後に、前の TXT レコードの TTL が切れるのを 期待して少し待っているようです。

5. nginx の設定を修正

sudo vi /etc/nginx/nginx.conf

証明書と秘密鍵は /etc/letsencrypt/live/ドメイン/ にあるので、設定を修正します。 ドメインは、*. を除いたものになります。

設定を修正したら nginx を restart します。

ブラウザからアクセスして、ワイルドカード証明書に切り替わっていることを確認しましょう。 問題なければ取得はこれで完了です。

6. 証明書を更新

これはまだ試せていないのですが、 証明書を取得した際のオプションは /etc/letsencrypt/renewal/ に保存されているので、 更新時にはオプションは必要ないはずで、

sudo ./certbot-auto renew --post-hook '/etc/init.d/nginx restart'

だけでできるはずです。

7. 旧証明書を revoke

これもまだ試していないのですが、 証明書の自動更新まで確認できたら、旧証明書は revoke しておくのが良いでしょう。

sudo ./certbot-auto revoke --cert-path /etc/letsencrypt/live/ホスト名/fullchain.pem

revoke すると、証明書ファイルは削除され、それ以降は renew による更新の対象にならなくなります。

まとめ

弊社ではこの手順で取得しました。今のところ問題ありませんが、更新が問題なくできるか、ちょっとドキドキです。

---

2018/07/23 追記。

これだけでうまくいくと思っていたのですが、予想外の事態が発生しました。 certbot-dns-route53 が削除されてしまいます。

certbot-auto は、実行された時に certbot-auto 自身や必要としている python ライブラリを更新しています。 おそらくこの時に削除されているものと思われます。

いくつか対策を実施して、現在動作確認中です。 今回の証明書更新は手動で実行したので、2ヶ月後に確認できると良いな、と思っています。

---

ではまた。

Ruby 関西でマルチテナントアプリの発表してきました

rubykansai.doorkeeper.jp

テーマが同じだけに、 id:gfx さんの

railsdmでマルチテナント・ウェブアプリの話をしました - Islands in the byte stream

と、内容が似通ってしまいましたが、自分なりにまとめてみました。

運用が始まり、ユーザ（テナント）が増えるとと色々難しい局面にも出くわします。 それは、また別の機会にお話しできれば。

tensorflow が遅くなる話

masm11 です。

最近、tensorflow を使って機械学習しています。 tensorflow がだんだん遅くなることがあって、気づいたことがあるので、書いてみます。 ただし、以下は私の想像であることをはじめにお断りしておきます。

まず、

a = tf.Variable(...)
b = tf.placeholder(...)

を実行します。この時、以下のように、2つのオブジェクトができて、 a, b がそれぞれのオブジェクトを指します。

次に、

c = a * b

を実行します。この時点では、実際の掛け算は行われません。掛け算を表すオブジェクトから a, b への参照ができます。以下のようになります。

そして、placeholder に値を設定して c を評価します。

d = c.eval(session=sess, feed_dict={b:...})

この時、ようやく実際に掛け算が行われます。

機械学習をしている時、epoch ごとに精度を評価することもあると思います。

for epoch in range(epochs):
    # 学習する
    # ...
    # 評価する
    d = c.eval(session=sess, feed_dict={b:...})

これは問題はありません。

ここで、以下のように書き換えてみます。

for epoch in range(epochs):
    # 学習する
    # ...
    # 評価する
    c = a * b
    d = c.eval(session=sess, feed_dict={b:...})

一見、変数 c を直前に作っているだけで、何も問題はないように思えます。

しかし、

c = a * b

この部分は、実際に掛け算を行わず、掛け算を表すオブジェクトを作るので、 以下のようにたくさんのオブジェクトが作られてしまいます。

さて、ここまで、コードのイメージでしか説明しませんでしたので、 実際のコードで時間を測定してみます。

まずは、ループの前で c を作った場合:

$ cat fast.py
#!/usr/bin/env python

import tensorflow as tf

a = tf.constant(2, dtype=tf.float32)
b = tf.constant(3, dtype=tf.float32)

sess = tf.Session()
sess.run(tf.global_variables_initializer())

c = a * b
for i in range(1000):
    c.eval(session=sess)
$ for i in `seq 5`; do time python fast.py; done

real    0m1.092s
user    0m1.092s
sys 0m0.200s

real    0m1.110s
user    0m1.104s
sys 0m0.208s

real    0m1.108s
user    0m1.116s
sys 0m0.196s

real    0m1.106s
user    0m1.100s
sys 0m0.208s

real    0m1.094s
user    0m1.080s
sys 0m0.216s
$ 

次に、ループの中で c を作った場合です:

$ cat slow.py
#!/usr/bin/env python

import tensorflow as tf

a = tf.constant(2, dtype=tf.float32)
b = tf.constant(3, dtype=tf.float32)

sess = tf.Session()
sess.run(tf.global_variables_initializer())

for i in range(1000):
    c = a * b
    c.eval(session=sess)
$ for i in `seq 5`; do time python slow.py; done

real    0m7.812s
user    0m7.808s
sys 0m0.196s

real    0m7.915s
user    0m7.896s
sys 0m0.208s

real    0m7.834s
user    0m7.824s
sys 0m0.200s

real    0m7.841s
user    0m7.812s
sys 0m0.224s

real    0m7.852s
user    0m7.812s
sys 0m0.232s
$ 

7倍も時間がかかっています。

以上の結果から、tensorflow のオブジェクト(式)は使い捨てにしない方が良さそうです。

そういえばオフィス移転しましたー。

先週金曜日オフィスを移転しました。 同じ大阪の梅田ですが、インキュベーションオフィスから自前のオフィスになりました。

広くなったのはいいけど、まだ空き机がいっぱいです。

エンジニア募集してまーす！

recruit.ingage.co.jp

Elasticsearch v2.3 から v.5.6 にバージョンアップしました

永田です。

ようやく。

この前、本番の #elasticsearch のバージョンを 2.X から 5.X にした。色々大変だったので、またブログに書こうっと。

— Kizashi (@kizashi1122) 2018年3月6日

先日おこなった Elasticsearch のバージョンアップについて書きます。

ちょうど年末に Cookpad の中の方がとても良い記事を書いていらっしゃいます。

qiita.com

私も色々と参考にさせていただきました。

ただバージョンアップといっても、使っている機能によって気にするところが色々違います。 この記事も今からバージョンアップされる方の参考になればと思います。

はじめに

2系から5系って、メジャーバージョンが3つもあがってるやん、って思いますが実はそうではないです。 Elasticsearchが2系のときに、関連製品である（いわゆる Elastic Stack）Kibana や Logstash とバージョンを合わせるため、一気にすべてが5系に上がったと記憶しています。

そしてもう Elasticsearch は6系が出ています。

概要

大きく以下のステップを実施しました。

• jdk のバージョンアップ
• elasticsearch 5.6 バージョンアップ
• elasticsearch-rails gem バージョンアップ
• deprecated なコードを修正

jdkのバージョンアップ

elasticsearch 5系は java8 が必須です。 2系を使っていた頃は open-jdk7 を使っていたのですが、これを機に、oracle-jdk8 に変えました（もう世の中ではJava10と騒いでいますが） まずは 2系を oracle-jdk8 で動かすようにしました。

elasticsearch 5.6 バージョンアップ

移行プラグインで事前調査

まずは移行プラグインで、移行に際して修正すべき点をチェックします。 2系から5系への移行の場合は、 https://github.com/elastic/elasticsearch-migration/tree/2.x を、2系の plugin でインストールします。

./bin/plugin install https://github.com/elastic/elasticsearch-migration/releases/download/v2.0.4/elasticsearch-migration-2.0.4.zip

以下、そのプラグインで挙がった指摘事項です。

Site plugin are no longer supported

サイトプラグインというのは、画面を持つプラグインということでしょう。 2系では、head と inquisitor を使っていましたが、もうプラグインとしては使えなくなります。

ここではひとまず使えないということを理解しておいて、5系ではインストールしないようにするということに注意します。

At least 65536 file descriptors must be available to Elasticsearch

2系利用時は 65535 に設定していました・・・・。

/etc/security/limits.conf の 65535 を 65536 に変更しました。

                soft    nofile          65536
                hard    nofile          65536

bootstrap.mlockall is set to true but mlockall has failed (elasticsearch.yml)

もともとの mlockall をコメントアウトし、memory_lock を付け足して有効にしました

#bootstrap.mlockall: true
bootstrap.memory_lock: true

index.mapper.dynamic can no longer be set in the config file (elasticsearch.yml)

コメントアウトしました。

#index.mapper.dynamic: false

discovery.zen.ping.timeout has been renamed to discovery.zen.ping_timeout (elasticsearch.yml)

言われたとおりにリネーム。

#discovery.zen.ping.timeout: 5s
discovery.zen.ping_timeout: 5s

以上で、移行プラグインの指摘の修正はおわりです。

その他の設定ファイルについて

• 2系では、使っていた logging.yml は不要になります。代わりに、log4j2.properties を使うことになります。このファイルはES5 に同梱されています。
• 2系では、elasticsearch.in.sh というスクリプトを用意し、その中で JAVA_OPTS をいじっていましたがこれも不要です。JAVA_OPTSの変更系の設定は jvm.options というファイルでおこないます。これもES5に同梱されています。

起動オプション

• -Des.default.path.data から -Edefault.path.data という指定の仕方に変わっています。

メインのアップグレード作業

メジャーバージョンの変更なので Rolling Upgrade はできません。 下記のリンクにしたがって、Full Cluster Restart Upgrade をしましょう。

www.elastic.co

我々のサービスでは、Elasticsearch への一切の更新をとめるために計画停止をおこないました。 既存の2系のElasticsearch をすべてシャットダウンし、1台ずつ5系に更新しました。

elasticsearch-rails gem バージョンアップ

-gem 'elasticsearch-rails', '~> 2.0.1'
-gem 'elasticsearch-model', '~> 2.0.1'
+gem 'elasticsearch-rails', '~> 5.0.2'
+gem 'elasticsearch-model', '~> 5.0.2'

deprecated なコードを修正

クエリの修正

missing query はES5からはサポートされなくなったとのこと。

https://www.bountysource.com/issues/47368001-elasticsearch-5-transporterror-400-parsing_exception-no-query-registered-for-missing

{ missing: { field: 'field_name' } }

としていたところを、

{ bool: { must_not: { exists: { field: 'field_name' } } } }

としました。

elasticsearch_deprecation.log

ES5 にすると自動的にこのログをはいてくれます。

Deprecated field [lowercase_expanded_terms] used, replaced by [Decision is now made by the analyzer]

2系では以下のURLにあるように lowercase_expanded_terms というオプションが使えましたが、使えなくなったので外しました。

www.elastic.co

おわりに

上述の Qiita の記事では平均レスポンスタイムが2倍も速くなったとありましたが、うちではちゃんと計測できてませんが、体感はそこまでの変化はありません。 うちのサービスでは通常の検索はそこそこ速いですが、データ量に依存して遅いこともあり、検索レスポンスについてはまだまだ課題を抱えています。

いつもの

エンジニア募集してます！ Ruby もやりたいし、Elasticsearch も触ってみたい！ってひとは声かけてくださいねー。