用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));