Python Desktop App CRUD (Create Read Update and Delete ) sqlite3 using python eel

Python Desktop App CRUD (Create Read Update and Delete ) sqlite3 using python eel

Eel is a little Python library for making simple Electron-like offline HTML/JS GUI apps, with full access to Python capabilities and libraries.

https://github.com/ChrisKnott/Eel

Install

pip install eel

C:\python_dev\crud>pip install eel

Install PyAutoGUI

https://pypi.org/project/PyAutoGUI/

pip install PyAutoGUI

C:\python_dev\crud>pip install PyAutoGUI

Create Database sqlite3 using python

createdb.py

//createdb.py
import sqlite3
from sqlite3 import Error 
   
def create_connection(db_file):
    """ create a database connection to a SQLite database """
    conn = None
    try:
        conn = sqlite3.connect(db_file)
        print(sqlite3.version)
    except Error as e:
        print(e)
    finally:
        if conn:
            conn.close()
   
   
if __name__ == '__main__':
    create_connection(r"C:\python_dev\crud\views\database\storage.db")

run
C:\python_dev\crud>python createdb.py

Create Database Table

CREATE TABLE tblemployee (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR (150),
position VARCHAR (150),
office VARCHAR (150)

);
main.py

#main.py
import eel
from views.models.employee import showallrecords, save_newemployee, show_selectedEmployee, update_newemployee, show_deleteEmployee
import pyautogui #https://pypi.org/project/PyAutoGUI/
  
eel.init('views')
   
@eel.expose
def fetchalldata():
    select_reg = showallrecords()
    eel.action_out(select_reg)
  
@eel.expose 
def btn_save(name, position, office):
    msg = save_newemployee(name, position, office)
    eel.save_return(str(msg))
 
@eel.expose
def get_employee(id):
    select_employee = show_selectedEmployee(id)
    eel.action_edit(select_employee)
     
@eel.expose 
def btn_update(name, position, office, id):
    msg = update_newemployee(name, position, office, id)
    eel.update_return(str(msg))    
 
@eel.expose
def get_delete_employee(id):
    select_del_employee = show_deleteEmployee(id)
    eel.delete_return(select_del_employee)
     
eel.start(
    'templates/index.html',
    size=pyautogui.size()
)

employee.py

#employee.py
import sqlite3
  
def showallrecords():
    try:
        connect = sqlite3.connect("views/database/storage.db")
        cursor = connect.cursor()
        cursor.execute("SELECT * FROM tblemployee")
        registers = []
        for item in cursor.fetchall():
            #test = item[1]
            #print(test)
            registers.append(item)
        return registers
    except Exception as error:
        print(error)
        msg = "Error"
        return msg
         
def save_newemployee(name, position, office):
    try:
        connect = sqlite3.connect("views/database/storage.db")
        cursor = connect.cursor()
 
        if name != "" and position != "" and office != "":
            cursor.execute("INSERT INTO tblemployee(name, position, office) VALUES(?,?,?)",(name, position, office))
            connect.commit()
            connect.close()
            msg = "success"
            return msg
        else:
            msg = "failure"
            return msg
    except Exception as Error:
        print(Error)
        msg = "failure"
        return msg
  
def show_selectedEmployee(id):
    try:
        connect = sqlite3.connect("views/database/storage.db")
        cursor = connect.cursor()
        cursor.execute("SELECT * FROM tblemployee WHERE id =?", (id,))
        editemployees = []
        for item in cursor.fetchone():
            editemployees.append(item)
        return editemployees
 
    except Exception as error:
        print(error)
        msg = "Error"
        return msg
 
def update_newemployee(name, position, office, id):
    try:
        connect = sqlite3.connect("views/database/storage.db")
        cursor = connect.cursor()
 
        if name != "" and position != "" and office != "":
            cursor.execute("UPDATE tblemployee SET name =?, position =?, office =? WHERE id =?",
                             (name, position, office, id,))
            connect.commit()
            connect.close()
            msg = "success"
            return msg
        else:
            msg = "failure"
            return msg
    except Exception as Error:
        print(Error)
        msg = "failure"
        return msg
         
def show_deleteEmployee(id):
    try:
        connect = sqlite3.connect("views/database/storage.db")
        cursor = connect.cursor()
        cursor.execute("DELETE FROM tblemployee WHERE id =?", (id,))
        connect.commit()
        connect.close()
        msg = "success"
        return msg
             
    except Exception as error:
        print(error)
        msg = "Error"
        return msg

Make your Front-end HTML CSS and Javascript
crud\views\templates\index.html

//crud\views\templates\index.html
<!DOCTYPE html>
<html lang="pt-br">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <script type="text/javascript" src="/eel.js"></script>
    <link rel="stylesheet" href="../css/index.css">
    <link rel="icon" href="../img/myglogo.png">
    <title>Python Desktop App CRUD (Create Read Update and Delete ) sqlite3 using python eel</title>
</head>
<body>
<div class="container">
<p style="text-align:center;"><h2>Python Desktop App CRUD (Create Read Update and Delete ) sqlite3 using python eel</h2></p>
<p style="text-align:right;"><button type="button" class="btn" id="btn_addnew">Add New</button></p>
<table id="employeedatatable" class="styled-table">
    <tr>
        <th>ID</th>
        <th>Name</th>
        <th>Position</th>
        <th>Office</th>
        <th>Action</th>
    </tr>
</table> 
 
<div class="modalContainer" id="Addnewmodal">
  <div class="modal-content">
    <span id="close" class="close">×</span>
    <p style="text-align:center;"><h2>Add New Employee</h2></p>
    <form class="form-detail" action="#" method="post" id="myform">
        <div class="form-row">
            <label>Full Name:</label> 
            <input type="text" name="txtfullname" id="txtfullname" class="input-text" required placeholder="ex: Cairocoders Ednalan">
        </div>
        <div class="form-row">
            <label>Position:</label>
            <input type="text" name="txtposition" id="txtposition" class="input-text" required>
        </div>
        <div class="form-row">
            <label>Office:</label>
            <input type="text" name="txtoffice" id="txtoffice" class="input-text" required>
        </div>
        <div class="form-row-last">
            <input type="submit" name="register" class="button" value="Add New Employee" onclick="save_register_js();">
        </div>
        <div class="box"><label class="label_input" id="return_register" style="width: 90%;color:green;font-weight:bold;"></label></div>
    </form>
  </div>
</div>
 
<div class="modalContainer" id="Edit_modal">
  <div class="modal-content">
    <span id="close" class="closeedit">×</span>
    <p style="text-align:center;"><h2>Edit Employee</h2></p>
    <form class="form-detail" action="#" method="post" id="myformedit"><input type="hidden" name="id" id="id">
        <div class="form-row">
            <label>Full Name:</label> 
            <input type="text" name="edit_name" id="edit_name" class="input-text" required>
        </div>
        <div class="form-row">
            <label>Position:</label>
            <input type="text" name="edit_position" id="edit_position" class="input-text" required>
        </div>
        <div class="form-row">
            <label>Office:</label>
            <input type="text" name="edit_office" id="edit_office" class="input-text" required>
        </div>
        <div class="form-row-last">
            <input type="submit" name="register" class="button" value="Update" onclick="save_edit();">
        </div>
        <div class="box"><label class="label_input" id="return_update" style="width: 90%;color:green;font-weight:bold;"></label></div>
    </form>
  </div>
</div>
 
<div class="modalContainer" id="Delete_modal">
  <div class="modal-content">
    <span id="close" class="closedelete">×</span>
    <p style="text-align:center;"><h2>Delete Employee</h2></p>
    <p style="text-align:center;"><h3>Are you sure you want to delete this data?</h3></p>
    <div class="form-detail">
    <input type="hidden" name="idvalue" id="idvalue"> 
    <input type="submit" name="btndelete" class="button" value="Delete" onclick="btn_submitdelete($('#idvalue').val());">
    <div class="box"><label class="label_input" id="return_delete" style="width: 90%;color:green;font-weight:bold;"></label></div>
    </div>
  </div>
</div>
 
</div>
<script type="text/javascript" src="../js/jquery.min.js"></script>
<script type="text/javascript" src="../js/jquery.validate.min.js"></script>
<script type="text/javascript" src="../js/main.js"></script>
</body>
</html>

crud\views\js\main.js

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

#crud\views\js\main.js
$(document).ready(function(){
    eel.fetchalldata()
     
  $("#btn_addnew").on("click", function() {
    $("#Addnewmodal").show();
  });
  $(".close").on("click", function() {
    $("#Addnewmodal").hide();
  });
   
  $(".closeedit").on("click", function() {
    $("#Edit_modal").hide();
  });  
  $(".closedelete").on("click", function() {
    $("#Delete_modal").hide();
  });
})
   
function link(target) {
    window.location.href=target;
}
 
eel.expose(action_out)
function action_out(registers){ 
    //alert("Show Table");
    registers.forEach(showdata)
}
 
eel.expose(action_edit)
function action_edit(editemployees){ 
   //alert(editemployees);
   editemployees.forEach(get_array_values)
}
 
function get_array_values(item, index){
    //alert(item);
    //alert(index);
    if (index == 0) {
        document.getElementById("id").value = item;
    } else if (index == 1) {
        document.getElementById("edit_name").value = item;
    } else if (index == 2) {
        document.getElementById("edit_position").value = item;
    } else if (index == 3) {
        document.getElementById("edit_office").value = item;
    }
    else {}
 
}
// SAVE 
async function save_edit(){
    $( "#myformedit" ).validate({
        messages: {
            edit_name: {
                required: "Please provide Name"
            },
            edit_position: {
                required: "Please provide Position"
            },
            edit_office: {
                required: "Please provide Office"
            },
        },
        submitHandler: function(form) {
            eel.btn_update($('#edit_name').val(),$('#edit_position').val(),$('#edit_office').val(),$('#id').val())
            //alert("Success");
        }
    });
}
         
function showdata(item, index){
    var get_table = document.getElementById("employeedatatable");
    var tr = document.createElement("tr");
    var td = document.createElement("td");
    var td2 = document.createElement("td");
    var td3 = document.createElement("td");
    var td4 = document.createElement("td");
    var td5 = document.createElement("td");
     
    var id = item[0]
    td.innerText = item[0]
    td2.innerText = item[1]
    td3.innerText = item[2]
    td4.innerText = item[3]
         
    td5.innerHTML = '<button type="button" class="btn" onclick="btn_edit('+ id +')">Edit</button> | <button type="button" class="btndelete" onclick="buttondelete(('+ id +'))">Delete</button>'
         
    get_table.appendChild(tr)
    tr.appendChild(td)
    tr.appendChild(td2)
    tr.appendChild(td3)
    tr.appendChild(td4)
    tr.appendChild(td5)
}
     
// NEW Employee
async function save_register_js(){
    $( "#myform" ).validate({
            messages: {
                txtfullname: {
                    required: "Please provide Name"
                },
                txtposition: {
                    required: "Please provide Position"
                },
                txtoffice: {
                    required: "Please provide Office"
                },
            },
            submitHandler: function(form) {
                eel.btn_save($('#txtfullname').val(),$('#txtposition').val(),$('#txtoffice').val())
            }
    });
};
     
eel.expose(save_return); 
function save_return(status){
    if (status == "success"){
        $('#return_register').text('New Employee completed successfully!');
        $('#txtfullname').val('');
        $('#txtposition').val('');
        $('#txtoffice').val('');
    }
    if (status == "failure"){
        $('#return_register').text('Error when registering, make sure you have no blank fields.');
    }
};
     
eel.expose(edit_return); 
function edit_return(status){
    if (status == "success"){
        $('#return_register').text('Employee Updated successfully!');
    }
    if (status == "failure"){
        $('#return_register').text('Error when updating, make sure you have no blank fields.');
    }
};
   
function buttondelete(id)
{
    document.getElementById("idvalue").value = id;
    $("#Delete_modal").show();
}
 
async function btn_edit(id){ 
    eel.get_employee(id)
    $("#Edit_modal").show();
}
 
async function btn_submitdelete(id){ 
    //alert(id);
    eel.get_delete_employee(id)
}
 
eel.expose(update_return); 
function update_return(status){
    if (status == "success"){
        $('#return_update').text('Employee Updated successfully!');
        $('#txtfullname').val('');
        $('#txtposition').val('');
        $('#txtoffice').val('');
    }
    if (status == "failure"){
        $('#return_update').text('Error when updating record, make sure you have no blank fields.');
    }
};
 
eel.expose(delete_return)
function delete_return(delemployees){ 
   alert(delemployees);
    if (status == "success"){
        location.href = "index.html";
    }
    if (status == "failure"){
        $('#return_delete').text('Error deleting record');
    }
}

crud\views\css\index.css

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

//crud\views\css\index.css
body {
    color: #162D5D;
    font-family: 'Roboto', sans-serif;
}
.container {margin:30px;}
.styled-table {
    border-collapse: collapse;width:100%;
    margin: 25px 0;
    font-size: 0.9em;
    font-family: sans-serif;
    min-width: 400px;
    box-shadow: 0 0 20px rgba(0, 0, 0, 0.15);
}
.styled-table thead tr {
    background-color: #009879;
    color: #ffffff;
    text-align: left;
}
.styled-table th,
.styled-table td {
    padding: 12px 15px;
}
.styled-table tr {
    border-bottom: 1px solid #dddddd;
}
.styled-table tr:nth-of-type(even) {
    background-color: #f3f3f3;
}
.styled-table tr:last-of-type {
    border-bottom: 2px solid #009879;
}
.btn {
    background-color: #04AA6D!important;
    border-radius: 5px;color:#ffffff;
    font-size: 17px;
    font-family: 'Source Sans Pro', sans-serif;
    padding: 6px 18px;
}
.btndelete {
    background-color: #cf2e2e!important;
    border-radius: 5px;color:#ffffff;
    font-size: 17px;
    font-family: 'Source Sans Pro', sans-serif;
    padding: 6px 18px;
}
.form-detail .form-row label.error {
    color: red;
}
.form-detail .input-text {
    margin-bottom: 27px;
}
.form-detail input {
    width: 91%;
    padding: 14.5px 15px;
    border: 1px solid #e5e5e5;
    border-radius: 5px;
    -o-border-radius: 5px;
    -ms-border-radius: 5px;
    -moz-border-radius: 5px;
    -webkit-border-radius: 5px;
    appearance: unset;
    -moz-appearance: unset;
    -webkit-appearance: unset;
    -o-appearance: unset;
    -ms-appearance: unset;
    outline: none;
    -moz-outline: none;
    -webkit-outline: none;
    -o-outline: none;
    -ms-outline: none;
    font-family: 'Roboto', sans-serif;
    font-size: 16px;
    color: #333;
}
.form-detail .form-row input:focus {
    border: 1px solid #53c83c;
}
.form-detail .button {
    background: #3b63ca;
    border-radius: 6px;
    -o-border-radius: 6px;
    -ms-border-radius: 6px;
    -moz-border-radius: 6px;
    -webkit-border-radius: 6px;
    width: 160px;
    border: none;
    margin: 6px 0 50px 0px;
    cursor: pointer;
    font-family: 'Roboto', sans-serif;
    color: #fff;
    font-weight: 500;
    font-size: 16px;
}
.form-detail .button:hover {
    background: #3356b0;
}
.modalContainer {
  display: none;
  position: fixed;
  z-index: 1;
  left: 0;
  top: 0;
  width: 100%;
  height: 100%;
  background-color: rgb(0, 0, 0);
  background-color: rgba(0, 0, 0, 0.4);
}
.modal-content {
  background-color: #fefefe;
  margin: 15% auto;
  padding: 20px;
  border: 1px solid #888;
  width: 40%;
}
 
#close {
  color: #aaa;
  float: right;
  font-size: 28px;
  font-weight: bold;
  cursor: pointer;
}

Jquery
https://jquery.com/download/
CDN : jsDelivr CDN
https://www.jsdelivr.com/package/npm/jquery
https://cdn.jsdelivr.net/npm/jquery@3.6.0/dist/jquery.min.js

jQuery Validation
http://jqueryvalidation.org/

Run : C:\python_dev\crud>python main.py

Tutorial101

article

Saturday, October 15, 2022

Python Desktop App CRUD (Create Read Update and Delete ) sqlite3 using python eel

Related Post

Search This Blog

HOME

Category

Popular Posts