用pyqt5实现简单的SQLite表显示与可视化修改

• 表显示与修改
• 显示表的列名和类型
• 打开保存sqlite文件

# 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>1200</width>
    <height>614</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="sizePolicy">
       <sizepolicy hsizetype="Expanding" vsizetype="Expanding">
        <horstretch>0</horstretch>
        <verstretch>0</verstretch>
       </sizepolicy>
      </property>
      <property name="orientation">
       <enum>Qt::Horizontal</enum>
      </property>
      <widget class="QTreeView" name="treeView">
       <property name="sizePolicy">
        <sizepolicy hsizetype="Expanding" vsizetype="Maximum">
         <horstretch>0</horstretch>
         <verstretch>0</verstretch>
        </sizepolicy>
       </property>
       <property name="maximumSize">
        <size>
         <width>16777215</width>
         <height>600</height>
        </size>
       </property>
      </widget>
      <widget class="QTabWidget" name="tabWidget">
       <property name="currentIndex">
        <number>0</number>
       </property>
       <widget class="QWidget" name="tabwidget">
        <attribute name="title">
         <string>TableData</string>
        </attribute>
        <layout class="QHBoxLayout" name="horizontalLayout_2">
         <item>
          <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="7">
             <widget class="QPushButton" name="newRecordButton">
              <property name="text">
               <string>New Record</string>
              </property>
              <property name="icon">
               <iconset>
                <normaloff>E:/.designer/backup/python/pyQT-sqlite-gui/icons/sc_newrecord.png</normaloff>E:/.designer/backup/python/pyQT-sqlite-gui/icons/sc_newrecord.png</iconset>
              </property>
             </widget>
            </item>
            <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="5">
             <widget class="QPushButton" name="submitChange">
              <property name="text">
               <string>Submit</string>
              </property>
             </widget>
            </item>
            <item row="4" column="0" colspan="9">
             <widget class="QTableView" name="tableView"/>
            </item>
            <item row="0" column="4">
             <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="6">
             <widget class="QPushButton" name="rollback">
              <property name="text">
               <string>Rollback</string>
              </property>
             </widget>
            </item>
            <item row="0" column="2">
             <widget class="QPushButton" name="searchButton">
              <property name="text">
               <string>Search</string>
              </property>
              <property name="icon">
               <iconset>
                <normaloff>E:/.designer/backup/python/pyQT-sqlite-gui/icons/sc_refresh.png</normaloff>E:/.designer/backup/python/pyQT-sqlite-gui/icons/sc_refresh.png</iconset>
              </property>
             </widget>
            </item>
            <item row="0" column="8">
             <widget class="QPushButton" name="deleteRecordButton">
              <property name="text">
               <string>Delete Record</string>
              </property>
              <property name="icon">
               <iconset>
                <normaloff>E:/.designer/backup/python/pyQT-sqlite-gui/icons/sc_deleterecord.png</normaloff>E:/.designer/backup/python/pyQT-sqlite-gui/icons/sc_deleterecord.png</iconset>
              </property>
             </widget>
            </item>
            <item row="0" column="1">
             <widget class="QLineEdit" name="searchFor"/>
            </item>
           </layout>
          </widget>
         </item>
        </layout>
       </widget>
       <widget class="QWidget" name="tab_2">
        <attribute name="title">
         <string>QueryData</string>
        </attribute>
        <layout class="QHBoxLayout" name="horizontalLayout_5">
         <item>
          <widget class="QSplitter" name="splitter_2">
           <property name="orientation">
            <enum>Qt::Horizontal</enum>
           </property>
           <widget class="QWidget" name="layoutWidget">
            <layout class="QVBoxLayout" name="verticalLayout">
             <item>
              <widget class="QTableView" name="sqlView"/>
             </item>
             <item>
              <widget class="QTextEdit" name="textQuery"/>
             </item>
             <item>
              <layout class="QHBoxLayout" name="horizontalLayout_4">
               <item>
                <spacer name="horizontalSpacer_3">
                 <property name="orientation">
                  <enum>Qt::Horizontal</enum>
                 </property>
                 <property name="sizeHint" stdset="0">
                  <size>
                   <width>40</width>
                   <height>20</height>
                  </size>
                 </property>
                </spacer>
               </item>
               <item>
                <widget class="QPushButton" name="query">
                 <property name="text">
                  <string>Query</string>
                 </property>
                </widget>
               </item>
              </layout>
             </item>
            </layout>
           </widget>
          </widget>
         </item>
        </layout>
       </widget>
      </widget>
     </widget>
    </item>
   </layout>
  </widget>
  <widget class="QMenuBar" name="menubar">
   <property name="geometry">
    <rect>
     <x>0</x>
     <y>0</y>
     <width>1200</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>