mysql qtableview filter

import sys
from PySide6.QtCore import Qt
from PySide6.QtGui import QStandardItemModel, QStandardItem
from PySide6.QtWidgets import QApplication, QMainWindow, QTableView, QVBoxLayout, QWidget, QLineEdit
import pymysql

class MainWindow(QMainWindow):
    def __init__(self):
        super().__init__()

        container = QWidget()
        layout = QVBoxLayout()

        self.search = QLineEdit()
        self.search.textChanged.connect(self.update_filter)
        self.table = QTableView()

        layout.addWidget(self.search)
        layout.addWidget(self.table)
        container.setLayout(layout)

        # MySQL database 연결
        self.db = pymysql.connect(host="localhost", user="root", password="비밀번호", charset="utf8", database="world")

        self.model = QStandardItemModel()

        # Populate the model with data from the 'city' table
        self.populate_model("country")

        self.table.setModel(self.model)

        self.setMinimumSize(1024, 600)
        self.setCentralWidget(container)

    def populate_model(self, table_name):
        cursor = self.db.cursor()
        cursor.execute(f'SELECT * FROM {table_name}')
        rows = cursor.fetchall()

        # Set column headers dynamically
        column_headers = [field[0] for field in cursor.description]
        self.model.setHorizontalHeaderLabels(column_headers)

        for row in rows:
            item = [QStandardItem(str(field)) for field in row]
            self.model.appendRow(item)

        cursor.close()

    def update_filter(self, s):
        s = s.strip().lower()
        for row in range(self.model.rowCount()):
            self.table.setRowHidden(row, not any(s in self.model.item(row, col).text().lower() for col in range(self.model.columnCount())))

app = QApplication(sys.argv)
window = MainWindow()
window.show()
sys.exit(app.exec())

 

mysql_001.py

import sys
from PySide6.QtCore import Qt
from PySide6.QtGui import QStandardItemModel, QStandardItem
from PySide6.QtWidgets import QApplication, QMainWindow, QTableView, QVBoxLayout, QWidget, QLineEdit
import pymysql

class MainWindow(QMainWindow):
    def __init__(self):
        super().__init__()

        container = QWidget()
        layout = QVBoxLayout()

        self.search = QLineEdit()
        self.search.textChanged.connect(self.update_filter)
        self.table = QTableView()

        layout.addWidget(self.search)
        layout.addWidget(self.table)
        container.setLayout(layout)

        # MySQL database 연결
        self.db = pymysql.connect(host="localhost", user="root", password="비밀번호", charset="utf8", database="world")

        self.model = QStandardItemModel()

        # Populate the model with data from the 'city' table
        self.populate_model("city")

        self.table.setModel(self.model)

        self.setMinimumSize(1024, 600)
        self.setCentralWidget(container)

    def populate_model(self, table_name):
        cursor = self.db.cursor()
        cursor.execute(f'SELECT * FROM {table_name}')
        rows = cursor.fetchall()

        # Set column headers dynamically
        column_headers = [field[0] for field in cursor.description]
        self.model.setHorizontalHeaderLabels(column_headers)

        for row in rows:
            item = [QStandardItem(str(field)) for field in row]
            self.model.appendRow(item)

        cursor.close()

    # def update_filter(self, s): # 모든 열에 필터
    #     s = s.strip().lower()
    #     # s = s.lower()
    #     for row in range(self.model.rowCount()):
    #         # self.table.setRowHidden(row, not any(s in self.model.item(row, col).text().lower() for col in range(self.model.columnCount())))
    #         self.table.setRowHidden(row, not any(s in self.model.item(row, 2).text().lower()))
    
    def update_filter(self, s): # Name열에만 필터
        s = s.lower()
        column_count = self.model.columnCount()
        name_column_index = None
        
        # Find the index of the 'Name' column
        for col in range(column_count):
            if self.model.horizontalHeaderItem(col).text() == 'Name':
                name_column_index = col
                break

        if name_column_index is None:
            return  # If 'Name' column not found, do nothing

        for row in range(self.model.rowCount()):
            item_text = self.model.item(row, name_column_index).text().lower()
            self.table.setRowHidden(row, s not in item_text)

app = QApplication(sys.argv)
window = MainWindow()
window.show()
sys.exit(app.exec())

 

mysql_filter.py

import sys
from PySide6.QtCore import QSize, Qt
from PySide6.QtSql import QSqlQuery
from PySide6.QtWidgets import QApplication, QLineEdit, QMainWindow, QTableView, QVBoxLayout, QWidget
from PySide6.QtGui import QStandardItemModel, QStandardItem

import pymysql

class MainWindow(QMainWindow):
    def __init__(self):
        super().__init__()

        container = QWidget()
        layout = QVBoxLayout()

        self.search = QLineEdit()
        self.search.textChanged.connect(self.update_filter)
        self.table = QTableView()

        layout.addWidget(self.search)
        layout.addWidget(self.table)
        container.setLayout(layout)

        self.model = None
        self.setup_model()

        self.setMinimumSize(QSize(1024, 600))
        self.setCentralWidget(container)

    def setup_model(self):
        self.model = QStandardItemModel()
        self.table.setModel(self.model)
        self.update_filter('')

    def update_filter(self, s):
        filter_str = f'%{s}%'
        sql_query = "SELECT * FROM city WHERE Name LIKE %s"

        # MySQL 연결 및 쿼리 실행
        db = pymysql.connect(host="localhost", user="root", password="비밀번호", database="world", charset="utf8")
        cursor = db.cursor()
        cursor.execute(sql_query, (filter_str,))

        # Clear previous data
        self.model.clear()

        # Set header
        column_headers = [header[0] for header in cursor.description]
        self.model.setHorizontalHeaderLabels(column_headers)

        # Add data to the model
        for row_data in cursor:
            row = [QStandardItem(str(item)) for item in row_data]
            self.model.appendRow(row)

app = QApplication(sys.argv)
window = MainWindow()
window.show()
sys.exit(app.exec())

 

mysql_search.py

import sys
from PySide6.QtCore import QSize, Qt
# from PySide6.QtSql import QSqlQuery
from PySide6.QtWidgets import QApplication, QLabel, QLineEdit, QMainWindow, QTableView, QVBoxLayout, QWidget, QHBoxLayout
from PySide6.QtGui import QStandardItemModel, QStandardItem

import pymysql

class MainWindow(QMainWindow):
    def __init__(self):
        super().__init__()

        container = QWidget()
        layout = QVBoxLayout()

        # 윗줄에 배치될 레이아웃 생성
        upper_layout = QHBoxLayout()

        # 레이블 생성
        self.label_name = QLabel("Name:")
        self.label_country = QLabel("CountryCode:")
        self.label_district = QLabel("District:")

        # 레이아웃에 레이블 추가
        upper_layout.addWidget(self.label_name)
        upper_layout.addWidget(self.label_country)
        upper_layout.addWidget(self.label_district)

        # 아래줄에 배치될 레이아웃 생성
        lower_layout = QHBoxLayout()

        # 라인 에디트 생성
        self.search_name = QLineEdit()
        self.search_name.setPlaceholderText("Search by Name...")
        self.search_name.textChanged.connect(self.update_filter)

        self.search_country = QLineEdit()
        self.search_country.setPlaceholderText("Search by CountryCode...")
        self.search_country.textChanged.connect(self.update_filter)

        self.search_district = QLineEdit()
        self.search_district.setPlaceholderText("Search by District...")
        self.search_district.textChanged.connect(self.update_filter)

        # 레이아웃에 라인 에디트 추가
        lower_layout.addWidget(self.search_name)
        lower_layout.addWidget(self.search_country)
        lower_layout.addWidget(self.search_district)

        # 상위 레이아웃과 하위 레이아웃을 메인 레이아웃에 추가
        layout.addLayout(upper_layout)
        layout.addLayout(lower_layout)

        self.table = QTableView()
        layout.addWidget(self.table)
        container.setLayout(layout)

        self.model = None
        self.setup_model()

        # self.setMinimumSize(QSize(800, 600))
        self.setMaximumSize(QSize(1024, 600))
        self.setCentralWidget(container)

    def setup_model(self):
        self.model = QStandardItemModel()
        self.table.setModel(self.model)
        self.update_filter()

    def update_filter(self):
        name_filter = self.search_name.text()
        country_filter = self.search_country.text()
        district_filter = self.search_district.text()

        filter_str = {
            'Name': f'%{name_filter}%',
            'CountryCode': f'%{country_filter}%',
            'District': f'%{district_filter}%'
        }

        sql_query = "SELECT * FROM city WHERE Name LIKE %s AND CountryCode LIKE %s AND District LIKE %s"

        # MySQL 연결 및 쿼리 실행
        db = pymysql.connect(host="localhost", user="root", password="비밀번호", database="world", charset="utf8")
        cursor = db.cursor()
        cursor.execute(sql_query, (filter_str['Name'], filter_str['CountryCode'], filter_str['District']))

        # Clear previous data
        self.model.clear()

        # Set header
        column_headers = [header[0] for header in cursor.description]
        self.model.setHorizontalHeaderLabels(column_headers)

        # Add data to the model
        for row_data in cursor:
            row = [QStandardItem(str(item)) for item in row_data]
            self.model.appendRow(row)

app = QApplication(sys.argv)
window = MainWindow()
window.show()
sys.exit(app.exec())