C9:Database Handling

Creating a database

Final Result

How to do it?

class MyForm(QDialog):
    def __init__(self):
        super().__init__()
        self.ui = Ui_Dialog()
        self.ui.setupUi(self)
        self.ui.pushButton.clicked.connect(self.creadteDatabase)
        self.show()
    def creadteDatabase(self):
        try:
            conn = sqlite3.connect(self.ui.lineEdit.text() + ".db")
            self.ui.label_2.setText("Database is created")
        except sqlite3.Error as e:
            self.ui.label_2.setText("Some error has occurred")
        finally:
            conn.close()
if __name__=="__main__":
    app = QApplication(sys.argv)
    w = MyForm()
    w.show()
    sys.exit(app.exec_())

Creating a database table

Final Result

How to do it ?

class MyForm(QDialog):
    def __init__(self):
        super().__init__()
        self.ui = Ui_Dialog()
        self.ui.setupUi(self)
        self.ui.pushButton_2.clicked.connect(self.createTable)
        self.ui.pushButton.clicked.connect(self.addColumns)
        self.show()
    def addColumns(self):
        try:
            conn = sqlite3.connect(self.ui.databasenm.text()+".db")
            c = conn.cursor()
            sql = "alter table "+self.ui.databasenm.text()+" add column "+self.ui.columnmn.text()+" "+self.ui.comboBox.itemText(self.ui.comboBox.currentIndex())
            print(sql)
            c.execute(sql)
            self.ui.message.setText("column add successfully")
        except:
            self.ui.message.setText("Error in creating table")
        finally:
            conn.close()
        self.ui.columnmn.setText("")
        self.ui.columnmn.setFocus()
    def createTable(self):
        try:
            conn = sqlite3.connect(self.ui.databasenm.text()+".db")
            self.ui.message.setText("Database is connected ")
            c = conn.cursor()
            sql = "create table "+self.ui.tablenm.text()+"("+self.ui.columnmn.text()+" "+self.ui.comboBox.itemText(self.ui.comboBox.currentIndex())+");"
            print(sql)
            c.execute(sql)
            self.ui.message.setText("table is successfully created")
        except sqlite3.Error as e:
            self.ui.message.setText("Error in creating table")
        finally:
            conn.close()
if __name__=="__main__":
    app = QApplication(sys.argv)
    w = MyForm()
    w.show()
    sys.exit(app.exec_())

Inserting rows in the specified database table

Final Result

How to do it?

class MyForm(QDialog):
    def __init__(self):
        super().__init__()
        self.ui = Ui_Dialog()
        self.ui.setupUi(self)
        self.ui.pushButton.clicked.connect(self.InsertRows)
        self.show()
    def InsertRows(self):
        sqlStatement = "insert into "+self.ui.tablenm.text()+" values("+self.ui.email.text()+","+self.ui.password.text()+");"
        print(sqlStatement)
        try:
            conn = sqlite3.connect(self.ui.datanm.text()+".db")
            with conn:
                cur = conn.cursor()
                cur.execute(sqlStatement)
                self.ui.message.setText("Row successfully")
        except sqlite3.Error as e:
            self.ui.message.setText("Error in inserting row")
        finally:
            conn.close()
if __name__ == "__main__":
    app = QApplication(sys.argv)
    w = MyForm()
    w.show()
    sys.exit(app.exec_())

Displaying rows in the specified database table

Final Result

How to do it?

class MyForm(QDialog):
    def __init__(self):
        super().__init__()
        self.ui =Ui_Dialog()
        self.ui.setupUi(self)
        self.ui.pushButton.clicked.connect(self.DisplayRows)
        self.show()
    def DisplayRows(self):
        sql = "select * from "+self.ui.lineEdit_2.text()
        try:
            conn = sqlite3.connect(self.ui.lineEdit.text()+".db")
            cur = conn.cursor()
            cur.execute(sql)
            rows = cur.fetchall()
            rowNo = 0
            for tuple in rows:
                self.ui.message.setText("")
                colNo = 0
                for columns in tuple:
                    oneC = QTableWidgetItem(columns)
                    self.ui.tableWidget.setItem(rowNo, colNo, oneC)
                    colNo += 1
                rowNo += 1
        except sqlite3.Error as e:
            self.ui.tableWidget.clear()
            self.ui.message.setText("Error in accessing table")
        finally:
            conn.close()
if __name__ == "__main__":
    app = QApplication(sys.argv)
    w = MyForm()
    w.show()
    sys.exit(app.exec_())

Navigating through the rows of the specified database table

Final Result

How to do it?

rowNo=1
sql="SELECT * FROM User"
conn = sqlite3.connect("_Database.db")
cur = conn.cursor()
class MyForm(QDialog):
    def __init__(self):
        super().__init__()
        self.ui = Ui_Dialog()
        self.ui.setupUi(self)
        cur.execute(sql)
        self.ui.first.clicked.connect(self.showFirstRow)
        self.ui.previous.clicked.connect(self.showPreviousRow)
        self.ui.next.clicked.connect(self.showNextRow)
        self.ui.last.clicked.connect(self.showLastRow)
        self.show()
    def showFirstRow(self):
        try:
            cur.execute(sql)
            row =cur.fetchone()
            if row:
                self.ui.eamil.setText(row[0])
                self.ui.passwd.setText(row[1])
        except sqlite3.Error as e:
            self.ui.message.setText("Error in accessing table")
    def showPreviousRow(self):
        global  rowNo
        rowNo -= 1
        sql = "select * from user where rowid = "+str(rowNo)
        cur.execute(sql)
        row = cur.fetchone()
        if row:
            self.ui.message.setText("")
            self.ui.eamil.setText(row[0])
            self.ui.passwd.setText(row[1])
        else:
            rowNo += 1
            self.ui.message.setText("This is the first row")
    def showNextRow(self):
        global rowNo
        rowNo += 1
        sql = "select * from user where rowid = "+str(rowNo)
        cur.execute(sql)
        row = cur.fetchone()
        if row:
            self.ui.message.setText("")
            self.ui.eamil.setText(row[0])
            self.ui.passwd.setText(row[1])
        else:
            rowNo -= 1
            self.ui.message.setText("This is the last row")
    def showLastRow(self):
        cur.execute(sql)
        for row in cur.fetchall():
            self.ui.eamil.setText(row[0])
            self.ui.passwd.setText(row[1])
if __name__ == "__main__":
    app = QApplication(sys.argv)
    w = MyForm()
    w.show()
    sys.exit(app.exec_())

Searching a database table for specific information

Final Result

How to do it?

class MyForm(QDialog):
    def __init__(self):
        super().__init__()
        self.ui = Ui_Dialog()
        self.ui.setupUi(self)
        self.ui.pushButton.clicked.connect(self.SearchRows)
        self.show()
    def SearchRows(self):
        sql = "select password from "+self.ui.tablenm.text() + " where address like '"+self.ui.email.text()+"'"
        try:
            conn = sqlite3.connect(self.ui.databasenm.text()+".db")
            cur = conn.cursor()
            print(sql)
            cur.execute(sql)
            row = cur.fetchone()
            if row == None:
                self.ui.message.setText("sorry,nouser found with this email address")
                self.ui.passwd.setText("")
            else:
                self.ui.message.setText("email address found, password of this user is:")
                self.ui.passwd.setText(row[0])
        except sqlite3.Error as e:
            self.ui.message.setText("Error in accessing row")
        finally:
            conn.close()
if __name__=="__main__":
    app = QApplication(sys.argv)
    w = MyForm()
    w.show()
    sys.exit(app.exec_())

Creating a sign in form

Final Result

How to do it?

class MyForm(QDialog):
    def __init__(self):
        super().__init__()
        self.ui = Ui_Dialog()
        self.ui.setupUi(self)
        self.ui.pushButton.clicked.connect(self.SearchRows)
        self.show()
    def SearchRows(self):
        sql = "select * from user where address like '"+self.ui.email.text()+ "' and password like '"+self.ui.password.text()+"'"
        try:
            conn = sqlite3.connect("_Database.db")
            cur = conn.cursor()
            cur.execute(sql)
            row = cur.fetchone()
            if row == None:
                self.ui.message.setText("sorry,Incorrect email or password")
            else:
                self.ui.message.setText("You are welcome")
        except sqlite3.Error as e:
            self.ui.message.setText("error in accessing row")
        finally:
            conn.close()
if __name__=="__main__":
    app = QApplication(sys.argv)
    w = MyForm()
    w.show()
    sys.exit(app.exec_())

Updating a database table

Final Result

How to do it?

class MyForm(QDialog):
    def __init__(self):
        super().__init__()
        self.ui = Ui_Dialog()
        self.ui.setupUi(self)
        self.ui.pushButton.clicked.connect(self.ChangePassword)
        self.show()
    def ChangePassword(self):
        sql = "select * from user where address='"+self.ui.email.text()+"' and password='"+self.ui.oldpasswd.text()+"';"
        try:
            conn = sqlite3.connect("_Database.db")
            cur = conn.cursor()
            print(sql)
            cur.execute(sql)
            row = cur.fetchone()
            if row == None:
                self.ui.message.setText("sorry,incorrect email or password")
            else:
                if self.ui.newpassword.text() == self.ui.renewpasswd.text():
                    sql = "update user set password='"+self.ui.newpassword.text()+"' where address like '"+self.ui.email.text()+"'"
                    with conn:
                        cur.execute(sql)
                        self.ui.message.setText("Password successfully changed")
                else:
                    self.ui.message.setText("the two password don't match")
        except sqlite3.Error as e:
            self.ui.message.setText("Error in accessing row")
        finally:
            conn.close()
if __name__ == "__main__":
    app = QApplication(sys.argv)
    w = MyForm()
    w.show()
    sys.exit(app.exec_())

Deleting a row from a database table

Final Result

How to do it?

class MyForm(QDialog):
    def __init__(self):
        super().__init__()
        self.ui = Ui_Dialog()
        self.ui.setupUi(self)
        self.ui.delbutton.clicked.connect(self.DeleteUser)
        self.ui.byes.clicked.connect(self.ConfirmDelete)
        self.ui.label_4.hide()
        self.ui.byes.hide()
        self.ui.bno.hide()
        self.show()
    def DeleteUser(self):
        sql = "select *from user where address like '"+self.ui.email.text()+"' and password like '"+self.ui.passwd.text()+"'"
        try:
            conn = sqlite3.connect("_Database.db")
            cur = conn.cursor()
            cur.execute(sql)
            row = cur.fetchone()
            if row == None:
                self.ui.label_4.hide()
                self.ui.byes.hide()
                self.ui.bno.hide()
                self.ui.message.setText("sorry,incorrect email address or password")
            else:
                self.ui.label_4.show()
                self.ui.byes.show()
                self.ui.bno.show()
                self.ui.message.setText("")
        except sqlite3.Error as e:
            self.ui.message.setText("Error in accessing user account")
        finally:
            conn.close()
    def ConfirmDelete(self):
        sql = "delete from user where address= '"+self.ui.email.text()+"'"
        try:
            conn = sqlite3.connect("_Database.db")
            cur = conn.cursor()
            with conn:
                print(sql)
                cur.execute(sql)
                self.ui.message.setText("user successfully deleted")
        except sqlite3.Error as e:
            self.ui.message.setText("Error in deleting user account")
        finally:
            conn.close()
if __name__=="__main__":
    app = QApplication(sys.argv)
    w = MyForm()
    w.show()
    sys.exit(app.exec_())