Python Crud Operations With SQLite - Practice Set

CRUD stands for create, read, update and delete.

• CREATE procedures: Performs the INSERT statement to create a new record.

• READ procedures: Reads the table records based on the primary keynoted within the input parameter.

• UPDATE procedures: Executes an UPDATE statement on the table based on the specified primary key for a record within the WHERE clause of the statement.

• DELETE procedures: Deletes a specified row in the WHERE clause.

First Need to install SQLite For database and then import it:

Import SQLite

import sqlite3

Create Table:

def create_table():
    conn=sqlite3.connect("lite.db")
    cur=conn.cursor()
    cur.execute("CREATE TABLE IF NOT EXISTS store (item TEXT, quantity INTEGER, price REAL)")
    conn.commit()
    conn.close()

Insert Data Into table

def insert_data(item, quantity, price):
    conn=sqlite3.connect("lite.db")
    cur=conn.cursor()
    cur.execute("INSERT INTO store VALUES (?,?,?)",(item, quantity, price))
    conn.commit()
    conn.close()

View Table Data

def view_data():
    conn=sqlite3.connect("lite.db")
    cur=conn.cursor()
    cur.execute("SELECT * FROM store")
    rows=cur.fetchall()
    conn.close()
    return rows

Update Record

def update():
    conn=sqlite3.connect("lite.db")
    cur=conn.cursor()
    values = ('nine','2')
    cur.execute("update store set item=? where quantity=?", values)
    conn.commit()
    cur.execute("SELECT * FROM store")
    rows=cur.fetchall()
    conn.close()
    return rows

Delete Record

def delete():
    conn=sqlite3.connect("lite.db")
    cur=conn.cursor()
    values = ('one', )
    cur.execute("delete from store where item=?", values)
    conn.commit()
    cur.execute("SELECT * FROM store")
    rows=cur.fetchall()
    conn.close()
    return rows

Call Create_table() Function

create_table()

Create Menu

user_choice=str(input("What would you like to do? \n Insert data (I) \n View data (V) \n Update data(U)\n Delete the data(D)\n Enter your choice, I or V or U or D: "))
user_choice=user_choice.lower()
print(user_choice)

while user_choice not in ['i','v','u','d']:
    print ("Your choice is invalid. Please try again.")
    user_choice=input("Choose I or V or U or D:  ")
    user_choice=user_choice.lower()
    
if user_choice == 'i':
    user_input = input("Enter the item, quantity and price you want to update: ")
    input_list = user_input.split(',')
    item=str(input_list[0])
    quantity=int(input_list[1])
    price=float(input_list[2])
    insert_data(item,quantity,price)
elif user_choice == 'v':
    print(view_data())
elif user_choice == 'v':
    print(view_data())
elif user_choice == 'u':
    print(update())
elif user_choice == 'd':
    print(delete())

Output After Run The Code