pyqt5 读取sql3数据库database1,并将其中的数据库表history_data_table显示到QTableWidget中,并实现对数据库表的增删改查功能
时间: 2024-05-03 20:20:00 浏览: 104
使用QsqlQueryModel和QSqlQuery实现数据的增删改查功能源码,使用的数据库是SQLITE
以下是实现的代码:
```python
import sqlite3
from PyQt5.QtWidgets import QApplication, QMainWindow, QTableWidget, QTableWidgetItem, QLineEdit, QPushButton, QVBoxLayout, QWidget
class MainWindow(QMainWindow):
def __init__(self):
super().__init__()
self.setWindowTitle("Database Editor")
self.setGeometry(100, 100, 800, 600)
# 创建表格和按钮
self.table_widget = QTableWidget()
self.button_add = QPushButton("Add")
self.button_update = QPushButton("Update")
self.button_delete = QPushButton("Delete")
# 布局
layout = QVBoxLayout()
layout.addWidget(self.table_widget)
layout.addWidget(self.button_add)
layout.addWidget(self.button_update)
layout.addWidget(self.button_delete)
# 设置主窗口的中央部件
central_widget = QWidget()
central_widget.setLayout(layout)
self.setCentralWidget(central_widget)
# 连接数据库
self.connection = sqlite3.connect("database1.db")
self.cursor = self.connection.cursor()
# 加载数据
self.load_data()
# 连接按钮的信号和槽
self.button_add.clicked.connect(self.add_data)
self.button_update.clicked.connect(self.update_data)
self.button_delete.clicked.connect(self.delete_data)
def load_data(self):
# 查询数据
self.cursor.execute("SELECT * FROM history_data_table")
data = self.cursor.fetchall()
# 设置表格的行数和列数
self.table_widget.setRowCount(len(data))
self.table_widget.setColumnCount(len(data[0]))
# 设置表格的表头
self.table_widget.setHorizontalHeaderLabels(["ID", "Date", "Value"])
# 填充表格
for i, row in enumerate(data):
for j, cell in enumerate(row):
self.table_widget.setItem(i, j, QTableWidgetItem(str(cell)))
def add_data(self):
# 创建对话框
dialog = QDialog(self)
dialog.setWindowTitle("Add Data")
# 创建文本框和按钮
line_edit_date = QLineEdit()
line_edit_value = QLineEdit()
button_ok = QPushButton("OK")
button_cancel = QPushButton("Cancel")
# 布局
layout = QVBoxLayout()
layout.addWidget(line_edit_date)
layout.addWidget(line_edit_value)
layout.addWidget(button_ok)
layout.addWidget(button_cancel)
dialog.setLayout(layout)
# 连接按钮的信号和槽
button_ok.clicked.connect(lambda: self.add_data_to_database(dialog, line_edit_date, line_edit_value))
button_cancel.clicked.connect(dialog.reject)
# 显示对话框
dialog.exec_()
def add_data_to_database(self, dialog, line_edit_date, line_edit_value):
# 插入数据
date = line_edit_date.text()
value = line_edit_value.text()
self.cursor.execute("INSERT INTO history_data_table (date, value) VALUES (?, ?)", (date, value))
self.connection.commit()
# 关闭对话框
dialog.accept()
# 重新加载数据
self.load_data()
def update_data(self):
# 获取选中的行和列
selected_items = self.table_widget.selectedItems()
if len(selected_items) == 0:
return
row = selected_items[0].row()
column = selected_items[0].column()
# 获取对应的数据
id = self.table_widget.item(row, 0).text()
date = self.table_widget.item(row, 1).text()
value = self.table_widget.item(row, 2).text()
# 创建对话框
dialog = QDialog(self)
dialog.setWindowTitle("Update Data")
# 创建文本框和按钮
line_edit_date = QLineEdit(date)
line_edit_value = QLineEdit(value)
button_ok = QPushButton("OK")
button_cancel = QPushButton("Cancel")
# 布局
layout = QVBoxLayout()
layout.addWidget(line_edit_date)
layout.addWidget(line_edit_value)
layout.addWidget(button_ok)
layout.addWidget(button_cancel)
dialog.setLayout(layout)
# 连接按钮的信号和槽
button_ok.clicked.connect(lambda: self.update_data_in_database(dialog, id, line_edit_date, line_edit_value))
button_cancel.clicked.connect(dialog.reject)
# 显示对话框
dialog.exec_()
def update_data_in_database(self, dialog, id, line_edit_date, line_edit_value):
# 更新数据
date = line_edit_date.text()
value = line_edit_value.text()
self.cursor.execute("UPDATE history_data_table SET date = ?, value = ? WHERE id = ?", (date, value, id))
self.connection.commit()
# 关闭对话框
dialog.accept()
# 重新加载数据
self.load_data()
def delete_data(self):
# 获取选中的行和列
selected_items = self.table_widget.selectedItems()
if len(selected_items) == 0:
return
row = selected_items[0].row()
column = selected_items[0].column()
# 获取对应的数据
id = self.table_widget.item(row, 0).text()
# 删除数据
self.cursor.execute("DELETE FROM history_data_table WHERE id = ?", (id,))
self.connection.commit()
# 重新加载数据
self.load_data()
if __name__ == "__main__":
app = QApplication([])
main_window = MainWindow()
main_window.show()
app.exec_()
```
在这个程序中,我们使用了 PyQT5 和 SQLite3 库。主窗口中包含一个 QTableWidget 和三个 QPushButton,分别用于添加、更新和删除数据。在程序启动时,我们连接了名为 database1.db 的 SQLite3 数据库,并加载了其中的 history_data_table 数据表中的数据到 QTableWidget 中。当用户点击添加、更新或删除按钮时,我们创建了一个 QDialog 显示对应的文本框和按钮,然后根据用户输入的数据来更新数据库。最后,我们重新加载了数据,并在 QTableWidget 中显示最新的数据。
阅读全文