QSqlTableModel是可读可写模型，QSqlQueryModel是只读模型，本次使用QSqlTableModel测试
测试代码
import sys
from PySide6.QtWidgets import (QApplication, QMainWindow, QWidget, QVBoxLayout, QPushButton,QTableView,QVBoxLayout)
from PySide6.QtCore import QTimer
from PySide6.QtSql import *
class MyWidget(QWidget):
def __init__(self):
super().__init__()
self.setWindowTitle("db test")
self.db_init()
self.sql_model = QSqlTableModel()
self.sql_model.setTable("test")
self.sql_model.setEditStrategy(QSqlTableModel.OnFieldChange)
self.sql_model.setHeaderData(0,Qt.Horizontal,"id")
self.sql_model.setHeaderData(1,Qt.Horizontal,"name")
self.sql_model.setHeaderData(2,Qt.Horizontal,"score")
self.table_view = QTableView()
self.table_view.setModel(self.sql_model)
self.button_select = QPushButton("查询数据")
self.button_select.clicked.connect(lambda: self.query_sql())
self.btn_delete = QPushButton("删除数据")
self.btn_delete.clicked.connect(lambda: self.db_delete())
self.btn_insert = QPushButton("插入数据")
self.btn_insert.clicked.connect(lambda: self.db_insert())
vlayout = QVBoxLayout()
vlayout.addWidget(self.table_view)
vlayout.addWidget(self.button_select)
vlayout.addWidget(self.btn_insert)
vlayout.addWidget(self.btn_delete)
self.setLayout(vlayout)
self.query_sql()
def db_insert(self):
print("insert data")
self.sql_model.insertRow(0)
id = random.randint(1,99)
self.sql_model.setData(self.sql_model.index(0,0),id)
self.sql_model.setData(self.sql_model.index(0,1),"test")
self.sql_model.setData(self.sql_model.index(0,2),100)
self.sql_model.submit()
pass
def db_delete(self):
print("delete data")
self.sql_model.removeRow(0)
self.sql_model.submit()
def query_sql(self):
self.sql_model.setQuery("select * from test")
self.table_view.setModel(self.sql_model)
for i in range(self.sql_model.rowCount()):
id = self.sql_model.data(self.sql_model.index(i,0))
name = self.sql_model.data(self.sql_model.index(i,1))
score = self.sql_model.data(self.sql_model.index(i,2))
print(id,name,score)
pass
def db_init(self):
self.db = QSqlDatabase.addDatabase("QSQLITE")
self.db.setDatabaseName("test.db")
if not self.db.open():
print("db open error")
error = self.db.lastError()
print(error.text())
else:
print("db open success")
def closeEvent(self, event):
try:
self.db.close()
event.accept()
except Exception as e:
print(e)
if __name__ == "__main__":
app = QApplication.instance() or QApplication([])
window = MyWidget()
window.show()
app.exec()问题：出现空行
解决办法1：修改查询函数
def query_sql(self):
self.sql_model.setTable("test") # 恢复表模式
self.sql_model.select() # 重新查询数据
self.table_view.setModel(self.sql_model) # 重新设置模型（可选）
# 打印数据（调试用）
for i in range(self.sql_model.rowCount()):
id = self.sql_model.data(self.sql_model.index(i, 0))
name = self.sql_model.data(self.sql_model.index(i, 1))
score = self.sql_model.data(self.sql_model.index(i, 2))
print(id, name, score)解决办法2：修改后，重新查询，(这要是很大的数据库，会不会很卡）
def db_delete(self):
print("delete data")
query = QSqlQuery()
query.exec("DELETE FROM test WHERE id = (SELECT id FROM test LIMIT 1)") # 删除第一条
self.query_sql() # 刷新模型
def db_insert(self):
print("insert data")
query = QSqlQuery()
id = random.randint(1, 99)
query.exec(f"INSERT INTO test (id, name, score) VALUES ({id}, 'test', 100)")
self.query_sql() # 刷新模型修改后的代码
import sys
from PySide6.QtWidgets import (QApplication, QMainWindow, QWidget, QVBoxLayout, QPushButton,QTableView,QVBoxLayout)
from PySide6.QtCore import QTimer
from PySide6.QtSql import *
class MyWidget(QWidget):
def __init__(self):
super().__init__()
self.setWindowTitle("db test")
self.db_init()
self.sql_model = QSqlTableModel()
self.sql_model.setTable("test")
self.sql_model.setEditStrategy(QSqlTableModel.OnFieldChange)
self.sql_model.setHeaderData(0,Qt.Horizontal,"id")
self.sql_model.setHeaderData(1,Qt.Horizontal,"name")
self.sql_model.setHeaderData(2,Qt.Horizontal,"score")
self.table_view = QTableView()
self.table_view.setModel(self.sql_model)
self.button_select = QPushButton("查询数据")
self.button_select.clicked.connect(lambda: self.query_sql())
self.btn_delete = QPushButton("删除数据")
self.btn_delete.clicked.connect(lambda: self.db_delete())
self.btn_insert = QPushButton("插入数据")
self.btn_insert.clicked.connect(lambda: self.db_insert())
vlayout = QVBoxLayout()
vlayout.addWidget(self.table_view)
vlayout.addWidget(self.button_select)
vlayout.addWidget(self.btn_insert)
vlayout.addWidget(self.btn_delete)
self.setLayout(vlayout)
self.query_sql()
def db_insert(self):
print("insert data")
self.sql_model.insertRow(0)
id = random.randint(1,99)
score = random.randint(1,100)
self.sql_model.setData(self.sql_model.index(0,0),id)
self.sql_model.setData(self.sql_model.index(0,1),"test")
self.sql_model.setData(self.sql_model.index(0,2),score)
self.sql_model.submit()
pass
def db_delete(self):
print("delete data")
query = QSqlQuery()
query.exec("DELETE FROM test WHERE id = (SELECT id FROM test LIMIT 1)") # 删除第一条
self.query_sql() # 刷新模型
def query_sql(self):
self.sql_model.setTable("test") # 恢复表模式
self.sql_model.select() # 重新查询数据
self.table_view.setModel(self.sql_model) # 重新设置模型（可选）
# 打印数据（调试用）
for i in range(self.sql_model.rowCount()):
id = self.sql_model.data(self.sql_model.index(i, 0))
name = self.sql_model.data(self.sql_model.index(i, 1))
score = self.sql_model.data(self.sql_model.index(i, 2))
print(id, name, score)
def db_init(self):
self.db = QSqlDatabase.addDatabase("QSQLITE")
self.db.setDatabaseName("test.db")
if not self.db.open():
print("db open error")
error = self.db.lastError()
print(error.text())
else:
print("db open success")
def closeEvent(self, event):
try:
self.db.close()
event.accept()
except Exception as e:
print(e)
if __name__ == "__main__":
app = QApplication.instance() or QApplication([])
window = MyWidget()
window.show()
app.exec()