用PYQT5实现非常sqlite简单数据显示编辑
目录 复制
主要思路
链接数据库
self.db = QSqlDatabase.addDatabase("QSQLITE")
filename = os.path.join(os.path.dirname(__file__),self.data_file)
self.db.setDatabaseName(filename)
if not self.db.open():
alert = QMessageBox()
alert.setText(self.db.lastError().text())
alert.exec_()
cachedTable
model.setEditStrategy(QSqlTableModel.OnManualSubmit)
treeview
self.tree_model.clear()
self.tree_model.setHorizontalHeaderLabels(['Name', 'Type','Schema'])
self.cvquery = QSqlQuery()
sql = u"SELECT name from sqlite_master where type = 'table' order by name"
if self.cvquery.exec_(sql):
while self.cvquery.next():
str_v = self.cvquery.value(0)
tab = QStandardItem('%ss (%d)' % (str_v,0))
self.tree_model.appendRow(tab)
driver = self.db.driver()
rec = driver.record(str_v)
for i in range(rec.count()):
col_name = QStandardItem(rec.field(i).name())
type_id=rec.field(i).type()
if type_id in TYPE_DICT: type_str=TYPE_DICT[type_id]
else: type_str=str(type_id)
col_type=QStandardItem(type_str)
tab.appendRow([col_name, col_type])
代码实现
# coding=utf-8
import sys
import os
from PyQt5.QtCore import *
from PyQt5.QtWidgets import *
from PyQt5.QtGui import *
from PyQt5.QtSql import *
from PyQt5 import uic
from PyQt5 import QtCore
TYPE_DICT={1:"BOOLEAN", 2:"INTEGER",6:"NUMERIC", 10:"TEXT", 12:"BLOB",13:"DATE"}
class WindowClass(QMainWindow):
def __init__(self,parent=None):
QMainWindow.__init__(self)
self.resize(800,480)
self.setWindowTitle("SQLite GUI")
self.initUI()
self.tree_model = QStandardItemModel()
self.treeView.setModel(self.tree_model)
def initUI(self):
uic.loadUi('sqlite.ui',self)
def load_sqlite(self):
path, _ = QFileDialog.getOpenFileName(self, u"打开sqlite文件",os.getcwd(), "sqlite db(*.db)")
if path:
self.data_file = path
self.connect_db()
def load_table(self):
str_arr = []
if not self.db.open():
return
self.cvquery = QSqlQuery()
sql = u"SELECT name from sqlite_master where type = 'table' order by name"
if self.cvquery.exec_(sql):
while self.cvquery.next():
str_v = self.cvquery.value(0)
str_arr.append(str_v)
return str_arr
def connect_db(self):
self.db = QSqlDatabase.addDatabase("QSQLITE")
filename = os.path.join(os.path.dirname(__file__),self.data_file)
self.db.setDatabaseName(filename)
if not self.db.open():
alert = QMessageBox()
alert.setText(self.db.lastError().text())
alert.exec_()
# self.load_table_group()
tb_names = self.load_table()
self.table_selector.clear()
for name in tb_names:
self.table_selector.addItem(name)
self.update_tables_table()
def update_tables_table(self):
self.tree_model.clear()
self.tree_model.setHorizontalHeaderLabels(['Name', 'Type','Schema'])
if not self.db.open():
return
self.cvquery = QSqlQuery()
sql = u"SELECT name from sqlite_master where type = 'table' order by name"
if self.cvquery.exec_(sql):
while self.cvquery.next():
str_v = self.cvquery.value(0)
tab = QStandardItem('%ss (%d)' % (str_v,0))
self.tree_model.appendRow(tab)
driver = self.db.driver()
rec = driver.record(str_v)
for i in range(rec.count()):
col_name = QStandardItem(rec.field(i).name())
type_id=rec.field(i).type()
if type_id in TYPE_DICT: type_str=TYPE_DICT[type_id]
else: type_str=str(type_id)
col_type=QStandardItem(type_str)
tab.appendRow([col_name, col_type])
def close_db(self):
if self.db:
if self.db.isOpen():
pass
def on_tableView_currentItemChanged(self,pre,current):
print("edit")
# twitem.setBackgroundColor(QColor(0,60,10))
# ui binding
@QtCore.pyqtSlot()
def on_actionNew_triggered(self):
save_file_dialog = QFileDialog.getSaveFileName(self, "Name of new database")
if save_file_dialog[0]:
self.loadDatabase(save_file_dialog[0])
@QtCore.pyqtSlot()
def on_actionOpen_triggered(self):
self.load_sqlite()
@QtCore.pyqtSlot(str)
def on_table_selector_currentIndexChanged(self,tbl_name):
if tbl_name:
model = QSqlTableModel()
model.setTable('"'+tbl_name+'"')
model.setEditStrategy(QSqlTableModel.OnManualSubmit)
# model.setEditStrategy(QSqlTableModel.OnFieldChange)
model.select()
# self.error_check(model)s
self.tableView.setModel(model)
self.tableView.model().dataChanged.connect(self.changed)
def changed(self,i,j):
# item = self.tableView.model().index(i.row(),i.column())
# model->item(i,0)->setForeground(QBrush(QColor(255, 0, 0)));
# self.tableView.model().record(num).value(1).toString()
pass
@QtCore.pyqtSlot()
def on_deleteRecordButton_pressed(self):
model = self.tableView.model()
model.removeRow(self.tableView.currentIndex().row())
model.submitAll()
model.select()
# QMessageBox.warning(self, "Delete",
# "The database reported an error: %s" % model.lastError().text())
@QtCore.pyqtSlot()
def on_newRecordButton_pressed(self):
model = self.tableView.model()
model.submitAll()
result = model.insertRows(model.rowCount(), 1)
if not result:
self.error_check(model)
@QtCore.pyqtSlot()
def on_submitChange_pressed(self):
model = self.tableView.model()
model.database().transaction()
if model.submitAll():
model.database().commit()
else:
model.database().rollback()
QMessageBox.warning(self, "Cached Table",
"The database reported an error: %s" % self.model.lastError().text())
@QtCore.pyqtSlot()
def on_rollback_pressed(self):
model = self.tableView.model()
model.database().transaction()
model.revertAll();
if __name__ == '__main__':
app = QApplication([])
mw = WindowClass()
mw.show()
app.exec_()
界面实现
<?xml version="1.0" encoding="UTF-8"?>
<ui version="4.0">
<class>MainWindow</class>
<widget class="QMainWindow" name="MainWindow">
<property name="geometry">
<rect>
<x>0</x>
<y>0</y>
<width>1096</width>
<height>754</height>
</rect>
</property>
<property name="windowTitle">
<string>SQLite GUI</string>
</property>
<widget class="QWidget" name="centralwidget">
<property name="enabled">
<bool>true</bool>
</property>
<property name="sizePolicy">
<sizepolicy hsizetype="Expanding" vsizetype="Expanding">
<horstretch>0</horstretch>
<verstretch>0</verstretch>
</sizepolicy>
</property>
<layout class="QHBoxLayout" name="horizontalLayout">
<item>
<widget class="QSplitter" name="splitter">
<property name="orientation">
<enum>Qt::Horizontal</enum>
</property>
<widget class="QTreeView" name="treeView"/>
<widget class="QWidget" name="Edit_widget" native="true">
<property name="enabled">
<bool>true</bool>
</property>
<property name="sizePolicy">
<sizepolicy hsizetype="Expanding" vsizetype="Expanding">
<horstretch>0</horstretch>
<verstretch>0</verstretch>
</sizepolicy>
</property>
<layout class="QGridLayout" name="gridLayout">
<item row="0" column="0">
<layout class="QGridLayout" name="gridLayout_2">
<item row="0" column="0">
<widget class="QLabel" name="label_2">
<property name="text">
<string>Table:</string>
</property>
</widget>
</item>
<item row="0" column="1">
<widget class="QComboBox" name="table_selector">
<property name="sizePolicy">
<sizepolicy hsizetype="Expanding" vsizetype="Fixed">
<horstretch>0</horstretch>
<verstretch>0</verstretch>
</sizepolicy>
</property>
<property name="minimumSize">
<size>
<width>150</width>
<height>0</height>
</size>
</property>
</widget>
</item>
</layout>
</item>
<item row="0" column="1">
<widget class="QPushButton" name="reloadTableButton">
<property name="text">
<string/>
</property>
<property name="icon">
<iconset>
<normaloff>sc_refresh.png</normaloff>sc_refresh.png</iconset>
</property>
</widget>
</item>
<item row="0" column="6">
<widget class="QPushButton" name="deleteRecordButton">
<property name="text">
<string>Delete Record</string>
</property>
<property name="icon">
<iconset>
<normaloff>sc_deleterecord.png</normaloff>sc_deleterecord.png</iconset>
</property>
</widget>
</item>
<item row="0" column="3">
<widget class="QPushButton" name="submitChange">
<property name="text">
<string>Submit</string>
</property>
</widget>
</item>
<item row="0" column="2">
<spacer name="horizontalSpacer">
<property name="orientation">
<enum>Qt::Horizontal</enum>
</property>
<property name="sizeHint" stdset="0">
<size>
<width>288</width>
<height>20</height>
</size>
</property>
</spacer>
</item>
<item row="0" column="5">
<widget class="QPushButton" name="newRecordButton">
<property name="text">
<string>New Record</string>
</property>
<property name="icon">
<iconset>
<normaloff>sc_newrecord.png</normaloff>sc_newrecord.png</iconset>
</property>
</widget>
</item>
<item row="0" column="4">
<widget class="QPushButton" name="rollback">
<property name="text">
<string>Rollback</string>
</property>
</widget>
</item>
<item row="1" column="0" colspan="7">
<widget class="QTableView" name="tableView"/>
</item>
</layout>
</widget>
</widget>
</item>
</layout>
</widget>
<widget class="QMenuBar" name="menubar">
<property name="geometry">
<rect>
<x>0</x>
<y>0</y>
<width>1096</width>
<height>22</height>
</rect>
</property>
<widget class="QMenu" name="menuFile">
<property name="title">
<string>File</string>
</property>
<addaction name="actionNew"/>
<addaction name="actionOpen"/>
<addaction name="actionRecent_Files"/>
<addaction name="separator"/>
<addaction name="actionQuit"/>
</widget>
<widget class="QMenu" name="menuHelp">
<property name="title">
<string>Help</string>
</property>
<addaction name="actionAbout"/>
</widget>
<addaction name="menuFile"/>
<addaction name="menuHelp"/>
</widget>
<widget class="QStatusBar" name="statusbar"/>
<action name="actionNew">
<property name="text">
<string>New</string>
</property>
<property name="shortcut">
<string>Ctrl+N</string>
</property>
</action>
<action name="actionOpen">
<property name="text">
<string>Open</string>
</property>
<property name="shortcut">
<string>Ctrl+O</string>
</property>
</action>
<action name="actionRecent_Files">
<property name="text">
<string>Recent Files</string>
</property>
</action>
<action name="actionQuit">
<property name="text">
<string>Quit</string>
</property>
</action>
<action name="actionAbout">
<property name="text">
<string>About</string>
</property>
</action>
</widget>
<resources/>
<connections/>
</ui>
功能介绍
解决的问题:
初级的GUI界面,可以修改或批量撤销修改
待解决的问题:
- 目前只能显示表和修改表
- 编辑过的单元格如何高亮显示
- 删除行是直接执行的,无法用database().revertAll()恢复
- 近期使用文件未显示