Pythonを使ってDBのテーブルをGUIで簡単に操作

初めに

WEBサイトのシステムを作るにあたってテストデータを入れたかったので簡単に入れられるように作りました。

まだまだ初心者なので回りくどいやり方していたり見苦しいとこもあるかもしれませんがご了承ください。

ライブラリのインストール

今回は「pysimplegui」「mysql-connector-python」を使用しました。

pip install pysimplegui
pip install mysql-connector-python

テーブルの準備

今回はXAMPPを使用してデータベースを作りました。
商品マスタをカテゴリ分けできるようにカテゴリテーブルを作って
↓のような形にしました。

商品マスタ(商品ID, 商品名, カテゴリID, 価格)
カテゴリマスタ(カテゴリID, カテゴリ名)

create.sql

 /* カテゴリマスタ */
CREATE TABLE categoryMstr(
	categoryID INT(2) NOT NULL AUTO_INCREMENT,
	categoryName VARCHAR(30) NOT NULL,
	PRIMARY KEY(categoryID)
);

 /* 商品マスタ */
CREATE TABLE itemMstr(
	itemID INT(3) NOT NULL AUTO_INCREMENT,
	itemName VARCHAR(30) NOT NULL,
	categoryID INT(2) NOT NULL,
	amount INT(5) NOT NULL,
	PRIMARY KEY(itemID),
	FOREIGN KEY(categoryID) REFERENCES categoryMstr(categoryID)
);

コード

import PySimpleGUI as sg
import mysql.connector

HOSTIP = "127.0.0.1"
USER = "root"
PASS = ""
DATABASE = "test"

#商品保存用リスト
item_list =[]
#カテゴリ保存用リスト
category_list = []

#itemMstrからデータを取得
def selectItem():
    item_list.clear()
    cur.execute("SELECT i.itemID,i.itemName,(SELECT c.categoryName FROM categoryMstr as c WHERE c.categoryID = i.categoryID),i.amount FROM itemMstr as i")
    for i in cur.fetchall():
        item_list.append(list(i))
    return cur.fetchall()

#categoryMstrからデータを取得
def selectCategory():
    category_list.clear()
    cur.execute("SELECT * FROM categoryMstr")
    for i in cur.fetchall():
        category_list.append(i)
    return cur.fetchall()

#itemMstrにデータを挿入
def insertItem(itemName,categoryID,amount):
    try:
        with con.cursor() as cur:
            sql = "INSERT INTO itemMstr (itemName,categoryID,amount) VALUES (%s,%s,%s);"
            cur.execute(sql, (itemName,categoryID,amount))
        con.commit()
    except:
        con.rollback()
    con.commit()

#itemMstrからデータ削除
def deleteItem(deleteID):
    try:
        with con.cursor() as cur:
            sql = "DELETE FROM itemMstr WHERE itemID=%s"
            cur.execute(sql,(deleteID,))
        con.commit()
    except:
        con.rollback()
    #削除した時にauto_incrementを初期化する
    try:
        with con.cursor() as cur:
            sql = "ALTER TABLE itemMstr auto_increment = 1;"
            cur.execute(sql)
        con.commit()
    except:
        con.rollback()
    con.commit()
    
#データベースに接続
con = mysql.connector.connect(host=HOSTIP, user=USER, password=PASS,database=DATABASE)
#カーソルを準備
cur = con.cursor()
#表に値を入れるために商品を取得
selectItem()
#コンボボックスに値を入れるためにカテゴリ取得
selectCategory()

# layput部
layout_top =[[sg.Table(headings=["ID","商品名","カテゴリ","価格"],values=item_list,auto_size_columns=False,key="-tbl_01-",col_widths=[5],enable_click_events=True)],
             [sg.Submit("削除",key="-btn_del-",button_color=(0,"#f00"))]]

layout_bottom = [[sg.Text("商品名"),sg.Input(key = "-input_name-",size=(20,1))],
                 [sg.Text("カテゴリー"),sg.Combo(category_list,key="-categoryID-")],
                 [sg.Text("価格"),sg.Input(key = "-input_amount-",size=(20,1))],
            [sg.Submit("追加",key="-btn_add-")]]

layout =[[sg.Frame("商品",layout_top)],
        [sg.Frame("商品追加",layout_bottom)]]

# window部
window = sg.Window("商品管理", layout)

# event部
while True:
    event, values = window.read()

    #追加ボタンが押されたら
    if event == "-btn_add-":
        
        #商品名取得
        itemName = values["-input_name-"]
        #カテゴリID取得
        cateID = values["-categoryID-"]
        #価格取得
        amount = values["-input_amount-"]
        #入力欄をからにする
        window["-categoryID-"].update("")
        window["-input_name-"].update("")
        window["-input_amount-"].update("")
        #入力されていない欄があったら
        if cateID == "" or itemName == "" or amount == "":
            sg.popup("入力していない欄があります",title="エラー")
            print("add")
        else:
            #amountに入力されている値が数字か判断
            try:
                amount = int(amount)
                insertItem(itemName,cateID[0],amount)
                selectItem()
                #テーブルを更新
                window["-tbl_01-"].update(values = item_list)
            except:
                sg.popup("価格には数字を入力してください",title="エラー")

   #削除ボタンが押されたら
    if event == "-btn_del-":
        if values["-tbl_01-"] != []:
            #選択されているIDを取得
            itemID = item_list[values["-tbl_01-"][0]][0]
            deleteItem(itemID)
            selectItem()
            #テーブルを更新
            window["-tbl_01-"].update(values = item_list)

    #×ボタンが押されたら
    if event == sg.WIN_CLOSED:
        cur.close()
        con.close()
        break

実際の見た目

終わりに

こちらはまだupdateができないので暇があれば実装したいです。

参考