PyQT 跟我学做密码管理器（4）

向右看齐 2022-04-03 04:56 139阅读 0赞

# PyQT 跟我学做密码管理器（4） —— 加载数据库 #

声明：本文借鉴[https://blog.csdn.net/bigbennyguo/article/details/50755207 修改而来][https_blog.csdn.net_bigbennyguo_article_details_50755207]

## 前言 ##

借鉴的文章是基于python2+pyqt4，本文是基于python3+pyqt5

## **环境** ##

编辑器：Sublime Text3

操作系统：win10

Python3

所需要的库：主要是PyQT5，因为加了一点拓展功能，所以还有sqlite3,Image,pyinstaller

pip3 install pyqt5
    pip3 install pyqt5-tools
    pip3 install sqilte3
    pip3 install Image
    pip3 install pyinstaller

首先是pyqt5, 这个是我们的主要工具，用来做界面的，pyqt5-tools这个库主要是是在用QT Creator设计好界面之后，将ui文件转化为py文件；

sqlite3这个库可以安也可以不安，因为pyqt5里面有一个模块QtSql，它支持sqlite的操作；  
Image这个库主要是用来处理一下图片资源，以及最后程序的图标的  
pyinstaller 最后将程序转化为可执行的exe文件

## 加载数据库 ##

选择的是sqlite3,因为这个轻量级，能够移植  
关于sqlite3的操作语句，其实没有必要了解特别多，网上随便搜一下，学点简单的语法就好了，这里不过多说sql语句的问题

首先是导入sqlite3(前面说过，因为是使用的Pyqt,可以不用导入sqlite3,直接使用pyqt自带的QtSql),它两个的数据库语句一样，只是导入方式，还有用法稍微有一点不一样，这里使用的是sqlite3的方法

初始化DB函数initDB

首先判断本地有没有这个DB文件，没有就初始化数据库文件，建表，获取连接句柄  
有这个文件，就直接获取数据库句柄  
根据数据库句柄，得到数据库中的所有数据

def initDB(self):
            home = os.path.expanduser('~')
            if '.PasswordManageSystem' not in os.listdir(home):
                os.mkdir(os.path.join(home, '.PasswordManageSystem'))
    
            dbpath = os.path.join(home, '.PasswordManageSystem', 'PasswordManagement.db')
    
            if os.path.exists(dbpath):
                self.conn = sqlite3.connect(dbpath)
                self.conn.isolation_level = None
            else:
                self.conn = sqlite3.connect(dbpath)
                self.conn.isolation_level = None
                self.conn.execute('''CREATE TABLE INFO
                            (ID int PRIMARY KEY NOT NULL,
                            WEBSITE char(255),
                            USERNAME char(255),
                            PASSWORD char(255),
                            URL char(255))''')
            cur = self.conn.cursor()
            cur.execute('SELECT * FROM INFO')
            self.displayData = cur.fetchall()
            cur.close()
            self.current_row = len(self.displayData)

然后将这些数据在初始化tableview的时候，将其加载出来

def initGrid(self):
            self.grid = QTableWidget()
            self.setCentralWidget(self.grid)
            self.grid.setColumnCount(4)
            self.grid.setRowCount(0)
            column_width = [75, 150, 270, 150]
            for column in range(4):
                self.grid.setColumnWidth(column, column_width[column])
            headerlabels = ['Website', 'Username', 'Password', 'Url']
            self.grid.setHorizontalHeaderLabels(headerlabels)
            self.grid.setEditTriggers(QAbstractItemView.NoEditTriggers)
            self.grid.setSelectionBehavior(QAbstractItemView.SelectRows)
            for row in range(len(self.displayData)):#根据数据库数据的数目，决定循环次数
                self.grid.insertRow(row)            #插入一行
                data = self.displayData[row]        #获取一条数据
                for i in range(4):
                    item=data[i+1]                #获取一条数据中的一个元素
                    #print(row, i, item)
                    new_item = QTableWidgetItem(item)    #将数据转化为QTableWidgetItem
                    self.grid.setItem(row, i, new_item)    #插入数据

新建功能：

def newAction_def(self):
            data = self.showDialog()
            if data[0]:
                self.current_row += 1
                self.conn.execute("INSERT INTO INFO VALUES(%d, '%s', '%s', '%s', '%s')"
                                  % (self.current_row, data[1], data[2], data[3], data[4]))
                self.grid.insertRow(self.current_row - 1)
                for i in range(4):
                    new_item = QTableWidgetItem(data[i + 1])
                    self.grid.setItem(self.current_row - 1, i, new_item)

编辑功能

def editAction_def(self):
            selected_row = self.grid.selectedItems()
            if selected_row:
                edit_row = self.grid.row(selected_row[0])
                old_data = []
                for i in range(4):
                    old_data.append(self.grid.item(edit_row, i).text())
                new_data = self.showDialog(*old_data)
                if new_data[0]:
                    self.conn.execute('''UPDATE INFO SET
                                     WEBSITE = '%s', USERNAME = '%s',
                                     PASSWORD = '%s', URL = '%s'
                                     WHERE ID = '%d' '''
                                  % (new_data[1], new_data[2], new_data[3], new_data[4], edit_row + 1))
                    for i in range(4):
                        new_item = QTableWidgetItem(new_data[i + 1])
                        self.grid.setItem(edit_row, i, new_item)
            else:
                self.showHint()

删除功能

def delAction_def(self):
            selected_row = self.grid.selectedItems()
            if selected_row:
                del_row = self.grid.row(selected_row[0])
                self.grid.removeRow(del_row)
                self.conn.execute("DELETE FROM INFO WHERE ID = %d" % (del_row + 1))
                for index in range(del_row + 2, self.current_row + 1):
                    self.conn.execute("UPDATE INFO SET ID = %d WHERE ID = %d" % ((index - 1), index))
                self.current_row -= 1
            else:
                self.showHint()

del函数要注意了，删除某项之后，其后项目ID并不会自动改变，我们需要手动把后面的项目序号-1……就是下面这两句：

for index in range(del_row + 2, self.current_row + 1):
                    self.conn.execute("UPDATE INFO SET ID = %d WHERE ID = %d" % ((index - 1), index))

## 小结 ##

基本上这功能就完成了，最后还剩一个备份功能，因为涉及到邮箱登陆，所以下一节再讲

目前完整的代码如下：

# -*- coding: utf-8 -*-
    # @Date     : 2018-12-17 16:50:23
    # @Author   : Jimy_Fengqi (jmps515@163.com)
    # @Link     : https://blog.csdn.net/qiqiyingse
    # @Version  : V1.0
    # @pyVersion: 3.6
    
    import sys,sqlite3,os
    from PyQt5.QtWidgets import *
    from PyQt5.QtGui import QIcon, QFont
    from PyQt5.QtCore import *
    import images
    
    
    class PWKeeper(QMainWindow):
        def __init__(self):
            super(PWKeeper, self).__init__()
            self.initToolbar()
            self.initDB()
            self.initGrid()
            #self.current_row = 0
            self.setGeometry(300, 300, 650, 300)
            self.setWindowTitle('密码管理器')
            self.setWindowIcon(QIcon(':images/logo.jpg'))
    
        def initToolbar(self):
            newAction = QAction(QIcon(':images/new.png'), 'New Ctrl+N', self)
            editAction = QAction(QIcon(':images/edit.png'), 'Edit Ctrl+E', self)
            delAction = QAction(QIcon(':images/del.png'), 'Delete', self)
            backupAction = QAction(QIcon(':images/backup.png'), 'Backup Ctrl+B', self)
            newAction.setShortcut('Ctrl+N')
            editAction.setShortcut('Ctrl+E')
            delAction.setShortcut('Delete')
            backupAction.setShortcut('Ctrl+B')
            newAction.triggered.connect(self.newAction_def)
            editAction.triggered.connect(self.editAction_def)
            delAction.triggered.connect(self.delAction_def)
            backupAction.triggered.connect(self.backupAction_def)
            self.tb_new = self.addToolBar('New')
            self.tb_edit = self.addToolBar('Edit')
            self.tb_del = self.addToolBar('Del')
            self.tb_backup = self.addToolBar('Backup')
            self.tb_new.addAction(newAction)
            self.tb_edit.addAction(editAction)
            self.tb_del.addAction(delAction)
            self.tb_backup.addAction(backupAction)
    
        def backupAction_def(self):
            pass
    
        def initDB(self):
            home = os.path.expanduser('~')
            if '.PasswordManageSystem' not in os.listdir(home):
                os.mkdir(os.path.join(home, '.PasswordManageSystem'))
    
            dbpath = os.path.join(home, '.PasswordManageSystem', 'PasswordManagement.db')
    
            if os.path.exists(dbpath):
                self.conn = sqlite3.connect(dbpath)
                self.conn.isolation_level = None
            else:
                self.conn = sqlite3.connect(dbpath)
                self.conn.isolation_level = None
                self.conn.execute('''CREATE TABLE INFO
                            (ID int PRIMARY KEY NOT NULL,
                            WEBSITE char(255),
                            USERNAME char(255),
                            PASSWORD char(255),
                            URL char(255))''')
            cur = self.conn.cursor()
            cur.execute('SELECT * FROM INFO')
            self.displayData = cur.fetchall()
            cur.close()
            self.current_row = len(self.displayData)
    
        def initGrid(self):
            self.grid = QTableWidget()
            self.setCentralWidget(self.grid)
            self.grid.setColumnCount(4)
            self.grid.setRowCount(0)
            column_width = [75, 150, 270, 150]
            for column in range(4):
                self.grid.setColumnWidth(column, column_width[column])
            headerlabels = ['Website', 'Username', 'Password', 'Url']
            self.grid.setHorizontalHeaderLabels(headerlabels)
            self.grid.setEditTriggers(QAbstractItemView.NoEditTriggers)
            self.grid.setSelectionBehavior(QAbstractItemView.SelectRows)
            for row in range(len(self.displayData)):#根据数据库数据的数目，决定循环次数
                self.grid.insertRow(row)            #插入一行
                data = self.displayData[row]        #获取一条数据
                for i in range(4):
                    item=data[i+1]                #获取一条数据中的一个元素
                    #print(row, i, item)
                    new_item = QTableWidgetItem(item)    #将数据转化为QTableWidgetItem
                    self.grid.setItem(row, i, new_item)    #插入数据
    
        def newAction_def(self):
            data = self.showDialog()
            if data[0]:
                self.current_row += 1
                self.conn.execute("INSERT INTO INFO VALUES(%d, '%s', '%s', '%s', '%s')"
                                  % (self.current_row, data[1], data[2], data[3], data[4]))
                self.grid.insertRow(self.current_row - 1)
                for i in range(4):
                    new_item = QTableWidgetItem(data[i + 1])
                    self.grid.setItem(self.current_row - 1, i, new_item)
    
        def editAction_def(self):
            selected_row = self.grid.selectedItems()
            if selected_row:
                edit_row = self.grid.row(selected_row[0])
                old_data = []
                for i in range(4):
                    old_data.append(self.grid.item(edit_row, i).text())
                new_data = self.showDialog(*old_data)
                if new_data[0]:
                    self.conn.execute('''UPDATE INFO SET
                                     WEBSITE = '%s', USERNAME = '%s',
                                     PASSWORD = '%s', URL = '%s'
                                     WHERE ID = '%d' '''
                                  % (new_data[1], new_data[2], new_data[3], new_data[4], edit_row + 1))
                    for i in range(4):
                        new_item = QTableWidgetItem(new_data[i + 1])
                        self.grid.setItem(edit_row, i, new_item)
            else:
                self.showHint()
    
        def delAction_def(self):
            selected_row = self.grid.selectedItems()
            if selected_row:
                del_row = self.grid.row(selected_row[0])
                self.grid.removeRow(del_row)
                self.conn.execute("DELETE FROM INFO WHERE ID = %d" % (del_row + 1))
                for index in range(del_row + 2, self.current_row + 1):
                    self.conn.execute("UPDATE INFO SET ID = %d WHERE ID = %d" % ((index - 1), index))
                self.current_row -= 1
            else:
                self.showHint()
       
        def showHint(self):
            hint_msg = QMessageBox()
            hint_msg.setText('No selected row!')
            hint_msg.addButton(QMessageBox.Ok)
            hint_msg.exec_()
    
        def showDialog(self, ws = '', un = '', pw = '', url = ''):
    	    edit_dialog = QDialog(self)
    	    group = QGroupBox('Edit Info', edit_dialog)
    
    	    lbl_website = QLabel('Website:', group)
    	    le_website = QLineEdit(group)
    	    le_website.setText(ws)
    	    lbl_username = QLabel('Username:', group)
    	    le_username = QLineEdit(group)
    	    le_username.setText(un)
    	    lbl_password = QLabel('Password:', group)
    	    le_password = QLineEdit(group)
    	    le_password.setText(pw)
    	    lbl_url = QLabel('Url:', group)
    	    le_url = QLineEdit(group)
    	    le_url.setText(url)
    	    ok_button = QPushButton('OK', edit_dialog)
    	    cancel_button = QPushButton('CANCEL', edit_dialog)
    
    	    ok_button.clicked.connect(edit_dialog.accept)
    	    ok_button.setDefault(True)
    	    cancel_button.clicked.connect(edit_dialog.reject)
    
    	    group_layout = QVBoxLayout()
    	    group_item = [lbl_website, le_website,
    	                  lbl_username, le_username,
    	                  lbl_password, le_password,
    	                  lbl_url, le_url]
    	    for item in group_item:
    	        group_layout.addWidget(item)
    	    group.setLayout(group_layout)
    	    group.setFixedSize(group.sizeHint())
    
    	    button_layout = QHBoxLayout()
    	    button_layout.addWidget(ok_button)
    	    button_layout.addWidget(cancel_button)
    
    	    dialog_layout = QVBoxLayout()
    	    dialog_layout.addWidget(group)
    	    dialog_layout.addLayout(button_layout)
    	    edit_dialog.setLayout(dialog_layout)
    	    edit_dialog.setFixedSize(edit_dialog.sizeHint())
    
    	    if edit_dialog.exec_():
    	        website = le_website.text()
    	        username = le_username.text()
    	        password = le_password.text()
    	        url = le_url.text()
    	        return True, website, username, password, url
    	    return False, None, None, None, None
    
    if __name__ == '__main__':
        app = QApplication(sys.argv)
        pwk = PWKeeper()
        pwk.show()
        app.exec_()
        pwk.conn.close()
        sys.exit(0)

[https_blog.csdn.net_bigbennyguo_article_details_50755207]: https://blog.csdn.net/bigbennyguo/article/details/50755207