SQLite | CRUD Operations in Python

SQLite CRUD Operations in Python

CRUD stands for Create Read Update Delete. I will show you how to perform CRUD Operations in Python. You need basic Tkinter and SQLite knowledge before you read further. This app is straightforward, when you will open this app, a GUI with 4 green colored buttons will open to perform CRUD(create read update delete) operations. SQLite3 library comes inbuilt with Python.

Folder structure for CRUD operations in Python

There are 5 files, and 1 main file handles GUI and works like the driver of the whole application. The other 4 files are there to perform CRUD operations in Python using the SQLite3 database. I have named those files with the CRUD name, making it easy to know which file does what. Check the image below:

folder structure

So, there are 5 files main.py, create.py, read.py, update.py, and delete.py. The folder(pycache) you are seeing is auto-generated. I will give you the code for each python file separately. You need to create a separate folder with any name and create these 5 files as shown in the image above. You can run this app from main.py file.

Complete code for SQLite CRUD operations in Python

As the code is divided into 5 parts so it’s easy to understand which part does what. For the database part, I have created separate files and performed SQLite CRUD operations with handling errors and exceptions using try and except block. Also, the ID will be auto-generated every time user will create a new row. I have also added a feature of showing the message to the user when he performs a CRUD operation.

main.py

from tkinter import *
import random
import string

top = Tk()
top.title('CopyAssignment Tkinter CRUD SQLite')
top.geometry('430x400')

def CreateOperation():
    from create import InsertData

    top1 = Tk()
    top1.geometry('300x200')
    top1.title('Create operation of CRUD')

    letters = string.ascii_lowercase
    random_id = ''.join(random.choice(letters) for i in range(8))

    name = StringVar(top1)
    ID = StringVar(top1)

    ID.set(random_id)

    Label(top1, text='Name').grid(row=0, column=0, padx=20, pady=20, sticky='w')
    Entry(top1, textvariable=name).grid(row=0, column=1, padx=20)

    Label(top1, text='ID(auto generated)').grid(row=1, padx=20, column=0, sticky='w')
    Entry(top1, textvariable=ID, state='disabled').grid(row=1, column=1)

    Button(top1, text='Create', fg='white', bg='green', font=('Arial', 20), command=lambda: InsertData(name.get(), random_id)).grid(row=2, column=0, columnspan=2, pady=20)

    top1.mainloop()


Button(top, text='Create', bg='green', fg='white', width=12, font=('Arial', 18), command=lambda: CreateOperation()).grid(row=0, column=0, padx=25, pady=30)

def ReadOperation():
    from read import Read

    top2 = Tk()
    top2.geometry('250x200')
    top2.title('Read operation of CRUD')

    Label(top2, text='Name').grid(row=0, column=0, padx=30, sticky='w')
    Label(top2, text='   |      ').grid(row=0, column=1)
    Label(top2, text='ID').grid(row=0, padx=20, column=2, sticky='w')

    Label(top2, text='--------'*5).grid(row=1, padx=20,  column=0, columnspan=5)

    data = Read()
    for i in range(len(data)):
        Label(top2, text=data[i][0]).grid(row=2+i, column=0, sticky='w', padx=20)
        Label(top2, text='   |      ').grid(row=2+i, column=1)
        Label(top2, text=data[i][1]).grid(row=2+i, column=2, sticky='w')

    top2.mainloop()


Button(top, text='Read', bg='green', fg='white', width=12, font=('Arial', 18), command=lambda: ReadOperation()).grid(row=0, column=1)


def UpdateOperation():
    from read import Read
    from update import Update

    top3 = Tk()
    top3.geometry('250x200')
    top3.title('Update operation of CRUD')
    
    data = Read()
    id_list = []
    for i in data:
        id_list.append(i[1])
        
    e = StringVar(top3)
    e.set('Select ID')
    OptionMenu(top3, e, *id_list).pack(pady=15)

    name = StringVar(top3)
    name.set('New name')
    Entry(top3, textvariable=name).pack(pady=15)

    def MyUpdate():
        Update(name.get(), e.get())
        top3.destroy()
    Button(top3, text='Update', command=lambda: MyUpdate(), bg='green', fg='white', font=('Arial', 18)).pack(pady=15)

    top3.mainloop()


Button(top, text='Update', bg='green', fg='white', width=12, font=('Arial', 18), command=lambda: UpdateOperation()).grid(row=1, pady=20, column=0)


def DeleteOperation():
    from read import Read
    from delete import Delete

    top4 = Tk()
    top4.geometry('250x200')
    top4.title('Delete operation of CRUD')
    
    data = Read()
    id_list = []
    for i in data:
        id_list.append(i[1])
        
    e = StringVar(top4)
    e.set('Select ID')
    OptionMenu(top4, e, *id_list).pack()

    def MyDelete():
        Delete(e.get())
        top4.destroy()
    Button(top4, text='Delete', bg='green', fg='white', width=12, font=('Arial', 18), command=lambda: MyDelete()).pack(pady=20)

    top4.mainloop()


Button(top, text='Delete', bg='green', fg='white', width=12, font=('Arial', 18), command=lambda: DeleteOperation()).grid(row=1, column=1)

top.mainloop()

create.py

import sqlite3
from tkinter import messagebox

def Create():
    conn = sqlite3.connect('mydatabase.db')
    cursor = conn.cursor()
    
    # statement to create a table in sqlite3 using python
    cursor.execute("CREATE TABLE IF NOT EXISTS mytable (myname TEXT, id TEXT PRIMARY KEY)")

    conn.close()

def InsertData(name, id):
    Create()
    try:
        conn = sqlite3.connect("mydatabase.db")
        cursor = conn.cursor()
        
        # statement to insert data into table in sqlite3 using python
        cursor.execute("INSERT INTO mytable (myname, id) VALUES (?, ?)", (name, id))
        
        conn.commit()
        conn.close()
        messagebox.showinfo('Success', 'Data inserted successfully')
    except sqlite3.Error as e:
        messagebox.showinfo('Error', e)
    finally:
        conn.close()

read.py

import sqlite3

def Read():
    conn = sqlite3.connect('mydatabase.db')
    cursor = conn.cursor()

    # statement to read or fetch all rows from a table in sqlite3 using python
    cursor.execute('SELECT * FROM mytable')
    all_rows = cursor.fetchall()
    
    conn.close()

    return all_rows

update.py

from tkinter import messagebox
import sqlite3

def Update(name, id):
    try:
        conn = sqlite3.connect('mydatabase.db')
        c = conn.cursor()

        # statement to update a row in sqlite3 using python
        c.execute("UPDATE mytable SET myname=?  WHERE id=?", (name, id))
        
        conn.commit()
        conn.close()
        messagebox.showinfo('Success', 'Data updated successfully')
    except sqlite3.Error as e:
        messagebox.showinfo('Error', e)
    finally:
        conn.close()

delete.py

import sqlite3
from tkinter import messagebox

def Delete(id):
    try:
        conn = sqlite3.connect("mydatabase.db")
        cursor = conn.cursor()
        
        # statement to delete a row in sqlite3 using python
        cursor.execute("DELETE FROM mytable WHERE id = ?", (id,))
        
        conn.commit()
        conn.close()
        messagebox.showinfo('Success', 'Data deleted successfully')
    except sqlite3.Error as e:
        messagebox.showinfo('Error', e)
    finally:
        conn.close()

Output for SQLite CRUD operations in Python:

Image output:

main screen of SQLite CRUD operations in Python

Create operation

create operation of crud in python

Update operation

update operation of crud in python

Read operation

read operation of crud in python

Delete operation

delete operation of crud in python

Video output:

Thank you for visiting our website.


Also Read:

Share:

Author: Yogesh Kumar