CRUD Operation in Database in Python using SQLite

python-video-tutorials

In this tutorial, we will learn how to perform CRUD Operation in Python. CRUD means Create Read Update and Delete operations. We already learned about all of these operations. But in this tutorial we will create an application which will perform all of these operations.

crud-in-python-using-sqlite

Our Table looks like:

sqlite-databse

database name: “data.db”
table name: “users”
table columns: “id“, “name“, “age“, “gender”,“salary”

Python Program for CRUD Operation:

import sqlite3

// FOR CREATING RECORDS FUNCTION DEFINITION
def create():
    try:
        con = sqlite3.connect("data.db")
        cursor = con.cursor()
        while (True):
            name = input("Enter Name: ")
            age = int(input("Enter Age: "))
            gender = input("Enter Gender: ")
            salary = int(input("Enter Salary: "))
            data = (name, age, gender, salary,)
            query = "INSERT into USERS (name, age, gender, salary) VALUES (?, ?, ?,?)"
            cursor.execute(query, data)
            con.commit()
            ch = input("Do You want to Add More Records(Y/N): ")
            if ch == "N" or ch == "n":
                cursor.close()
                break
            else:
                pass
    except:
        print("Error in Record Creation")

# FOR READING ONE RECORD FUNCTION DEFINITION
def read_one():
    con = sqlite3.connect("data.db")
    cursor = con.cursor()
    ids = int(input("Enter Your ID: "))
    query = "SELECT * from USERS WHERE id = ?"
    result = cursor.execute(query, (ids,))
    if (result):
        for i in result:
            print(f"Name is: {i[1]}")
            print(f"Age is: {i[2]}")
            print(f"Salary is: {i[4]}")
    else:
        print("Roll Number Does not Exist")
        cursor.close()

# FOR READING ALL RECORDS FUNCTION DEFINITION
def read_all():
    con = sqlite3.connect("data.db")
    cursor = con.cursor()
    query = "SELECT * from USERS"
    result = cursor.execute(query)
    if (result):
        print("\n<===Available Records===>")
        for i in result:
            print(f"Name is : {i[1]}")
            print(f"Age is : {i[2]}")
            print(f"Salary is : {i[4]}\n")
    else:
        pass
    
# FOR UPDATING RECORDS FUNCTION DEFINITION
def update():
    con = sqlite3.connect("data.db")
    cursor = con.cursor()
    idd = int(input("Enter ID: "))
    name = input("Enter Name: ")
    age = int(input("Enter Age: "))
    gender = input("Enter Gender: ")
    salary = int(input("Enter Salary: "))
    data = (name, age, gender, salary, idd,)
    query = "UPDATE USERS set name = ?, age = ?, gender = ?, salary = ? WHERE id = ?"
    result = cursor.execute(query, data)
    con.commit()
    cursor.close()
    if (result):
        print("Records Updated")
    else:
        print("Something Error in Updation")

# FOR DELETING RECORDS FUNCTION DEFINITION
def delete():
    con = sqlite3.connect("data.db")
    cursor = con.cursor()
    idd = int(input("Enter ID: "))
    query = "DELETE from USERS where ID = ?"
    result = cursor.execute(query, (idd,))
    con.commit()
    cursor.close()
    if (result):
        print("One record Deleted")
    else:
        print("Something Error in Deletion")

# MAIN BLOCK
try:
    while (True):
        print("1). Create Records: ")
        print("2). Read Records: ")
        print("3). Update Records: ")
        print("4). Delete Records: ")
        print("5). Exit")
        ch = int(input("Enter Your Choice: "))
        if ch == 1:
            create()
        elif ch == 2:
            print("1). Read Single Record")
            print("2). Read All Records")
            choice = int(input("Enter Your Choice: "))
            if choice == 1:
                read_one()
            elif choice == 2:
                read_all()
            else:
                print("Wrong Choice Entered")
        elif ch == 3:
            update()
        elif ch == 4:
            delete()
        elif ch == 5:
            break
        else:
            print("Enter Correct Choice")
except:
    print("Database Error")

#END

crud-operation-in-sqlite-in-python

Explanation: I will explain you this complete program section wise. Because, it will mix up and become complex to understand if all code is discussed/explained together or at once.

1). For Creating Records

We have defined a function def create() at line 6. We have used try & except block to handle database exception. At line 8 & 9 we initialized “con” variable with connect() method with our database name “data.db“. The connect() method used here is pre-defined in sqlite3 library. Next we used continuous while() loop to take multiple entries from user to insert into our database table. After taking all inputs, all data input will be stored in our database table.
After that, it will again confirm user that “Do you want to enter more records” if user enter No as “N” then loop breaks and exits from the create operation.

That’s how our table looks like after performing Create Record Operation:

crud-operation-python-with-sqlite .

2). For Reading Records

We have defined two functions for reading operation: (i). def read_one() for reading one record at a time (ii). def read_all() for reading all records at a time.
(i). def read_one() in this function, at line 32 we take id as input from user and display the available record as per id entered by user. At line 33 we used the query for selecting all the records from the database table with respective id. In next line, we used execute() function to execute our query and passed id as a data, entered by user. Now our variable “result” holds the data read from the database table related to respective id. Next in line 36, we used for loop to iterate over the each information fetched from the database table and hence displayed the record using print() function.
(ii). def read_all() in this function, at line 48 we have just used query for displaying all the available records from database table. Rest all the code is same as we discussed above for def read_one() function.

That’s how we perform Reading Records Operation:

crud-operation-python .

3). For Updating Records

We have defined a function def update() at line 60. We have defined this function to update records form database table with respect to id, taken as input from user. At line 80 & 81 we initialized “con” variable with connect() method with our database name “data.db“. The connect() method used here is pre-defined in sqlite3 library. We have taken the id and other required information from user as input to update the record. After taking all inputs, all data input will be updated in our database table with respect to id entered by user.

That’s how we perform Updating Records Operation:

crud-operation-in-python .

4). For Deleting Records

We have defined a function def delete() at line 79. We have defined this function to delete records form database table with respect to id, taken as input from user. We have taken the id from user as input to delete that record available from our database table. Rest all the process is same as discussed already.

That’s how we perform Delete Records Operation:

crud-operation-in-python-in-sqlite

5). Functions Calling

Here we have called all our functions (we defined above) as per requirement and used some basic core concepts of python programming to run our application as per easy user interface and requirement.