gologiusの巣

プログラミングなどの技術メモです。誰かの役に立てるとうれしいです。

【Python】 CSVをシンプルなExcel形式に変換する

やりたいこと

前置き

今回使ったCSVサンプルはここから拝借した。SJIS変換を手元でしている。

個人情報テストデータジェネレーター

ダミーとはいえ個人情報っぽい内容が記載されているのが気持ち悪かったので、 一応貼った画像は加工している。

本題

システムからCSVでファイルをダウンロードした後に、 加工せずにExcelで開くと↓のようになりますよね

これをExcelで人に渡す形式にしようとすると

  • 0飛びを防ぐために書式を文字列にしたり
  • フィルタしたり
  • ウインドウ固定したり
  • 列幅修正したり

とかしないといけなくて、面倒。

まともに取り込んで編集するためには↓のようなことをしないといけない。狂っている Excel 2019:従来のウィザードでデータをインポートするには

目指す姿

CSVを入力として、上記の対応を全部反映したExcelを自動生成する

結論(コード)

Python + pandas + openpyxl あたりでコード書いた。

CSVファイルをシンプルなExcelに変換する

超簡単な使い方説明

引数となるcsvファイルを対象に、excelファイルに変換する。

windowsなら

python converter.py %*

な感じのことをバッチファイル書いて、CSVファイルをバッチファイル上にD&Dして実行すればいい。

↓こういうこと

雑なスクリプト解説

CSVEXCELファイルへの変換自体は、pandasで実施している。 (openpyxlで完結しそうな気もするが、手元の環境でうまくいかなかったたため)

一回Excelファイルを作成し、そのExcelファイルに対して、openpyxlにて加工している。 pandasはあくまでデータ加工が得意なので、 Excel書式などの対応はpandasで頑張らないほうがよい模様。

列幅は愚直にループ回して最も文字数が多いセルの幅をセットしている。 幅の計算は半角全角によって適当な係数をかけて計算している。 厳密に計算しようとすると、フォントとフォントサイズとその文字のサイズをきちんと計算する必要がある模様。

余談

  • 必要であれば適宜改造して使ってもらってよいです。
  • ブログ加工より、CSVサンプルを探す→画像加工が地味に面倒だった
  • もっときれいに短く書ける人いれば教えて。

【SQLite+Python】 on conflict で syntax error(near "on") になる場合は、SQLiteのバージョンを疑う

備忘録なんで、厳密性は求めないでほしい。

問題

  • SQLitePython経由で使う
  • on conflict 構文を使う 場合に
near "on": syntax error

などと出て動作しない

原因

  • エラーに書いてある通り、構文が間違っている
  • SQLiteの実行バージョンが古く、on conflict の構文に対応していない

のどちらかかと思われる。

私が解決した方法

前者は頑張ってバグ取りしましょう。

後者が若干面倒な模様。

DBファイル側にはバージョンという概念がないらしく、 バージョンアップはsqliteを叩く側のライブラリのバージョンを上げる必要がある。

私の場合はPythonの標準モジュールsqlite3から叩いていたが、 標準モジュールなので、pip install sqlite3 などでのバージョンアップができない模様。

方法としては、

の二択になる。

面倒なので、私の場合はPythonのバージョンを上げることで対応した (厳密にいうと、Anaconda環境でバージョンアップしようとすると、 諸々の操作が原因で環境崩壊したので、Anaconda環境の入れ直しをしている)

SQLiteのバージョンについて

DBファイル側にも一応バージョンを保持する項目がある。 96byte目。

https://www.sqlite.org/fileformat.html

ただ、私がバイナリエディタで覗いたファイルは、値はセットされていなかった。 恐らくアプリ側がファイル生成時にセットするかどうかに依存しているのだと思う。 ちなみに除いたファイルは DB Browser for SQLiteで作ったファイル。

そもそも、SQLite自体が2050年までの長期サポートを目標にしてるそうなので、 ファイルにバージョン(依存性)を持たせることはしてないのだと思われる。

Long Term Support

余談

いわゆるUPSERTをしたかったのでon conflictを使った。

insert or replace into でも代用できるが、「UPDATEの場合には更新しないでほしい項目がある」 場合などには、全部上書きされてしまうので使えない。

もっと具体例をいうと、レコードに「レコード作成日」「レコード更新日」を持たせる場合に、 insert or replace into だと「レコード作成日」が上書きで消えてしまう。

トリガーで実装する方法もあるが、私の環境・プログラムだと重すぎて使いものにならなかったので、 on conflictを今回は採用した。

おしまい。

【AWS】 ALB+EC2の構成に、あとからIP固定のLBを追加する

前段

PC → ALB → EC2 

というような構成になっている場合、

ALB のIPは不定になる 。

internal-hogehoge-11111111.ap-northeast-1.elb.amazonaws.com

のようなエンドポイントにアクセスし、

AWS側が適切なIPを返すためである。

問題

透過設定やNATの関係で、

アクセスする仕組みがIPアドレスが固定であることを

前提としているような端末がある場合に,

IP不定だと一意にIPが決まらないので困る。

結論

PC → NLB → ALB → EC2 

というような通信経路にし、

NLBをIP固定で構築し、特殊PCだけNLB経由で通信させるようにする。

昔はできなかったようだが、

  • NLBで固定IPのLBが作成できるようになったこと
  • ターゲットグループに ALB を設定できるようになったこと

から、去年ごろ?(2021年?)から、この構成が実現できるようになっている。

手元の環境でも構築して確認したが、スクショ貼るのが面倒+細かい説明は参考リンクのほうが詳しいので細かい説明は割愛する。

参考

dev.classmethod.jp

dev.classmethod.jp

Chrome,Edgeなどでの個人情報自動補完(オートフィル、オートコンプリート)をWEBページ側で頑張って制御する

IEが6月で使えなくなることで、Edgeの利用率が上がってくるはずである。

今回Edgeで業務し始めたことで問題が表面化した機能があるので、全国の情シス担当のためにメモっておく。

やりたいこと

個人情報の自動補完(オートフィル)を無効化したい。

※補完にも2パターンあるらしく、下記のように単純に入力候補が出るものと、 上記のように個人情報を補完する2パターンあるらしい。

autocomplete="off"でいいじゃないか、と思った人は最後まで読んでほしい。

これで止められるのは「単純に入力候補が出るもの」だけである。

背景(なぜ問題なのか)

いわゆるECサイトなどは個人のお客様が使う仕組みなので、この機能があった方がむしろ便利なのだが、

業務システムの場合、

  • お客様の情報 (例:配送先
  • 自分や第三者の情報 (例:配送元

を同じ画面で入力する場合がある。

配送は大体同じ情報を毎回入力すると思うが、

その配送欄の候補表示を選択してしまうと、

「以前入力した配送の情報」が入力されてしまう可能性がある。

例:

事象を確認する

実験サイトを用意した。EgdeとChromeで挙動確認した。

サイト内の(1)で問題になっている挙動を確認できる。 オートフィルの挙動は先ほどGIFを貼っているので割愛する。

https://gologius.github.io/test/test_brouser_suggest/login1.html

テストするには個人情報をブラウザに登録しておかないといけないので、 予め登録しておいてほしい。

autocomplete="off" 使えば?

先行して記載しているが、個人情報のオートフィルは止められない(クソかな?)

下記で確認可能

https://gologius.github.io/test/test_brouser_suggest/login1a.html

実験して分かったこと

ポイントになるのが、Chrome、Edgeの場合は 「フォームのデフォルト値でない値が入力されていた場合は、オートフィルで上書きされない」 ようである。

要するに、「自分でなにか入力してたら、オートフィルの対象にならない」模様。

逆に言うと触らないとオートフィルの対象になるので、

WEBページ側で過去の値をセットするような機能がある場合、

オートフィルで上書きされてしまう。

挙動は実験サイトの(2)で確認できる。

https://gologius.github.io/test/test_brouser_suggest/login2.html

対応方法

下記の記事通り、autocomplete="" に毎回ランダムな値を入れてやれば解決する模様。

(2017年の記事なので探すのに苦労した)

autocomplete="off" があっても無視される場合がある (#3143032) | Webブラウザの自動入力機能を悪用して意図しない個人情報を送信させるデモ | スラド

この記事を元に対策を適用してみた。

下記URLから表示が出ないことが確認できる。

https://gologius.github.io/test/test_brouser_suggest/login3.html

なお、ランダム文字列は重複の危険性もあるので、 現在日時(yyyymmddhhmmss)値をセットするjavascriptを利用した。 コードは下記。

gologius.github.io/test.js at master · gologius/gologius.github.io · GitHub

感想

これはブラウザの挙動がよくないと思いました。

せめてautocomplete="off" を適用するようにしてほしい・・・

こうやって情シス担当の仕事がまた一つ増えるのであった・・・

おしまい。

AWS SAAを受験した話

AWS SAA(ソリューションアーキテクトアソシエイト)の試験を受けてきたので感想を書く。

※試験の内容をバラすのはNGなのでそこには触れない。

試験概要

雑に説明すると、AWSのサービスをつかっていい感じに設計できる能力があるかを測る試験。

試験自体が 「プロフェショナル>アソシエイト>ベーシック 」のランクに分かれている。

今回受けたのは「アソシエイト」

勉強方法と勉強時間

この試験のために 勉強したのは1週間程度。 ただし元々AWSでのVPCやらサーバー構築の知識はあった(作業歴:半年程度)

使ったのは下記二つの教材。

本は家にあった親父のやつを借りパクした。1日かけて読む×2週くらいした。

読んだあとはWEBサイトの過去問※を、通勤中の電車内で解いてた。

※厳密にはAWS試験の過去問は公開されていない・・・はず

試験方法

WEB上で受ける試験なので自宅などでも受けられるらしいが、大阪のテストセンターで受ける方法を選択した。 そもそも自宅で試験受けるのは気分的にもノラないので。

写真付き身分証明書が二つ必要らしく。前日に焦った。 運転免許とパスポートを使ったが、もってない人どうするんですかね・・・

着いたらあとは係の人が色々誘導してくれるので、特に心配しなくていい。

試験中

ここに書いていることなので、ブログに書いても問題ないはず。

WEB試験がSPI以来だったので新鮮だった。

持ち込めるものはほぼないと思っていい。ハンカチくらい。 メモ用紙?のようなものを貸し出ししてくれるので、 そのあたりは心配しなくていい。 ただ、もちろん画面上に直接書き込めないので、そのあたりは普段の筆記試験と勝手が違う。

特徴的な点としては、英語の原文が見れる。 意味不明な日本語訳になってることがあるので、必要であれば英語も見れる。 実際一つ意味不明な問題があったので見た。 コメントもつけれるので意見しておいた。 (コメント投下中は試験時間から除外されるらしい。フィードバックは一切ないが)

時間は2時間くらいあるが、正直1時間くらいで終わる。

結果

合格

試験終了ボタンを押した次のページで即合否が出てきた。

もう少し余韻とかあってもいいんじゃないですかね・・・

正式な連絡は後日メールで来る。昼12時に受けて夜19時くらいには来た。

感想

勉強方法について

基本的なNWやWEB関連の知識があるのであれば、基本方針としては

  • 教科書を一通り読む
  • 実際にAWSで色々触ってみる
  • 空き時間で過去問を解く

で十分だと思う。 一通り触って自分のサイトとか構築していればなお良い。 AWSを触らずにこの試験に合格することも出来るとは思うが、 意味があるのかは正直疑問。

試験対策としては、上記に挙げた教材だけだと少し危なかった。 教材の質が悪いという話ではなく、ただの勉強不足。 深い部分で理解できていないことがいくつかあるなぁと反省した。

あと、情報の鮮度はかなり気にした方がいい。 実際2021年のリリースで変わっている点も多々あった。 その点、上記の過去問サイトはコメント機能がついてて、おかしい部分はツッコミが入っているのでよかった。

試験のレベルについて

個人的にはちょうどよかったと思う。

過去問やっている時には「簡単すぎワロタ」と調子に乗っていたが、 実際受けると1.3倍くらいの難しさに感じた。 調子に乗ってはいけない。

技術営業や発注者レベルであれば、この試験で十分業務に活かせると思う。 が、この試験に受かっただけだといわゆる「完全に理解した」※の状態な気はする。 慢心せずに今後も勉学に励もうとおもった。

※元ネタ

ブラウザからアプリ(EXE)を呼び出す方法調査(プロトコルハンドラー、カスタムURLスキーム、URIハンドラー)

2023/06/24 追記 どんな方法があるの? を思いついたので追記

2021/12/13 追記 「URIハンドラー」という用語や参考リンクを追記。

2021/11/20 追記 GitHubのサンプルへのリンクを追加。

2021/11/13 追記 拡張子の関連付けの方法を追記、全体構成を加筆修正。

何がしたいの

ブラウザからクライアントアプリ(EXE)を呼び出したい。

身近な例としては メールアドレスをクリック→メーラーが起動

ってのが一番身近だと思う。

どんな方法あるの?

ActiveX経由

  • 基本的にIEしか動かない。
  • Chromeだと謎のアプリをインストールする必要がある

IEは今後サポート切れになるので没

NPAPI

→没

JavaApplet

  • Java11から廃止されている
  • 今時JavaAppletは・・・

→没

ファイルを開く

事前にファイルの関連付けを設定しておき※

ファイルをダウンロードする際に「開く」をすれば、任意のプログラムを起動できる。

コマンドプロンプト assoc - [ファイルと拡張子の関連を表示・設定する] とか

edgeの場合↓

この方法なら、ファイルにパラメータを埋め込んでおけば、 ほぼ無制限にパラメータを渡すことができる。

ただし、

  • 毎回「開く」をクリックしないといけない。
  • ダウンロードフォルダにtempファイルが一時的に溜まる
  • 操作をミスるとファイルがDLされてファイルが溜まってしまう

というデメリットが存在する

→ 実現はできるが、ベストではない感じはする。

ローカルにWEBサーバーを立てて、そのサーバー経由でアプリを呼び出す

2023/6/24追記

ローカルにWEBサーバーを立てて、

http://localhost:8080/hogehoge/fugafuga

みたいなURLをクリックすると、WEBサーバーにリクエストが飛ぶので、 それをトリガーとしてアプリを呼ぶ。

後述する方法は大量データを受け渡しができない。 この方法ならデータのやり取りはある程度自由にできるし、WEBの仕組みを全部使える。

ただし、デメリットとして、ローカルにWEBサーバーを立てる手間が発生する。 実務的に言うなら、ローカルサーバーを起動するアプリのインストーラーを配って面倒をみないといけなくなる。 あと、ポートの競合問題なども地味に面倒そう。

レジストリに追記する

レジストリを弄った後に、

<a href="mailto:hogehoge@example.com?subject=件名&amp;body=本文">メールを送信する</a>

のようなURLをクリックすると呼べるようになる。

以下参考文献

windows - ActiveXを使わずにwebブラウザ上でクライアントのexeを実行する方法 - スタック・オーバーフロー

Registering an Application to a URI Scheme (Windows) | Microsoft Learn

→ 今回の本題。以下に詳細を記載

レジストリに追記する」の具体例

サンプルサイトは下記。

クライアント呼び出し

ソース → gologius.github.io/test/blowser_to_call_exe/test1.html at master · gologius/gologius.github.io · GitHub

1)自分のPCのレジストリに下記のようなものを登録する

xyz_regist.reg

Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\xyz]
"URL Protocol"=""

[HKEY_CLASSES_ROOT\xyz\shell]

[HKEY_CLASSES_ROOT\xyz\shell\open]

[HKEY_CLASSES_ROOT\xyz\shell\open\command]
@="\"C:\\Windows\\System32\\notepad.exe\" \"%1\""

2)html内に <a href="xyz:">ここをクリックするとメモ帳が開きます</a> などと記載しておく

3)リンクをクリックする

するとメモ帳が開く

内部で何してるの?

これはプロトコルハンドラーと呼ばれる技術を使用している。 カスタムURLスキームや、URIハンドラーとも呼ばれるらしい。呼び方を統一してほしい・・・

Edgeの設定だと「アプリケーション リンク」、chromeだと「プロトコル ハンドラ」の設定が該当する。

先のメールアドレスの例だと、下記のような感じのHTMLが埋め込まれているのが一般的になる

<a href="mailto:hogehoge@example.com?subject=件名&amp;body=本文">メールを送信する</a>

コロン ':' の後に文字列をセットすると、その文字列を別アプリに渡すことができる。

別アプリに値を渡す

レジストリキーに %1 が登録されていた場合、パラメータをアプリに引き渡す

(Default) = "C:\Windows\notepad.exe" "%1"

エンコードされた文字列が連携されるため、クライアント側でデコードして処理する必要がある。 よって、文字コードなどにも注意する必要がある。

%1 以外の意味 → Windows レジストリ 解剖記: HKEY_CLASSES_ROOT - Programming Field

<a href="xyz:hogehoge">ここをクリックするとメモ帳が開きます</a>

の場合だと

C:\レジストリに登録した.exe xyz:hogehoge

のような感じでOS側でコールされる模様

サンプルサイト

下記サイトで伝送できるバイト値を変えて遊べるようにした。

クライアント呼び出し

ソース → gologius.github.io/test/blowser_to_call_exe/test2.html at master · gologius/gologius.github.io · GitHub

サンプルレジストリバッチを起動しているなら

[HKEY_CLASSES_ROOT\xyz\shell\open\command]
@="\"C:\\Windows\\System32\\notepad.exe\" \"%1\""

になってるはずなので、メモ帳が開く先のファイルパスにランダム文字列を指定することになる。

使い方は単純で、バイト数入力→反映ボタンを押すとそのバイト数に応じたURLを生成→リンククリックでメモ帳が開く

※ただしファイルパスですらないのでエラーは出る。

こんな感じのリンクが裏では作られている↓

<a id="exetarget_ex" href="xyz:5nBGDkabx9tz4eCrl4xsEjMZbzle9v">サンプルアプリxyz呼び出し バイト数指定</a>

下記はChromeの例

判明している不具合

大量のデータを伝送できない模様。 特に日本語をbase64エンコードしようものなら、バイト量が増えてアプリが開かなくなるパターンを見つけた。

ちなみに、ブラウザによって限界値が違う模様。

  • chrome → 1000バイトまではメモ帳起動する。3000バイトは起動すらしない
  • firefox → 10000バイトまではメモ帳起動する。100000バイトは起動すらしない

※厳密に何バイトまでいけるかという実験ではないのでご了承下さい。

原因考察

二つある気がしている

  • ブラウザ側の伝送制限(そうでないと、ChromeFirefoxとの挙動差の説明がつかない)
  • OS側の制限 ※1

※ 文字数制限がある。8191文字が最大っぽい。が上記の結果とつじつまが合わない・・・

コマンド プロンプトの行文字列の制限 - Windows Client | Microsoft Learn

余談

C#でつくったWindowsFormアプリに値を飛ばしてみた。 ここでもブラウザの違いが見られた。 パラメータに全角文字をそのままぶちこんで飛ばすと、

された。

Chrome

Firefox

作ったもの github.com

所感

まぁ便利なんですが、下記のようにセキュリティ的なデメリットもあります。

カスタムURLスキームの乗っ取りとその対策 - Akaki I/O

パラメータにOSコマンド入れたりすれば、OSへの攻撃もできそうですよね。

レジストリを弄ってないと開かないとはいえ、注意したほうがいいと思いました。

というか、ブラウザからOSの機能にアクセスできるってのが個人的には怖いですね。

AWS ELB +EC2で構築しているWEBサイトにて、iPhoneのSafariだけHTTP通信になる不具合

iPhoneSafariだけ挙動がおかしい。

AWSELBでHTTPSへリダイレクトしているはずなのに、HTTP通信が発生して「安全ではありません」と出る。

初回アクセスはHTTPSでアクセスするのだが、戻るボタン→再度リンクからアクセスするとHTTPになる。

キーワードだけで調べると「ELBでHTTP/2通信を無効化すればよい」的な情報にヒットしたが、 下記リンクの対象はApacheのようで、なんかひっかかるので少し調べることにした。

https://salumarine.com/only-safari-cannot-load-a-webpage/

切り分け

  • 同じドメインの別ページでは発生していない。
  • 別の開発環境でも発生していない。
  • 単体環境でも発生していない。

なので

  • 呼び出し元がおかしい
  • AWSのなにかが悪い

の二択まで絞れた

同等のサーバーを構築する

本番環境と同等の構成を作る。AWSなら証明書含めてさっさと作れるので楽。

下記のように構築した。なおELBでHTTP→HTTPSリダイレクトしている。

構築方法は別サイトや本に死ぬほどあるのでここには記載しない。

f:id:gologius:20210821124731p:plain

開発者ツールでデータを見てみる

実際に構築してみると、リダイレクトが二回発生しているのである。

f:id:gologius:20210821130104p:plain

動きとしては

  1. HTTPSの末尾スラッシュなしページをリクエス
  2. (なぜか)HTTPで末尾スラッシュありのページへリダイレクト
  3. ELBの機能で、HTTPSのスラありページへリダイレクト

呼び出しているURLを確認すると確かに、

  • 末尾スラッシュなし
  • ファイル指定が明示的にされていない

のである

/hogehoge?param1=1

他URLで確認してみる

他の指定方法で調べてみるとリダイレクトは発生しない f:id:gologius:20210821131324p:plain

/hogehoge/?param1=1
/hogehoge/index.html?param1=1

原因(推察)と対策

通信関連の推測

だいたいのサーバーは末尾にスラッシュ(トレイリングスラッシュというらしい)がないと自動リダイレクトするらしい。

URLの最後に付ける「トレイリング スラッシュ」ありなしの違いはSEOに影響するのか? | 海外SEO情報ブログ

で、APサーバー側の挙動にもよるが、おそらく下記のような感じの通信になっている模様。

リダイレクトする担当がそれぞれ違う。 f:id:gologius:20210821220746p:plain

EC2(IIS)側としてはELBに対してレスポンスを返すのだが、ELB⇔EC2間はHTTP通信になっているはず(EC2に証明書入れてないので)

よって、IISが返却するLocation属性はhttpのURLが記載されるが、結局ELBでhttpはhttpsにリダイレクトされる。

根本原因は?

正直分からないが、iPhoneSafariで「二回リダイレクトしているが、リダイレクト途中の状態のページがキャッシュされてる?」 みたいな動きになってるんじゃないでしょうか。 iPhoneの開発環境がないので推察しかできない。

対策としては明示的にスラッシュとファイル名を記載しましょう、ということになる。

/hogehoge/index.html?param1=1

明示的にというのは大事ですね。 f:id:gologius:20210821131620j:plain