---

CREATE_TABLE_QUERY = "CREATE TABLE test (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255));"
CREATE_PROCEDUTE_QUERY = "create procedure test_proc() begin select * from test; end"
ADD_NEW_USER = "INSERT INTO `test` (`name`) VALUES (%s)"

import pymysql

from query import *

def get_connect():
	"""Создает подключение к базе данных."""
	return pymysql.connect(
		host="localhost",
		user="root",
		password="root",
		database="db_test"
	)

def create_table(connection):
	"""Создает таблицу `test`."""
	with connection.cursor() as cursor:
		cursor.execute(CREATE_TABLE_QUERY)
		connection.commit()

def create_procedure(connection):
	"""Создает процедуру `test_proc`."""
	with connection.cursor() as cursor:
		cursor.execute(CREATE_PROCEDUTE_QUERY)
		connection.commit()

def create_row_user(connection, username: str):
	"""Создает новую запись пользователя."""
	with connection.cursor() as cursor:
		cursor.execute(ADD_NEW_USER, (username,))
		connection.commit()

def call_procedure(connection):
	"""Вызывает процедуру `test_proc`."""
	with connection.cursor() as cursor:
		cursor.callproc("test_proc")
		return cursor.fetchall()

from PyQt6.QtWidgets import *

from db import *

class App(QWidget):
    def __init__(self):
        super().__init__()

        layout = QHBoxLayout()
        self.setLayout(layout)
        
        self.table = QTableWidget()
        self.table.setColumnCount(2)
        self.table.setHorizontalHeaderLabels(["id", "user"])
        layout.addWidget(self.table)
    
        self.upd_ui()

    def upd_ui(self):
        conn = get_connect()
        # create_table(conn)
        # create_procedure(conn)
        create_row_user(conn, "test")

        users = call_procedure(conn)

        conn.close()
        
        # -----------------------------------------

        self.table.setRowCount(0)

        for row, user in enumerate(users):
            self.table.insertRow(row)
            self.table.setItem(row, 0, QTableWidgetItem(str(user[0])))
            self.table.setItem(row, 1, QTableWidgetItem(user[1]))
            

import sys
from PyQt6.QtWidgets import QApplication

from gui import App

def main():
    app = QApplication(sys.argv)
    window = App()
    window.show()
    sys.exit(app.exec())

if __name__ == "__main__":

    main()


"""
{
    "workbench.colorTheme": "Monokai",
    "workbench.iconTheme": "Monokai Classic Icons",
    "python.defaultInterpreterPath": "C:/Python312/python.exe"
}
"""