Python + PySimpleGUI+MySQLの動的SQL処理

2024年1月27日2024年1月29日

Pythonの世界で PySimpleGUI ライブラリが地味な存在なのは”デスクトップ”という要素だろうな。
つまりスマホアプリは作れないが、自分の用途には関係がないのでこれでOK。
無償提供してくれた作者さんには感謝しかありません。

テーブル検索機能実装

ようやく初期の目標が達成できた。データベース検索の基本機能を備えたデスクトップアプリケーションとなる。
まさか1月中にここまでできるとは想像もしなかったが、良くやった ➡ 自分 (´∀｀)

完成した検索用初期画面。データは大阪府の法人番号リストは約2年前にダウンロードで入手したもの。
ちょっと古くなったとはいえ、有名企業も豊富に登録されていて、モチベーション維持にも最適。
当然固有名のデータばかりだが、個人情報でもあるまいし公開情報なので遠慮なく利用している。
完成した検索プログラム演習に最適なデータで、何しろ登録数が50万件近くあるのでいじりがいがある。
プロの受託開発では大量と呼ぶには桁が違うだろうが、個人や小規模事業者の用途では十分だろう。
最新情報が取得できる API も提供されているが、それは後日のお楽しみにとっておく。

データベースは自宅の ubuntuマシンをサーバーとし、宅内LANで接続している。
サーバーといっても10年ものの旧式PCだが、名称の部分一致検索でも胸のすく超速で処理してくれる。
自分の体験範囲ではあるが、数カ所とのVPNリモート接続でもレスポンス低下の懸念はほぼない。

準備処理

import PySimpleGUI as SG
import mysql.connector
SG.theme("Default1")
ベースフォント="Helvetica"
ベースフォント="meiryo"
try:
    CN=mysql.connector.connect(
    user="paradox",host="192.168.1.20",password=パスワード,database="sample")
    print("-- MySQL接続OK ( ﾟ∀ﾟ)v --")
except:
    print("接続失敗")

ODBC 接続はそれなりに経験があるので、苦もなく書けた。

SQL の関数化

# MySQL データベースに接続
cursor=CN.cursor()
SQL_MAIN="SELECT 法人番号,名称,フリガナ,郵便番号,市区町村,会社住所,ID from v_法人番号マスタ "
def SQL更新(SQL条件):
    SQL=SQL_MAIN+SQL条件
    cursor.execute(SQL)
    print("実行したSQL文は : ",SQL)  # 削除予定
    return

大きな課題のひとつだった””SQL文生成機能の関数化”。これをメインループ外に出すことで汎用化できた。
print 文はバグチェック目的で残してあるが、安定動作すれば不要。
つまり関数のコードはわずか2行のコードで実現したのだった。
ここで例外処理を省くなんてふざけるなよと、自問自答もしているが動けばOK。(´∀｀)
いくらかつまづきはあったが、テンプレートとしてそのまま使い回せるだろう。
cursor メソッド(?)をまだ理解出来ていないためトライアンドエラーの繰り返しにはなったが、何とかここまでたどり着いた。深く学習する予定はまったくない。

カラム処理

見出=["法人番号","名称","フリガナ","郵便番号","市区町村","会社住所","ID"]
列幅 = [14,30,20,8,12,30,8]
リスト行数=20
リスト取得行数=100
リスト開始行=0

SQL更新(" limit " + str(リスト取得行数))
DATA=cursor.fetchall()  # 全レコードを取得 関数内で機能しない
CN.close

テーブルのリスト表示に合わせてカラムの設定。見出変数のリストはベースのSQL文から取得またはリスト変数で出来るはずだが、今後の課題としてお楽しみにとっておく。
後半部では関数を呼び出して必要分だけのテーブルデータを取得し、さっさと接続を閉じている。

テーブルとSQL文に依存するのはここまで。
以降のコードは他のデータベースやテーブルなどにも転用出来るのが素晴らしい。(´∀｀)

エレメント配置デザイン

L1=[
    [SG.Table(key="-リスト-",values=list(DATA),headings= 見出,col_widths=列幅,justification="left",
      row_height=24,font=(ベースフォント,11),background_color="white",alternating_row_color="lightsteelblue",
      header_text_color="white", header_background_color="navy",header_relief ="RELIEF_SOLID",
      header_font=(ベースフォント,11,"bold"),
      selected_row_colors =("white","black"),num_rows=リスト行数,
      auto_size_columns=False,enable_click_events=True)],
    [SG.Text("実行結果:"),SG.Text(key="-選択値-",size=80,font=(ベースフォント,11,"bold"),
                                   text_color="blue",background_color="lightyellow")]
    ]

テーブルの外観を設定。多数のオプションを使用しているが、一度覚えてしまえば本当にラク出来る。
PySimpleGUI はシンプル・イズ・ベストが最大のメリットであり、あまり凝ったデザインは不可。
自分の目的にはこれらで必要十分である。

L2= [[
     SG.Text("検索項目"),SG.Combo(key="-検索項目-",values=見出,size=10,default_value=見出[1]),
     SG.Input(key="-検索語-",size=20),
     SG.Text("並び順:"), SG.Combo(key="-対象列-",values=見出,size=10,default_value=見出[0]),
     SG.Radio("昇順","並び順",key="-Normal-",default=True),SG.Radio("降順","並び順",key="-desc-"),     SG.Text("表示開始行"),SG.Input(key="-リスト開始行-",size=8,default_text=0,justification="c"),
     SG.Button("<",key="-リスト前-",pad=(0,0)),SG.Button(">",key="-リスト次-",pad=(0,0)),
     SG.Button("リスト更新",key="-リスト更新-",font=(ベースフォント,11,"bold"),size=12)
    ]]

検索用エレメントのブロック。

Frame : 枠の一部使用も可

L3=[[
    SG.Push(),SG.Button("OK",key="OK",size=12,font=(ベースフォント,11),border_width=2),
     SG.Button("閉じる",key="Cancel",size=12,font=(ベースフォント,11,"bold"),border_width=2),
    ]]
    
L=L1,[SG.Frame("検索条件設定",L2,font=(ベースフォント,9),expand_x=True)],L3
# expand_x はフレーム幅をウィンドウ一杯に広げる
window=SG.Window("PySimpleGUI 練習",L,resizable=True,use_default_focus=False,font=("meiryo",10))

push : ボタンの右寄せ配置

ボタンなどのエレメント基本的には左寄せになる。好みに過ぎないが、ボタンの存在感を出したくて push() オプションを使ってみた。右寄せにしたいエレメントの直前に書けば良いだけ。
さて。本アプリを造ってみるまで、Frame エレメントの一部配置は出来ないと思い込んでいた。(;`ー´)
ところがダメ元でやってみたら実際にはいともカンタンに配置出来たのだった。
3つのレイアウトブロックをひとつにまとめる時に、枠囲みしたいレイアウト変数部分を
[SG.Frame(“検索条件設定”,L2,font=(ベースフォント,9),expand_x=True)] とするだけ。
ちょっと煩雑になっているが、見出の文字を小さくするためのオプションなので、
[SG.Frame(“検索条件設定”,L2)] でも問題なく表示できるので拡幅オプションも必須ではない。

メインループ(本体ブロック)

while True:
    event,values=window.read()
    print(event,values)
    
    if event=="Cancel":
        break
    if event=="OK":
        SG.popup("このボタンの機能はありません",auto_close=True,
                 auto_close_duration=5)
        continue    # ループに戻り、以降のプログラムを実行しない
    if "-リスト-" in event and "+CLICKED+" in event:
        列番=event[2][1]
        選択行=event[2][0]
        カラム名=見出[列番]
        選択項目値=DATA[選択行][列番]
        window["-選択値-"].update(カラム名 +" : "+ str(選択項目値))          
    #else:
        continue        
    
    if  event=="-リスト更新-":
        条件式=""
        検索項目=values["-検索項目-"]
        検索語=values["-検索語-"]
        if len(検索項目)>0 and  len(検索語)>0:
            条件式=" Where "+ 検索項目 + " Like '%"+ 検索語+"%' "
            print("検索語あり",条件式 )
                      
        try:
            条件式=条件式 + " ORDER by "+ values["-対象列-"]
            if values["-desc-"]==True:
                条件式=条件式 +" desc "     
            window["-選択値-"].update("うまく出来た")
            # 条件式 = 条件式+" limit "+  str(リスト行数)

        except:
            print("エラー（;´Д｀）")
            window["-選択値-"].update("あかん （;´Д｀）")
            continue

    # VSCエディタでは ctrl + "/" で一括コメントアウト ※テンキーの / は無効
        
    elif event=="-リスト次-":
        START=int(values["-リスト開始行-"])
        リスト開始行=START+リスト取得行数
        window["-リスト開始行-"].update(リスト開始行)   
        continue    # 数値変更だけにしてSQLを実行させない

    elif event=="-リスト前-":
        START=int(values["-リスト開始行-"])
        if START>リスト取得行数-1:
            リスト開始行=START-リスト取得行数
            window["-リスト開始行-"].update(リスト開始行)
        continue
  
    N=int(values["-リスト開始行-"])
       
    条件式 = 条件式+" limit "+ str(N) + "," + str(リスト取得行数)
    window["-選択値-"].update(条件式)
    SQL更新(条件式)
    DATA=cursor.fetchall()
    window["-リスト-"].update(values=list(DATA))
    window["-選択値-"].update(条件式)

CN.close    
SG.Window.close()

メインループのメモはぼちぼち加筆していく予定。
お疲れ様でした ➡ 自分 (´∀｀)

よかったらシェアしてね！