おじさんのSQL

DBは業務の整理に使える？

おじさんはややこしいことが大嫌いで全てのことをシンプルに整理して考えたいと思っています。なので、内容は全く理解できませんでしたが、satさんの以下Tweetに衝撃を受けました。

ややこしい仕事も1対多の関係に落とし込んで整理してけば関係が随分スッキリまとめられる。

これを使いやすい形で使えるように出来るのがDBの特性の一つだけど、うちの営業ココ理解しようとしないからExcelでややこしいコトをややこしいままデータ作ろうとするから進歩がない。

— sat (@sat01281) 2023年4月19日

satさんにはご迷惑だかもしれませんが、おじさんはSQLを勉強する宣言をReTweetしました。するとsatさんから以下のコメントをいただきました。

コメントありがとうございます。

必要に迫られて覚えた所はありますが、正規化とリレーションによるデータ整理の仕方を覚えた事は、仕事の組み立てや実際にデータを利用する事を含めて随分役に立っていると感じます。

専門ではないので作れる物はしれていますが、考え方を学ぶのは有益と思いますよ！

— sat (@sat01281) 2023年4月28日

satさんのSQL周りのコメントにいいねを付けて、意味が理解できるようになるまでSQLを勉強しようと思います。今は正規化やリレーションのところにようやく入ったところで、だいぶ物事をシンプルにできる実感を得たところです。

SQLの構文でデータを引っ張ってこれることはOK分かりました。DBを分けておくと情報更新もめっちゃくちゃ楽になるということが、理解できました。プログラミングでも、パーツ分けすることでコードの変更が楽になるんだなと、別の気づきもありました。

ただ、おじさんはまだ、肝心のDBはどうやって作成するのかを勉強する必要があります。ぐちゃぐっちゃの情報をDB化する方法を模索してみます。

SQLというかDBの業務への落とし込み方を習得できれば嬉しいです。

考え方を少し変えるとプログラミングで挫折はなくなります。

ChatGPTで自動化のその後

ChatGPTは凄い風

おじさんは先日ChatGPTの記事を書きました。

it.ojisan999.com

この記事の中でも書きましたが、ChatGTPは間違えることもあります。かなりそれっぽい回答を自信満々に出してきます。これ、プログラミングの知見がないと気が付けないってことがわかりました。

その時の記事では数百にもなるExcelファイルの特定列の値を抽出したのですが、重複があるので、10数個の値かなと想定していました。ちょうとそれぐらいの結果が出たので、うまく行ったと思っていたのですが、どうも、結果に不信感がありました。記事の中での１つ目のコードと２つ目のコードでも同じ値が１つもありません。

私たちはどんな手を使っても自動化・効率化できればよいという精神ではありますが、誤りに気が付かずに使ってしまうのは危険だと感じました。ChatGTPの私たちなりの使い方を模索していきたいと思います。

これはいけません。やり方を変える事にしました。時間が取れなくて修正までは至っていませんが、この後はGoogleで検索し、内容を理解した上でコードを書いていこうと思っています。これが成長のチャンスですね！

こういうトライ＆エラーで少しずつpythonを理解していくのが、挫折のないプログラミングのやり方です。また、深追いしないことも挫折のないプログラミングのやり方ですサッと諦めて、次の手を考えましょう。

考え方を少し変えると、プログラムに挫折というものはなくなります。

Excelでの効率化・自動化

Excelの効率化・自動化はVBAだけじゃない

内容によってはVBAを使用するまでもないことがあります。

システムからcsvやxlsxファイル等の生データを取得し、整形する場合、VBAを使用したくなります。おじさんもこれらを一生懸命VBAで行おうとしてました。おじさんはプログラミングが苦ではないので、まだ良いですが、これを他の人にもやってもらわなくてはいけませんでした。そんな時に先輩からコメント頂いた「それって関数でできるんじゃない？」という言葉に目から鱗が落ちました。

順番を変えるだけなら＝L3のような参照機能を使えばシンプルに解決できます。

元データ

並べ替え

COUNTIF関数やIF関数で条件にマッチしているセルをカウントしたり、表示させる文字を変えたりできます。

COUNTIF

おじさんは皆さんにVBAの敷居の高さのせいで効率化・自動化を諦めて欲しくありません。自分の中のプログラミング像に固執していると効率化・自動化はなかなか進みません。できることからやれば良いんです。順番変えた後、罫線引くのはVBAでやってみるなど、少しずつチャレンジのステップを上げていくのが良いと思います。おじさんは成功体験を自分の中で重ねていって、少しずつレベルアップするのが一番効率がよかったです。

考え方を少し変えると、プログラムに挫折というものはなくなります。

プログラミングが出来るになるまで

プログラミングが出来るってどういうこと？

おじさんはプログラミングが出来るようになりました。プログラミングの出来る・出来ないは本人次第です。１つでもプログラムを作ったら、実は後は本人が「私はプログラミングが出来る」って、言えばいいだけなんです。

プログラミングが得意な方にできる人とできない人の違いをヒアリングしてみますと、「とにかく、やるかやらないかだ」という回答が多く返ってきます。おじさんはこれはできる人の理論だなと思っていました。でもプログラミングの実力も相対的なものなんですよね。深く突き詰めると終わりは見えませんが、おじさんは実用的なプログラムを作ることができたら、それはプログラミングが出来るということだと思います。

おじさんのプログラム遍歴

おじさんは小学生の時にストレージがカセットテープになっているBasicを打ち込めるパソコンが家にありましたが、小学生には敷居が高く、1番最初のプログラミング挫折を味わいました。
高校生の時にWindows3.1向けのVisualC++をお年玉で購入し、当時は自分に合う書籍に出会うことができず、再度、プログラミング挫折を味わいました。
大学でも一度Delpiとかいう言語にチャレンジしましたが、挫折しました。おじさんは大卒で就職ができなかったため、改めてIT系の専門学校に通いました。そこでもC言語を授業でやりましたが、ちょっとでも誤るとうまく動作せず、いまいちどこが誤っているのかもよく理解でず、イライラしながらプログラミングをしていました。
それでも、C言語を習得したいと思いましたので、授業では一番下の難易度の本を使用していましたが、自分は一番難しい本まで進めるのだと、数冊本を買い込みました。結局、おじさんは授業で使用していた本で挫折しました。
就職してからも、専門時代に購入したCの本をやってみたり、VBAの本を購入してやってみたり、pythonの本を購入してやってみたりしましたがやはり挫折しました。一方で業務の中ではスクリプトというのでしょうか、バッチファイルやPowerShellで簡単なファイル操作をしたり、VBAで記録したマクロを少し手直したりして、自動化・効率化のようなことをしていました。

簡単なものはVBAで組んでいましたが、自分にプログラミングが出来るという認識はありませんでした。それはプログラミングが出来る人は何も参照せずにプログラミングしているとか、書籍の内容は全て理解しているとか、自分の中で作った幻想がそう思わせていました。

まとめ

かようにプログラミング学習には終わりがなく、逆に本が１冊終われば何か作れるようになるというわけでもありません。挫折の繰り返しです。だから、簡単な構文を理解したら、作りたいアプリ自動化・効率化プログラムを書いてしまうというのが、おじさんの見つけた正解でした。習得しようとするから辛くなるんです。ある程度わかったら調べながら作ってしまいましょう。

考え方を少し変えると、プログラムに挫折というものはなくなります。

ChatGPTで自動化

ChatGPTは凄い

おじさんは仕事で、フォルダの中に無数にある同じフォーマットのエクセルファイルの特定列にある値にどんな種類があるを知る必要がありました。
パッとイメージすると、全部のエクセルファイルを開いて、特定列を１つずつコピーし、新たなエクセルファイルに貼り付けるのを繰り返し、最後に重複データを削除すればできそうです。

今回おじさんはこれを自動化してみました。プログラムはChatGPTにpythonで作って頂きました。

下のサイトでユーザ登録してログインします。

openai.com

以下のような質問をしてみました。おじさんは列と行を間違えて聞いてます。

ChatGPTに聞いた履歴

その結果、こんなプログラムを教えてくれました。

import os
import pandas as pd

# フォルダ内の全てのExcelファイルのパスを取得する
folder_path = "フォルダのパス"
files = os.listdir(folder_path)
excel_files = [f for f in files if f.endswith(".xlsx")]

# 抽出したい列の番号を指定する
col_num = 2

# 抽出した値を格納するリストを初期化する
values = []
for file in excel_files:
    # Excelファイルを読み込む
    df = pd.read_excel(os.path.join(folder_path, file), sheet_name="シート名")
    
    # 特定の列の値を抽出する
    value = df.iloc[0, col_num]
    
    # 重複する値はいらないため、リストに追加する前に重複をチェックする
    if value not in values:
        values.append(value)

フォルダパス、シート名、列番号を記載し、このまま実行可能でした。コメントまでつけてくれます。1月前より進化している印象を受けました。何をしたいか質問するだけでちゃんと動作するプログラムを提示してくれます。無料で使えますので、これは使わない手はないですね。

 その後、改めて実行結果を見ると、誤りに気がつきました。これでは１行目しか抽出されません。

    # 特定の列の値を抽出する
    value = df.iloc[0, col_num]

こういう時は回答の再生成です。Regenerate　responseをクリックします。

回答の再生成

import os
import pandas as pd
# フォルダ内の全てのExcelファイルのパスを取得する
folder_path = "フォルダのパス"
files = os.listdir(folder_path)
excel_files = [f for f in files if f.endswith(".xlsx")]
# 重複を除いた値を格納するセット
values_set = set()
# Excelファイルを読み込み、特定の列の値をセットに追加する
for file in excel_files:
    df = pd.read_excel(os.path.join(folder_path, file))
    values = df['列名'].tolist()
    values_set.update(set(values))
print(values_set)

よりシンプルになりました。こういうトライ＆エラーで少しずつpythonを理解していくのが、挫折のないプログラミングのやり方です。

考え方を少し変えると、プログラムに挫折というものはなくなります。

Pythonプログラムのexeファイル化

Pythonはコンパイルできる

Pythonはインタープリタ方式のプログラムのため、そのままだと実行するPCにもPythonをインストールする必要があります。でも、PythonプログラムはPyinstallerというツールでexeファイル化できますので、Pythonをインストールしていない同僚にも自分の自動化プログラムを渡すことができます。

Pyinstallerを開発環境にインストールします。

root@acfc9c090855:/tmp# pip install pyinstaller
Collecting pyinstaller
Downloading pyinstaller-5.9.0-py3-none-manylinux2014_x86_64.whl (650 kB)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 650.4/650.4 kB 11.6 MB/s eta 0:00:00
Requirement already satisfied: setuptools>=42.0.0 in /usr/local/lib/python3.11/site-packages (from pyinstaller) (67.6.1)
Collecting altgraph
Downloading altgraph-0.17.3-py2.py3-none-any.whl (21 kB)
Collecting pyinstaller-hooks-contrib>=2021.4
Downloading pyinstaller_hooks_contrib-2023.1-py2.py3-none-any.whl (256 kB)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 256.8/256.8 kB 40.6 MB/s eta 0:00:00
Installing collected packages: altgraph, pyinstaller-hooks-contrib, pyinstaller
Successfully installed altgraph-0.17.3 pyinstaller-5.9.0 pyinstaller-hooks-contrib-2023.1
WARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv

コマンドでコンパイルします。

pyinstaller プログラムファイル名 --onefileコマンドでPythonプログラムをコンパイルします。

root@acfc9c090855:/tmp# pyinstaller hello.py --onefile
159 INFO: PyInstaller: 5.9.0
160 INFO: Python: 3.11.2
：略
15059 INFO: Building EXE from EXE-00.toc completed successfully.

今いるフォルダ(カレントフォルダ)のdistフォルダにexeファイルが生成されます。

root@acfc9c090855:/tmp# ls dist
hello.exe

生成されたファイルが実行できるPCはコンパイルしたPCのOSと同じものでなくてはいけません。はWindowsで生成したならWindows、MACで生成したならMACでしか実行できませんが、Pythonをインストールするよりはずっと制約が少ないと思います。

Pyinstallerでコンパイルするとファイルが大きくなるみたいですが、大きくても良いんです。昨今では大概の企業がファイサーバやクラウドでファイルの受け渡し可能だと思います。

考え方を少し変えると、プログラムに挫折というものはなくなります。

Dockerの利用

Dockerは難しくありませんでした

開発環境を準備するときPythonの環境準備のようにPCに直接環境を準備する方法もありますが、うまくできないことがあります。色々触っている間にPC自体がおかしくなってしまうこともあります。
おじさんはPCが汚れるのがあまり好きではありません。こういう人にはDockerがとても向いています。Dockerとは仮想環境を提供するものです。下記のようにPCの中にくじら(Docker)がいて、くじらが餌(image)を食べて、その餌を栄養にしてくじらの中で仮想環境(Container)が動くイメージです。

よくわからないと思うので、PCの中で仮想PCが動くと思えば良いです。

おじさん的Docker

本当はこれだと仮想PCの部類に入ってしまい、Dockerとは違うらしいのですが、ユーザの知識としてはおじさんはどっちでも良いと思います。

仮想環境に色々インストールして、もし壊れてしまったら、その仮想環境は削除すれば良いんです。勉強では色々と触りたいものですから、勉強にもってこいですね。

業務PCではもしかするとDockerのインストールはできないかもしれません。でももし、インストールできたら、業務PC自体には影響を与えずに、開発環境が準備できますので、良いと思います。

Dockerの実演を少々

動画で実演してみました。

www.youtube.com

イメージにはDockerが公式で準備しているものがあります。これをそのまま使用しても十分なのですが、何度もやり直しで同じ環境を作るなんてことがある場合、イメージをカスタマイズすると便利です。今回はDockerが準備しているイメージをカスタマイズしてイメージを作成してみました。

イメージをカスタマイズする場合、Dockerfileというものを作成します。

Dockerfileの例：

FROM python　　　　　　　　　　　#①

RUN pip install --upgrade pip　　　#②
RUN pip install pandas_datareader
RUN pip install mplfinance

Dockerfileを解説します

①FROMはベースとなるイメージを指定します

DockerのイメージはDocker Hubに置いてあります。pythonというのはこれです。

②RUNはイメージをカスタマイズする際に実行するコマンドを記載します
例では下記をインストールしています
pandas_datareader
mplfinance

Dockerfileの準備ができたら、docker build -t コンテナ名 Dockerfile保存場所コマンドを実行します

user@usernoMacBook-Pro Python % docker build -t python3 .
[+] Building 0.3s (8/8) FINISHED                                                                                                     
 => [internal] load build definition from Dockerfile   0.1s
 => => transferring dockerfile: 73B   0.0s
 => [internal] load .dockerignore   0.0s
 => => transferring context: 2B    0.0s
 => [internal] load metadata for docker.io/library/python:latest  0.0s
 => [1/4] FROM docker.io/library/python  0.0s
 => CACHED [2/4] RUN pip install --upgrade pip  0.0s
 => CACHED [3/4] RUN pip install pandas_datareader   0.0s
 => CACHED [4/4] RUN pip install mplfinance   0.0s
 => exporting to image   0.0s
 => => exporting layers   0.0s
 => => writing image sha256:9d1e9782a0e9ba734d2298245d4a3~   0.0s
 => => naming to docker.io/library/python3     0.0s
Use 'docker scan' to run Snyk tests against images to find vulnerabilities and learn how to fix them

docker image lsコマンドでPCに保存されているイメージを確認します。
python3が保存されています

user@usernoMacBook-Pro Python % docker image ls
REPOSITORY          TAG       IMAGE ID       CREATED       SIZE
python3             latest    9d1e9782a0e9   3 days ago    1.24GB
python              latest    a8405b7e74cf   9 days ago    921MB
docker101tutorial   latest    6ef1a9b77478   6 weeks ago   47MB

docker run -it -d -p コンテナのポート番号:PCのポート番号 -v コンテナにアクセスさせるPCのフォルダ:コンテナのフォルダ --name コンテナ名 イメージ名コマンドで、作成したイメージからコンテナを起動します。

user@usernoMacBook-Pro Python % docker run -it -d -p 18080:8080 -v /Users/user/Documents/GitHub/Python/src:/mnt --name python-c python3
acfc9c09085575e5682321826c852cca48eccf89418a6dbd6e05e23260df36b8

docker ps -aコマンドでコンテナの状態を確認します。
python-cはUPしています

user@usernoMacBook-Pro Python % docker ps -a
CONTAINER ID   IMAGE     COMMAND     CREATED          STATUS          PORTS                     NAMES
acfc9c090855   python3   "python3"   23 seconds ago   Up 21 seconds   0.0.0.0:18080->8080/tcp   python-c

docker exec -it コンテナ名 指定シェル コマンドでコンテナにアクセスします

user@usernoMacBook-Pro Python % docker exec -it python-c bash
root@acfc9c090855:/# pwd
/
root@acfc9c090855:/# ls
bin  boot  dev  etc  home  lib  lib64  media  mnt  opt  proc  root  run  sbin  srv  sys  tmp  usr  var
root@acfc9c090855:/# cd /mnt
root@acfc9c090855:/mnt# pwd
/mnt
root@acfc9c090855:/mnt# ls -l
total 4
-rw-r--r-- 1 root root 24 Mar 20 06:24 hello.py

フォルダに用意してあるhello.pyを実行します。

hello.py
print('hello python!!!')

root@acfc9c090855:/mnt# python hello.py 
hello python!!!

エディタでhello.pyを変更します。

hello.py
print('hello python')　#　←!!!を削除

root@acfc9c090855:/mnt# python hello.py 
hello python

とても簡単にpythonの開発環境が準備できました。この環境はdocker run -it -d -p コンテナのポート番号:PCのポート番号 -v コンテナにアクセスさせるPCのフォルダ:コンテナのフォルダ --name コンテナ名 イメージ名コマンドのコンテナ名を変更すれば何個も作成することができます。色々試しておかしくなってしまったら、コンテナを削除すればまた環境をリセットすることができます。もし、使っていて必要なソフトウェアが発生した場合は、Dockerfileに記載して、イメージファイルを作り直すと、以降のリセット時に便利になります。

おじさんでもできましたので、Dockerは全く難しくありませんでした。

考え方を少し変えると、プログラムに挫折というものはなくなります。