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
#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
//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
VIDEO
