用PySide6和Sqlite3写了一个demo，具备基本的增删改查功能，稍作修改PyQt也可以用。
编辑
编辑
编辑
话不多说，贴上代码：
Main.py
import sys
from PySide6.QtCore import QRect, QSize
from PySide6.QtGui import QIcon, QCursor, QAction, QKeySequence, Qt
from PySide6.QtWidgets import (QApplication, QWidget, QVBoxLayout,
QTreeWidget, QTreeWidgetItem, QHBoxLayout, QPushButton, QMenu, QToolButton, QFrame,
QTableWidget, QSpacerItem, QSizePolicy, QLineEdit, QTableWidgetItem, QAbstractItemView,
QStyleFactory)
from demo.sqlite3 import SqliteDB
from demo.sqlite3.UserEditDialog import UserEditDialog
class MainWindow(QWidget):
def __init__(self, parent=None):
super(MainWindow, self).__init__(parent)
self.setWindowTitle("Sqlite3增删改查")
self.resize(600, 432)
self.verticalLayout = QVBoxLayout(self)
self.frame = QWidget(self)
self.horizontalLayout = QHBoxLayout(self.frame)
self.horizontalLayout.setContentsMargins(0, 0, 0, 0)
self.createButton = QPushButton("新建", self.frame)
self.horizontalLayout.addWidget(self.createButton)
self.createButton.clicked.connect(self.createUser)
self.modifyButton = QPushButton("修改", self.frame)
self.horizontalLayout.addWidget(self.modifyButton)
self.modifyButton.clicked.connect(self.modifyUser)
self.deleteButton = QPushButton("删除", self.frame)
self.horizontalLayout.addWidget(self.deleteButton)
self.deleteButton.clicked.connect(self.deleteUser)
self.horizontalSpacer = QSpacerItem(40, 20, QSizePolicy.Expanding, QSizePolicy.Minimum)
self.horizontalLayout.addItem(self.horizontalSpacer)
self.searchLineEdit = QLineEdit(self.frame)
self.horizontalLayout.addWidget(self.searchLineEdit)
self.searchButton = QPushButton("查询", self.frame)
self.horizontalLayout.addWidget(self.searchButton)
self.searchButton.clicked.connect(self.searchUser)
self.verticalLayout.addWidget(self.frame)
self.tableWidget = QTableWidget(self)
self.verticalLayout.addWidget(self.tableWidget)
# 设置表头是否显示
self.tableWidget.verticalHeader().setVisible(False)
self.tableWidget.horizontalHeader().setVisible(True)
# 设置行数
self.tableWidget.setRowCount(0)
# 设置列数
self.tableWidget.setColumnCount(6)
# 设置是否显示网格线
self.tableWidget.setShowGrid(True)
# 设置最后一列宽度自动填充
self.tableWidget.horizontalHeader().setStretchLastSection(True)
# 设置水平表头标签
self.tableWidget.setHorizontalHeaderLabels(['ID', '姓名', '性别', '年龄', '手机', '邮箱'])
# 设置垂直表头内容居中显示
self.tableWidget.horizontalHeader().setDefaultAlignment(Qt.AlignCenter)
# 将表格变为禁止编辑
self.tableWidget.setEditTriggers(QAbstractItemView.NoEditTriggers)
# 用于控制当选择了tableView视图中数据项时，对应的表头区域是否高亮，默认高亮显示
self.tableWidget.verticalHeader().setHighlightSections(False)
self.tableWidget.horizontalHeader().setHighlightSections(False)
# 设置表格整行选中
self.tableWidget.setSelectionMode(QAbstractItemView.SingleSelection)
# self.setSelectionMode(QAbstractItemView.ContiguousSelection)
self.tableWidget.setSelectionBehavior(QAbstractItemView.SelectRows)
self.tableWidget.setColumnWidth(0, 30)
self.tableWidget.setColumnWidth(1, 70)
self.tableWidget.setColumnWidth(2, 40)
self.tableWidget.setColumnWidth(3, 40)
self.tableWidget.setColumnWidth(4, 100)
self.tableWidget.setColumnWidth(5, 120)
# self.tableWidget.hideColumn(0)
user_list = SqliteDB.FindAllUser()
self.loadUsers(user_list)
def loadUsers(self, user_list):
self.tableWidget.clearContents()
self.tableWidget.setRowCount(len(user_list))
sex = ['男', '女']
for i, row in enumerate(user_list):
item = QTableWidgetItem(str(row[0]))
self.tableWidget.setItem(i, 0, item)
item = QTableWidgetItem(row[1])
self.tableWidget.setItem(i, 1, item)
item = QTableWidgetItem(sex[row[2]])
item.setTextAlignment(Qt.AlignCenter)
self.tableWidget.setItem(i, 2, item)
item = QTableWidgetItem(str(row[3]))
item.setTextAlignment(Qt.AlignCenter)
self.tableWidget.setItem(i, 3, item)
item = QTableWidgetItem(row[4])
self.tableWidget.setItem(i, 4, item)
item = QTableWidgetItem(row[5])
self.tableWidget.setItem(i, 5, item)
return user_list
def createUser(self):
dialog = UserEditDialog(self, '新建')
dialog.exec()
if dialog.user_dict:
SqliteDB.SaveUser(dialog.user_dict)
user_list = SqliteDB.FindAllUser()
self.loadUsers(user_list)
def modifyUser(self):
selectedItems = self.tableWidget.selectedItems()
if selectedItems:
userid = selectedItems[0].text()
user = SqliteDB.FindOneByID(userid)
dialog = UserEditDialog(self, '修改')
dialog.setData(user)
dialog.exec()
if dialog.user_dict:
SqliteDB.Update(userid, dialog.user_dict)
user_list = SqliteDB.FindAllUser()
self.loadUsers(user_list)
def deleteUser(self):
selectedItems = self.tableWidget.selectedItems()
if selectedItems:
userid = selectedItems[0].text()
SqliteDB.DeleteUser(userid)
row = self.tableWidget.row(selectedItems[0])
self.tableWidget.removeRow(row)
def searchUser(self):
user_dict = {}
condition = self.searchLineEdit.text()
if condition:
user_dict['id'] = condition
user_dict['name'] = condition
if condition == '男':
user_dict['sex'] = 0
if condition == '女':
user_dict['sex'] = 1
user_dict['age'] = condition
user_dict['mobile'] = condition
user_dict['email'] = condition
user_list = SqliteDB.FindUserByConditions(user_dict)
self.loadUsers(user_list)
if __name__ == "__main__":
app = QApplication([])
app.setStyle(QStyleFactory.create('Fusion'))
window = MainWindow()
window.show()
sys.exit(app.exec())
Sqlite3DB.py
from dbutils.persistent_db import PersistentDB
import sqlite3
class Pool(object): # 数据库连接池
__pool = None # 记录第一个被创建的对象引用
config = {
'database': 'user.db' # 数据库文件路径
}
def __new__(cls, *args, **kwargs):
"""创建连接池对象 单例设计模式(每个线程中只创建一个连接池对象) PersistentDB为每个线程提供专用的连接池"""
if cls.__pool is None: # 如果__pool为空，说明创建的是第一个连接池对象
cls.__pool = PersistentDB(sqlite3, maxusage=None, closeable=False, **cls.config)
return cls.__pool
class Connect:
def __enter__(self):
"""自动从连接池中取出一个连接"""
db_pool = Pool()
self.conn = db_pool.connection()
self.cur = self.conn.cursor()
return self
def __exit__(self, exc_type, exc_val, exc_tb):
"""自动释放当前连接资源 归还给连接池"""
self.cur.close()
self.conn.close()
# 保存一条记录
def SaveUser(data_dict):
data = []
fields = ''
values = ''
for k, v in enumerate(data_dict):
fields = fields + v
values = values + '?'
if k < len(data_dict) - 1:
fields = fields + ','
values = values + ','
data.append(data_dict[v])
sql = f'INSERT INTO user ({fields}) VALUES({values})'
with Connect() as db:
db.cur.execute(sql, data)
db.conn.commit()
# 查询指定表格所有数据
def FindAllUser():
sql = 'SELECT * FROM user'
with Connect() as db:
db.cur.execute(sql)
result = db.cur.fetchall()
return result
def Update(id, data_dict):
data = []
sql = 'update user set '
for k, v in enumerate(data_dict):
sql = sql + v + ' = ?'
if k < len(data_dict) - 1:
sql = sql + ', '
data.append(data_dict[v])
sql = sql + ' where id = ' + str(id)
with Connect() as db:
db.cur.execute(sql, data)
db.conn.commit()
# 根据ID删除一条记录
def DeleteUser(id):
sql = f'DELETE FROM user WHERE id={id}'
with Connect() as db:
db.cur.execute(sql)
db.conn.commit()
# 根据ID查询一条记录
def FindOneByID(id):
sql = f'SELECT * FROM user WHERE id = {id}'
with Connect() as db:
db.cur.execute(sql)
result = db.cur.fetchone()
return result
# 执行一条SQL语句，查询多条记录
def FindAllBySQL(sql):
with Connect() as db:
db.cur.execute(sql)
result = db.cur.fetchall()
return result
# 根据ID查询
def FindUserByConditions(data_dict):
sql = 'select * from user '
condition = ''
for k, v in enumerate(data_dict):
if condition == '':
condition = ' where '
else:
condition = f'{condition} or '
if v == 'sex':
condition = f'{condition} sex={data_dict[v]}'
else:
condition = f'{condition} {str(v)} like "%{data_dict[v]}%"'
sql = sql + condition
with Connect() as db: # 从连接池中取出一个连接
db.cur.execute(sql)
result = db.cur.fetchall()
return result
UserEditDialog.Py
from PySide6.QtCore import Qt
from PySide6.QtWidgets import QVBoxLayout, QLineEdit, QHBoxLayout, QSpacerItem, QSizePolicy, QPushButton, \
QDialog, QFormLayout, QLabel, QRadioButton, QSpinBox, QMessageBox
class UserEditDialog(QDialog):
def __init__(self, parent=None, title=None):
super(UserEditDialog, self).__init__(parent)
self.setWindowTitle(title)
self.resize(300, 200)
self.setWindowModality(Qt.ApplicationModal)
self.user_dict = {}
self.verticalLayout = QVBoxLayout(self)
self.formLayout = QFormLayout()
self.formLayout.setVerticalSpacing(10)
self.formLayout.setHorizontalSpacing(10)
self.label_1 = QLabel("姓 名", self)
self.formLayout.setWidget(0, QFormLayout.LabelRole, self.label_1)
self.nameEditText = QLineEdit(self)
self.formLayout.setWidget(0, QFormLayout.FieldRole, self.nameEditText)
self.label_2 = QLabel("性 别", self)
self.formLayout.setWidget(1, QFormLayout.LabelRole, self.label_2)
self.radioButton_1 = QRadioButton("男", self)
self.radioButton_1.setChecked(True)
self.radioButton_2 = QRadioButton("女", self)
self.horizontalLayout_1 = QHBoxLayout()
self.horizontalLayout_1.addWidget(self.radioButton_1)
self.horizontalLayout_1.addWidget(self.radioButton_2)
self.horizontalSpacer = QSpacerItem(40, 20, QSizePolicy.Expanding, QSizePolicy.Minimum)
self.horizontalLayout_1.addItem(self.horizontalSpacer)
self.formLayout.setLayout(1, QFormLayout.FieldRole, self.horizontalLayout_1)
self.label_3 = QLabel("年 龄", self)
self.formLayout.setWidget(2, QFormLayout.LabelRole, self.label_3)
self.horizontalLayout_2 = QHBoxLayout()
self.ageSpinBox = QSpinBox(self)
self.horizontalLayout_2.addWidget(self.ageSpinBox)
self.horizontalSpacer_2 = QSpacerItem(40, 20, QSizePolicy.Expanding, QSizePolicy.Minimum)
self.horizontalLayout_2.addItem(self.horizontalSpacer_2)
self.formLayout.setLayout(2, QFormLayout.FieldRole, self.horizontalLayout_2)
self.label_4 = QLabel("手 机", self)
self.formLayout.setWidget(3, QFormLayout.LabelRole, self.label_4)
self.mobileEditText = QLineEdit(self)
self.formLayout.setWidget(3, QFormLayout.FieldRole, self.mobileEditText)
self.label_5 = QLabel("邮 箱", self)
self.formLayout.setWidget(4, QFormLayout.LabelRole, self.label_5)
self.emailEditText = QLineEdit(self)
self.formLayout.setWidget(4, QFormLayout.FieldRole, self.emailEditText)
self.verticalLayout.addLayout(self.formLayout)
self.horizontalLayout = QHBoxLayout()
self.horizontalSpacer = QSpacerItem(40, 20, QSizePolicy.Expanding, QSizePolicy.Minimum)
self.horizontalLayout.addItem(self.horizontalSpacer)
self.okButton = QPushButton("确定", self)
self.horizontalLayout.addWidget(self.okButton)
self.cancelButton = QPushButton("取消", self)
self.horizontalLayout.addWidget(self.cancelButton)
self.okButton.clicked.connect(self.onOkButton)
self.cancelButton.clicked.connect(lambda: self.close())
self.verticalLayout.addLayout(self.horizontalLayout)
def setData(self, user):
print(user)
self.nameEditText.setText(user[1])
if user[2] == 0:
self.radioButton_1.setChecked(True)
elif user[2] == 1:
self.radioButton_2.setChecked(True)
self.ageSpinBox.setValue(user[3])
self.mobileEditText.setText(user[4])
self.emailEditText.setText(user[5])
def onOkButton(self):
if not self.nameEditText.text():
QMessageBox().information(None, "提示", "请输入姓名", QMessageBox.Yes)
return
self.user_dict['name'] = self.nameEditText.text()
sex = 0
if self.radioButton_1.isChecked():
sex = 0
else:
sex = 1
self.user_dict['sex'] = sex
age = self.ageSpinBox.value()
self.user_dict['age'] = age
self.user_dict['mobile'] = self.mobileEditText.text()
self.user_dict['email'] = self.emailEditText.text()
self.close()
数据库创建脚本：
CREATE TABLE [user](
[id] INTEGER PRIMARY KEY AUTOINCREMENT,
[name] VARCHAR(20),
[sex] INTEGER,
[age] INTEGER,
[mobile] VARCHAR(20),
[email] VARCHAR(50));