컴퓨터/mysql
python mysql qtablewidget filter 셀 수정
풍경소리^^
2024. 1. 23. 17:13
https://www.youtube.com/watch?v=-198i7Lm0Yk
mysql_qtableview.py
from PySide6.QtWidgets import QApplication
from database import Widget
import sys
app = QApplication(sys.argv)
Widget = Widget()
Widget.show()
app.exec()
database.py
from typing import Optional
from PySide6.QtCore import Qt
import mysql.connector
from PySide6.QtWidgets import QPushButton, QWidget, QVBoxLayout, QHBoxLayout, QMessageBox, QTableWidget, QTableWidgetItem, QLabel, QLineEdit, QGroupBox
class Widget(QWidget):
def __init__(self):
super().__init__()
self.setWindowTitle("MySQL Database Connect")
self.setGeometry(100,100, 800, 100)
# Create QLabels
name_label = QLabel("이름")
countrycode_label = QLabel("지역코드")
district_label = QLabel("구역")
population_label = QLabel("인구")
# Create QLineEdits
self.name_line_edit = QLineEdit()
self.countrycode_line_edit = QLineEdit()
self.district_line_edit = QLineEdit()
self.population_line_edit = QLineEdit()
# Create QPushButton for this form
button_add_data = QPushButton("추가")
button_add_data.clicked.connect(self.add_data)
button_update_data = QPushButton("수정")
button_update_data.clicked.connect(self.update_data)
# Name QLabel and QLineEdit horizontal layout
h_layout1 = QHBoxLayout()
h_layout1.addWidget(name_label)
h_layout1.addWidget(self.name_line_edit)
# CountryCode QLabel and QLineEdit horizontal layout
h_layout2 = QHBoxLayout()
h_layout2.addWidget(countrycode_label)
h_layout2.addWidget(self.countrycode_line_edit)
# CountryCode QLabel and QLineEdit horizontal layout
h_layout3 = QHBoxLayout()
h_layout3.addWidget(district_label)
h_layout3.addWidget(self.district_line_edit)
# CountryCode QLabel and QLineEdit horizontal layout
h_layout4 = QHBoxLayout()
h_layout4.addWidget(population_label)
h_layout4.addWidget(self.population_line_edit)
# QPushButton Horizontal layout
h_layout5 = QHBoxLayout()
h_layout5.addWidget(button_add_data)
h_layout5.addWidget(button_update_data)
# Group labals and QLineEdit
add_form = QGroupBox("추가하기")
# Layout items in this group vertically
form_layout = QVBoxLayout()
form_layout.addLayout(h_layout1)
form_layout.addLayout(h_layout2)
form_layout.addLayout(h_layout3)
form_layout.addLayout(h_layout4)
form_layout.addLayout(h_layout5)
add_form.setLayout(form_layout)
# Create QTableWidget
self.table = QTableWidget(self)
self.table.setMaximumWidth(800)
# self.table.setColumnCount(5) ########################
self.table.setColumnCount(4)
# self.table.setColumnWidth(0,150)
# self.table.setColumnWidth(1,150)
# self.table.setColumnWidth(2,150)
# self.table.setColumnWidth(3,150)
# self.table.setColumnWidth(4,150)
# self.table.setHorizontalHeaderLabels(["ID","Name","CountryCode","District","Population"]) ########################
self.table.setHorizontalHeaderLabels(["이름","지역코드","구역","인구"])
# Create more QPushButton
button_insert_data = QPushButton("Insert demo content")
# button_insert_data.clicked.connect(self.insert_data)
button_load_data = QPushButton("Load data")
button_load_data.clicked.connect(self.load_data)
button_call_data = QPushButton("선택")
button_call_data.clicked.connect(self.call_data)
button_delete_data = QPushButton("삭제")
button_delete_data.clicked.connect(self.delete_data)
# Display all elements
layout = QVBoxLayout()
layout.addWidget(add_form)
layout.addWidget(self.table)
layout.addWidget(button_load_data)
layout.addWidget(button_call_data)
layout.addWidget(button_delete_data)
self.setLayout(layout)
# MySQL
# mysql -u root -p
# 패스워드
# show databases;
# use world;
# show tables;
# select * from city;
# select * from city where ID < 11;
# 일부 데이터만으로 city2 테이블 만들기
# create table city2 as select * from city where ID < 11;
def create_connection(self):
# Create mySQL Database connection
self.mydb = mysql.connector.connect(
host="localhost",
user="root",
password="비밀번호",
database="world"
)
return self.mydb
def insert_data(self):
# Create cursor object to execute mySQL queries
cursor = self.create_connection().cursor()
# Create list of tuples for demo data
self.list_of_city = [
("서울","02","중구",10000000),
("부산","051","해운대구",8000000),
("대구","053","달서구",6000000),
("광주","041","서구",5000000),
]
# To insert multiple rows
cursor.executemany("INSERT INTO city2 (Name, CountryCode, District, Population) VALUES (%s,%s,%s,%s)", self.list_of_city)
print("Demo data inserted in table")
self.mydb.commit()
self.mydb.close()
def load_data(self):
cursor = self.create_connection().cursor()
sqlquery = "SELECT * FROM city2"
# Clear existing data in the table
self.table.setRowCount(0)
# get all records
cursor.execute(sqlquery)
records = cursor.fetchall()
# Set the row count as the number of city in the list
self.table.setRowCount(len(records))
# add each city from the database to the table
table_row = 0
for i in records:
# self.table.setItem(table_row, 0, QTableWidgetItem(str(i[0]))) ########################
self.table.setItem(table_row, 0, QTableWidgetItem(i[1])) ########################
self.table.setItem(table_row, 1, QTableWidgetItem(i[2])) ########################
self.table.setItem(table_row, 2, QTableWidgetItem(i[3])) ########################
self.table.setItem(table_row, 3, QTableWidgetItem(str(i[4]))) ########################
table_row = table_row + 1
self.mydb.commit()
self.mydb.close()
def add_data(self):
cursor = self.create_connection().cursor()
# Check if the required fields are not empty
if not self.name_line_edit.text() or not self.countrycode_line_edit.text() or not self.district_line_edit.text() or not self.population_line_edit.text():
QMessageBox.warning(self, "경고", "추가할 레코드를 모두 입력해주세요.")
return
# Collect all text from the QLineEdits and put them in a list
self.new_city = [
self.name_line_edit.text(),
self.countrycode_line_edit.text(),
self.district_line_edit.text(),
self.population_line_edit.text(),
]
# Insert row into the city_list
cursor.execute("INSERT INTO city2 (Name, CountryCode, District, Population) VALUES (%s,%s,%s,%s)", self.new_city)
print("You added: ", self.name_line_edit.text())
self.name_line_edit.clear()
self.countrycode_line_edit.clear()
self.district_line_edit.clear()
self.population_line_edit.clear()
self.mydb.commit()
self.mydb.close()
# 추가한 데이터를 테이블에 다시 로드
self.load_data()
def call_data(self):
current_row_index = self.table.currentRow()
# Call city details in row and assign to variables
self.name_edit = str(self.table.item(current_row_index, 0).text())
self.countrycode_edit = str(self.table.item(current_row_index, 1).text())
self.district_edit = str(self.table.item(current_row_index, 2).text())
self.population_edit = str(self.table.item(current_row_index, 3).text())
# Set text in QLineEdits to the above variables
self.name_line_edit.setText(self.name_edit)
self.countrycode_line_edit.setText(self.countrycode_edit)
self.district_line_edit.setText(self.district_edit)
self.population_line_edit.setText(self.population_edit)
def update_data(self):
cursor = self.create_connection().cursor()
# Check if a row is selected
current_row_index = self.table.currentRow()
if current_row_index < 0:
QMessageBox.warning(self, "경고", "수정할 레코드를 선택해주세요.")
return
# Create a list of current text in line edit
new_values = (
self.name_line_edit.text(),
self.countrycode_line_edit.text(),
self.district_line_edit.text(),
self.population_line_edit.text(),
self.name_edit,
)
cursor.execute("UPDATE city2 SET Name=%s, CountryCode=%s, District=%s, Population=%s WHERE Name=%s", new_values)
print("The old name is :", self.name_edit)
print("The new name is :", self.name_line_edit.text())
# Clear QLine edit after updating
self.name_line_edit.clear()
self.countrycode_line_edit.clear()
self.district_line_edit.clear()
self.population_line_edit.clear()
self.mydb.commit()
self.mydb.close()
# 추가한 데이터를 테이블에 다시 로드
self.load_data()
def delete_data(self):
cursor = self.create_connection().cursor()
current_row_index = self.table.currentRow()
if current_row_index < 0:
warning = QMessageBox.warning(self, "경고", "삭제할 레코드를 선택해주세요.")
else:
# Call name item in row and assign to a variable
name_item = str(self.table.item(current_row_index, 0).text())
message = QMessageBox.question(self, "확인", "선택된 레코드를 삭제 하시겠습니까?",
QMessageBox.StandardButton.Yes | QMessageBox.StandardButton.No)
if message == QMessageBox.StandardButton.Yes:
# Delete the row
sqlquery = "DELETE FROM city2 WHERE Name=%s"
cursor.execute(sqlquery, (name_item,))
print("You deleted the row with name : ", name_item)
self.mydb.commit()
self.mydb.close()
# 추가한 데이터를 테이블에 다시 로드
self.load_data()
mysql
mysql -u root -p
# 패스워드
show databases;
use world;
show tables;
select * from city;
select * from city where ID < 11;
# 일부 데이터만으로 city2 테이블 만들기
create table city2 as select * from city where ID < 11;
mysql_sakila.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 address WHERE address LIKE %s"
# MySQL 연결 및 쿼리 실행
db = pymysql.connect(host="localhost", user="root", password="비밀번호", database="sakila", 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_sakila_002.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, text):
filter_str = f'%{text}%'
sql_query = """
SELECT
address.address_id,
address.address,
address.district,
city.city,
country.country
FROM
address
INNER JOIN
city ON address.city_id = city.city_id
INNER JOIN
country ON city.country_id = country.country_id
WHERE
address.address LIKE %s
"""
# print("SQL Query:", sql_query) # 쿼리 확인
# MySQL 연결 및 쿼리 실행
db = pymysql.connect(host="localhost", user="root", password="비밀번호", database="sakila", charset="utf8")
cursor = db.cursor()
cursor.execute(sql_query, (filter_str,))
# print("Query executed successfully!") # 쿼리 실행 확인
# Clear previous data
self.model.clear()
# Set header
column_headers = ["Address ID", "Address", "District", "City", "Country"]
self.model.setColumnCount(len(column_headers))
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)
# print("Data added to the model successfully!") # 모델 데이터 확인
app = QApplication(sys.argv)
window = MainWindow()
window.show()
sys.exit(app.exec())
mysql_sakila_003.py filter
import sys
from PySide6.QtCore import QSize, Qt
from PySide6.QtSql import QSqlQuery
from PySide6.QtWidgets import QApplication, QLineEdit, QMainWindow, QTableView, QVBoxLayout, QWidget, QLabel, 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("Address:")
self.label_district = QLabel("District:")
self.label_city = QLabel("City:")
# 레이아웃에 레이블 추가
upper_layout.addWidget(self.label_name)
upper_layout.addWidget(self.label_district)
upper_layout.addWidget(self.label_city)
# 아래줄에 배치될 레이아웃 생성
lower_layout = QHBoxLayout()
# 라인 에디트 생성
self.search_address = QLineEdit()
self.search_address.setPlaceholderText("Search by Address...")
self.search_address.textChanged.connect(self.update_filter)
self.search_district = QLineEdit()
self.search_district.setPlaceholderText("Search by District...")
self.search_district.textChanged.connect(self.update_filter)
self.search_city = QLineEdit()
self.search_city.setPlaceholderText("Search by City...")
self.search_city.textChanged.connect(self.update_filter)
# 레이아웃에 라인 에디트 추가
lower_layout.addWidget(self.search_address)
lower_layout.addWidget(self.search_district)
lower_layout.addWidget(self.search_city)
# 상위 레이아웃과 하위 레이아웃을 메인 레이아웃에 추가
layout.addLayout(upper_layout)
layout.addLayout(lower_layout)
# self.search.textChanged.connect(self.update_filter)
self.table = QTableView()
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):
address_filter = self.search_address.text()
district_filter = self.search_district.text()
city_filter = self.search_city.text()
filter_str = {
'Address': f'%{address_filter}%',
'District': f'%{district_filter}%',
'City': f'%{city_filter}%'
}
sql_query = """
SELECT
address.address_id,
address.address,
address.district,
city.city,
country.country
FROM
address
INNER JOIN
city ON address.city_id = city.city_id
INNER JOIN
country ON city.country_id = country.country_id
WHERE
address.address LIKE %s
AND district LIKE %s
AND city LIKE %s
"""
# print("SQL Query:", sql_query) # 쿼리 확인
# MySQL 연결 및 쿼리 실행
db = pymysql.connect(host="localhost", user="root", password="비밀번호", database="sakila", charset="utf8")
cursor = db.cursor()
cursor.execute(sql_query, (filter_str['Address'], filter_str['District'], filter_str['City']))
# print("Query executed successfully!") # 쿼리 실행 확인
# Clear previous data
self.model.clear()
# Set header
column_headers = ["Address ID", "Address", "District", "City", "Country"]
self.model.setColumnCount(len(column_headers))
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)
# print("Data added to the model successfully!") # 모델 데이터 확인
app = QApplication(sys.argv)
window = MainWindow()
window.show()
sys.exit(app.exec())
mysql_sakila_007.py 셀 수정 저장 하기
import sys
from PySide6.QtCore import QSize, Qt
from PySide6.QtWidgets import QApplication, QLineEdit, QMainWindow, QTableView, QVBoxLayout, QWidget, QLabel, QHBoxLayout
from PySide6.QtGui import QStandardItemModel, QStandardItem
from PySide6.QtCore import QModelIndex
import pymysql
class MainWindow(QMainWindow):
def __init__(self):
super().__init__()
container = QWidget()
layout = QVBoxLayout()
# 윗줄에 배치될 레이아웃 생성
upper_layout = QHBoxLayout()
# 레이블 생성
self.label_name = QLabel("Address:")
self.label_district = QLabel("District:")
self.label_city = QLabel("City:")
# 레이아웃에 레이블 추가
upper_layout.addWidget(self.label_name)
upper_layout.addWidget(self.label_district)
upper_layout.addWidget(self.label_city)
# 아래줄에 배치될 레이아웃 생성
lower_layout = QHBoxLayout()
# 라인 에디트 생성
self.search_address = QLineEdit()
self.search_address.setPlaceholderText("Search by Address...")
self.search_address.textChanged.connect(self.update_filter)
self.search_district = QLineEdit()
self.search_district.setPlaceholderText("Search by District...")
self.search_district.textChanged.connect(self.update_filter)
self.search_city = QLineEdit()
self.search_city.setPlaceholderText("Search by City...")
self.search_city.textChanged.connect(self.update_filter)
# 레이아웃에 라인 에디트 추가
lower_layout.addWidget(self.search_address)
lower_layout.addWidget(self.search_district)
lower_layout.addWidget(self.search_city)
# 상위 레이아웃과 하위 레이아웃을 메인 레이아웃에 추가
layout.addLayout(upper_layout)
layout.addLayout(lower_layout)
# self.search.textChanged.connect(self.update_filter)
self.table = QTableView()
layout.addWidget(self.table)
container.setLayout(layout)
self.model = QStandardItemModel()
self.table.setModel(self.model)
self.setMinimumSize(QSize(1024, 600))
self.setCentralWidget(container)
# 데이터베이스 연결
self.db = pymysql.connect(host="localhost", user="root", password="비밀번호", database="sakila", charset="utf8")
self.cursor = self.db.cursor()
if self.db:
self.update_filter()
# 셀 편집 후 신호 연결
self.table.model().dataChanged.connect(self.handle_edit)
def update_filter(self):
address_filter = self.search_address.text()
district_filter = self.search_district.text()
city_filter = self.search_city.text()
# Clear previous data
self.model.clear()
# Set header
column_headers = ["Address ID", "Address", "District", "City", "Country"]
self.model.setColumnCount(len(column_headers))
self.model.setHorizontalHeaderLabels(column_headers)
# MySQL 쿼리 실행
sql_query = """
SELECT
address.address_id,
address.address,
address.district,
city.city,
country.country
FROM
address
INNER JOIN
city ON address.city_id = city.city_id
INNER JOIN
country ON city.country_id = country.country_id
WHERE
address.address LIKE %s
AND district LIKE %s
AND city LIKE %s
"""
self.cursor.execute(sql_query, (f"%{address_filter}%", f"%{district_filter}%", f"%{city_filter}%"))
# Add data to the model
for row_data in self.cursor:
row = [QStandardItem(str(item)) for item in row_data]
self.model.appendRow(row)
def handle_edit(self, index: QModelIndex):
row = index.row()
col = index.column()
item = self.model.item(row, col)
if item is not None:
new_value = item.text()
# Get the primary key of the edited row
address_id = self.model.item(row, 0).text()
# Execute the update query
sql_update_query = f"UPDATE address SET {self.model.headerData(col, Qt.Horizontal)} = '{new_value}' WHERE address_id = {address_id}"
self.cursor.execute(sql_update_query)
self.db.commit()
if __name__ == "__main__":
app = QApplication(sys.argv)
window = MainWindow()
window.show()
sys.exit(app.exec())
mysql_sakila_010.py 콤보상자
import sys
from PySide6.QtCore import QSize, Qt
from PySide6.QtWidgets import QApplication, QLineEdit, QMainWindow, QTableView, QVBoxLayout, QWidget, QLabel, QHBoxLayout, QStyledItemDelegate, QComboBox
from PySide6.QtGui import QStandardItemModel, QStandardItem
from PySide6.QtCore import QModelIndex
import pymysql
class ComboBoxDelegate(QStyledItemDelegate):
def __init__(self, parent=None):
super().__init__(parent)
self.city_data = []
self.country_data = []
self.populate_data()
def populate_data(self):
try:
db = pymysql.connect(host="localhost", user="root", password="비밀번호", database="sakila", charset="utf8")
cursor = db.cursor()
cursor.execute("SELECT city FROM city")
cities = cursor.fetchall()
self.city_data = [city[0] for city in cities]
cursor.execute("SELECT country FROM country")
countries = cursor.fetchall()
self.country_data = [country[0] for country in countries]
except pymysql.Error as e:
print("Error while connecting to MySQL", e)
finally:
cursor.close()
db.close()
def createEditor(self, parent, option, index):
combo_box = QComboBox(parent)
if index.column() == 3: # City 열
combo_box.addItems(self.city_data)
elif index.column() == 4: # Country 열
combo_box.addItems(self.country_data)
combo_box.currentIndexChanged.connect(self.update_city_combo)
return combo_box
def update_city_combo(self, index):
if index >= 0:
selected_country = self.country_data[index]
try:
db = pymysql.connect(host="localhost", user="root", password="비밀번호", database="sakila", charset="utf8")
cursor = db.cursor()
cursor.execute("SELECT city.city FROM city INNER JOIN country ON city.country_id = country.country_id WHERE country.country = %s", (selected_country,))
cities = cursor.fetchall()
self.city_data = [city[0] for city in cities]
cursor.close()
db.close()
except pymysql.Error as e:
print("Error while updating city combo:", e)
class MainWindow(QMainWindow):
def __init__(self):
super().__init__()
container = QWidget()
layout = QVBoxLayout()
# 윗줄에 배치될 레이아웃 생성
upper_layout = QHBoxLayout()
# 레이블 생성
self.label_name = QLabel("Address:")
self.label_district = QLabel("District:")
self.label_city = QLabel("City:")
self.label_country = QLabel("Country:")
# 레이아웃에 레이블 추가
upper_layout.addWidget(self.label_name)
upper_layout.addWidget(self.label_district)
upper_layout.addWidget(self.label_city)
upper_layout.addWidget(self.label_country)
# 아래줄에 배치될 레이아웃 생성
lower_layout = QHBoxLayout()
# 라인 에디트 생성
self.search_address = QLineEdit()
self.search_address.setPlaceholderText("Search by Address...")
self.search_address.textChanged.connect(self.update_filter)
self.search_district = QLineEdit()
self.search_district.setPlaceholderText("Search by District...")
self.search_district.textChanged.connect(self.update_filter)
self.search_city = QLineEdit()
self.search_city.setPlaceholderText("Search by City...")
self.search_city.textChanged.connect(self.update_filter)
self.search_country = QLineEdit()
self.search_country.setPlaceholderText("Search by Country...")
self.search_country.textChanged.connect(self.update_filter)
# self.search_country.textChanged.connect(self.update_filter_by_country)
# 레이아웃에 라인 에디트 추가
lower_layout.addWidget(self.search_address)
lower_layout.addWidget(self.search_district)
lower_layout.addWidget(self.search_city)
lower_layout.addWidget(self.search_country)
# 상위 레이아웃과 하위 레이아웃을 메인 레이아웃에 추가
layout.addLayout(upper_layout)
layout.addLayout(lower_layout)
# self.search.textChanged.connect(self.update_filter)
self.table = QTableView()
layout.addWidget(self.table)
container.setLayout(layout)
self.model = QStandardItemModel()
self.table.setModel(self.model)
self.setMinimumSize(QSize(1024, 600))
self.setCentralWidget(container)
# 데이터베이스 연결
self.db = pymysql.connect(host="localhost", user="root", password="비밀번호", database="sakila", charset="utf8")
self.cursor = self.db.cursor()
if self.db:
self.update_filter()
# 셀 편집 후 신호 연결
self.table.model().dataChanged.connect(self.handle_edit)
# Delegate 설정
self.delegate = ComboBoxDelegate()
self.table.setItemDelegateForColumn(3, self.delegate) # City 열
self.table.setItemDelegateForColumn(4, self.delegate) # Country 열
def update_filter(self):
address_filter = self.search_address.text()
district_filter = self.search_district.text()
city_filter = self.search_city.text()
country_filter = self.search_country.text()
# Clear previous data
self.model.clear()
# Set header
column_headers = ["Address ID", "Address", "District", "City", "Country"]
self.model.setColumnCount(len(column_headers))
self.model.setHorizontalHeaderLabels(column_headers)
# MySQL 쿼리 실행
sql_query = """
SELECT
address.address_id,
address.address,
address.district,
city.city,
country.country
FROM
address
INNER JOIN
city ON address.city_id = city.city_id
INNER JOIN
country ON city.country_id = country.country_id
WHERE
address.address LIKE %s
AND district LIKE %s
AND city LIKE %s
AND country LIKE %s
"""
self.cursor.execute(sql_query, (f"%{address_filter}%", f"%{district_filter}%", f"%{city_filter}%", f"%{country_filter}%"))
# Add data to the model
for row_data in self.cursor:
row = [QStandardItem(str(item)) for item in row_data]
self.model.appendRow(row)
# def update_filter_by_country(self):
# country_filter = self.search_country.text()
# # Clear previous data
# self.model.clear()
# # Set header
# column_headers = ["Address ID", "Address", "District", "City", "Country"]
# self.model.setColumnCount(len(column_headers))
# self.model.setHorizontalHeaderLabels(column_headers)
# # MySQL 쿼리 실행
# sql_query = """
# SELECT
# address.address_id,
# address.address,
# address.district,
# city.city,
# country.country
# FROM
# address
# INNER JOIN
# city ON address.city_id = city.city_id
# INNER JOIN
# country ON city.country_id = country.country_id
# WHERE
# country.country LIKE %s
# """
# self.cursor.execute(sql_query, (f"%{country_filter}%",))
# # Add data to the model
# for row_data in self.cursor:
# row = [QStandardItem(str(item)) for item in row_data]
# self.model.appendRow(row)
def handle_edit(self, index: QModelIndex):
row = index.row()
col = index.column()
item = self.model.item(row, col)
if item is not None:
new_value = item.text()
# Get the primary key of the edited row
address_id = self.model.item(row, 0).text()
# Execute the update query
sql_update_query = f"UPDATE address SET {self.model.headerData(col, Qt.Horizontal).lower()} = '{new_value}' WHERE address_id = {address_id}"
try:
self.cursor.execute(sql_update_query)
self.db.commit()
except pymysql.Error as e:
print("Error while updating database:", e)
if __name__ == "__main__":
app = QApplication(sys.argv)
window = MainWindow()
window.show()
sys.exit(app.exec())import sys
from PySide6.QtCore import QSize, Qt
from PySide6.QtWidgets import QApplication, QLineEdit, QMainWindow, QTableView, QVBoxLayout, QWidget, QLabel, QHBoxLayout, QStyledItemDelegate, QComboBox
from PySide6.QtGui import QStandardItemModel, QStandardItem
from PySide6.QtCore import QModelIndex
import pymysql
class ComboBoxDelegate(QStyledItemDelegate):
def __init__(self, parent=None):
super().__init__(parent)
self.city_data = []
self.country_data = []
self.populate_data()
def populate_data(self):
try:
db = pymysql.connect(host="localhost", user="root", password="비밀번호", database="sakila", charset="utf8")
cursor = db.cursor()
cursor.execute("SELECT city FROM city")
cities = cursor.fetchall()
self.city_data = [city[0] for city in cities]
cursor.execute("SELECT country FROM country")
countries = cursor.fetchall()
self.country_data = [country[0] for country in countries]
except pymysql.Error as e:
print("Error while connecting to MySQL", e)
finally:
cursor.close()
db.close()
def get_country_id(self, country_name):
try:
db = pymysql.connect(host="localhost", user="root", password="비밀번호", database="sakila", charset="utf8")
cursor = db.cursor()
cursor.execute("SELECT country_id FROM country WHERE country = %s", (country_name,))
country_id = cursor.fetchone()[0]
cursor.close()
db.close()
return country_id
except pymysql.Error as e:
print("Error while getting country ID:", e)
return None
def get_city_id(self, city_name):
try:
db = pymysql.connect(host="localhost", user="root", password="비밀번호", database="sakila", charset="utf8")
cursor = db.cursor()
cursor.execute("SELECT city_id FROM city WHERE city = %s", (city_name,))
city_id = cursor.fetchone()[0]
cursor.close()
db.close()
return city_id
except pymysql.Error as e:
print("Error while getting city ID:", e)
return None
def createEditor(self, parent, option, index):
combo_box = QComboBox(parent)
if index.column() == 3: # City 열
combo_box.addItems(self.city_data)
elif index.column() == 4: # Country 열
combo_box.addItems(self.country_data)
combo_box.currentIndexChanged.connect(self.update_city_combo)
return combo_box
def update_city_combo(self, index):
if index >= 0:
selected_country = self.country_data[index]
try:
db = pymysql.connect(host="localhost", user="root", password="비밀번호", database="sakila", charset="utf8")
cursor = db.cursor()
cursor.execute("SELECT city.city FROM city INNER JOIN country ON city.country_id = country.country_id WHERE country.country = %s", (selected_country,))
cities = cursor.fetchall()
self.city_data = [city[0] for city in cities]
view = self.parent()
if view:
model = view.model()
index = model.index(view.currentIndex().row(), 3) # City 열
if index.isValid():
model.setData(index, self.city_data[0], Qt.EditRole) # 첫번째 도시로 설정
cursor.close()
db.close()
except pymysql.Error as e:
print("Error while updating city combo:", e)
class MainWindow(QMainWindow):
def __init__(self):
super().__init__()
container = QWidget()
layout = QVBoxLayout()
# 윗줄에 배치될 레이아웃 생성
upper_layout = QHBoxLayout()
# 레이블 생성
self.label_name = QLabel("Address:")
self.label_district = QLabel("District:")
self.label_city = QLabel("City:")
self.label_country = QLabel("Country:")
# 레이아웃에 레이블 추가
upper_layout.addWidget(self.label_name)
upper_layout.addWidget(self.label_district)
upper_layout.addWidget(self.label_city)
upper_layout.addWidget(self.label_country)
# 아래줄에 배치될 레이아웃 생성
lower_layout = QHBoxLayout()
# 라인 에디트 생성
self.search_address = QLineEdit()
self.search_address.setPlaceholderText("Search by Address...")
self.search_address.textChanged.connect(self.update_filter)
self.search_district = QLineEdit()
self.search_district.setPlaceholderText("Search by District...")
self.search_district.textChanged.connect(self.update_filter)
self.search_city = QLineEdit()
self.search_city.setPlaceholderText("Search by City...")
self.search_city.textChanged.connect(self.update_filter)
self.search_country = QLineEdit()
self.search_country.setPlaceholderText("Search by Country...")
self.search_country.textChanged.connect(self.update_filter)
# 레이아웃에 라인 에디트 추가
lower_layout.addWidget(self.search_address)
lower_layout.addWidget(self.search_district)
lower_layout.addWidget(self.search_city)
lower_layout.addWidget(self.search_country)
# 상위 레이아웃과 하위 레이아웃을 메인 레이아웃에 추가
layout.addLayout(upper_layout)
layout.addLayout(lower_layout)
self.table = QTableView()
layout.addWidget(self.table)
container.setLayout(layout)
self.setCentralWidget(container)
# 모델 생성
self.model = QStandardItemModel()
self.table.setModel(self.model)
# 데이터베이스 연결
self.db = pymysql.connect(host="localhost", user="root", password="비밀번호", database="sakila", charset="utf8")
self.cursor = self.db.cursor()
# 셀 편집 후 데이터베이스에 변경 사항 반영
self.model.itemChanged.connect(self.handle_edit)
# Delegate 설정
self.delegate = ComboBoxDelegate(self.table)
self.table.setItemDelegateForColumn(3, self.delegate) # City 열
self.table.setItemDelegateForColumn(4, self.delegate) # Country 열
# 필터 업데이트
self.update_filter()
def update_filter(self):
address_filter = self.search_address.text()
district_filter = self.search_district.text()
city_filter = self.search_city.text()
country_filter = self.search_country.text()
self.model.clear()
column_headers = ["Address ID", "Address", "District", "City", "Country"]
self.model.setColumnCount(len(column_headers))
self.model.setHorizontalHeaderLabels(column_headers)
sql_query = """
SELECT
address.address_id,
address.address,
address.district,
city.city,
country.country
FROM
address
INNER JOIN
city ON address.city_id = city.city_id
INNER JOIN
country ON city.country_id = country.country_id
WHERE
address.address LIKE %s
AND district LIKE %s
AND city LIKE %s
AND country LIKE %s
ORDER BY
address.address_id ASC
"""
self.cursor.execute(sql_query, (f"%{address_filter}%", f"%{district_filter}%", f"%{city_filter}%", f"%{country_filter}%"))
for row_data in self.cursor:
row = [QStandardItem(str(item)) for item in row_data]
self.model.appendRow(row)
def handle_edit(self, item):
row = item.row()
col = item.column()
new_value = item.text()
address_id = self.model.item(row, 0).text()
if col == 1:
column_name = "address"
elif col == 2:
column_name = "district"
elif col == 3:
column_name = "city_id"
new_value = self.delegate.get_city_id(new_value)
elif col == 4:
column_name = "country_id"
new_value = self.delegate.get_country_id(new_value)
sql_update_query = f"UPDATE address SET {column_name} = '{new_value}' WHERE address_id = {address_id}"
try:
self.cursor.execute(sql_update_query)
self.db.commit()
except pymysql.Error as e:
print("Error while updating database:", e)
if __name__ == "__main__":
app = QApplication(sys.argv)
window = MainWindow()
window.show()
sys.exit(app.exec())
mysql_sakila_013.py
import sys
from PySide6.QtCore import Qt
from PySide6.QtWidgets import QApplication, QLineEdit, QMainWindow, QTableView, QVBoxLayout, QWidget, QLabel, QHBoxLayout, QStyledItemDelegate, QComboBox, QStyledItemDelegate
from PySide6.QtGui import QStandardItemModel, QStandardItem
import pymysql
class ComboBoxDelegate(QStyledItemDelegate):
host = "localhost"
user = "root"
password = "비밀번호"
database = "sakila"
charset = "utf8"
def __init__(self, parent=None):
super().__init__(parent)
self.city_data = []
self.country_data = []
self.populate_data()
def populate_data(self):
try:
with pymysql.connect(
host=self.host,
user=self.user,
password=self.password,
database=self.database,
charset=self.charset
) as db:
cursor = db.cursor()
cursor.execute("SELECT city FROM city")
self.city_data = [city[0] for city in cursor.fetchall()]
cursor.execute("SELECT country FROM country")
self.country_data = [country[0] for country in cursor.fetchall()]
except pymysql.Error as e:
print("Error while connecting to MySQL", e)
def get_id(self, table, name):
try:
with pymysql.connect(
host=self.host,
user=self.user,
password=self.password,
database=self.database,
charset=self.charset
) as db:
cursor = db.cursor()
cursor.execute(f"SELECT {table}_id FROM {table} WHERE {table} = %s", (name,))
return cursor.fetchone()[0]
except pymysql.Error as e:
print(f"Error while getting {table} ID:", e)
return None
def createEditor(self, parent, option, index):
combo_box = QComboBox(parent)
if index.column() == 3:
combo_box.addItems(self.city_data)
elif index.column() == 4:
combo_box.addItems(self.country_data)
combo_box.currentIndexChanged.connect(self.update_city_combo)
return combo_box
def update_city_combo(self, index):
if index >= 0:
selected_country = self.country_data[index]
try:
with pymysql.connect(
host=self.host,
user=self.user,
password=self.password,
database=self.database,
charset=self.charset
) as db:
cursor = db.cursor()
cursor.execute("SELECT city.city FROM city INNER JOIN country ON city.country_id = country.country_id WHERE country.country = %s", (selected_country,))
self.city_data = [city[0] for city in cursor.fetchall()]
view = self.parent()
if view:
model = view.model()
index = model.index(view.currentIndex().row(), 3)
if index.isValid():
model.setData(index, self.city_data[0], Qt.EditRole)
except pymysql.Error as e:
print("Error while updating city combo:", e)
class MainWindow(QMainWindow):
host = "localhost"
user = "root"
password = "비밀번호"
database = "sakila"
charset = "utf8"
def __init__(self):
super().__init__()
container = QWidget()
layout = QVBoxLayout()
upper_layout = QHBoxLayout()
self.label_name = QLabel("Address:")
self.label_district = QLabel("District:")
self.label_city = QLabel("City:")
self.label_country = QLabel("Country:")
upper_layout.addWidget(self.label_name)
upper_layout.addWidget(self.label_district)
upper_layout.addWidget(self.label_city)
upper_layout.addWidget(self.label_country)
lower_layout = QHBoxLayout()
self.search_address = QLineEdit()
self.search_address.setPlaceholderText("Search by Address...")
self.search_address.textChanged.connect(self.update_filter)
self.search_district = QLineEdit()
self.search_district.setPlaceholderText("Search by District...")
self.search_district.textChanged.connect(self.update_filter)
self.search_city = QLineEdit()
self.search_city.setPlaceholderText("Search by City...")
self.search_city.textChanged.connect(self.update_filter)
self.search_country = QLineEdit()
self.search_country.setPlaceholderText("Search by Country...")
self.search_country.textChanged.connect(self.update_filter)
lower_layout.addWidget(self.search_address)
lower_layout.addWidget(self.search_district)
lower_layout.addWidget(self.search_city)
lower_layout.addWidget(self.search_country)
layout.addLayout(upper_layout)
layout.addLayout(lower_layout)
self.table = QTableView()
layout.addWidget(self.table)
container.setLayout(layout)
self.setCentralWidget(container)
self.model = QStandardItemModel()
self.table.setModel(self.model)
self.db = pymysql.connect(
host=self.host,
user=self.user,
password=self.password,
database=self.database,
charset=self.charset
)
self.cursor = self.db.cursor()
self.model.itemChanged.connect(self.handle_edit)
self.delegate = ComboBoxDelegate(self.table)
self.table.setItemDelegateForColumn(3, self.delegate)
self.table.setItemDelegateForColumn(4, self.delegate)
self.update_filter()
def update_filter(self):
address_filter = self.search_address.text()
district_filter = self.search_district.text()
city_filter = self.search_city.text()
country_filter = self.search_country.text()
self.model.clear()
column_headers = ["Address ID", "Address", "District", "City", "Country"]
self.model.setColumnCount(len(column_headers))
self.model.setHorizontalHeaderLabels(column_headers)
sql_query = """
SELECT
address.address_id,
address.address,
address.district,
city.city,
country.country
FROM
address
INNER JOIN
city ON address.city_id = city.city_id
INNER JOIN
country ON city.country_id = country.country_id
WHERE
address.address LIKE %s
AND district LIKE %s
AND city LIKE %s
AND country LIKE %s
ORDER BY
address.address_id ASC
"""
self.cursor.execute(sql_query, (f"%{address_filter}%", f"%{district_filter}%", f"%{city_filter}%", f"%{country_filter}%"))
for row_data in self.cursor:
row = [QStandardItem(str(item)) for item in row_data]
self.model.appendRow(row)
def handle_edit(self, item):
row = item.row()
col = item.column()
new_value = item.text()
address_id = self.model.item(row, 0).text()
columns = {
1: "address",
2: "district",
3: "city_id",
4: "country_id"
}
column_name = columns.get(col)
if column_name and col in (3, 4):
new_value = self.delegate.get_id(column_name[:-3], new_value)
if column_name:
sql_update_query = f"UPDATE address SET {column_name} = '{new_value}' WHERE address_id = {address_id}"
try:
self.cursor.execute(sql_update_query)
self.db.commit()
except pymysql.Error as e:
print("Error while updating database:", e)
if __name__ == "__main__":
app = QApplication(sys.argv)
window = MainWindow()
window.show()
sys.exit(app.exec())