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

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

声明：本文借鉴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)