[Python] tkinter 를 이용한 GUI 개발 #02

개발목표

1. Config 파일(ini) 에서 접속 정보를 Loading 해서 접속 정보를 보여줌.

2. 여러 Database 접속 테스트

- Postgresql 을 중심으로 개발 했음.

- H2 는 접속 테스트만 진행함. ( JDBC 를 이용한 접속 테스트 )

2. 접속 후 접속성공/실패 메시지 출력

3. 메시지가 닫히면, 해당 Database 에 접속해서 Table 목록을 보여준다.

4. 테이블을 더블클릭하면 테이블의 상세정보를 보여준다.

개발환경

Python : 3.9.9 -> 3.10.1 에서 다운그레이드 함.

Tools : Pycharm

Database : Postgresql

               H2 - JDBC 를 이용한 접속 테스트

---

https://hermeslog.tistory.com/541

[SpringBoot] PostgreSQL 연결하기

https://hermeslog.tistory.com/550?category=302345

[H2] H2 DB 설치

https://hermeslog.tistory.com/561

[Exception] error: Microsoft Visual C++ 14.0 or greater is required. Get it with "Microsoft C++ Build Tools": https://visualstud

---

1. Database 선택

2. 선택된 Database의 접속 정보를 Config 파일에서 Loading 후 보여준다.

3. 확인을 클릭하면 접속성공/실패 메시지가 오픈된다.

4. 접속 성공 메시지를 확인하면, 해당 접속 Database의 테이블 목록을 보여준다.

5. 해당 테이블의 버블클릭하면 테이블정보를 상세하게 보여준다.

---

# main.py
import tkinter as tk
from tkinter import ttk

from sub.choice_database import ChoiceDatabase
from sub.postgresql_config import PostgresqlConfig


class MainApp(tk.Frame):
    def __init__(self, parent, *args, **kwargs):
        tk.Frame.__init__(self, parent, *args, **kwargs)
        self.tree_1 = None
        self.tree_2 = None
        self.database = None
        self.parent = parent
        parent.resizable(True, True)
        parent.geometry('1000x700+100+100')

        popup = ChoiceDatabase(self)
        popup.choice_database()

    def on_double_click(self, event):
        item = self.tree_1.selection()
        for i in item:
            print("you clicked on", self.tree_1.item(i, "values")[0])

            rows = self.database.get_column_list(self.tree_1.item(i, "values")[0])
            for row in rows:
                print(row)
                self.tree_2.insert("", "end", values=row)

    # todo 일단 Postgresql에만 연결.
    def set_data_info(self, ip, port, user_id, pass_word, nm):
        # Main Frame 생성
        top_frame = tk.Frame(self.parent, borderwidth=5)
        top_frame.pack(side="top", fill="both")
        # Main Frame > Left Frame 생성
        left_frame = tk.Frame(top_frame, borderwidth=5)
        left_frame.pack(side="left", fill="none")

        self.tree_1 = ttk.Treeview(left_frame, columns=["1"], displaycolumns=[1], show='headings')
        self.tree_1.heading(1, text="Table")
        self.tree_1.column(1, anchor="w")

        self.tree_1.pack()

        self.database = PostgresqlConfig(ip, port, user_id, pass_word, nm)
        rows = self.database.get_table_list()
        for row in rows:
            print(row)
            self.tree_1.insert("", "end", values=row)

        self.tree_1.bind("<Double-1>", self.on_double_click)

        # Main Frame > Right Frame 생성
        right_frame = tk.Frame(top_frame, borderwidth=5)
        right_frame.pack(side="right", fill="none")

        self.tree_2 = ttk.Treeview(right_frame, columns=["1", "2", "3", "4", "5"], displaycolumns=[1, 2, 3, 4, 5],
                                   show='headings')
        self.tree_2.heading(1, text="No")
        self.tree_2.column(1, anchor="w")
        self.tree_2.heading(2, text="Column")
        self.tree_2.column(2, anchor="w")
        self.tree_2.heading(3, text="Type")
        self.tree_2.column(3, anchor="w")
        self.tree_2.heading(4, text="Length")
        self.tree_2.column(4, anchor="w")
        self.tree_2.heading(5, text="Comment")
        self.tree_2.column(5, anchor="w")
        self.tree_2.pack()

    # Main 창 닫기
    def main_destroy(self):
        self.parent.destroy()


# 함수 선언과 구분을 위해 2줄 띄워야 함.
if __name__ == "__main__":
    main = tk.Tk()
    # main.title = "테이블 정보"
    MainApp(main).pack()
    main.mainloop()

# sub.choice_database.py

import tkinter as tk
from tkinter import messagebox
from tkinter import ttk

from sub.h2_config import H2Config
from sub.postgresql_config import PostgresqlConfig
from sub.ini_config import *

# Radiobutton Globals
var_db_1 = "Postgresql"
var_db_2 = "H2"
var_db_3 = "Maria"


class ChoiceDatabase:

    def __init__(self, parent, *args, **kwargs):
        self.myParent = parent
        self.sub_win = tk.Toplevel()
        self.sub_win.resizable(False, False)
        self.sub_win.title('Database 선택')

        self.frm_main = tk.Frame(self.sub_win, borderwidth=10, width=250)
        self.frm_main.pack(side="top", fill="both")

    # Database 선택
    def choice_database(self):

        """
        버튼 영역
        """
        # 종료 버튼 클릭
        def on_close():
            yn = messagebox.askquestion(title="Exit Application", message="프로그램을 종료 하시겠습니까 ?.", parent=self.sub_win)
            if yn == 'yes':
                self.myParent.main_destroy()
            else:
                return

        # 확인 버튼 클릭
        def on_confirm():
            if not str_ip.get():
                messagebox.showwarning(title="경고", message="Host IP 은(는) 필수 입력 항목 입니다.", parent=self.sub_win)
                return
            if not str_port.get():
                messagebox.showwarning(title="경고", message="Port 은(는) 필수 입력 항목 입니다.", parent=self.sub_win)
                return
            if not str_id.get():
                messagebox.showwarning(title="경고", message="User ID 은(는) 필수 입력 항목 입니다.", parent=self.sub_win)
                return
            if not str_pw.get():
                messagebox.showwarning(title="경고", message="Password 은(는) 필수 입력 항목 입니다.", parent=self.sub_win)
                return
            if not str_nm.get():
                messagebox.showwarning(title="경고", message="DB Name 은(는) 필수 입력 항목 입니다.", parent=self.sub_win)
                return

            var_rdo_sel = rdo_db_type.get()
            test = None
            if var_rdo_sel == 1:
                test = PostgresqlConfig(str_ip.get(), str_port.get(), str_id.get(), str_pw.get(), str_nm.get())
            elif var_rdo_sel == 2:
                test = H2Config(str_ip.get(), str_port.get(), str_id.get(), str_pw.get(), str_nm.get())
            elif var_rdo_sel == 3:
                # todo test = MariaConfig(str_ip.get(), str_port.get(), str_id.get(), str_pw.get(), str_nm.get())
                messagebox.showinfo(title="메시지", message="미구현", parent=self.sub_win)
                return

            yn = test.connection_test()
            if yn:
                messagebox.showinfo(title="메시지", message="접속 성공.", parent=self.sub_win)
                self.myParent.set_data_info(str_ip.get(), str_port.get(), str_id.get(), str_pw.get(), str_nm.get())
                self.sub_win.destroy()
            else:
                messagebox.showinfo(title="메시지", message="접속 실패.", parent=self.sub_win)
                return

        btn_confirm = tk.Button(self.frm_main, text="확인", overrelief="solid", width=15, command=on_confirm)
        btn_confirm.pack(side="top", fill="none", anchor="e")
        btn_close = tk.Button(self.frm_main, text="종료", overrelief="solid", width=15, command=on_close)
        btn_close.pack(side="top", fill="none", anchor="e")

        '''
        DB 를 선택한다.
        '''
        # We are creating a container frame to hold all other widgets
        mighty1 = ttk.LabelFrame(self.frm_main, text=' Database 선택 하세요 ')
        mighty1.pack(side="top", fill="both", padx=5, pady=10, ipadx=10, ipady=5)

        # Radiobutton Callback
        def radio_call():
            var_rdo_sel = rdo_db_type.get()
            db_info = None
            if var_rdo_sel == 1:
                db_info = get_ini_config(var_db_1)          # config - Database 정보 Call
            elif var_rdo_sel == 2:
                db_info = get_ini_config(var_db_2)          # config - Database 정보 Call
            elif var_rdo_sel == 3:
                db_info = get_ini_config(var_db_3)          # config - Database 정보 Call

            str_ip.set(db_info["ip"])
            str_port.set(db_info["port"])
            str_nm.set(db_info["name"])
            str_id.set(db_info["user"])
            str_pw.set(db_info["passwd"])

        # create three Radio buttons using one variable
        rdo_db_type = tk.IntVar()

        rad1 = tk.Radiobutton(mighty1, text=var_db_1, variable=rdo_db_type, value=1, command=radio_call, width=20)
        rad1.grid(column=0, row=0)

        rad2 = tk.Radiobutton(mighty1, text=var_db_2, variable=rdo_db_type, value=2, command=radio_call, width=20)
        rad2.grid(column=1, row=0)

        rad3 = tk.Radiobutton(mighty1, text=var_db_3, variable=rdo_db_type, value=3, command=radio_call, width=20)
        rad3.grid(column=2, row=0)

        '''
        DB 정보를 등록한다.
        '''
        # We are creating a container frame to hold all other widgets
        mighty2 = ttk.LabelFrame(self.frm_main, text=' Database 정보 ')
        mighty2.pack(side="top", fill="both", padx=5, pady=5, ipadx=10, ipady=10)

        lbl_temp_1 = ttk.Label(mighty2, width=20)
        lbl_temp_1.pack(side="left")
        lbl_temp_2 = ttk.Label(mighty2)
        lbl_temp_2.pack(side="left")

        # Modify adding a Label using mighty as the parent instead of win
        lbl_ip = ttk.Label(lbl_temp_1, text="Host IP : ")
        lbl_ip.pack(side="top", anchor="e", padx=5, pady=5)
        lbl_port = ttk.Label(lbl_temp_1, text="Port : ")
        lbl_port.pack(side="top", anchor="e", padx=5, pady=5)
        lbl_nm = ttk.Label(lbl_temp_1, text="DB Name : ")
        lbl_nm.pack(side="top", anchor="e", padx=5, pady=5)
        lbl_id = ttk.Label(lbl_temp_1, text="User ID : ")
        lbl_id.pack(side="top", anchor="e", padx=5, pady=5)
        lbl_pw = ttk.Label(lbl_temp_1, text="Password : ")
        lbl_pw.pack(side="top", anchor="e", padx=5, pady=5)

        # Adding a Textbox Entry widget
        str_ip = tk.StringVar()
        str_ip_entered = ttk.Entry(lbl_temp_2, textvariable=str_ip, width=40)
        str_ip_entered.pack(side="top", fill="both", padx=5, pady=5)
        str_port = tk.StringVar()
        str_port_entered = ttk.Entry(lbl_temp_2, textvariable=str_port, width=40)
        str_port_entered.pack(side="top", fill="both", padx=5, pady=5)
        str_nm = tk.StringVar()
        str_nm_entered = ttk.Entry(lbl_temp_2, textvariable=str_nm)
        str_nm_entered.pack(side="top", fill="both", padx=5, pady=5)
        str_id = tk.StringVar()
        str_id_entered = ttk.Entry(lbl_temp_2, textvariable=str_id)
        str_id_entered.pack(side="top", fill="both", padx=5, pady=5)
        str_pw = tk.StringVar()
        str_pw_entered = ttk.Entry(lbl_temp_2, textvariable=str_pw)
        str_pw_entered.pack(side="top", fill="both", padx=5, pady=5)

        self.sub_win.attributes('-topmost', 'true')
        self.sub_win.grab_set()

# sub.database_config.py

import sys

sys.path.insert(0, '/dbconfig')  # Database 접속정보가 담긴 Config 파일을 저장한 폴더 경로
import dbconfig as config  # 나의 Database 접속정보를 가져온다.


def get_db_info(db_name):
    return {"Maria": config.Maria, "Postgresql": config.Postgresql}.get(db_name, config.nodb)

C:\dbconfig\dbconfig.ini

[Postgresql]
name = springboot
ip = localhost
user = hermeswing
passwd = pass
port = 5432
charset = utf8

# sub.postgresql_config.py
# https://wiki.python.org/moin/DbApiModuleComparison
import psycopg2 as psycopg2


class PostgresqlConfig:

    def __init__(self, host, port, user, pw, name):
        self._host = host
        self._port = port
        self._user = user
        self._pw = pw
        self._name = name

    # Postgresql Connection
    def get_connection(self):
        try:
            info = "host=" + self._host + " dbname=" + self._name + " user=" + self._user + " password=" + self._pw
            conn = psycopg2.connect(info)
            return conn
        except (Exception, psycopg2.Error):
            return False

    # Postgresql 접속 테스트
    def connection_test(self):
        print('[PostgresqlConfig > connection_test]')
        try:
            conn = self.get_connection()
            conn.close()
            return True
        except (Exception, psycopg2.Error):
            return False

    # Postgresql Table List
    def get_table_list(self):
        print('[PostgresqlConfig > get_table_list]')
        conn = None
        cur = None
        try:
            conn = self.get_connection()
            cur = conn.cursor()  # 2. 커서 생성 (트럭, 연결로프)
            sql = 'SELECT RELNAME AS TABLE_NAME' \
                  '  FROM PG_STAT_USER_TABLES'
            cur.execute(sql)
            rows = cur.fetchall()

            return rows

        except (Exception, psycopg2.Error):
            return False
        finally:
            cur.close()
            conn.close()

    # Postgresql Column List
    def get_column_list(self, table_name):
        print('[PostgresqlConfig > get_column_list]')
        conn = None
        cur = None
        try:
            conn = self.get_connection()
            cur = conn.cursor()  # 2. 커서 생성 (트럭, 연결로프)
            sql = "SELECT ORDINAL_POSITION" \
                  "     , COLUMN_NAME" \
                  "     , DATA_TYPE" \
                  "  FROM INFORMATION_SCHEMA.COLUMNS" \
                  " WHERE TABLE_CATALOG = %s" \
                  "   AND TABLE_NAME    = %s" \
                  " ORDER BY ORDINAL_POSITION"
            data = (self._name, table_name)
            cur.execute(sql, data)
            rows = cur.fetchall()

            return rows

        except (Exception, psycopg2.Error):
            return False
        finally:
            cur.close()
            conn.close()

# sub.h2_config.py
# https://wiki.python.org/moin/DbApiModuleComparison
import jaydebeapi

import utils.logger as logger


class H2Config:

    def __init__(self, host, port, user, pw, name):
        self.logger = logger.logger_with_name("MainThread")

        self._host = host
        self._port = port
        self._user = user
        self._pw = pw
        self._name = name

    # h2 접속 테스트
    def connection_test(self):
        self.logger.info('[H2Config > connection_test]')
        try:
            driver = "org.h2.Driver"
            jar = "c:/Temp/h2-1.4.199.jar"
            conn = jaydebeapi.connect(driver, "jdbc:h2:tcp://localhost/~/jpashop", ["sa", "sa"], jar)
            conn.close()
            return True
        except jaydebeapi.DatabaseError as e:
            self.logger.error('[H2Config > connection_test]' + str(e))
            return False

# sub.ini_config.py

# python 2.x
# from ConfigParser import SafeConfigParser
# config = SafeConfigParser()

# python 3.x
from configparser import ConfigParser, NoSectionError

config_path = '/dbconfig/dbconfig.ini'
config = ConfigParser()
config.read(config_path, encoding='utf-8')


# 설장파일 색션 확인
def get_sections():
    return config.sections()

#
def get_ini_config(db_name):
    rtn_map = None
    try:
        rtn_map = {"name": config.get(db_name, "name"), "ip": config.get(db_name, "ip"),
                   "user": config.get(db_name, "user"), "passwd": config.get(db_name, "passwd"),
                   "port": config.get(db_name, "port"), "charset": config.get(db_name, "charset")}
    except NoSectionError:
        rtn_map = {"name": "", "ip": "", "user": "", "passwd": "", "port": "", "charset": ""}

    return rtn_map


class SaveConfig:
    def __init__(self, section, name, ip, user, pw, port, charset):
        self.section = section
        self.name = name
        self.ip = ip
        self.user = user
        self.pw = pw
        self.port = port
        self.charset = charset

    def save_ini_config(self):
        config.add_section(self.section)
        config.set(self.section, 'name', self.name)
        config.set(self.section, 'ip', self.ip)
        config.set(self.section, 'user', self.user)
        config.set(self.section, 'passwd', self.pw)
        config.set(self.section, 'port', self.port)
        config.set(self.section, 'charset', self.charset)

        with open(config_path, 'w', encoding='utf-8') as f:
            config.write(f)

# utils.logger.py
import logging
import traceback
import time
import random


MAX_RETRY = 20


def logger_with_name(name):
    extra = {'thread_name': name}
    logger = logging.getLogger(name)
    logger.setLevel(logging.DEBUG)
    sh = logging.StreamHandler()
    formatter = logging.Formatter('%(asctime)s - %(levelname)s - %(thread_name)s: %(message)s')
    sh.setFormatter(formatter)
    logger.addHandler(sh)
    logger = logging.LoggerAdapter(logger, extra)
    return logger


def try_execution(f, operation, logger, default_result=None):

    for i in range(MAX_RETRY):
        try:
            return f()
        except:
            wait_time = random.randint(0, 15)
            logger.warning(traceback.format_exc())
            logger.warning("Failed: " + operation)
            logger.warning("Try %d of %d", i + 1, MAX_RETRY)
            if i < MAX_RETRY - 1:
                logger.info("Next retry in %d s", wait_time)
                time.sleep(wait_time)

    logger.error("Operation failed %d times and was not completed", MAX_RETRY)
    return default_result